How to view active transactions in a Sql server database

Microsoft SQL Server operates in three transaction modes:
Autocommit transactions
Each individual statement is a transaction.
Explicit transactions
Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
Implicit transactions
A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

By default, SQL Server operates in the autocommit mode.However, if you use the SET IMPLICIT_TRANSACTIONS ON command, you can override the automatic commitment so that SQL Server will wait for you to issue an explicit COMMIT or ROLLBACK statement to do anything with the transaction

if you want to check the isolation level of your sql server database

use the following command

DBCC USEROPTIONS

 

 

 

 

 

 

 

 

 

Isolation level : Read Committed

which means you can only read the data after committing (in order to prevent the dirty reads)

lets look at the following query

BEGIN TRAN 

UPDATE dbo.EMPLOYEE

SET ADDRESS = NULL

WHERE ID = 1

In this case we are updating a record but we are not committing the transaction

Execute the above query and check the table for update  after query finishes

SELECT * FROM DBO.EMPLOYEE WHERE ID = 1 , this query will  execute for ever as resources are locked by the previous uncommitted transaction

Now, lets see how to kill the session and transaction which are locking these resources, easy way is to use a DBCC command which will display oldest active transaction

DBCC OPENTRAN

now we have the session id

kill it with command

KILL  55

Now execute the same query and see the results ,SELECT * FROM DBO.EMPLOYEE WHERE ID = 1

Thank you