Deadlock with uncommited INSERT on Select/Update in Sql Server 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Deadlock with uncommited INSERT on Select/Update in Sql Server 2000

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-06 : 12:20:19

gavin writes "Hi,
I'm in the final stages of migrating some Oracle code to
Sql Server 2000 and I've started to see unexpected deadlocks.

The problem comes down to the Sql Server implementation of
READ COMMITTED isolation level. The following code can deadlock
if run concurrently:

BEGIN TRANSACTION
INSERT INTO foo VALUES (1,'2')
SELECT * FROM foo
COMMIT

The SELECT in transaction A tries to acquire a shared lock on the
row INSERTed by transaction B. And vice versa....deadlock. However
coming from the Oracle world I find it bizarre that another
transaction would attempt to lock a row that hasn't been committed
yet. For Oracle the INSERTed rows are only visible within the
current transaction.

The actual problem I'm hitting is a mixture of INSERT/SELECT/UPDATE
but the key issue is that transaction A knows about rows inserted by
transaction B that haven't been committed yet!!

I've explored a number of options but none offer a solution robust
enough for the real world:

1) READPAST lock hint. Using READPAST would make the simple example
above behave correctly. However READPAST will read past ALL locks
the correct (well required) behaviour is:
- If a row has been inserted then it's valid to read past it.
- If a row has been updated then it should block and wait for the
lock to be freed.

2) ISOLATION LEVEL. None of the ISOLATION LEVELs help, the issue is
that the row is visible and I can't find any way to make it
invisible
outside of the current transaction.

3) TABLOCKX. Sadly this is my last resort. Locking the table on the
insert works, but only because it effectively serializes all the
calls.
This is my absolute last resort.

Any ideas on how best to approach this? Is there anyway to make inserts invisible until after they're committed?

thanks,
gavin"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-06 : 13:47:31

the t-sql (with a read committed isolation level):
BEGIN TRANSACTION
INSERT INTO foo VALUES (1,'2')
SELECT * FROM foo
COMMIT

inserts a row and will place a exclusive page lock on the page containing the row.
the select places a shared lock on the table.

so putting them side by side and assigning an execution sequence:


BEGIN TRANSACTION                           BEGIN TRANSACTION                 
INSERT INTO foo VALUES (1,'2') (1)          INSERT INTO foo VALUES (1,'2') (2)
SELECT * FROM foo              (3) (block)  SELECT * FROM foo (4) (block)
COMMIT                                      COMMIT

this will produce a cycle deadlock. both transactions need to acquire a shared table lock but neither can since the other has locked a page in the table for the insert.

solution: redesign the transaction so it does not need table-wide resources. why do you need to run a 'select * from foo'?

setBasedIsTheTruepath
<O>

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-06 : 13:55:23

quote:

The SELECT in transaction A tries to acquire a shared lock on the
row INSERTed by transaction B. And vice versa....deadlock. However
coming from the Oracle world I find it bizarre that another
transaction would attempt to lock a row that hasn't been committed
yet. For Oracle the INSERTed rows are only visible within the
current transaction.

If only there was a lingua franca for transactions. Comically, I in turn find it bizarre that Oracle would let a table lock be acquired on a table which has exclusive child locks on it. Visibility does not mean the inserted row simply doesn't exist; rather all it implies is that you can't have access to it. Oracle may steamroll over the lock, as SQL Server will with the READPAST hint, but that's not behavior I'm familiar with.

setBasedIsTheTruepath
<O>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-06 : 14:25:04

I remember reading somewhere that Oracle only locked rows, it might not be that way now but it was at one time. In any case, locking a row doesn't have much to do with isolation. In fact, since Oracle is very row-based in its operations, instead of set-based, I would think it has to do MORE work than SQL Server in isolating rows from other transactions. Oh well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources