How to view active transactions in a Sql server database
Microsoft SQL Server operates in three transaction modes:
Each individual statement is a transaction.
Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
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
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
now we have the session id
kill it with command
Now execute the same query and see the results ,SELECT * FROM DBO.EMPLOYEE WHERE ID = 1