July 8, 2009 at 9:09 pm
Yes It actually worked for me now. My table has no index. First I ran the below statement in one query window
BEGIN TRAN
update dbo.Profit_Act1 with(rowlock)
--rowlock is the default lock level
set France = 24000
where ID=2
Then I opened another query window and ran the below statement
BEGIN TRAN
update dbo.Profit_Act1 with(rowlock)
--rowlock is the default lock level
set France = 22000
where ID=1
The first one ran but the second didnt since I had not commited the tran on my query window
Then i commited them and added index to the table. Then I recreated the same scenario but at this time the query in my second window ran without any problems
Also the execution plan clearly showed the table scan missing after adding the index
July 8, 2009 at 9:09 pm
Yes It actually worked for me now. My table has no index. First I ran the below statement in one query window
BEGIN TRAN
update dbo.Profit_Act1 with(rowlock)
--rowlock is the default lock level
set France = 24000
where ID=2
Then I opened another query window and ran the below statement
BEGIN TRAN
update dbo.Profit_Act1 with(rowlock)
--rowlock is the default lock level
set France = 22000
where ID=1
The first one ran but the second didnt since I had not commited the tran on my query window
Then i commited them and added index to the table. Then I recreated the same scenario but at this time the query in my second window ran without any problems
Also the execution plan clearly showed the table scan missing after adding the index
July 11, 2009 at 6:41 pm
Loved it. Familiar topic, but very well explained!
June 4, 2011 at 4:07 pm
In this example you imply both queries are locked by the other in the first set of queries. The first query has achieved all the locks needed to complete the transaction. The command to complete the transaction doesn't exist so the locks will remain in place while that spid&trans remain alive. (Even though the batch query did complete and it is "idle".)
You should have made the point that this first transaction isn't blocked, but the second query is blocked. You should have indicated that by including a commit statement in the seond query. It wouldn't have completed because the locks haven't completed. You should have mentioned the second query doesn't stop while the first one did stop. It isn't finished with the update because it is blocked.
On the other hand, not putting in the commit statement really would have shown that the update, not the commit is being blocked.
June 4, 2011 at 4:42 pm
Your query does make sense, but because you are new, you didn't realize this is not a deadlock situation, it is a blocking situation. In order for a deadlock to occur two different resourses have to contend for resources that the other has achieved. In this example the first query will complete and stop, the second query will never complete until the resource is released which never happens.
You can get a deadlock by creating a second table with the same resouces and data loaded into it. In the first query use the transaction and update query originally used, run it. In the second query begin the transaction and then update BOTH tables, but first update the SECOND table!
You'll get a time indicater and the query won't stop.
Go back to the second window, clear out your commands and just update the second table and run it.
Both queries will stop almost simultaniously, one indicating it is finished without error, the second will finish with a deadlock message. I have some idea the first query will be the deadlock victim, but that isn't a sure thing. (The second query has spent some time being blocked so it's cost is climbing.)
If you run the lock query now, you'll only see the (successful) locks on both tables in the surviving spid still exist.
June 4, 2011 at 4:46 pm
Great, I replied to a newbie and the engine goes to the end of the queue.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply