TRANSACTION and @@IDENDITY

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » TRANSACTION and @@IDENDITY

14 posts, Page 1 of 2

1 2 »»

TRANSACTION and @@IDENDITY

Rate Topic

Display Mode

Topic Options

Author

Message

C# Gnu

Posted Tuesday, October 16, 2012 6:56 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:35 AM
Points: 224, Visits: 629

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 V2 ..
Multi user data edit Task based solution - Excel with SQL Server / Access / MySQL ..

Version 2.5 released - See Blog

Post #1373214

GilaMonster

Posted Tuesday, October 16, 2012 7:12 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996

C# Gnu (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.

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

C# Gnu

Posted Tuesday, October 16, 2012 7:27 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:35 AM
Points: 224, Visits: 629

GilaMonster (10/16/2012)

Aha moment!

now that sounds allot better, many thanks for answering.

C# Gnu
____________________________________________________

Excel Database Tasks V2 ..
Multi user data edit Task based solution - Excel with SQL Server / Access / MySQL ..

Version 2.5 released - See Blog

Post #1373246

SQLKnowItAll

Posted Tuesday, October 16, 2012 7:33 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 2,613, Visits: 3,128

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

C# Gnu

Posted Tuesday, October 16, 2012 7:49 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:35 AM
Points: 224, Visits: 629

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 V2 ..
Multi user data edit Task based solution - Excel with SQL Server / Access / MySQL ..

Version 2.5 released - See Blog

Post #1373268

C# Gnu

Posted Tuesday, October 16, 2012 7:52 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:35 AM
Points: 224, Visits: 629

C# Gnu (10/16/2012)

Addition: so this in effect was my worry - that systems would be locked out while the transaction is open

C# Gnu
____________________________________________________

Excel Database Tasks V2 ..
Multi user data edit Task based solution - Excel with SQL Server / Access / MySQL ..

Version 2.5 released - See Blog

Post #1373270

GilaMonster

Posted Tuesday, October 16, 2012 7:52 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996

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

GilaMonster

Posted Tuesday, October 16, 2012 7:53 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996

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

C# Gnu

Posted Tuesday, October 16, 2012 8:02 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:35 AM
Points: 224, Visits: 629

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

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 V2 ..
Multi user data edit Task based solution - Excel with SQL Server / Access / MySQL ..

Version 2.5 released - See Blog

Post #1373281

GilaMonster

Posted Tuesday, October 16, 2012 8:24 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996

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

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 »

14 posts, Page 1 of 2

1 2 »»

Permissions