Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Using Indexes to Reduce Blocking in Concurrent Transactions Expand / Collapse
Author
Message
Posted Tuesday, July 7, 2009 7:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, July 7, 2009 9:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 4:17 AM
Points: 6, Visits: 92
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
Posted Wednesday, July 8, 2009 2:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 8:53 PM
Points: 3, Visits: 31
When there is no index, why database engine need to place RID lock on both row 1 & 2?
Post #749102
Posted Wednesday, July 8, 2009 3:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, July 8, 2009 9:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 4:17 AM
Points: 6, Visits: 92
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
Posted Wednesday, July 8, 2009 9:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 4:17 AM
Points: 6, Visits: 92
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
Posted Wednesday, July 8, 2009 9:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 4:17 AM
Points: 6, Visits: 92
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
Posted Saturday, July 11, 2009 6:41 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 26, 2014 12:02 PM
Points: 536, Visits: 767
Loved it. Familiar topic, but very well explained!


Post #751656
Posted Saturday, June 4, 2011 4:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Saturday, June 4, 2011 4:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 »

Add to briefcase ««123»»

Permissions Expand / Collapse