Home > SQL Server > Get the current transaction state of user request using XACT_STATE() – SQL Server

Get the current transaction state of user request using XACT_STATE() – SQL Server


XACT_STATE() function returns the state of currently running user transaction. It returns a small integer value (0, 1 or -1). Using this transaction we can determine whether it is an active transaction and it is committable or not.

Status value

0 – there is no active user transaction. So, no problem.

1 – The current request has an active user transaction. So, Read, write and commit operations are permitted.

-1 – The current request has an active user transaction. But, an error occurred in the transaction and it is uncommittable. So, read operation is permitted until transaction is roll back.

@@TRANCOUNT function is used to identify the nested transactions in the current session. But, XACT_STATE() is used to detect the uncommittable transaction in the current session.

Implementation

I am using MSDN example in this demo.

pic1This T-SQL statement has a constraint violation error while deleting the records. So, using the try–catch and xact_state() function we have rolled back the uncommittable transaction.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: