Tuesday, October 25, 2016
SQL Pass Summit... SQL Teaser Transactions
I am sitting in Kalen Delaney;s session on Locking, Blocking, Versions: Concurrency for Maximum Performance and had a great idea for a SQL Server
Teaser.
<<<<<
Without running it, what will printed when the following two blocks of code are run?
Now run it.... Were you right?
What about the code below, what will happen if you run these two code blocks? Does it matter that the transactions are created in separate batches?
Without running it, what will printed when the following two blocks of code are run?
BEGIN TRAN BEGIN TRAN BEGIN TRAN BEGIN TRAN BEGIN TRAN BEGIN TRAN WHILE @@TRANCOUNT > 0 BEGIN PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT) COMMIT END PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT) GO ----------------------- BEGIN TRAN BEGIN TRAN BEGIN TRAN BEGIN TRAN BEGIN TRAN BEGIN TRAN WHILE @@TRANCOUNT > 0 BEGIN PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT) ROLLBACK END PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)
Now run it.... Were you right?
What about the code below, what will happen if you run these two code blocks? Does it matter that the transactions are created in separate batches?
BEGIN TRAN GO 7 WHILE @@TRANCOUNT > 0 BEGIN PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT) COMMIT END PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT) GO
-----------------------
BEGIN TRAN
GO 7
WHILE @@TRANCOUNT > 0
BEGIN
PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)
ROLLBACK
END
PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)
Labels:
Teaser
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment