Using Indexes to Reduce Blocking in Concurrent Transactions

Question

Using Indexes to Reduce Blocking in Concurrent Transactions

nilav.ghosh

SSC Rookie

Points: 39
More actions
June 21, 2009 at 2:59 am

#384812

Comments posted to this topic are about the item Using Indexes to Reduce Blocking in Concurrent Transactions

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Bob Fazio SSChampion Points: 10794 More actions · Answer 1

A good article, that with a thorough proof read would have resulted in a very good article.

11757457 ObjID is actually the ID for the Bibby.Profit_Act table. 1:153:1 is the first row in the Bibby.Profit_Act table. The interesting thing to note is the X lock has been granted on this first row, but the transaction goes into a waiting state when it tries to lock the second row as well which is 1:153:1 (Note the Status column showing ‘WAIT’ status) for the update lock being requested.

11757457 - Should be 117575457

1:153:1 is the first row - should be 1:153:0 is the first row

It was easy for me to catch these mistakes (But I knew this topic already).

Also an explanation of the lock resource values and how to understand how you know that

1:153:0 is the first row would have been appreciated.

DatabaseID:FileID:PageID:Slot(row) or FileID:PageID:Slot(row)

dchintalapudi Grasshopper Points: 24 More actions · Answer 2

Excellent Article! Concepts were explained very well.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

It was a fairly good article, and brings an interesting approach to the table, but I think the concept is a little outdated. This would have been more relevant in SQL Server 2000 where you couldn't avoid the dirty read vs. shared locks question. In SQL Server 2005 and 2008 you can use multi-version concurrency via the READ_COMMITTED_SNAPSHOT database option in an OLTP system to avoid the problems of both dirty reads and shared locks.

Thomas LeBlanc SSCrazy Eights Points: 8075 More actions · Answer 4

Great article with examples and explanations...

I follow up article would be great, please advance the level.

God Bless,

ThomasLL

Thomas LeBlanc, MVP Data Platform Consultant

Noel Jiwanmall Grasshopper Points: 21 More actions · Answer 5

Hello All,

I am new to SQL Server Databases so please forgive if my question doesn't make sense. So here I go, how can one ever know that only Mutually Exclusive rows will be updated in a table at the same time so not to apply the Update Lock to at least prevent the deadlock situations from occuring? What I mean to say is that if I were to prioritize the interactions of different queries with a table, I would first want to make sure that no deadlocks are ever created which would lead me to use the Update lock, but then I would also like to acheive better performance so not to use the update lock and simply applying the index to the table. In other words, is it possible to use both an Index as well as the Update Lock to achieve improved performance as well as avoid deadlocks?

If you were able to understand what I was getting at, please help clarify.

Regards

s.c.simmons SSC Enthusiast Points: 172 More actions · Answer 6

Question: Does the index have to be clustered for this to work?

keliu SSC Rookie Points: 37 More actions · Answer 7

Very good article with clear instructions.

Hope to read more papers from you again.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 8

s.c.simmons (7/7/2009)
Question: Does the index have to be clustered for this to work?

Doesn't have to be. Basically, the trick is to show queries different paths.

rob.lobbe-964963 Old Hand Points: 307 More actions · Answer 9

Of course this all falls in a screaming heap when larger 'row selections' are used, and 'Lock escalation' kicks in.

A selection of 5000+ rows - will escalate to a single Table-lock (rather than 5000+ row locks) and you're back to where you started.

But in essence, any indexing will reduce blocking (not eliminate it) as the database touches fewer elements of the table -- unless you have an index scan going on...

leigh.shayler SSC Rookie Points: 28 More actions · Answer 10

Thanks, Provides a good (and brief) overview of resource locking and lock investigation as well.

guruprasat85 SSC Enthusiast Points: 136 More actions · Answer 11

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 Grasshopper Points: 13 More actions · Answer 12

When there is no index, why database engine need to place RID lock on both row 1 & 2?

leigh.shayler SSC Rookie Points: 28 More actions · Answer 13

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 SSC Enthusiast Points: 136 More actions · Answer 14

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