-3

I know this is a theorical question since i don't have code to or any logs to show, but i hope that someone can give me some idea on this question.

The problem in question was the following:

On my work, on a deploy to a production system a migration took so much time running that it failed due to a timeout and this happened on 2 tries. What's worse is that the system started to be really slow and throwing 500 error to users on the application while the running of the migration was being executed.

This happened on a production environment with load while on a testing environment the same migration runned without problems.

To give more details we are talking about a Sql server Database that can have dozens of requests on a small amount of time. And on the .NET framework API application, i created a new migration using entity framework 6 for a new table, which had foreign key constraints to other tables. Some of this related tables have hundreds of thousands of records.

For the deploy of both environments (test and production) i'm using the azure pipelines and on a specific task the migrations will run (if there is anything new for the database)

I already searched about this, but couldnt find much information, is this timeout happening because a database is with heavy load? somehow some tables can be blocked by other queries taking long? is this taking so long that it reaches the limit time allowed by migrations system? How to overcome this kind of problem? Do it off-hours only?

I appreciate the answers.

Improve this question

1 Answer 1

Reset to default
4

You will need to look at the sql generated for the migration to determine why it is slow.

Possible reasons include:

  • Deadlocks with other queries
  • Rebuilding indexes
  • Moving lots of data around
  • Running out of memory due to large transaction

This kind of thing wont show up on a small database, but can kill your production system.

Once you have determined the problem you can work around it by either hand crafting a migration script that avoids the issue, breaking the migration up into multiple steps or using a green blue deployment methodology, where you can turn off the server while doing the update without taking your site down

Improve this answer
1
  • You're right, on this case it blocked when creating a new table that has indexes and foreign key constraint from other tables, some of these table are on heavy usage from production system. I guess that the deadlock with other queries can make sense i think i need to investigate more about that in SQL server Commented Apr 29, 2021 at 21:40

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.