April 14, 2016 at 5:12 am
Hi,
Is it possible to find out which updates were made against a first, second, third etc nested transactions?
For example, if select @@trancount returns 4 can I find out what update was carried out for nested transaction 1, 2, 3 etc?
I hope this makes sense.
Any help welcome.
Thanks,
Eamon
April 14, 2016 at 6:53 am
EamonSQL (4/14/2016)
Hi,Is it possible to find out which updates were made against a first, second, third etc nested transactions?
For example, if select @@trancount returns 4 can I find out what update was carried out for nested transaction 1, 2, 3 etc?
I hope this makes sense.
Any help welcome.
Thanks,
Eamon
Not unless you have a trace running. But be careful with "nested transactions". In sql server they are a myth. http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 15, 2016 at 2:55 am
EamonSQL (4/14/2016)
Is it possible to find out which updates were made against a first, second, third etc nested transactions?For example, if select @@trancount returns 4 can I find out what update was carried out for nested transaction 1, 2, 3 etc?
No, because SQL doesn't actually have nested transactions. It's got some syntax that makes you think it does, but it doesn't.
http://sqlinthewild.co.za/index.php/2015/11/03/a-mess-of-nested-transactions/
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
April 15, 2016 at 3:01 am
Thanks alot and that's what I thought.
King regards,
Eamon
April 22, 2016 at 6:00 am
GilaMonster (4/15/2016)
EamonSQL (4/14/2016)
Is it possible to find out which updates were made against a first, second, third etc nested transactions?For example, if select @@trancount returns 4 can I find out what update was carried out for nested transaction 1, 2, 3 etc?
No, because SQL doesn't actually have nested transactions. It's got some syntax that makes you think it does, but it doesn't.
http://sqlinthewild.co.za/index.php/2015/11/03/a-mess-of-nested-transactions/
Don't be too quick to judge based solely on log work. Logging Transactions, which is by design, to recreate your data/database in event of a restore does not necessarily translate into no nested transactions.
Nesting transactions is in fact possible in SQL Server.
https://technet.microsoft.com/en-us/library/ms189336(v=sql.105).aspx
I can, in fact, ROLLBACK a transaction that is nested and still have a trancount > 0
April 22, 2016 at 7:53 am
Smendle (4/22/2016)
GilaMonster (4/15/2016)
EamonSQL (4/14/2016)
Is it possible to find out which updates were made against a first, second, third etc nested transactions?For example, if select @@trancount returns 4 can I find out what update was carried out for nested transaction 1, 2, 3 etc?
No, because SQL doesn't actually have nested transactions. It's got some syntax that makes you think it does, but it doesn't.
http://sqlinthewild.co.za/index.php/2015/11/03/a-mess-of-nested-transactions/
Don't be too quick to judge based solely on log work. Logging Transactions, which is by design, to recreate your data/database in event of a restore does not necessarily translate into no nested transactions.
Nesting transactions is in fact possible in SQL Server.
https://technet.microsoft.com/en-us/library/ms189336(v=sql.105).aspx
I can, in fact, ROLLBACK a transaction that is nested and still have a trancount > 0
There is far more to this than looking at @@TRANCOUNT. Look closely are the article you linked.
Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction.
Not sure how much more clear that can be that nested transactions do NOT work like it seems like.
The article continues...
If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.
Each call to COMMIT TRANSACTION or COMMIT WORK applies to the last executed BEGIN TRANSACTION. If the BEGIN TRANSACTION statements are nested, then a COMMIT statement applies only to the last nested transaction, which is the innermost transaction. Even if a COMMIT TRANSACTION transaction_name statement within a nested transaction refers to the transaction name of the outer transaction, the commit applies only to the innermost transaction.
Nested transactions are not independent blocks of work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply