How to Manage Transactions and Locking in SQL.(11)
SQL is a powerful language for manipulating data in relational databases. However, when multiple users or applications access the same database concurrently, there is a risk of data inconsistency or corruption. To prevent this, SQL provides mechanisms for managing transactions and locking.
In this blog post, you will learn what transactions and locking are, why they are important, and how to use them in SQL. This blog post is part of a series of SQL Learning guides that I am writing to help students and beginners learn SQL. If you want to join this SQL series learning community, follow to my LinkedIn and stay tuned for more updates.
What is a Transaction?
A transaction is a logical unit of work that consists of one or more SQL statements that are executed as a whole. A transaction has four properties, known as ACID →: Refer my blog (Understanding ACID Properties)
- Atomicity: A transaction either succeeds or fails as a whole. If any statement in the transaction fails, the entire transaction is rolled back and the database is restored to its previous state.
- Consistency: A transaction preserves the integrity and validity of the database. It ensures that the database satisfies all the constraints and rules before and after the transaction.
- Isolation: A transaction is isolated from other concurrent transactions. It does not see the intermediate changes made by other transactions, and it does not affect other transactions until it commits.
- Durability: A transaction’s effects are permanent and persistent. Once a transaction commits, its changes are saved in the database and cannot be lost, even in the event of a system failure.
To start a transaction, you can use the BEGIN TRANSACTION statement. To end a transaction, you can use either the COMMIT statement or the ROLLBACK statement. The COMMIT statement saves the changes made by the transaction to the database, while the ROLLBACK statement discards the changes and restores the database to its previous state.
For example, suppose you have a table called accounts that stores the balance of different users. You want to transfer 1000 from user A to user B. You can use the following transaction to do this:
SQL
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE user = 'A';
UPDATE accounts SET balance = balance + 1000 WHERE user = 'B';
COMMIT;If the transaction succeeds, the balance of user A and user B will be updated accordingly. However, if there is an error in any of the statements, such as a negative balance or a non-existent user, the transaction will fail and the balance of both users will remain unchanged.
What is Locking?
Locking is a mechanism for controlling the access and modification of data by concurrent transactions. Locking ensures that only one transaction can modify a data item at a time, and that other transactions can only read the consistent and committed data.
There are different types of locks in SQL, such as:
- Shared lock: A shared lock allows a transaction to read a data item, but not to modify it. Multiple transactions can hold shared locks on the same data item at the same time, as long as there is no exclusive lock on it.
- Exclusive lock: An exclusive lock allows a transaction to modify a data item, but not to read it. Only one transaction can hold an exclusive lock on a data item at a time, and no other transaction can hold any lock on it.
- Update lock: An update lock is a special type of lock that allows a transaction to read a data item and to request an exclusive lock on it later. It prevents other transactions from acquiring an exclusive lock on the same data item, but it allows them to acquire a shared lock.
SQL Server uses a locking strategy called optimistic concurrency control, which means that it assumes that conflicts between transactions are rare and that transactions can proceed without locking the data. However, if a conflict does occur, such as two transactions trying to update the same data item, SQL Server will detect it and roll back one of the transactions.
For example, suppose you have a table called products that stores the name and price of different products. You want to update the price of a product based on its current value. You can use the following transaction to do this:
SQL
BEGIN TRANSACTION;
SELECT price FROM products WHERE name = 'Laptop' WITH (UPDLOCK);
-- Do some calculations based on the current price
UPDATE products SET price = price * 1.1 WHERE name = 'Laptop';
COMMIT;The WITH (UPDLOCK) clause acquires an update lock on the selected row, which prevents other transactions from updating the same row, but allows them to read it. This ensures that the price of the laptop will not change between the SELECT and the UPDATE statements.
A diagram showing the effect of an update lock on the products table.
Conclusion
Transactions and locking are essential concepts for ensuring the consistency and integrity of data in SQL. Transactions allow you to execute a group of SQL statements as a single unit of work, while locking allows you to control the access and modification of data by concurrent transactions. By understanding and applying these concepts, you can avoid data inconsistency or corruption and improve the performance and reliability of your SQL applications.
If you found this blog post helpful, please like 👍, share, and clap 👋 for it. You can also follow me on Medium and LinkedIn for more SQL tips and tricks. Remember, the best way to learn SQL is to practice and test your skills. If you want to learn SQL in depth and practice your SQL skills, check out LearnSQL, a comprehensive online platform that offers SQL courses, exercises, quizzes, and projects. Happy learning! 😊
For more information, please use the links below:
- Locking and Blocking Tips
- https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15
- https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15
“SQL is to databases what HTML is to the web.” — Tom Jewett
Comments
Post a Comment