{"id":113333,"date":"2026-11-06T12:00:00","date_gmt":"2026-11-06T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=113333"},"modified":"2026-10-19T08:40:29","modified_gmt":"2026-10-19T08:40:29","slug":"5-common-sql-server-transaction-problems-and-how-to-solve-them","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/5-common-sql-server-transaction-problems-and-how-to-solve-them\/","title":{"rendered":"5 common SQL Server transaction problems (and how to solve them)"},"content":{"rendered":"\n
\n

SQL Server transactions can produce results that seem wrong even when the code looks correct. Perhaps it’s a row count that doesn’t match, a query that waits when it shouldn’t, or an audit record that vanishes after a rollback. This article works through five such problems as hands-on tasks, each with a setup script, an explanation of why it happens, and a solution.<\/strong><\/p>\n\n\n\n

The problems (and their solutions) covered in this guide:<\/h4>\n\n\n\n

A query blocks while a long-running transaction is still inserting rows<\/a><\/strong><\/p>\n\n\n\n

A transaction reads different values for the same row twice<\/a><\/strong><\/p>\n\n\n\n

A transaction gets a different row count without inserting or deleting anything itself<\/a><\/strong><\/p>\n\n\n\n

A rolled-back transaction wipes out the audit record with it<\/a><\/strong><\/p>\n\n\n\n

A nested rollback undoes more than it should<\/a><\/strong><\/p>\n<\/blockquote>\n\n\n\n

<\/p>\n\n\n\n

I encourage you to try identifying and solving the problem yourself, before reading my solution.<\/em> Also, please note: each task should be tested independently and, before starting a new task, run the setup script<\/em> associated with it.<\/em><\/p>\n\n\n\n

The problems (and their solutions)<\/h1>\n\n\n\n

Without further ado, let\u2019s move onto the problems – and, most importantly, the solutions I recommend for each.<\/p>\n\n\n\n

The problem: a query blocks while a long-running transaction is still inserting rows<\/h2>\n\n\n\n

Suppose we have a table named TestTable<\/code> with the following structure:<\/p>\n\n\n\n

IF DB_ID(N'TestDB') IS NULL\n    EXEC('CREATE DATABASE TestDB');\nGO\n\nUSE TestDB\nGO\n\nDROP TABLE IF EXISTS TestTable;\nGO\n-- Create TestTable\nCREATE TABLE TestTable\n(\n    ID INT IDENTITY(1,1),\n    Value INT\n)<\/pre><\/div>\n\n\n\n
We are inserting a large amount of data into TestTable<\/code> using the query below:<\/p>\n\n\n\n
USE TestDB\n\nGO\n\nBEGIN TRY\n\n    BEGIN TRANSACTION\n\n        -- Insert data into TestTable\n\n        -- The value 500000 is used to facilitate the testing process.\n\n        -- Please choose this value carefully for your server to avoid overloading it.\n\n        DECLARE @i INT = 1\n\n        WHILE (@i <= 500000)\n\n        BEGIN\n\n            INSERT INTO TestTable(Value)\n\n            VALUES(@i)\n\n            SET @i = @i + 1\n\n        END\n\n    COMMIT\n\nEND TRY\n\nBEGIN CATCH\n\n    IF @@TRANCOUNT > 0\n\n        ROLLBACK\n\n    SELECT ERROR_MESSAGE();\n\nEND CATCH<\/pre><\/div>\n\n\n\n
As the transaction<\/a> runs for a long time, we want to periodically check how many rows have been inserted into the table<\/a> so far. <\/p>\n\n\n\n
So, we open another query window, and run the SELECT COUNT(*) FROM TestTable<\/code> query.<\/p>\n\n\n\n
This query waits until the first query finishes and then returns the result:<\/p>\n\n\n\n
\"The<\/figure>\n\n\n\n
\"The<\/figure>\n\n\n\n
How can we rewrite the query to check the number of rows inserted so far without waiting for the transaction to complete? Let’s find out.<\/p>\n\n\n\n
The solution: read uncommitted data with NOLOCK<\/code> or READ UNCOMMITTED<\/code> (how-to guide)<\/h2>\n\n\n\n
The reason the second query waits for the first transaction to complete before returning the result is that it uses the READ COMMITTED<\/code> transaction isolation level<\/a> by default (this example assumes the database is using the default locking behavior for the READ COMMITTED<\/code> isolation level). <\/strong><\/p>\n\n\n\n
This isolation level prevents dirty reads<\/strong>.<\/p>\n\n\n\n
In simple terms, showing the row count while rows are still being inserted into the table may not<\/em> be accurate because the transaction has yet to be completed (so can still be rolled back<\/a>.)<\/p>\n\n\n\n
However, since we want to check the number of inserted rows at a specific moment, we can allow dirty reads – bearing in mind that the result may include rows that can be rolled back later on. <\/p>\n\n\n\n
To do this, in the second query, we can use the WITH (NOLOCK)<\/strong><\/code> table hint<\/a> (Solution 1), or set the transaction isolation level for the session to READ UNCOMMITTED<\/strong><\/code> (Solution 2):<\/p>\n\n\n\n
-- Solution 1: Read uncommitted data without waiting for the transaction to complete\nSELECT COUNT(*) \nFROM TestTable WITH (NOLOCK)<\/pre><\/div>\n\n\n\n
\"image<\/figure>\n\n\n\n
-- Solution 2: Set the isolation level to read uncommitted data\nSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED\n\nSELECT COUNT(*) \nFROM TestTable\n\n-- Optional: reset the isolation level back to the default\nSET TRANSACTION ISOLATION LEVEL READ COMMITTED<\/pre><\/div>\n\n\n\n
\"image<\/figure>\n\n\n\n
It’s worth mentioning that NOLOCK<\/strong><\/code> can also produce inaccurate results due to concurrent data modifications, such as missing or reading the same rows multiple times.<\/p>\n\n\n\n
The problem: a transaction reads different values for the same row twice<\/h2>\n\n\n\n
Suppose we have TestTable<\/code> with the following structure:<\/p>\n\n\n\n
IF DB_ID(N'TestDB') IS NULL\n    EXEC('CREATE DATABASE TestDB');\nGO\n\nUSE TestDB\nGO\n\nDROP TABLE IF EXISTS TestTable;\nGO\n\n-- Create TestTable\nCREATE TABLE TestTable\n(\n    ID INT PRIMARY KEY, \n    FirstName VARCHAR(40) NOT NULL,\n    LastName VARCHAR(40) NOT NULL,\n    Email VARCHAR(254) NOT NULL\n)\n\n-- Create a unique index on the Email column\nCREATE UNIQUE INDEX UIX_TestTable_Email \nON TestTable(Email)\n\n-- Insert sample data into TestTable\nINSERT INTO TestTable(ID, FirstName, LastName, Email)\nVALUES  (1, 'FirstName1', 'LastName1', 'user1@example.com'),\n        (2, 'FirstName2', 'LastName2', 'user2@example.com'),\n        (3, 'FirstName3', 'LastName3', 'user3@example.com');<\/pre><\/div>\n\n\n\n
We are running the following transaction, which has many other statements between \u201cStatement 1\u201d and \u201cStatement 2\u201d. <\/p>\n\n\n\n
In this example, we are using W<\/strong><\/a>AITFOR DELAY<\/strong><\/a><\/code> instead of these statements to simplify the demonstration:<\/p>\n\n\n\n
USE TestDB\nGO\n\n-- Query 1\nDECLARE @fname VARCHAR(40),\n        @lname VARCHAR(40),\n        @email VARCHAR(254) = 'user2@example.com'\n\nBEGIN TRANSACTION;\n\n    -- Statement 1: Get the first name by email\n    SET @fname = (SELECT FirstName FROM TestTable WHERE Email = @email)\n\n    -- Simulate other statements running between Statement 1 and Statement 2\n    WAITFOR DELAY '00:00:05'\n\n    -- Statement 2: Get the last name by email\n    SET @lname = (SELECT LastName FROM TestTable WHERE Email = @email)\n\nCOMMIT\n\nSELECT @fname + ' ' + @lname AS FullName<\/pre><\/div>\n\n\n\n
After \u201cStatement 1\u201d finishes and before \u201cStatement 2\u201d starts, \u201cQuery 2\u201d is executed in another query window:<\/p>\n\n\n\n
USE TestDB\nGO\n\n-- Query 2\nUPDATE TestTable\nSET Email = 'user4@example.com'\nWHERE Email = 'user2@example.com'\n\nUPDATE TestTable\nSET Email = 'user2@example.com'\nWHERE Email = 'user3@example.com'<\/pre><\/div>\n\n\n\n
When \u201cQuery 1\u201d finishes, we can see that the result of the last SELECT<\/strong><\/a><\/code> statement is confusing as there’s no person with the full name returned by the query:<\/p>\n\n\n\n
\"image<\/figure>\n\n\n\n
However, the final SELECT<\/code> statement needs to return the full name of an existing person.<\/p>\n\n\n\n
How can we modify \u201cQuery 1\u201d to make it work correctly without changing \u201cStatement 1\u201d and \u201cStatement 2\u201d, which must assign the first and last names of the same person to the variables, respectively?<\/p>\n\n\n\n
Let’s find out.<\/p>\n\n\n\n
The solution: use REPEATABLE READ<\/code> to prevent non-repeatable reads (how-to)<\/h2>\n\n\n\n
The issue happens because the default READ COMMITTED<\/code> isolation level protects each individual statement from reading uncommitted data. <\/strong><\/p>\n\n\n\n
It does not<\/em>, however, guarantee that two separate SELECT<\/code> statements inside the same transaction will read the same version of the data.<\/p>\n\n\n\n
In this example, after \u201cStatement 1\u201d reads the first name for user2@example.com<\/code>, another query can update the email values before \u201cStatement 2\u201d reads the last name. This results in both \u201cStatement 1\u201d and \u201cStatement 2\u201d being able to read data from different rows. <\/p>\n\n\n\n
To solve this problem, we can use the REPEATABLE READ<\/code> isolation level<\/strong>. It keeps shared locks on the rows read by the transaction until<\/em> the transaction is actually complete, so another query can’t update the rows between \u201cStatement 1\u201d and \u201cStatement 2\u201d.<\/p>\n\n\n\n
As a result, the first name and last name will be read from the same person. <\/p>\n\n\n\n
Before testing the solution, recreate TestTable<\/code> using the original setup script:<\/p>\n\n\n\n
USE TestDB\nGO\n\n-- Query 1\nSET TRANSACTION ISOLATION LEVEL REPEATABLE READ;\n\nDECLARE @fname VARCHAR(40),\n        @lname VARCHAR(40),\n        @email VARCHAR(254) = 'user2@example.com'\n\nBEGIN TRANSACTION;\n\n    -- Statement 1: Get the first name by email\n    SET @fname = (SELECT FirstName FROM TestTable WHERE Email = @email)\n\n    -- Simulate other statements running between Statement 1 and Statement 2\n    WAITFOR DELAY '00:00:05'\n\n    -- Statement 2: Get the last name by email\n    SET @lname = (SELECT LastName FROM TestTable WHERE Email = @email)\n\nCOMMIT\n\nSELECT @fname + ' ' + @lname AS FullName;\n\n-- Optional: reset the isolation level back to the default\nSET TRANSACTION ISOLATION LEVEL READ COMMITTED<\/pre><\/div>\n\n\n\n
\"an<\/figure>\n\n\n\n
\n    
\n        
\n            
\n                
Fast, reliable and consistent SQL Server development…<\/h3>\n                
\n                                            …with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                                            Learn more & try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
The problem: a transaction gets a different row count without inserting or deleting anything itself<\/h2>\n\n\n\n
Let’s consider a table named TestTable<\/code> with the following structure:<\/p>\n\n\n\n
IF DB_ID(N'TestDB') IS NULL\n    EXEC('CREATE DATABASE TestDB');\nGO\n\nUSE TestDB\nGO\n\nDROP TABLE IF EXISTS TestTable;\nGO\n-- Create TestTable\nCREATE TABLE TestTable\n(\n    ID INT PRIMARY KEY, \n    Value INT\n)\n\n-- Insert sample data into TestTable\nINSERT INTO TestTable(ID, Value)\nVALUES  (1, 10),\n        (3, 30),\n        (5, 50);<\/pre><\/div>\n\n\n\n
We are running the following transaction, which has many other statements between \u201cStatement 1\u201d and \u201cStatement 2\u201d. We use WAITFOR DELAY<\/code> instead of these statements to simplify the demonstration:<\/p>\n\n\n\n
USE TestDB\nGO\n-- Query 1\nBEGIN TRANSACTION\n\n    -- Statement 1: Get the current row count\n    SELECT COUNT(*) AS CountOfRows \n    FROM TestTable\n\n    -- Simulate other statements running between Statement 1 and Statement 2\n    WAITFOR DELAY '00:00:05'\n\n    -- Statement 2: Get the current row count again\n    SELECT COUNT(*) AS CountOfRows \n    FROM TestTable\n\nCOMMIT<\/pre><\/div>\n\n\n\n
After \u201cStatement 1\u201d finishes and before \u201cStatement 2\u201d starts, \u201cQuery 2\u201d is executed in another query window:<\/p>\n\n\n\n
USE TestDB\nGO\n-- Query 2\n-- Insert new rows into TestTable from another session\nINSERT INTO TestTable(ID, Value)\nVALUES  (2, 20),\n        (4, 40),\n        (6, 60);<\/pre><\/div>\n\n\n\n
When \u201cQuery 1\u201d finishes, we can see that different row counts are returned for TestTable<\/code> at the beginning and at the end of our transaction (although we didn’t add or remove any rows in the current transaction):<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
However, we need to get the same row count for TestTable<\/code> even when we don’t add or delete rows in the current transaction.<\/p>\n\n\n\n
How, then, can we modify \u201cQuery 1\u201d to make it work correctly? After all, \u201cStatement 1\u201d and \u201cStatement 2\u201d must return the same result if the row count hasn’t changed inside the current transaction.<\/p>\n\n\n\n
Here’s how to ensure that happens.<\/p>\n\n\n\n
The solution: use SERIALIZABLE<\/code> to prevent phantom reads (how-to)<\/h2>\n\n\n\n
The issue happens because, under the default READ COMMITTED<\/code> isolation level, \u201cStatement 1\u201d and \u201cStatement 2\u201d are executed separately and another session can insert new rows between them. <\/p>\n\n\n\n
As a result, the row count can be different at the beginning and<\/em> at the end of the same transaction.<\/p>\n\n\n\n
To avoid this, we can use the SERIALIZABLE<\/code> isolation level<\/strong>. This prevents SQL Server from allowing another transaction to insert new rows into the range read by the current transaction until it’s complete – meaning \u201cStatement 1\u201d and \u201cStatement 2\u201d will return the same row count.<\/p>\n\n\n\n
Before testing the solution, recreate TestTable<\/code> using the original setup script:<\/p>\n\n\n\n
USE TestDB\nGO\n\n-- Query 1\nSET TRANSACTION ISOLATION LEVEL SERIALIZABLE;\n\nBEGIN TRANSACTION\n\n    -- Statement 1: Get the current row count\n    SELECT COUNT(*) AS CountOfRows \n    FROM TestTable\n\n    -- Simulate other statements running between Statement 1 and Statement 2\n    WAITFOR DELAY '00:00:05'\n\n    -- Statement 2: Get the current row count again\n    SELECT COUNT(*) AS CountOfRows \n    FROM TestTable\n\nCOMMIT;\n\n-- Optional: reset the isolation level back to the default\nSET TRANSACTION ISOLATION LEVEL READ COMMITTED<\/pre><\/div>\n\n\n\n
\"image<\/figure>\n\n\n\n
The problem: a rolled-back transaction wipes out the audit record with it<\/h2>\n\n\n\n
We need to audit user logins in the system. We have a LoginAudit<\/code> table to store information about user logins:<\/p>\n\n\n\n
IF DB_ID(N'TestDB') IS NULL\n    EXEC('CREATE DATABASE TestDB');\nGO\n\nUSE TestDB\nGO\n\n-- Drop tables if they already exist\nDROP TABLE IF EXISTS LoginAudit\nDROP TABLE IF EXISTS UserData\nGO\n\n-- Create UserData table\nCREATE TABLE UserData\n(\n    ID INT PRIMARY KEY, \n    LoginName VARCHAR(254) NOT NULL,\n    PassHash BINARY(64) NOT NULL\n)\n\n-- Create a unique index on the LoginName column\nCREATE UNIQUE INDEX UIX_UserData_LoginName \nON UserData(LoginName)\n\n-- Insert sample user data\nINSERT INTO UserData(ID, LoginName, PassHash)\nVALUES (1, 'user1@example.com', HASHBYTES('SHA2_512', 'abc123'))\n\n-- Create LoginAudit table\nCREATE TABLE LoginAudit\n(\n    LoginName VARCHAR(254), \n    ActionType VARCHAR(10), \n    Result VARCHAR(7), \n    ActionDate DATETIME DEFAULT GETDATE()\n)<\/pre><\/div>\n\n\n\n
User authentication occurs within a transaction, which may be rolled back:<\/p>\n\n\n\n
-- Log in and audit\nDECLARE @LoginName VARCHAR(254) = 'user1@example.com',\n        @pass VARCHAR(50) = 'abc123',\n        @Result VARCHAR(10)\n\nIF OBJECT_ID('tempdb..#tmpAudit') IS NOT NULL\n    DROP TABLE #tmpAudit\n\n-- Create a temporary table to store audit information\nCREATE TABLE #tmpAudit\n(\n    LoginName VARCHAR(254), \n    ActionType VARCHAR(10), \n    Result VARCHAR(7)\n)\n\nBEGIN TRANSACTION\n\n    -- Check user credentials\n    IF EXISTS \n    (\n        SELECT ID \n        FROM UserData \n        WHERE LoginName = @LoginName \n          AND PassHash = HASHBYTES('SHA2_512', @pass)\n    )\n        SET @Result = 'Success'\n    ELSE \n        SET @Result = 'Fail'\n\n    -- Insert audit information into the temporary table\n    INSERT INTO #tmpAudit(LoginName, ActionType, Result)\n    VALUES(@LoginName, 'Log In', @Result)\n\n-- For simplicity, we manually roll back the transaction here to simulate a case where something went wrong.\n\nROLLBACK\n\n-- Insert audit information into LoginAudit table\nINSERT INTO LoginAudit(LoginName, ActionType, Result)\nSELECT LoginName, ActionType, Result\nFROM #tmpAudit\n\nSELECT @Result AS Result\n\nSELECT * \nFROM LoginAudit<\/pre><\/div>\n\n\n\n
When the transaction is rolled back, we can see that there are no audit records for this login:<\/p>\n\n\n\n
\"Image<\/figure>\n\n\n\n
So, how can we modify the code above to record user login information even when the transaction is rolled back? Let’s find out.<\/p>\n\n\n\n
The solution: use a table variable to survive a rollback (and how to do it)<\/h2>\n\n\n\n
We can use a table variable instead of a temporary table inside the transaction to store audit information. This is because ROLLBACK TRANSACTION<\/a><\/code> does not<\/em> erase changes made to table variables. <\/strong><\/p>\n\n\n\n
After the transaction is rolled back, we can still insert the audit information from the table variable into the LoginAudit<\/code> table:<\/p>\n\n\n\n
-- Log in and audit\nDECLARE @LoginName VARCHAR(254) = 'user1@example.com',\n        @pass VARCHAR(50) = 'abc123',\n        @Result VARCHAR(10)\n\n-- Declare a table variable to store audit information\nDECLARE @tbvAudit AS TABLE\n(\n    LoginName VARCHAR(254), \n    ActionType VARCHAR(10), \n    Result VARCHAR(7)\n)\n\nBEGIN TRANSACTION\n\n    -- Check user credentials\n    IF EXISTS \n    (\n        SELECT ID \n        FROM UserData \n        WHERE LoginName = @LoginName \n          AND PassHash = HASHBYTES('SHA2_512', @pass)\n    )\n        SET @Result = 'Success'\n    ELSE \n        SET @Result = 'Fail'\n\n    -- Insert audit information into the table variable\n    INSERT INTO @tbvAudit(LoginName, ActionType, Result)\n    VALUES(@LoginName, 'Log In', @Result)\n-- For simplicity, we manually roll back the transaction here to simulate a case where something went wrong.\nROLLBACK\n\n-- Insert audit information into LoginAudit table\nINSERT INTO LoginAudit(LoginName, ActionType, Result)\nSELECT LoginName, ActionType, Result\nFROM @tbvAudit\n\nSELECT @Result AS Result\n\nSELECT * \nFROM LoginAudit<\/pre><\/div>\n\n\n\n
We can now see the result in the LoginAudit<\/code> table:<\/p>\n\n\n\n
\"Image<\/figure>\n\n\n\n
Please note that this example is simplified for demonstration purposes. In real systems, the password verification logic, authentication and audit logging should be designed carefully based on security and compliance<\/a> requirements.<\/p>\n\n\n\n
The problem: a nested rollback undoes more than it should<\/h2>\n\n\n\n
In the previous task, we used a table variable to keep audit information after rollback. Now, let\u2019s consider another case where we work with permanent tables inside the transaction and need to roll back only part of the work. <\/p>\n\n\n\n
Suppose we have both the LoginAudit<\/code> and UserLogin<\/code> tables:<\/p>\n\n\n\n
IF DB_ID(N'TestDB') IS NULL\n    EXEC('CREATE DATABASE TestDB');\nGO\n\nUSE TestDB\nGO\n\n-- Drop tables if they already exist\nDROP TABLE IF EXISTS UserLogin\nDROP TABLE IF EXISTS LoginAudit\nGO\n\n-- Create LoginAudit table\nCREATE TABLE LoginAudit\n(\n    LoginName VARCHAR(254), \n    ActionType VARCHAR(10), \n    Result VARCHAR(7), \n    ActionDate DATETIME DEFAULT GETDATE()\n)\n\n-- Create UserLogin table\nCREATE TABLE UserLogin\n(\n    ID INT IDENTITY(1,1) PRIMARY KEY,\n    LoginName VARCHAR(254),\n    LoginDate DATETIME DEFAULT GETDATE()\n)<\/pre><\/div>\n\n\n\n
We need to insert login audit information into LoginAudit<\/code> first, and then<\/em> insert login information into UserLogin<\/code>. <\/p>\n\n\n\n
If something goes wrong during the insert into UserLogin<\/code>, we need to roll back that insert only<\/em>, but still also need to keep the audit record in LoginAudit<\/code>. <\/p>\n\n\n\n
A developer wrote the following code using a nested transaction<\/strong><\/a>, expecting that the ROLLBACK<\/code> within it would only<\/em> roll back the insert into UserLogin<\/code>:<\/p>\n\n\n\n
-- Log in and audit\nDECLARE @LoginName VARCHAR(254) = 'user1@example.com',\n        @SomethingWentWrong BIT = 1\n\nBEGIN TRANSACTION\n\n    -- Statement 1: Insert audit information\n    INSERT INTO LoginAudit(LoginName, ActionType, Result)\n    VALUES(@LoginName, 'Log In', 'Fail')\n\n    -- Nested transaction\n    BEGIN TRANSACTION\n\n        -- Statement 2: Insert login information\n        INSERT INTO UserLogin(LoginName)\n        VALUES(@LoginName)\n\n        -- For simplicity, we use this variable to simulate a case\n        -- where something went wrong while inserting login information.\n        IF @SomethingWentWrong = 1\n        BEGIN\n            ROLLBACK \n        END\n        ELSE\n        BEGIN\n            -- Commit the nested transaction\n            COMMIT \n\n            -- Commit the outer transaction\n            COMMIT \n        END\n\nSELECT *\nFROM LoginAudit\n\nSELECT *\nFROM UserLogin<\/pre><\/div>\n\n\n\n
However, when ROLLBACK TRANSACTION<\/code> is executed, the<\/strong> whole transaction is rolled back<\/strong> – and the audit record isn’t saved either:<\/p>\n\n\n\n
\"image<\/figure>\n\n\n\n
So, how can we modify the code to only<\/em> roll back the insert into UserLogin<\/code> and, at the same time, keep the audit record in LoginAudit<\/code>? Let’s find out.<\/p>\n\n\n\n
The solution: use SAVE TRANSACTION<\/code> to roll back to a savepoint (and how to do so)<\/h2>\n\n\n\n
The issue happens because ROLLBACK TRANSACTION<\/code> inside a nested transaction does not<\/em> just roll back the inner transaction – in SQL Server, it rolls back the outer transaction as well. <\/strong><\/p>\n\n\n\n
The only exception is if a savepoint<\/a><\/strong> is specified in the ROLLBACK<\/code> statement. And, since in our case both<\/em> the insert into LoginAudit<\/code> and<\/em> the insert into UserLogin<\/code> are rolled back, we can use the savepoint as our solution.<\/p>\n\n\n\n
After inserting the audit information into LoginAudit<\/code>, we can create a savepoint by using SAVE TRANSACTION<\/strong><\/code>. <\/p>\n\n\n\n
Then, if something goes wrong while inserting into UserLogin<\/code>, we can roll back to that savepoint only<\/em>, and commit the remaining part of the transaction:<\/p>\n\n\n\n
-- Log in and audit\nDECLARE @LoginName VARCHAR(254) = 'user1@example.com',\n        @SomethingWentWrong BIT = 1\n\nBEGIN TRANSACTION\n\n    -- Statement 1: Insert audit information\n    INSERT INTO LoginAudit(LoginName, ActionType, Result)\n    VALUES(@LoginName, 'Log In', 'Fail')\n\n    -- Create a savepoint after inserting audit information\n    SAVE TRANSACTION AuditInserted\n\n    -- Statement 2: Insert login information\n    INSERT INTO UserLogin(LoginName)\n    VALUES(@LoginName)\n\n    -- For simplicity, we use this variable to simulate a case\n    -- where something went wrong while inserting login information.\n    IF @SomethingWentWrong = 1\n    BEGIN\n        -- Roll back only the work done after the savepoint\n        ROLLBACK TRANSACTION AuditInserted\n    END\n\nCOMMIT \n\nSELECT *\nFROM LoginAudit\n\nSELECT *\nFROM UserLogin<\/pre><\/div>\n\n\n\n
As a result, the insert into UserLogin<\/code> will be rolled back, but the audit record in LoginAudit<\/code> will be saved:<\/p>\n\n\n\n
\"an<\/figure>\n\n\n\n
It’s worth noting that in real-world scenarios, some errors can leave a transaction in an uncommittable state<\/strong>, and rolling back to a savepoint becomes impossible. <\/p>\n\n\n\n
Also be aware that, for simplicity, this example only demonstrates a failed operation.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
Working with transactions can be tricky, and writing transaction-heavy SQL code requires a fundamental understanding of transaction management nuances, isolation levels, rollback mechanisms, and more. <\/p>\n\n\n\n
In this article, I’ve shed some light on a handful of common mistakes in transaction management (and offered potential solutions to them) – but there are many others. In general, a deep understanding of the theory is strongly recommended.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Subscribe to the Simple Talk newsletter<\/h3>\n                
\n                                            All the latest articles, guides, podcasts, and more – delivered straight to your inbox.                                    <\/div>\n            <\/div>\n                                            Subscribe now<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
\n    
FAQs<\/h2>\n\n                        
1. Why does NOLOCK return inaccurate results in SQL Server?<\/h3>\n            
\n                
NOLOCK<\/code> (or READ UNCOMMITTED<\/code>) reads uncommitted data that could still be rolled back, and can also produce inaccurate counts from concurrent modifications \u2014 missing rows or reading the same row multiple times. It’s a trade-off for avoiding waits, not a free performance win.<\/p>\n            <\/div>\n                    
2. Does ROLLBACK undo changes made to a table variable in SQL Server?<\/h3>\n            
\n                
No \u2014 the article demonstrates this directly: ROLLBACK<\/code> erases changes to a temp table but not to a table variable, which is why a table variable can be used to preserve audit information even when the surrounding transaction is rolled back.<\/p>\n            <\/div>\n                    
3. Does ROLLBACK inside a nested transaction only undo the inner transaction in SQL Server?<\/h3>\n            
\n                
No. A plain ROLLBACK<\/code> inside a nested transaction rolls back the entire outer transaction too. The only way to undo just part of the work is SAVE TRANSACTION<\/code> to create a named savepoint, then ROLLBACK TRANSACTION <savepoint_name><\/code> to roll back to just that point.<\/p>\n            <\/div>\n            <\/section>\n\n\n\n
Useful resources to help develop your understanding of transactions<\/h2>\n\n\n
\n