Transactions vs Locking Tables in SQL

1. Overview

In database management, maintaining data integrity while enabling multiple users to access and modify data simultaneously is a critical challenge. We often grapple with the question of ensuring data consistency without sacrificing performance. This is where SQL transactions and table locking come into play.

In this tutorial, we explore these two fundamental concepts and provide a practical guide on when to use each approach.

The examples in this article were based on a sample schema.

2. Transactions in SQL

A transaction in SQL is a sequence of one or more database operations executed as a single unit of work. It ensures that all operations within the transaction either complete successfully or have no effect at all. This all-or-nothing principle is important for maintaining data integrity in complex database operations.

2.1. ACID Properties

Transactions adhere to the ACID properties, which stand for atomicity, consistency, isolation, and durability:

• Atomicity: all operations in a transaction succeed or fail together
• Consistency: a transaction brings the database from one valid state to another
• Isolation: concurrent transactions don’t interfere with each other
• Durability: once a transaction is committed, its effects are permanent and survive system failures

These properties work together to ensure the reliability and integrity of database transactions.

2.2. Beginning and Committing Transactions

To start a transaction, we use the START TRANSACTION statement. Once we’ve completed all operations, we commit the changes using the COMMIT statement.

Let’s consider an example:

START TRANSACTION;

UPDATE Student SET gpa = 4.0 WHERE id = 1001;
INSERT INTO Course (id, name, textbook, credits, is_active, department_id) 
VALUES ('CS999', 'Advanced SQL', 'SQL Mastery', 5, 'Yes', 1);

COMMIT;

In this transaction, we update a student’s GPA and add a new course. Thus, if either operation fails, the entire transaction is rolled back, ensuring the database remains in a consistent state.

Further, the COMMIT statement at the end makes these changes permanent in the database.

2.3. Rolling Back Changes

Sometimes, we might need to undo the changes made within a transaction.

We can use the ROLLBACK statement for this:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT gpa FROM Student WHERE id = 1001;
+------+
| gpa  |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

mysql> UPDATE Student SET gpa = 5.0 WHERE id = 1001;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> -- Oh no, we made a mistake! Let's roll back.
mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

mysql> -- The student's GPA remains unchanged.
mysql> SELECT gpa FROM Student WHERE id = 1001;
+------+
| gpa  |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

The transaction begins by selecting the GPA of the student with ID 1001 and then updating the GPA to 5.0. The transaction is rolled back, for example, due to a mistake. Thus, the update is canceled so that the student’s GPA remains unchanged. The final SELECT confirms we didn’t modify the GPA.

2.4. Using Savepoints

For more granular control within transactions, SQL provides savepoints. These create checkpoints within a transaction to which we can roll back without affecting the entire transaction:

mysql> START TRANSACTION;

UPDATE Course SET credits = credits + 1 WHERE id = 'CS111';
SAVEPOINT update_cs111;

UPDATE Course SET credits = credits + 1 WHERE id = 'CS112';
SAVEPOINT update_cs112;

-- Oops, we made a mistake on the second update
ROLLBACK TO SAVEPOINT update_cs111;

UPDATE Course SET credits = credits + 1 WHERE id = 'CS113';

COMMIT;
Query OK, 0 rows affected (0.00 sec)

In this transaction, we update the credit values for multiple courses. By using savepoints, we can roll back to a specific point if we detect an error, without undoing all changes. This flexibility can be particularly useful in complex transactions involving multiple related updates.

3. Locking Tables in SQL

Locking tables plays an important role in maintaining data integrity, especially in multi-user environments.

3.1. Purpose of Locking

To begin with, table locking serves to control concurrent access to data. It prevents multiple users or processes from modifying the same data simultaneously, which could otherwise lead to inconsistencies or data corruption.

For instance, let’s say we have two administrators trying to update the same student’s GPA at the same time in the database. Without proper locking, this scenario could result in lost updates or incorrect data.

3.2. Types of Locks

MySQL supports two primary types of locks:

• shared locks
• exclusive locks

Shared locks, also known as read locks, enable multiple transactions to read data concurrently without writing into it. Therefore, when a transaction acquires a shared lock on a piece of data, it can read that data. Other transactions can also acquire shared locks on the same data, allowing multiple transactions to read the data simultaneously. However, while a shared lock is held, any attempt by another transaction to acquire an exclusive lock on the same data will be blocked until all shared locks are released.

Exclusive locks, on the other hand, prevent other transactions from reading or modifying the locked data. These locks are used for write operations to ensure data consistency. When an exclusive lock is held on a table, no other transaction can read from or write to that table until the lock is released.

To illustrate, let’s consider an example:

mysql> -- Acquire a read lock on the Course table
mysql> LOCK TABLE Course READ;
Query OK, 0 rows affected (0.01 sec)
mysql> -- This SELECT operation is allowed
mysql> SELECT * FROM Course WHERE department_id = 1;
+-------+---------------------------------------------+------------------------------------------------------------------------+---------+-----------+---------------+
| id    | name                                        | textbook                                                               | credits | is_active | department_id |
+-------+---------------------------------------------+------------------------------------------------------------------------+---------+-----------+---------------+
| CS111 | Introduction to Operating Systems           | OS by Tanenbaum                                                        |       9 | Yes       |             1 |
| CS112 | Introduction to Real Time Operating Systems | Real-Time Operating Systems Book 1: The Theory by Cooling              |       8 | Yes       |             1 |
| CS113 | Introduction to Computer Architecture       | Computer Architecture by Patterson                                     |       8 | Yes       |             1 |
| CS121 | Introduction to Databases                   | Database Systems: The Complete Book                                    |       7 | Yes       |             1 |
| CS122 | Relational Databases                        | Database Systems: The Complete Book                                    |       7 | No        |             1 |
...
| CS535 | Non-Planar Graphs                           | Graph Theory by Diestel                                                |       5 | No        |             1 |
| CS999 | Advanced SQL                                | SQL Mastery                                                            |       5 | Yes       |             1 |
+-------+---------------------------------------------+------------------------------------------------------------------------+---------+-----------+---------------+
32 rows in set (0.01 sec)
mysql> -- But an UPDATE operation would be blocked
mysql> UPDATE Course SET credits = 4 WHERE id = 'CS101';
ERROR 1100 (HY000): Table 'department' was not locked with LOCK TABLES
mysql> -- Release the lock
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

In this example, we acquire a read lock on the Course table, allowing SELECT operations but preventing any UPDATE or other altering until we release the lock.

As another example, let’s perform a series of operations on the Student table with the WRITE lock:

mysql> -- Acquire an exclusive lock on the Student table
mysql> LOCK TABLES Student WRITE;
Query OK, 0 rows affected (0.02 sec)

mysql> -- Perform operations
mysql> UPDATE Student SET gpa = 4.0 WHERE id = 1001;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> INSERT INTO Student (id, name, national_id, enrollment_date)
    -> VALUES (2022, 'Jane Doe', 9876543210, '2023-09-01');
Query OK, 1 row affected (0.01 sec)

mysql> -- Release the lock
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

Notably, releasing locks with the UNLOCK TABLES statement to avoid holding locks longer than necessary is critical.

Further, we can apply locks at different levels:

• table-level locks affect the entire table
• row-level locks only lock specific rows

MySQL uses default row-level locking for InnoDB tables.

4. Comparative Analysis

Now that we’ve explored both transactions and table locks, let’s compare these two mechanisms across several key dimensions

4.1. Granularity and Scope

Transactions operate on a logical level, meaning we can group a sequence of operations from one or more tables and treat them as a single unit. This includes inserting, updating, deleting, or selecting rows in various tables within the same transaction. They also offer fine-grained control over database operations by specifying a series of steps that must be completed entirely or not at all.

On the other hand, table locks operate at the table level, which means that they lock the entire table, restricting access to it for other operations. They’re particularly useful for operations that affect an entire table, such as bulk updates or schema changes.

4.2. Concurrency Implications

When it comes to concurrency, transactions generally enable better parallel processing. This is because they don’t necessarily block other operations unless there’s a direct conflict. For instance, while one transaction is updating a specific student’s record, another transaction can still read or update other student records.

Contrarily, table locks, especially WRITE locks, can significantly reduce concurrency by blocking entire tables. For example, if we have a WRITE lock on the Student table to update one student’s information, no other process can read or write to the Student table until our lock is released

4.3. Error Handling

Usually, transactions can handle complex, multi-step operations more elegantly. They also provide built-in error handling with ROLLBACK, making it easier to maintain consistency in case of failures.

In contrast, table locks can often be less flexible, and error handling with them is more manual, requiring explicit checks and handling.

5. Conclusion

In this article, we explored transactions and locking. Transactions ensure that a series of changes either all happen successfully or none of them happen at all while locks prevent multiple users from changing the same information at the same time.

In conclusion, both concepts work hand in hand to create a robust and consistent database system.