7 questions foundWhat is a transaction in SQL Server, and why is grouping multiple statements into one important?
Beginner A transaction groups one or more SQL statements together so they are treated as a single unit of work, meaning either all of the statements succeed together and their changes are saved, or if something goes wrong, all of the changes are undone together, protecting your data from being left in an inconsistent, partially updated state.
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountId = 2;
COMMIT TRANSACTION;
Real-world example A bank transfer uses a transaction to ensure that money is either successfully moved from one account to another completely, or not moved at all if something fails, preventing money from disappearing or being duplicated.
Common follow-ups: What happens if the second statement in a transaction fails after the first one already succeeded?;What is the difference between COMMIT and ROLLBACK?
Error Handling with TRY CATCH;Isolation & Locking
What does ACID stand for, and what does each letter represent in the context of database transactions?
Beginner ACID stands for Atomicity, meaning a transaction fully succeeds or fully fails with nothing in between, Consistency, meaning a transaction always leaves the database in a valid state, Isolation, meaning concurrent transactions do not interfere with each other in unexpected ways, and Durability, meaning once a transaction is committed, its changes are permanently saved even if the system crashes immediately afterward.
-- A properly committed transaction guarantees all four ACID properties
BEGIN TRANSACTION;
UPDATE Inventory SET Quantity = Quantity - 1 WHERE ProductId = 5;
COMMIT TRANSACTION; -- durably saved once committed
Real-world example A payment processing system relies on all four ACID properties together, ensuring that a completed payment transaction is fully applied, leaves account balances valid, does not interfere with other simultaneous payments, and survives even a sudden server crash immediately after committing.
Common follow-ups: Which ACID property is most directly related to how isolation levels work?;How does durability actually get guaranteed at a technical level?
Isolation & Locking;Backup & Recovery
How do you use a SAVE TRANSACTION savepoint within a larger transaction to allow partial rollback of just a portion of the work?
Intermediate You mark a specific point within your transaction using SAVE TRANSACTION with a given name, and if you later need to undo only the changes made after that point, you can roll back specifically to that savepoint using ROLLBACK TRANSACTION with the same name, without undoing the entire transaction from the very beginning.
BEGIN TRANSACTION;
UPDATE Orders SET Status = 'Processing' WHERE OrderId = 1;
SAVE TRANSACTION BeforeRiskyStep;
UPDATE Inventory SET Quantity = Quantity - 1 WHERE ProductId = 5;
-- If something goes wrong here
ROLLBACK TRANSACTION BeforeRiskyStep;
COMMIT TRANSACTION;
Real-world example An order processing transaction uses a savepoint before a risky inventory update step, allowing it to roll back just that specific risky part if it fails, while still keeping the earlier order status update intact and committing the overall transaction.
Common follow-ups: Can you have multiple savepoints within a single transaction?;What happens to the transaction if you never explicitly roll back to a savepoint?
Error Handling with TRY CATCH;Isolation & Locking
What is the difference between an implicit and an explicit transaction in SQL Server?
Intermediate An explicit transaction is one you deliberately start with BEGIN TRANSACTION and end with COMMIT or ROLLBACK, giving you full control over exactly what statements are grouped together, while by default SQL Server treats each individual statement as its own implicit transaction, automatically committing it immediately if it succeeds without requiring any explicit transaction control statements.
-- Implicit: each statement is automatically its own transaction
UPDATE Orders SET Status = 'Shipped' WHERE OrderId = 1;
-- Explicit: multiple statements grouped together deliberately
BEGIN TRANSACTION;
UPDATE Orders SET Status = 'Shipped' WHERE OrderId = 1;
UPDATE Shipments SET ShippedDate = GETDATE() WHERE OrderId = 1;
COMMIT TRANSACTION;
Real-world example A developer wraps two related updates that must succeed or fail together inside an explicit transaction, rather than relying on the default implicit behavior that would treat each update as entirely independent of the other.
Common follow-ups: What are the risks of relying only on implicit transactions for a multi step business process?;Does SQL Server support any other transaction modes besides implicit and explicit?
Error Handling with TRY CATCH;Isolation & Locking
How would you design a distributed transaction that needs to span operations across two different database servers, and what additional complexity does this introduce?
Advanced You would typically use the Microsoft Distributed Transaction Coordinator, which manages a two phase commit protocol across the involved servers, first asking each server to prepare its portion of the transaction, and only committing on both servers once all participants confirm they are ready, though this introduces additional complexity, latency, and a dependency on the coordinator service being available and properly configured.
BEGIN DISTRIBUTED TRANSACTION;
UPDATE ServerA.SalesDB.dbo.Orders SET Status = 'Complete' WHERE OrderId = 1;
UPDATE ServerB.InventoryDB.dbo.Stock SET Quantity = Quantity - 1 WHERE ProductId = 5;
COMMIT TRANSACTION;
Real-world example A company synchronizing an order update across two separate database servers uses a distributed transaction to guarantee that both the order status and inventory quantity are updated together consistently, even though they live in completely separate systems.
Common follow-ups: What happens if one of the two servers becomes unavailable during a distributed transaction?;What are the performance tradeoffs of using distributed transactions compared to single server transactions?
Linked Servers;Isolation & Locking
How do long running transactions negatively impact overall system performance, and what strategies help avoid this problem?
Advanced A long running transaction holds its locks for an extended period, potentially blocking many other transactions trying to access the same data, and can also cause the transaction log to grow significantly since its log records cannot be cleared until the transaction finishes, so strategies to avoid this include breaking large operations into smaller batches, minimizing the work done between BEGIN and COMMIT, and avoiding user interaction or slow external calls within an open transaction.
-- Avoid holding a transaction open across many small batches
-- Instead, batch large updates into smaller, faster transactions
WHILE EXISTS (SELECT 1 FROM Orders WHERE Status = 'Pending' AND OrderId < 10000)
BEGIN
BEGIN TRANSACTION;
UPDATE TOP (1000) Orders SET Status = 'Processed' WHERE Status = 'Pending';
COMMIT TRANSACTION;
END;
Real-world example A large data cleanup process is redesigned to update records in smaller batches of one thousand rows at a time, each within its own short transaction, rather than one enormous, hours long transaction that had been blocking other users.
Common follow-ups: How do you identify long running transactions currently active on a server?;What is the relationship between long running transactions and transaction log growth?
Isolation & Locking;Backup & Recovery
How do you check whether a transaction is currently active and view its properties within a session?
Intermediate You can use the @@TRANCOUNT system function to check how many transactions are currently active and nested within the current session, and the XACT_STATE function to determine whether the current transaction, if any, is in a normal, committable state or has become an uncommittable state due to an earlier error.
BEGIN TRANSACTION;
PRINT @@TRANCOUNT; -- shows 1
SELECT XACT_STATE(); -- shows 1 for a normal, committable transaction
Real-world example A developer troubleshooting an unexpected error checks @@TRANCOUNT within their stored procedure to confirm exactly how many nested transactions are currently open, helping them understand and fix a mismatched BEGIN and COMMIT pairing.
Common follow-ups: What does an XACT_STATE value of negative one actually indicate?;How do nested transactions in SQL Server actually behave regarding commit and rollback?
Error Handling with TRY CATCH;Isolation & Locking