vendredi 24 juin 2016

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 incorrectly?

Any suggestions would be appreciated.

Aucun commentaire:

Enregistrer un commentaire