Most major DBMS products use sophisticated locking techniques to handle concurrent SQL transactions for many simultaneous users. However, the basic concepts behind locking and transactions are very simple. Figure 12-10 shows a simple locking scheme and how it handles contention between two concurrent transactions.
As Transaction A in the figure accesses the database, the DBMS automatically locks each piece of the database that the transaction retrieves or modifies. Transaction B proceeds in parallel, and the DBMS also locks the pieces of the database that it accesses. If Transaction B tries to access part of the database that has been locked by Transaction A, the DBMS blocks Transaction B, causing it to wait for the data to be unlocked. The DBMS releases the locks held by Transaction A only when it ends in a COMMIT or ROLLBACK operation. The DBMS then unblocks Transaction B, allowing it to proceed. Transaction B can now lock that piece of the database on its own behalf, protecting it from the effects of other transactions.
As the figure shows, the locking technique temporarily gives a transaction exclusive access to a piece of a database, preventing other transactions from modifying the locked data. Locking thus solves all of the concurrent transaction problems. It prevents lost updates, uncommitted data, and inconsistent data from corrupting the database. However, locking introduces a new problem—it may cause a transaction to wait for a very long time while the pieces of the database that it wants to access are locked by other transactions.
1. Locking Levels
Locking can be implemented at various levels of the database. In its most basic form, the DBMS could lock the entire database for each transaction. This locking strategy would be simple to implement, but it would allow processing of only one transaction at a time. If the transaction included any think time at all (such as time to discuss an order with a customer), all other access to the database would be blocked during that time, leading to unacceptably slow performance. However, database-level locking may be appropriate for certain types of transactions, such as those that modify the structure of the database or for complex queries that must sequentially scan many large tables. In these cases, it may be more efficient to rapidly do a single locking operation, quickly execute the database operation, and then quickly unlock the database than to individually lock dozens of tables.
An enhanced form of locking is table-level locking. In this scheme, the DBMS locks only the tables accessed by a transaction. Other transactions can concurrently access other tables. This technique permits more parallel processing, but can still lead to unacceptably slow performance in applications such as order entry, where many users must share access to the same table or tables.
Many DBMS products implement locking at the page level. In this scheme, the DBMS locks individual blocks of data (pages) from the disk as they are accessed by a transaction. Other transactions are prevented from accessing the locked pages but may access (and lock for themselves) other pages of data. Page sizes of 2KB, 4KB, and 16KB are commonly used. Since a large table will be spread out over hundreds or thousands of pages, two transactions trying to access two different rows of a table will usually be accessing two different pages, allowing the two transactions to proceed in parallel.
Over the last several years, most of the major commercial DBMS systems have moved beyond page-level locking to row-level locks. Row-level locking allows two concurrent transactions that access two different rows of a table to proceed in parallel, even if the two rows fall in the same disk block. While this may seem a remote possibility, it can be a real problem with small tables containing small records, such as the OFFICES table in the sample database.
Row-level locking provides a high degree of parallel transaction execution. Unfortunately, keeping track of locks on variable-length pieces of the database (in other words, rows) rather than fixed-size pages is a much more complex task, so increased parallelism comes at the cost of more sophisticated locking logic and increased overhead. In fact, for certain transactions or applications, the overhead of row-level locking might be greater than the performance gains of permitting more parallel operation within the database.
Some DBMS products address this situation by automatically promoting many individual row-level locks into a page-level or table-level lock when the number of row-level locks for a given transaction rises above a certain limit. As this example shows, it’s not always the case that a more granular (smaller) level of lock implementation is better; the best scheme heavily depends on the specific transactions and the SQL operations that they contain.
It’s theoretically possible to move beyond row-level locking to locking at the individual data item level. In theory, this would provide even more parallelism than row-level locks, because it would allow concurrent access to the same row by two different transactions, provided they were accessing different sets of columns. The overhead in managing item-level locking, however, has thus far outweighed its potential advantages. No commercial SQL DBMS uses item-level locking. In fact, locking is an area of considerable research in database technology, and the locking schemes used in commercial DBMS products are much more sophisticated than the fundamental scheme described here. The most straightforward of these advanced locking schemes, using shared and exclusive locks, is described in the next section.
2. Shared and Exclusive Locks
To increase concurrent access to a database, most commercial DBMS products use a locking scheme with more than one type of lock. A scheme using shared and exclusive locks is quite common:
- Shared lock. Used by the DBMS when a transaction wants to read data from the database. Another concurrent transaction can also acquire a shared lock on the same data, allowing the other transaction to also read the data.
- Exclusive lock. Used by the DBMS when a transaction wants to update data in the database. When a transaction has an exclusive lock on some data, other transactions cannot acquire any type of lock (shared or exclusive) on the data.
Figure 12-11 shows the rules for this locking scheme and the permitted combinations of locks that can be held by two concurrent transactions. Note that a transaction can acquire an exclusive lock only if no other transaction currently has a shared or an exclusive lock on the data. If a transaction tries to acquire a lock not permitted by the rules in Figure 12-11, it is blocked until other transactions unlock the data that it requires.
Figure 12-12 shows the same transactions shown in Figure 12-10, this time using shared and exclusive locks. If you compare the two figures, you can see how the new locking scheme improves concurrent access to the database. Mature and complex DBMS products, such as DB2, have more than two types of locks and use different locking techniques at different levels of the database. Despite the increased complexity, the goal of the locking scheme remains the same: to prevent unwanted interference between transactions while providing the greatest possible concurrent access to the database, all with minimal locking overhead.
3. Deadlocks *
Unfortunately, the use of any locking scheme to support concurrent SQL transactions leads to a problem called a deadlock. Figure 12-13 illustrates a deadlock situation.
Program A updates the ORDERS table, thereby locking part of it. Meanwhile, Program B updates the PRODUCTS table, locking part of it. Now Program A tries to update the PRODUCTS table and Program B tries to update the ORDERS table, in each case trying to update a part of the table that has been previously locked by the other program (the same row or the same page, depending on the type of locking implemented). Without outside intervention, each program will wait forever for the other program to commit its transaction and unlock the data. The situation in the figure is a simple deadlock between two programs, but more complex situations can occur where three, four, or more programs are in a cycle of locks, each waiting for data that is locked by one of the other programs.
To deal with deadlocks, a DBMS typically includes logic that periodically (for example, once every five seconds) checks the locks held by various transactions. When it detects a deadlock, the DBMS arbitrarily chooses one of the transactions as the deadlock loser and rolls back the transaction. This frees the locks held by the losing transaction, allowing the deadlock winner to proceed. The losing program receives an error code informing it that it has lost a deadlock and that its current transaction has been rolled back.
This scheme for breaking deadlocks means that any SQL statement can potentially return a deadlock loser error code, even if nothing is wrong with the statement per se. Thus, even though COMMIT and ROLLBACK are the SQL transaction-processing statements, it’s possible for other SQL statements—an INSERT statement, for example, or even a SELECT statement—to be a deadlock loser. The transaction attempting the statement is rolled back through no fault of its own, but because of other concurrent activity in the database. This may seem unfair, but in practice, it’s much better than the other two alternatives—eternal deadlock or database corruption. If a deadlock loser error occurs in interactive SQL, the user can simply retype the SQL statement(s). In programmatic SQL, the application program must be prepared to handle the deadlock loser error code. Typically, the program will respond by either alerting the user or automatically retrying the transaction.
The probability of deadlocks can be dramatically reduced by carefully planning database updates. All programs that update multiple tables during a transaction should, whenever possible, update the tables in the same sequence. This allows the locks to flow smoothly across the tables, minimizing the possibility of deadlocks. In addition, some of the advanced locking features described in later sections of this chapter can be used to further reduce the number of deadlocks that occur.
4. Advanced Locking Techniques *
Many commercial database products offer advanced locking facilities that go well beyond those provided by standard SQL transactions. These include:
- Explicit locking. A program can explicitly lock an entire table or some other part of the database if it will be repeatedly accessed by the program.
- Isolation levels. You can tell the DBMS that a specific program will not reretrieve data during a transaction, allowing the DBMS to release locks before the transaction ends.
- Locking parameters. The database administrator can manually adjust the size of the lockable piece of the database and other locking parameters to tune locking performance.
These facilities tend to be nonstandard and product-specific. However, several of them, particularly those initially introduced in mainframe versions of DB2 years ago, have been implemented in several commercial SQL products and have achieved the status of common, if not standard, features. In fact, the isolation-level capabilities introduced in DB2 have found their way into the SQL2 standard.
5. Explicit Locking *
If a transaction repeatedly accesses a table, the overhead of acquiring small locks on many parts of the table can be very substantial. A bulk update program that walks through every row of a table, for example, will lock the entire table, piece by piece, as it proceeds. For this type of transaction, the program should explicitly lock the entire table, process the updates, and then unlock the table. Locking the entire table has three advantages:
- It eliminates the overhead of row-by-row (or page-by-page) locking.
- It eliminates the possibility that another transaction will lock part of the table, forcing the bulk update transaction to wait.
- It eliminates the possibility that another transaction will lock part of the table and deadlock the bulk update transaction, forcing it to be restarted.
Of course, locking the table has the disadvantage that all other transactions attempting to access the table must wait while the update is in process. However, because the bulk update transaction can proceed much more quickly, the overall throughput of the DBMS can be increased by explicitly locking the table.
In the IBM databases, the LOCK TABLE statement, shown in Figure 12-14, is used to explicitly lock an entire table. It offers two locking modes:
- EXCLUSIVE mode acquires an exclusive lock on the entire table. No other transaction can access any part of the table for any purpose while the lock is held. This is the mode you would request for a bulk update transaction.
- SHARE mode acquires a shared lock on the entire table. Other transactions can read parts of the table (that is, they can also acquire shared locks), but they cannot update any part of it. Of course, if the transaction issuing the LOCK TABLE statement now updates part of the table, it will still incur the overhead of acquiring exclusive locks on the parts of the table that it updates. This is the mode you would request if you wanted a snapshot of a table, accurate at a particular point in time.
Oracle also supports a DB2-style LOCK TABLE statement. The same effect can be achieved in Ingres with a different statement. Several other database management systems do not support explicit locking at all, choosing instead to optimize their implicit locking techniques.
6. Isolation Levels *
Under the strict definition of a SQL transaction, no action by a concurrently executing transaction is allowed to impact the data visible during the course of your transaction. If your program performs a database query during a transaction, proceeds with other work, and later performs the same database query a second time, the SQL transaction mechanism guarantees that the data returned by the two queries will be identical (unless your transaction acted to change the data). This ability to reliably reretrieve a row during a transaction is the highest level of isolation that your program can have from other programs and users. The level of isolation is called the isolation level of your transaction.
This absolute isolation of your transaction from all other concurrently executing transactions is very costly in terms of database locking and loss of database concurrency. As your program reads each row of query results, the DBMS must lock the row (with a shared lock) to prevent concurrent transactions from modifying the row. These locks must be held until the end of your transaction, just in case your program performs the query again. In many cases, the DBMS can significantly reduce its locking overhead if it knows in advance how a program will access a database during a transaction.
To gain this efficiency, the major IBM mainframe databases added support for the concept of a user-specified isolation level that gives the user control over the trade-off between isolation and processing efficiency. The SQL2 specification formalized the IBM isolation-level concept and expanded it to include four isolation levels, shown in Figure 12-15. The isolation levels are linked directly to the fundamental multiuser update problems discussed earlier in this chapter. As the level of isolation decreases (moving down the rows of the table), the DBMS insulates the user from fewer of the multiuser update problems.
The SERIALIZABLE isolation level is the highest level provided. At this level, the DBMS guarantees that the effects of concurrently executing transactions are exactly the same as if they executed in sequence. This is the default isolation level specified in the ANSI/ISO SQL standard, because it is the way SQL databases are supposed to work.
If your program needs to perform the same multirow query twice during a transaction and be guaranteed that the results will be identical regardless of other activity in the database, then it should use the SERIALIZABLE isolation level.
The REPEATABLE READ isolation level is the second highest level. At this level, your transaction is not allowed to see either committed or uncommitted updates from other transactions, so the lost update, uncommitted data, and modified data problems cannot occur. However, a row inserted into the database by another concurrent transaction may become visible during your transaction. As a result, a multirow query run early in your transaction may yield different results than the same query run later in the same transaction (the phantom insert problem). If your program does not depend on the capability to repeat a multirow query during a single transaction, you can safely use the REPEATABLE READ isolation level to improve DBMS performance without sacrificing data integrity. This is one of the isolation levels supported in the IBM mainframe database products.
The READ COMMITTED isolation level is the third highest level. In this mode, your transaction is not allowed to see uncommitted updates from other transactions, so the lost update and the uncommitted data problems cannot occur. However, updates that are committed by other concurrently executing transactions may become visible during the course of your transaction. Your program could, for example, perform a single-row SELECT statement twice during the course of a transaction and find that the data in the row had been modified by another user. If your program does not depend on the capability to reread a single row of data during a transaction, and it is not accumulating totals or doing other calculations that rely on a self-consistent set of data, it can safely use the READ COMMITTED isolation level. Note that if your program attempts to update a row that has already been updated by another user, your transaction will automatically be rolled back, to prevent the lost update problem from occurring.
The READ UNCOMMITTED isolation level is the lowest level specified in the SQL standard. In this mode, your transaction may be impacted by committed or uncommitted updates from other transactions, so the uncommitted data, modified data, and phantom insert problems can occur. The DBMS still prevents the lost update problem. Generally, the READ UNCOMMITTED level is appropriate only for certain ad hoc query applications where the user can tolerate the fact that the query results may contain dirty data. (Some DBMS brands call this isolation mode a dirty read capability because of this possibility.)
If it is important that query results contain only information that has, in fact, been committed to the database, your program should not use this mode.
The SQL2 standard specifies a SET TRANSACTION statement, shown in Figure 12-16, which is used to set the isolation level of the current transaction. The SET TRANSACTION statement also allows you to specify whether the transaction is READ ONLY (that is, it will only query the database) or READ WRITE (it may query or update the database). The DBMS can use this information, along with the isolation level, to optimize its database processing. The default isolation level is SERIALIZABLE. If the READ UNCOMMITTED isolation level is specified, then READ ONLY is assumed, and you may not specify a READ WRITE transaction. Otherwise, a READ WRITE transaction is the default. These defaults provide for the maximum safety of transactions, at the expense of database performance, but they prevent inexperienced SQL programmers from inadvertently suffering one of the multiuser transaction-processing problems.
Note that the SET TRANSACTION statement specified in the SQL2 standard is an executable SQL statement. It’s possible, in fact sometimes very desirable, to have one transaction of a program execute in one mode and have the next transaction execute in a different mode. However, you can’t switch isolation levels or read/write modes in the middle of a transaction. The standard effectively requires that the SET TRANSACTION statement be the first statement of a transaction. This means it must be executed as the first statement after a COMMIT or ROLLBACK, or as the first statement of a program, before any other statements affecting the content or structure of a database.
As noted earlier in the “Advanced Locking Techniques” section, many of the commercial DBMS products implemented their own locking and performance- enhancement schemes long before the publication of the SQL2 standard, and these locking strategies affect the heart of the internal database architecture and logic. It’s not surprising that the adoption of the SQL2 standard in this area has been relatively slow compared to some other areas where implementation was much easier. For example, the IBM mainframe databases (DB2 and SQL/DS) historically offered a choice of two isolation levels—REPEATABLE READ or READ COMMITTED (called CURSOR STABILITY mode in IBM terminology). In the IBM implementations, the choice is made during the program development process, in the BIND step described in Chapter 17.
Although the modes are not strictly part of the SQL language, the choice of mode strongly impacts how the application performs and how it can use retrieved data.
The Ingres DBMS offers a capability similar to the isolation modes of the IBM databases but provides it in a different form. Using the SET LOCKMODE statement, an application program can tell Ingres which type of locking to use when handling a database query. The options are the following:
- No locking. Similar to the IBM CURSOR STABILITY mode just described
- Shared locking. Similar to the IBM REPEATABLE READ mode just described
- Exclusive locking. Provides exclusive access to the table during the query and offers a capability like the IBM LOCK TABLE statement
The Ingres default is shared locking, which parallels the repeatable read default in the IBM scheme. Note, however, that the Ingres locking modes are set by an executable SQL statement. Unlike the IBM modes, which must be chosen at compile time, the Ingres modes can be chosen when the program executes and can even be changed from one query to the next.
7. Locking Parameters *
A mature DBMS such as DB2, SQL/DS, Oracle, Informix, Sybase, or SQL Server employs much more complex locking techniques than those described here. The database administrator can improve the performance of these systems by manually setting the locking parameters. Typical parameters that can be tuned include these:
- Lock size. Some DBMS products offer a choice of table-level, page-level, row-level, and other lock sizes. Depending on the specific application, a different size lock may be appropriate.
- Number of locks. A DBMS typically allows each transaction to have some finite number of locks. The database administrator can often set this limit, raising it to permit more complex transactions or lowering it to encourage earlier lock escalation.
- Lock escalation. A DBMS will often automatically escalate locks, replacing many small locks with a single larger lock (for example, replacing many page-level locks with a table-level lock). The database administrator may have some control over this escalation process.
- Lock timeout. Even when a transaction is not deadlocked with another transaction, it may wait a very long time for the other transaction to release its locks. Some DBMS brands implement a timeout feature, where a SQL statement fails with a SQL error code if it cannot obtain the locks it needs within a certain period of time. The timeout period can usually be set by the database administrator.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.