CODEWITHSUNDEEP
sqlsql-servertransactions
user3638992
user3638992

Reputation: 87

ReadUncommitted not working across transactions

I have two tables: TableA with columns id(UNIQUEIDENTIFIER) and name(NVARCHAR)( uses NEWSEQUENTIALID() to auto-generate values for 'id' column)

TableB with columns id( IDENTITY), parentId(UNIQUEIDENTIFIER). parentId in TableB has a foreign key constraint on TableA id.

I'm trying the execute the following queries:

In session 1:

BEGIN TRAN test1 INSERT INTO dbo.TableA( name ) OUTPUT INSERTED.id VALUES ('foo')

Note that I do not want to commit the transaction here yet.

In session 2: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRANSACTION test2 INSERT INTO dbo.TableB(parentId) VALUES('<use_id_from_session1_here>')

The second insert fails to execute and hangs up in SQL Server Management Studio and in my application code as well. Shouldn't setting an isolation level of 'ReadUncommitted' allow the insert in the second transaction to read what was uncommitted in the first?

Am I missing something here or the way I'm using/setting the transaction isolation level incorrect?

Any suggestions would be appreciated.

Upvotes: 0

Views: 734

Answers (2)

Ciro Corvino
Ciro Corvino

Reputation: 2128

you might commit every single insert in session 1, or every some batch of a size that don't limit too about wait time of session 2

Upvotes: 0

Jeffrey Van Laethem
Jeffrey Van Laethem

Reputation: 2651

What's happening is when you're doing the second insert, SQL Server is trying to check the Foreign Key to make sure you're not inserting something you can't. But since the lock on TableA is still being held by your first transaction, your second transaction is waiting. Read uncommitted doesn't matter there. Constraints have to be checked before an insert can take place, so it will wait for the first transaction to finish. You'd still be violating the Foreign key constraint if the lock weren't in place because the first transaction hasn't been committed yet.

Upvotes: 2

Related Questions