<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Nilav Baran Ghosh
»
Using Indexes to Reduce Blocking in...
21 posts, Page 2 of 3
««
1
2
3
»»
Using Indexes to Reduce Blocking in Concurrent Transactions
Rate Topic
Display Mode
Topic Options
Author
Message
leigh.shayler
leigh.shayler
Posted Tuesday, July 07, 2009 7:39 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, September 22, 2010 12:39 AM
Points: 10,
Visits: 47
Thanks, Provides a good (and brief) overview of resource locking and lock investigation as well.
Post #748970
guruprasat85
guruprasat85
Posted Tuesday, July 07, 2009 9:18 PM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, April 11, 2013 11:44 PM
Points: 6,
Visits: 88
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?
Post #749000
william-812661
william-812661
Posted Wednesday, July 08, 2009 2:56 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, July 25, 2012 8:32 PM
Points: 2,
Visits: 22
When there is no index, why database engine need to place RID lock on both row 1 & 2?
Post #749102
leigh.shayler
leigh.shayler
Posted Wednesday, July 08, 2009 3:26 AM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, September 22, 2010 12:39 AM
Points: 10,
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?
Post #749117
guruprasat85
guruprasat85
Posted Wednesday, July 08, 2009 9:08 PM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, April 11, 2013 11:44 PM
Points: 6,
Visits: 88
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
Post #749824
guruprasat85
guruprasat85
Posted Wednesday, July 08, 2009 9:09 PM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, April 11, 2013 11:44 PM
Points: 6,
Visits: 88
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
Post #749826
guruprasat85
guruprasat85
Posted Wednesday, July 08, 2009 9:09 PM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, April 11, 2013 11:44 PM
Points: 6,
Visits: 88
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
Post #749827
mishaluba
mishaluba
Posted Saturday, July 11, 2009 6:41 PM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:54 PM
Points: 525,
Visits: 617
Loved it. Familiar topic, but very well explained!
Post #751656
Ken Lee-263418
Ken Lee-263418
Posted Saturday, June 04, 2011 4:07 PM
SSC Rookie
Group: General Forum Members
Last Login: Friday, December 21, 2012 1:46 AM
Points: 28,
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.
Post #1119896
Ken Lee-263418
Ken Lee-263418
Posted Saturday, June 04, 2011 4:42 PM
SSC Rookie
Group: General Forum Members
Last Login: Friday, December 21, 2012 1:46 AM
Points: 28,
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.
Post #1119898
« Prev Topic
|
Next Topic »
21 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
