Hi, I have a table with about 10 millions of records, this table is update and inserted very often during the day (approx. 200 per second) , in the night the activity is a lot less, so in the first seconds of a day (00:00:01) a batch process update some columns (used like counters) of this table setting his value to 0. Yesterday, the first time it occurs, I got a deadlock when other process try to delete multiple (about 10 or 20) rows of the same table. I think that maybe the situation was: Process A (PA) (massive update) Process B (PB) (multiple delete) PA Block record 1, update PA Block record 2, update PA Block record 3, update PB Block record 4, delete PB Block record 5, delete PA Block record 4, waiting PB Block record 3, waiting The other situation could be that update process while blocking rows scale to block page and the try to scale to lock table while the delete process as some locked rows. Any ideas how to prevent this situation? Thanks! |
