Adding INDEX causes DEADLOCKS?

I have a very small table (50 rows, 50 columns).
I have 4 servers using the table:
1 server updates the table ("UPDATE table SET col1='', col2='',
LastUpdate=GetDate()").. about 20 rows updated a second.
3 servers pull data ("SELECT * FROM table Where
LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
times a second each.
Prior to last week, i did not have the lastupdate column. but it
obviously made sense to me to add it (why pull everything every 100ms
when you can only pull what you need via the LastUpdate, maybe 3-4
rows at a time). so i add the lastupdate column and make an index on
it.
now as soon as i run it, every 2-4 minutes i get a deadlock (!).
execution path is showing a bookmark lookup with index seek because i
have two conditions in the where clause: "lastupdate>(time
oflastrequest) and COL3>0". i have tried making an index based upon
both lastUpdate,Col3 but the results are the same (lots of deadlocks)
I have removed the index, kept the lastupdate column, same queries,
and everything works fine -- except, of course, that it is doing a
table scan for every lookup. the table will be growing soon and i
will be in trouble. so i need some help here, any ideas?
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
You update the whole table every time? Why?
|||"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.co m...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
Easiest way to solve these problems is with more locking. I know, it sounds
weird, but more locking will separate the deadlocking processes and properly
serialize them. I suggest in your Update statement add a TABLOCKX hint.
That will exclusively lock the entire table for the duration of the update.
This will also give you more predictable results as the table will only be
read when it is in a completely consistent state between updates.
David
|||The deadlock is due to that for the 3 server pulling data, it needs to get
page/row lock on the non-clustered index first, then get page/row lock on
the base table(while holding the lock on the non-clustered index), while the
update gets lock on base table first, then the lock on page/row in the
non-clustered index. To break the circle, you can either create a clustered
index on the LastUpdate field instead of a non-clustered index, or either
specify TABLOCKX hint for the update or specify readpast lock hint for the
reader.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.co m...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
|||"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:u4Gn6SuQEHA.2408@.tk2msftngp13.phx.gbl...
> The deadlock is due to that for the 3 server pulling data, it needs to get
> page/row lock on the non-clustered index first, then get page/row lock on
> the base table(while holding the lock on the non-clustered index), while
the
> update gets lock on base table first, then the lock on page/row in the
> non-clustered index. To break the circle, you can either create a
clustered
> index on the LastUpdate field instead of a non-clustered index, or either
> specify TABLOCKX hint for the update or specify readpast lock hint for the
> reader.
> --
> Gang He
> SQL Server Storage Engine Development
Good info. Thanks for the post Gang.
David