A database transaction is a sequence of operations performed as a single logical unit of work. The key idea is that all the operations in a transaction either complete successfully together or fail completely. There’s no in-between. This ensures the database remains consistent even if something goes wrong, like a power outage or a failed query.
In simpler terms, a transaction lets you group multiple SQL statements into one reliable operation. If everything runs smoothly, you commit the changes. If not, you roll back the entire transaction, leaving the database exactly as it was before it started.
The ACID Principles
Every proper transaction follows four core properties, often summarized as ACID:
- Atomicity: The transaction is all or nothing. Either every part succeeds or it all gets rolled back.
- Consistency: All data must remain consistent before and after the transaction. All data will be valid according to all defined rules, including any constraints, cascades, and triggers that have been applied on the database.
- Isolation: Multiple transactions can run at the same time without interfering with each other. No transaction will be affected by any other transaction.
- Durability: Once committed, the results remain in the database, even if the system crashes.
These principles ensure data integrity and reliability, especially in multi-user or high-volume systems.
Example of an Explicit Transaction
Imagine a banking system where one customer transfers money to another. If one step fails (say, updating one account balance), we don’t want half the money to disappear or duplicate. So both updates must happen together.
Let’s create and populate the accounts table:
-- Create the accounts table
CREATE TABLE accounts (
account_id INT PRIMARY KEY,
account_name VARCHAR(100),
balance DECIMAL(10,2)
);
-- Insert sample data
INSERT INTO accounts (account_id, account_name, balance)
VALUES
(1, 'Nola', 1000.00),
(2, 'Dylan', 500.00);Now let’s move 200 from Nola’s account to Dylan’s:
-- Start a transaction
BEGIN TRANSACTION;
-- Deduct from Nola
UPDATE accounts
SET balance = balance - 200
WHERE account_id = 1;
-- Add to Dylan
UPDATE accounts
SET balance = balance + 200
WHERE account_id = 2;
-- Check for errors (conceptually; in code you’d check this in your app logic)
-- If all good:
COMMIT;
-- If something went wrong (e.g., Nola's account didn’t exist):
-- ROLLBACK;
-- Check the result
SELECT * FROM accounts;Result:
account_id account_name balance
---------- ------------ -------
1 Nola 800
2 Dylan 700
This code works in SQL Server and should work in most other major RDBMSs without too much modification. In some cases you might need to use START TRANSACTION instead of BEGIN TRANSACTION.
In the above example the ROLLBACK part is just a placeholder for actual code. In reality we might put the transaction into a TRY/CATCH block so that we can rollback the transaction only if it encounters an issue. For example:
-- Begin the transaction with error handling
BEGIN TRY
BEGIN TRANSACTION;
-- Deduct from Nola
UPDATE accounts
SET balance = balance - 200
WHERE account_id = 1;
-- Add to Dylan
UPDATE accounts
SET balance = balance + 200
WHERE account_id = 2;
-- If both succeed, commit
COMMIT TRANSACTION;
PRINT 'Transaction committed successfully.';
END TRY
BEGIN CATCH
-- Something went wrong, rollback
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
PRINT 'Transaction failed. Rolling back changes.';
PRINT 'Error Message: ' + ERROR_MESSAGE();
END CATCH;
GO
-- View the final balances
SELECT * FROM accounts;Result:
account_id account_name balance
---------- ------------ -------
1 Nola 600
2 Dylan 900
This time we’ve removed another 200 from Nola’s account and added 200 to Dylan’s.
Example of an Autocommit Transaction
The previous example demonstrates how explicit transactions work. This is where we explicitly specify where the transaction starts and ends. In our case we used BEGIN TRANSACTION to start it and COMMIT TRANSACTION to end it or commit it.
But explicit transactions are only one of various types of transactions. Another one is the autocommit transaction. This is the simplest type of transaction (at least from a coding perspective). Here’s an example:
INSERT INTO accounts (account_id, account_name, balance)
VALUES (3, 'Kyle', 250.00);That’s it.
When this statement runs successfully, the database automatically commits the change – no BEGIN TRANSACTION or COMMIT needed. If the statement fails (for example, because of a primary key conflict), it automatically rolls back that single operation.
Transaction Modes
We’ve just seen examples of two common transaction modes that are used in SQL programming. Different RDBMSs handle transactions in slightly different ways, but most of them support several transaction modes that control when transactions start and how they are committed or rolled back. These modes define whether each statement runs in its own transaction or whether multiple statements can be grouped together before committing.
Here’s a general overview of the common modes you’ll encounter across relational database systems:
| Mode | Description |
|---|---|
| Autocommit | Each individual SQL statement runs in its own transaction. If it succeeds, the database commits automatically; if it fails, the change is rolled back. This is the simplest and most common mode for general operations. |
| Explicit Transactions | You manually control when a transaction starts and ends. This allows you to group multiple operations so they either all succeed or all fail together. |
| Implicit Transactions | The database automatically starts a transaction when you execute a qualifying statement (like an UPDATE or INSERT), but you must explicitly issue COMMIT or ROLLBACK to finish it. Once you do, a new transaction begins automatically. |
| Manual Commit Mode (Autocommit Off) | The database stays in a continuous transaction until you explicitly commit or roll back. You decide when to make the changes permanent. |
| Chained Mode | After every commit or rollback, a new transaction automatically begins. It’s similar to implicit transactions, but the trigger for the next one is the completion of the previous. |
| Batch-scoped transaction | Automatically ends when the current batch of SQL statements finishes executing, whether you commit it or not. A batch is simply a group of SQL statements sent to the database as one unit of work (for example, everything you run at once in a script or code block). |
Transaction modes determine how safely and predictably data changes are applied:
- Autocommit is convenient but can’t guarantee multiple statements succeed or fail as a group.
- Explicit transactions give you full control and are the standard approach for critical or multi-step operations.
- Implicit and chained modes provide a balance between automation and safety, ensuring that no change is permanent until explicitly confirmed.
- Manual commit mode is often used for session-based workflows or long-running operations where multiple changes must be confirmed together.
- Batch-scoped transactions are tied to the execution batch itself. If a transaction isn’t committed or rolled back before the batch ends, the database automatically rolls it back when that batch completes.
No matter the mode, every relational database ultimately ensures that changes happen within a transactional context – the only difference is when those transactions begin and who is responsible for ending them.
Understanding the Role of Transactions
Without transactions, systems would be extremely fragile. Imagine losing power halfway through transferring money or inserting half of an order into a database. You’d end up with incomplete data, which can cause major issues down the line.
Transactions keep databases trustworthy. They ensure that even when things go wrong, the data doesn’t lie. That reliability is what makes modern applications possible.