February 19, 2016 at 2:52 pm
Today I was tracing some process with Profiler GUI. When I stopped it, I decided to load trace results to a SQL Server table from GUI. I thought that it's already loaded, so I ran select * from my "tracing" table in SSMS. It returned with error that this process was chosen as a deadlock victim. I tried again. Same result. I waited little bit and tried again, now it was success.
I also run another tracing that collects all deadlock metrics. It showed two deadlocks, in both cases my "select * from ..." was a victim, and "offender" was a process that inserts data to same table.
My question is why SQL Server did not simply blocked my select? Why it decided to deadlock it instead?
Thanks
February 20, 2016 at 12:12 am
"Select * from Tracing table" cause Table scan which in turn cause lock escalation leading to deadlock. Please see MS article on this with much more details and examples.
https://support.microsoft.com/en-us/kb/323630
SELECT use Shared(S) lock. Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control. No other transactions can modify the data while shared (S) locks exist on the resource.
https://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx
SELECT is chosen as deadlock victim because it's the least expensive to rollback.
-Regards
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply