Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TRANSACTION and @@IDENDITY


TRANSACTION and @@IDENDITY

Author
Message
C# Gnu
C# Gnu
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 659
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 TRANSACTION

INSERT 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

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47189 Visits: 44356
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 recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


C# Gnu
C# Gnu
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 659
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

SQLKnowItAll
SQLKnowItAll
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2796 Visits: 3681
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.

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
C# Gnu
C# Gnu
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 659
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
C# Gnu
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 659
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

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47189 Visits: 44356
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 recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47189 Visits: 44356
C# Gnu (10/16/2012)
Addition: so this in effect was my worry - that systems would be locked out while the transaction is open


If they try to read rows that are locked, they have to wait until the locks are released.

Chapter 6: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


C# Gnu
C# Gnu
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 659
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 open


If they try to read rows that are locked, they have to wait until the locks are released.

Chapter 6: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/


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

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47189 Visits: 44356
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 open


If they try to read rows that are locked, they have to wait until the locks are released.

Chapter 6: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/


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 recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search