Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

TRANSACTION and @@IDENDITY Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 6:56 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #1373214
Posted Tuesday, October 16, 2012 7:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 40,210, Visits: 36,619
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 2008, MVP
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

Post #1373229
Posted Tuesday, October 16, 2012 7:27 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #1373246
Posted Tuesday, October 16, 2012 7:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 2,702, Visits: 3,412
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
Post #1373255
Posted Tuesday, October 16, 2012 7:49 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #1373268
Posted Tuesday, October 16, 2012 7:52 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #1373270
Posted Tuesday, October 16, 2012 7:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 40,210, Visits: 36,619
Readpast hint.
Snapshot isolation
Read committed snapshot isolation



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1373271
Posted Tuesday, October 16, 2012 7:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 40,210, Visits: 36,619
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 2008, MVP
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

Post #1373272
Posted Tuesday, October 16, 2012 8:02 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:07 PM
Points: 225, Visits: 645
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
____________________________________________________


Multi user data edit / data transfer Excel Task based solution
Version 2.7 released June 2013
Post #1373281
Posted Tuesday, October 16, 2012 8:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 40,210, Visits: 36,619
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 2008, MVP
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

Post #1373307
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse