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?


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)

No comments: