Geek asks and answers

TransactionScope Priority (get rid of deadlock situation)

I have a process A that starts every 5 minutes and needs to write something in the "EventLog" table. This works all day, but at night there is another process B that should remove a lot of old data from this table. The table contains millions of rows (including blobs) and many related tables (cascading), so process B runs up to ~ 45 minutes. While process B is running, I get many lock warnings for process A, and I want to get rid of them.

The easy option would be "Do not start process A when starting process B," but there should be a better approach. I use EntityFramework 6 and TransactionScope in both processes. I did not learn how to set priority or something similar in my processes. Is it possible?

EDIT: I forgot to say that I already use one delete transaction per record, and not one transaction for all records. Inside the loop, I create a new DBContext and TransactionScope, so each record has its own transaction. My problem is that deleting a record still takes some time due to related BLOB data and data in other related tables (say, about 5 seconds per row). I still get deadlocks when the delete process (B) crosses the insert process (A).

+3
source share
2 answers

Transactions have no priority. The dead are selected by the database, most often used things like "the work needed to roll back." One way to avoid locking is to provide a lock, not a dead end, by accessing the tables in the same order and by locking at the final level (for example, when reading data UPDLOCK, to avoid reading two requests, get read locks, and then try to switch to write locks ) Ultimately, however, this is a complex area - and something that takes 45M to complete (please say this is not a single transaction!) Will always cause problems.

+5
source

B, , , 1 . , , .

+1

More articles:


All Articles