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 «««123

Fun with Transactions - Part I Expand / Collapse
Author
Message
Posted Tuesday, September 14, 2010 12:17 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:48 AM
Points: 3,958, Visits: 3,644
Good question, thanks.
Post #985766
Posted Tuesday, September 14, 2010 11:29 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
Nice question and discussion, thanks!
Post #986030
Posted Wednesday, September 15, 2010 7:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:36 PM
Points: 2,818, Visits: 2,565
QoTD keeping us on our toes. Nice question, thanks.
Post #986244
Posted Wednesday, September 15, 2010 8:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
I don't even know how excitingly appropriate this is.

Yesterday I learned about savepoints. Today a trigger that was invoking a stored procedure that created an inner transaction and rolled it back if a test case was pushed into production.

Nobody could figure out why the table with the tirgger was never getting a record.

Stuck in a transaction save point right after the BEGIN TRANSACTION in the stored proc and set the ROLLBACK to rollback to the savepoint and made the problem go away.

Awesome




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #986317
Posted Wednesday, September 15, 2010 8:40 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 1,434, Visits: 1,846
mtassin (9/15/2010)
I don't even know how excitingly appropriate this is.

Yesterday I learned about savepoints. Today a trigger that was invoking a stored procedure that created an inner transaction and rolled it back if a test case was pushed into production.

Nobody could figure out why the table with the tirgger was never getting a record.

Stuck in a transaction save point right after the BEGIN TRANSACTION in the stored proc and set the ROLLBACK to rollback to the savepoint and made the problem go away.

Awesome


Hey, Mark!

That's really good to know!

Coincidence is a great phenomenon - I wonder how they happen?

Have a wonderful rest-of-the-week ahead!


Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #986325
Posted Wednesday, September 15, 2010 9:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
Nakul Vachhrajani (9/15/2010)


Hey, Mark!

That's really good to know!

Coincidence is a great phenomenon - I wonder how they happen?

Have a wonderful rest-of-the-week ahead!


For me,

around here this happens about once every 3 months.

But usually I learn about it 2-3 weeks before I need to use it.. not 24 hours :)

Tally Tables, Dynamic Paramterized Queries and several other topics typically hit here about 3 weeks before I need them. This time it was in a QOTD.

As I said, awesome.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #986364
Posted Saturday, September 18, 2010 11:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, October 19, 2014 11:12 AM
Points: 7,791, Visits: 9,545
da-zero (9/14/2010)
[quote]Nesting does work with transactions. It's just that if you use rollback transaction, then all open transactions are rolled back. If you commit a transaction, then only the innermost transaction is committed.


No, you can't commit an inner transaction - what happens when you issue a commit command is that the transaction count gets decremented, and if the result is zero, the transaction (which is the outermost transaction) is committed (and the data modifications affected by this commit include all made by any so-called nested transactions); if the result is non-zero, nothing is committed. Neither can you roll back a nested transaction - you can only roll back the whole nest (including the outermost transaction).


Tom
Post #988762
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse