TRANSACTION and @@IDENDITY
-
Consider something this:
If, within a transaction we insert a row into a table (say Orders) containing an Identity column, and we then use the
@@IDENTITY value to insert into another table (say OrderLines), and we then ROLLBACK the transaction
BEGIN TRANSACTIONINSERT INTO Orders (ClientID, GetDate(),..)
SELECT @OrderID = @@INENDITY
INSERT INTO OrderLines (OrderID, ..) VALUES (@OrderID, ..)
.....
ROLLBACK TRANSACTION
Can I confirm I understand this correctly - I believe no other connection will be able to insert into the Orders Table until commit or rollback, and upon rollback the next identity value of the Orders table will not have changed.
I wonder because if you had many thousands of rows to insert into OrderLines this would lock/prevent any new Orders being created until completion.
Is this right?
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
C# Gnu (10/16/2012)
Can I confirm I understand this correctly - I believe no other connection will be able to insert into the Orders Table until commit or rollback, and upon rollback the next identity value of the Orders table will not have changed.Shouldn't block other inserts, unless for some reason the insert takes a table lock.
Upon rollback the identity values will have changed, rollback doesn't undo identity increments. Gaps are possible and normal.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (10/16/2012)
Shouldn't block other inserts, unless for some reason the insert takes a table lock.
Upon rollback the identity values will have changed, rollback doesn't undo identity increments. Gaps are possible and normal.
Aha moment!
now that sounds allot better, many thanks for answering.
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Also, note that @@identity will return the last identity created, NOT necessarily the one that this transaction generated. you should use scope_identity() to use the indentity generated from the "scope" of your transaction.
Jared
CE - Microsoft -
Your right !
I did notice though that a select (ie select * from orders) hangs until the transaction commits or rolls back.
I can see that SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED allows the select statement to complete, but it then return rows that might be rolled back.
Is there a way of selecting only committed without the blocking effect?
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
C# Gnu (10/16/2012)
Your right !I did notice though that a select (ie select * from orders) hangs until the transaction commits or rolls back.
I can see that SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED allows the select statement to complete, but it then return rows that might be rolled back.
Is there a way of selecting only committed without the blocking effect?
Addition: so this in effect was my worry - that systems would be locked out while the transaction is open
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Readpast hint.
Snapshot isolation
Read committed snapshot isolation
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
C# Gnu (10/16/2012)
Addition: so this in effect was my worry - that systems would be locked out while the transaction is openIf they try to read rows that are locked, they have to wait until the locks are released.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (10/16/2012)
C# Gnu (10/16/2012)
Addition: so this in effect was my worry - that systems would be locked out while the transaction is openIf they try to read rows that are locked, they have to wait until the locks are released.
Hmm, Seems like an option is missing to me!
BEGIN TRANSACTION NOBLOCK
I would have thought the default 'ISOLATION LEVEL COMMITED' select statement should return all committed rows and ignore any uncommitted rows - rather than wait forever.
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
C# Gnu (10/16/2012)
GilaMonster (10/16/2012)
C# Gnu (10/16/2012)
Addition: so this in effect was my worry - that systems would be locked out while the transaction is openIf they try to read rows that are locked, they have to wait until the locks are released.
Hmm, Seems like an option is missing to me!
BEGIN TRANSACTION NOBLOCK
That's the read uncommitted isolation level, read committed snapshot isolation level and snapshot isolation level and the readpast locking hint depending exactly what behaviour you want.
I would have thought the default 'ISOLATION LEVEL COMMITED' select statement should return all committed rows and ignore any uncommitted rows - rather than wait forever.
So what happens when you select while rows are being update? Just not read them because they're currently locked for update? That would be interesting in terms of inconsistent results.
If you want that behaviour, you use the readpast hint.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
ALTER DATABASE test SET READ_COMMITTED_SNAPSHOT ON;That's seems to produce exactly the behavior I am after.
I gave it a test - and no select statements are blocked by open transactions - yipee.
(I have no control of select statement hints as many applications / users are querying the database)
Could using this mode have an adverse effect on existing applications?
Thanks a million.
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Yes, you may want to do some research into that switch.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Ah ..
this is a pretty good read on the subject
http://www.jimmcleod.net/blog/index.php/2009/08/27/the-potential-dangers-of-the-read-committed-snapshot-isolation-level/[/url]
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Plus the book chapter I referenced earlier....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply