![]() In query window #1, select data from the City table for the USA records. This willġ9980000, -As Google just said and that's cool! Transaction, but not committing or rolling back the transaction. In query window #1 run the following code. The same T-SQL command with different data to show how the deadlock will be reproduced. In the scope of each transaction I will run Wanted to show that our system had many useful indexes and even with useful indexesĪs usual, to demonstrate a deadlock we need two connections to be opened. You might have noticed that I created an index on the “CountryID”Ĭolumn and included the “CityName” column. Indeed, I recommend them).- script 1 - Creation of County and City tables (I'd like to reiterate - this is Brent Ozar's demo not mine! I'm just passing it on. 5, 4, 3, 2, 1 Boom deadlock - because the second window already had a lock on the table and therefore the SELECT in the first window couldn't run (and the second window couldn't run because the first had a lock on a table it needed). In the first window, go back and run the SELECT * FROM Righty and COMMIT TRAN. It sits there waiting for your first window, and will wait forever. In the second window, run the whole transaction. Now, in the first window, run the first two commands ( BEGIN TRAN AND UPDATE LEFTY). In the second window put in this code (also don't run it yet). In the first put this code (don't run it yet) BEGIN TRAN To start with, create two tables CREATE TABLE Lefty (ID int PRIMARY KEY) ![]() This example is ripped almost 100% from Brent Ozar's video on deadlocks, but changed one command to a SELECT. It is possible for a SELECT to cause a deadlock if someone else is using the table. If an answer/comment comes up with specific queries/steps to do to trace the problem, I'll be happy to recreate it. So, even though the query was bad, I am led to believe that there were strange (dead)locks around. Could it be that there was correct parallelism appended, but the operations were too many? Still, I also witnessed a huge dm_exec_requests.wait_time. However, remember the index that did not exist which would cause abysmal performance. I did some searching around, but option(maxdop 1) did not solve the problem. How much is "the past"? I noticed in sys.dm_os_waiting_tasks the session being blocked by itself multiple times with wait type CXPACKET. I found Jeroen Mostert's comment interesting. I am in no position to make recommendations, much less update the server though. However, in hopes to keep this useful and hopefully come up with an answer, here is some more things I investigated, some facts, and thoughts on comments:įirst, the sql server version is 2008. When it was created, the performance was OK. I abandoned further debugging because I noticed that an index I thought existed didn't. If I am not mistaken on my 2 points, is it ever possible to have a deadlock? Wouldn't the deadlock require all of the involved users of a resource to be doing write operations on them, which would create a cycle in the resource request graph? I understand a timeout error in a select, but cannot understand a deadlock.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |