SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Indexes to Reduce Blocking in Concurrent Transactions


Using Indexes to Reduce Blocking in Concurrent Transactions

Author
Message
leigh.shayler
leigh.shayler
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 47
Thanks, Provides a good (and brief) overview of resource locking and lock investigation as well.
guruprasat85
guruprasat85
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 93
I actually tried to run the query without adding an index and it still worked for me. There were no waiting locks. Any reason why?
william-812661
william-812661
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 48
When there is no index, why database engine need to place RID lock on both row 1 & 2?
leigh.shayler
leigh.shayler
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 47
Without an index, the database will have to perform a table scan meaning it will check each row in the table so it will place a lock on each of the rows to ensure none of them change while it does the processing.

william (7/8/2009)
When there is no index, why database engine need to place RID lock on both row 1 & 2?

guruprasat85
guruprasat85
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 93
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
guruprasat85
guruprasat85
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 93
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
guruprasat85
guruprasat85
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 93
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
Misha_SQL
Misha_SQL
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1678 Visits: 1010
Loved it. Familiar topic, but very well explained!



Ken Lee-263418
Ken Lee-263418
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 59
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.
Ken Lee-263418
Ken Lee-263418
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 59
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search