SQL Server – What Happens to Open Transaction When Session Window Closes?

sql serversql-server-2012transaction

I executed the following query in SQL Server 2012:

create table testTransaction
(
id int
name varchar(100)
)

insert into testTransaction values (1,'ABC'),(2,'XYZ')

/* Query1 */
begin try
begin transaction
  insert into testTransaction values (3,'FGH')
commit transaction
end try
begin catch
if @@trancount > 0
rollback transaction
end catch

I executed query1 and got an error that the account I was using did not have permission to insert values into that table. In another window, I tried to do a select on testTransaction but the query kept going on and on.

It was then I realized that the previous query I ran in the other window had an open transaction. I went and rolled it back and everything worked fine.

What would have happened if I had closed the window that had the open transaction without rolling it back?
Would the transaction be open till someone manually rolls it back or does SQL server rollback any uncommitted transactions when a window is closed?

Also, how do I find out all the open transactions in a database across any session?

Best Answer

Answering Your Questions

What would have happened if I had closed the window that had the open transaction without rolling it back?

On the premise that most RDBMS function on the basis of the ACID principle, your transaction would be rolled back.

_{(emphasis mine)}

In computer science, ACID (atomicity, consistency, isolation, durability) is a set of properties of database transactions intended to guarantee data validity despite errors, power failures, and other mishaps.[1] In the context of databases, a sequence of database operations that satisfies the ACID properties (which can be perceived as a single logical operation on the data) is called a transaction. For example, a transfer of funds from one bank account to another, even involving multiple changes such as debiting one account and crediting another, is a single transaction.

^{Reference: ACID (Wikipedia)}

Closing a window should result in the transaction being rolled back to guarantee the ACID properties of the database transactions.

Closing Query Window in SSMS (actual case)

In SSMS you are presented with a dialog which lets you decide how to react to closing a window:

Forcefully Closing SSMS via Taskmanager (actual case)

When SSMS is terminated via Taskmanager and/or forcefully closed, then the transaction is automatically rolled back.

This can be reproduced with the following steps:

Create the following table in a database

USE [EverGrowingDatabase]
GO

/****** Object:  Table [dbo].[EverGrowingTable]    Script Date: 21.10.2022 15:15:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[EverGrowingTable](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [shorttext] [nchar](20) NOT NULL,
    [longtext] [nchar](1000) NULL
    ) ON [PRIMARY]
GO

Open up one SSMS and execute the following script:

USE [EverGrowingDatabase]
go
BEGIN TRAN
INSERT INTO EverGrowingTable
(
    -- id -- this column value is auto-generated
    shorttext,
    longtext
)
VALUES
(
    'SomeRealyShortText',
    REPLICATE('SomeRealyLongText',50)

)
--ROLLBACK TRANSACTION

Open up a second SSMS and execute the following script:

USE [EverGrowingDatabase]
GO
SELECT * FROM EverGrowingTable AS egt WHERE egt.shorttext = 'SomeRealyShortText';

Kill the first SSMS via Task Manager.
Verify that the second script from step 3. does not display any data.

...and as mentioned by J.D. in his linked answer.

Would the transaction be open till someone manually rolls it back or does SQL server rollback any uncommitted transactions when a window is closed?

The transaction would be rolled back under normal circumstances.

Also, how do I find out all the open transactions in a database across any session?

To determine the currently open transactions you could query the sys.dm_exec_sessions, sys.dm_exec_connections, sys.dm_exec_reuqests and other System Management Views to determine any open transactions in a request or in a session.

Example script:

SELECT      des1.session_id             AS Session_ID_S,
            sdb.name                    AS DatabaseName,
            ssp.name                    AS SQL_Login_Name,
            des1.nt_user_name           AS NT_User_Name,  
            dowt.wait_type              AS Wait_Type,
            dowt.blocking_session_id    AS Blocking_Session_ID,
            dowt.resource_description   AS Ressource_Description,
            der.[status]                AS Request_Status,
            der.wait_type               AS Request_WaitType,
            der.open_transaction_count  AS Request_Open_Transactions,
            des1.open_transaction_count AS Session_Open_Transactions,
            des1.host_name              AS HostName,
            des1.host_process_id        AS HostProcessID,
            des1.program_name           AS Program_Name,
            dest.[text]                 AS SQL_Text,
            deqp.query_plan             AS Query_Plan,
            CASE  WHEN dowt.blocking_session_id  IS NOT NULL AND dowt.blocking_session_id != des1.session_id THEN '--kill ' + cast(dowt.blocking_session_id AS nvarchar(20)) ELSE ' ' END AS killcommand,
            'EOR'                       AS EOR
FROM        sys.dm_exec_sessions        AS des1
            LEFT 
            JOIN sys.dm_exec_connections    AS dec1    
                ON  des1.session_id         = dec1.session_id
            LEFT -- comment out LEFT to display only sessions that have gone parallel
            JOIN sys.dm_os_waiting_tasks    AS dowt 
                ON  des1.session_id         = dowt.session_id
            LEFT -- comment out LEFT to display only sessions currently executing statements
            JOIN sys.dm_exec_requests       AS der    
                ON  des1.session_id         = der.session_id
            LEFT -- comment out LEFT to ...... (I'm not telling)
            JOIN sys.server_principals      AS ssp  
                ON  des1.login_name         = ssp.name
            /* ==================== This is for SQL Server 2012 + ===================*/
            LEFT 
            JOIN sys.databases              AS sdb
                ON des1.database_id         = sdb.database_id
             /* ==================== This is for SQL Server 2012 + ===================*/
            
            /* ==================== This is for SQL Server 2008 R2 ===================
            LEFT
            JOIN sys.sysprocesses as ss
                ON ss.spid = des1.session_id
            LEFT 
            JOIN sys.databases as sdb
                ON sdb.database_id = ss.dbid
             ==================== This is for SQL Server 2008 R2 ===================*/
            
            OUTER APPLY sys.dm_exec_sql_text(der.sql_handle)     AS dest -- Retrieve Actual SQL Text
            OUTER APPLY sys.dm_exec_query_plan(der.plan_handle)  AS deqp -- Retrieve Query Plan (XML)
WHERE       1=1
    -- AND      sdb.name in ('XPDATA', 'XPVDIR')
    AND        des1.is_user_process     = 1

The columns Session_Open_Tranactionsand Request_Open_Transaction would show you the transactions currently still running for any given Session_ID.

Related Solutions

Sql-server – connection pooling, transactions, nested transaction and rollback

There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).

From the documentation:

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A demonstration:

CREATE TABLE dbo.foo(a INT);

BEGIN TRANSACTION;
  INSERT dbo.foo(a) SELECT 1;
  BEGIN TRANSACTION;
    INSERT dbo.foo(a) SELECT 2;
    BEGIN TRANSACTION;
    ROLLBACK TRANSACTION;

SELECT tc = @@TRANCOUNT;

SELECT a FROM dbo.foo;

DROP TABLE dbo.foo;

Results:

tc    
----
0

1 row(s) affected.

a
----

0 row(s) affected.

So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.

SQL Server – How Transactions Affect Each Other

Assuming they are running on the same session, no, as you've already said. Are there ways around it? Sure, take it out of that session by plopping the messages in a service broker queue with an activated stored procedure. The only part I'm not quite sure on is if the WITH LOG part of raiserror would be subject to that or not - though you probably don't want to write the results to the errorlog.

It could also be shortened so that each part is its own transaction but that may not be suitable for your needs.