What happens to an open transaction when the session window is closed?

Question

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?

Answer 1

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:

1. 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
   

2. 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
   

3. Open up a second SSMS and execute the following script:

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

4. Kill the first SSMS via Task Manager.

5. 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.

Answer 2

Assuming when you say "session window" you're referring to a query window in SSMS. In SSMS each query window is typically its own connection. When you close the window, it theoretically closes the connection. (Theoretically because it depends on if the connection is used in connection pooling or not.)

When the connection between the client and SQL Server is severed in the middle of an open transaction, that transaction will automatically rollback. This is noted in Hannah's answer in What are all the ways an open transaction will rollback?: "Disconnection by the client, or server, or any bit of infrastructure in between".