Transaction Management
Master database transactions and ensure data integrity in your applications.
Database transactions are units of work that must be executed atomically and consistently. Understanding transaction management is crucial for maintaining data integrity and handling concurrent operations in your database applications.
ACID Properties
Atomicity
All operations in a transaction must succeed, or all changes are rolled back. This ensures that the database remains in a consistent state even if errors occur.
Consistency
Transactions must maintain database consistency by enforcing integrity constraints, foreign keys, and business rules.
Isolation
Concurrent transactions must not interfere with each other. Each transaction should appear to execute in isolation.
Durability
Once a transaction is committed, its changes must persist even in the event of system failures.
Basic Transaction Structure
A simple transaction typically follows this pattern:
-- Basic transaction structure
BEGIN TRANSACTION;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;
COMMIT;Error Handling
Proper error handling is crucial for maintaining data integrity:
-- Transaction with error handling
BEGIN TRANSACTION;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
ROLLBACK;
RAISERROR ('Transfer failed', 16, 1);
RETURN;
END
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
ROLLBACK;
RAISERROR ('Transfer failed', 16, 1);
RETURN;
END
COMMIT;Key Points
- •Always check for errors after each operation
- •Roll back on any failure
- •Provide meaningful error messages
Isolation Levels
Different isolation levels provide different guarantees for concurrent transactions:
-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT * FROM orders
WHERE status = 'pending'
FOR UPDATE;
UPDATE orders
SET status = 'processing'
WHERE status = 'pending';
COMMIT;Common Levels
- •READ UNCOMMITTED
- •READ COMMITTED
- •REPEATABLE READ
- •SERIALIZABLE
Considerations
- •Higher isolation = lower concurrency
- •Balance consistency vs. performance
- •Consider business requirements
Deadlock Handling
Properly handling deadlocks is crucial for robust applications:
-- Deadlock handling
BEGIN TRY
BEGIN TRANSACTION;
SET DEADLOCK_PRIORITY LOW;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
-- Add delay to simulate concurrent transactions
WAITFOR DELAY '00:00:01';
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK;
-- Handle deadlock error
IF ERROR_NUMBER() = 1205
PRINT 'Deadlock detected. Retrying...';
THROW;
END CATCH;Practice Examples
Try these examples to practice transaction management:
Basic Money Transfer
BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;
Order Processing
BEGIN TRANSACTION; UPDATE orders SET status = 'processing' WHERE id = 1; UPDATE inventory SET stock = stock - 1 WHERE product_id = 100; INSERT INTO order_history (order_id, status, timestamp) VALUES (1, 'processing', CURRENT_TIMESTAMP); COMMIT;
Handling Errors
BEGIN TRY BEGIN TRANSACTION; -- Transaction logic here IF @error_condition = 1 THROW 51000, 'Custom error message', 1; COMMIT; END TRY BEGIN CATCH ROLLBACK; -- Error handling logic END CATCH;
Next Steps
Deepen your understanding of database management: