Understanding Locks
|
|
Concurrency is one of the major concerns in a multiuser environment. When multiple sessions write or read data to and from shared resources, a database might loose its integrity. To prevent this from happening, every RDBMS worth its salt implements a concurrency control mechanisms. In the case of RDBMS servers, the concurrency is managed through various locking mechanisms. All three leading RDBMS vendors have implemented sophisticated mechanisms for concurrency management.
Oracle has probably the most evolved and complex locking schema. It follows the rule that reading and writing processes cannot block each other, even if working on the same (or a close) set of data. Each session receives a read-consistent image of the data. Thus, even if some other process has begun modifying data in the set but did not commit the changes, every subsequent session will be able to read the data just as it was before; once the changes are committed in the first session, every other session is able to see it. The locks are acquired only when the changes are being committed to the database. Oracle automatically selects the least-restrictive lock. User can choose to manually lock a resource (a table, for example). In this case, other users still might be able to access the data, depending on the type of lock deployed.
IBM DB2 UDB and Microsoft SQL Server 2000 both employ locks that can enable a reader to block a writer and vice versa. The problem of concurrent access to the data is somewhat alleviated by the granularity of the locking — table, page, row, and so on. There are locks acquired by read-only queries, DDL statements, DML queries, and so on. There are different lock types for each scenario, which we're going to discuss in more detail.
Most of the time, a user does not have to worry about locking, as RDBMS automatically select the most appropriate lock (or locks) for a particular operation; only if this programmed logic fails should you attempt to specify the locks manually, using the SQL statements.
Locking modes
There are two broad categories of concurrency — optimistic and pessimistic. The names are self-explanatory. Transactions with optimistic concurrency work on the assumption that resource conflicts — when more than one transaction works on the same set of data — are unlikely (though possible). Optimistic transactions check for potential conflicts when committing changes to a database and conflicts are resolved by resubmitting data. Pessimistic transactions expect conflicts from the very beginning and lock all resources they intend to use. Usually RDBMS employ both optimistic and pessimistic transactions, and users can instruct their transactions to use either.
| Note | Locking granularity has a significant effect on system performance. Row-level locking increases concurrency (i.e., does not block other transactions from accessing a table) but usually incurs overhead costs of administration. A full table lock is much less expensive in terms of system resources but comes at the price of concurrency. This is something to keep in mind when designing database applications. |
Locks are used to implement pessimistic transactions, and each RDBMS has its own levels of locking, though there are some similarities. In general, there are either share locks or exclusive locks, which refer to the way a resource (e.g., a table) is being used.
In Oracle, when a client process accesses a resource, it can explicitly lock the resource using one of the lock types specified in Table 7-6. Such a lock overrides any automatic lock settings.
| Lock Mode | Description |
|---|---|
| EXCLUSIVE | Allows a SELECT query on the locked table, all other operations (i.e., UPDATE, DELETE, etc.) are prohibited to other transactions. |
| SHARE | Allows concurrent queries, but updates are prohibited for all transactions. |
| ROW SHARE | Allows concurrent access to the table, but no other users can acquire an exclusive lock on the table. Also, the SHARE UPDATE mode is provided for backward compatibility. |
| ROW EXCLUSIVE | Is essentially the same as ROW SHARE but also prevents locking in SHARE mode. |
| SHARE ROW EXCLUSIVE | Locks the whole table; queries are allowed but no other transaction can acquire any lock on the table. |
For example, the following statement locks table CUSTOMER of the ACME database in exclusive mode:
LOCK TABLE customer IN EXCLUSIVE MODE;
The transaction that issues this statement will attempt to lock the table for its exclusive use, subject to the restrictions specified in Table 7-6. If any other process keeps a lock on the table, the transaction will be put in a queue, and the lock will be acquired in priority received. The lock will be in place for the duration of the transaction (i.e., until COMMIT is executed). A deadlock situation might occur (see next paragraph) if the transaction that already holds a lock on the table attempts to acquire a lock on a resource that the second transaction has a lock on. The clause NOWAIT instructs a transaction to move on if a table it tries to lock is already locked.
LOCK TABLE customer IN EXCLUSIVE MODE NOWAIT;
If the lock command is issued for a view, Oracle will attempt to lock the base tables for the view. Certain types of operations require locking. Oracle will allow you to perform DDL operations on a table only if that table can be locked. (It is possible to use this statement to lock some other types of objects in Oracle, e.g., dblink).
| Note | Oracle allows specifying a special clause in CREATE and ALTER TABLE statements that either allows or disallows locking for the table. Disabling locking for the table effectively prevents any DDL operation against such a table. |
Oracle provides several hints for performance optimization; some of these would affect the locking used by Oracle. The hints, while being very important for Oracle database tuning and optimization, are beyond the scope of this book; please refer to the vendor's documentation for more information.
In IBM DB2 UDB, the custom locking control is somewhat similar to that in Oracle, though less granular. A user can specify two modes of table locking — SHARE or EXCLUSIVE. For example:
db2=>LOCK TABLE customer IN EXCLUSIVE MODE DB20000I The SQL command completed successfully.
The SHARE mode prevents any other transaction from executing any type of operation on the locked table, except for a read-only SELECT; also, no other transaction can acquire a lock to that table The EXCLUSIVE mode prevents any operation on the table, including read-only operations.
The lock is held for the duration of the transaction and is released once a COMMIT statement is issued. Except for these two modes, the locking for operations in DB2 UDB databases is governed by isolation levels set for the transactions (described earlier in the chapter).
All other locks are at the discretion of the RDBMS. Default locking is row-level, and a lock may escalate to a table-level lock (there is no page-level locking in DB2 UDB); the lock escalation may be avoided using the LOCK TABLE statement from above. The escalation thresholds are configurable by the DBA through a number of parameters (i.e., maxlocks, locksize, etc.).
Microsoft SQL Server 2000 provides several lock options to be specified for the transactions (Table 7-7). These represent categories of locks that further could be divided by specific lock HINTS, some of which are presented in Table 7-8.
| Lock Mode | Description |
|---|---|
| SHARED (S) | This type of lock is used for read-only operations. |
| UPDATE (U) | This lock is used whenever the data is updated. |
| EXCLUSIVE (X) | Prevents all other transactions from performing UPDATE, DELETE or INSERT. |
| INTENT | This is used to establish a hierarchy of locking: intent, shared intent, exclusive, and shared with intent exclusive. An intent lock indicates that SQL Server wants to acquire a shared or exclusive lock on some resources down in the hierarchy (e.g., table — page — row); at the very least the intent lock prevents any transactions from acquiring an exclusive lock on the resource. |
| SCHEMA | This lock type is used when a DDL operation is performed. |
| BULK UPDATE (BU) | These locks are used when bulk copying is taking place. |
| Locking Hint | Description |
|---|---|
| NOLOCK | This hint issued in a SELECT statement specifies that no shared locks should be used and no exclusive locks should be honored; this means that the SELECT statement could potentially read uncommitted transactions (dirty reads). |
| UPDLOCK | Instructs SQL Server to use UPDATE locking (as opposed to shared locks) while reading data; makes sure that data has not changed if an UPDATE statement follows next. |
| XLOCK | Places an exclusive lock until the end of a transaction on all data affected by the transaction. Additional levels of granularity can be specified with this lock. |
| ROWLOCK | Specifically instructs SQL Server to use row-level locks (as opposed to page and table-level). |
The lock mode is either selected by the SQL Server itself, or based on the type of operation performed. To manually specify the locking mode, one should use the table-level locking hints that fall into one of the categories listed in Table 7-7. These locking hints override the transaction isolation level and should be used judiciously. The hints in the Table 7-8 provide just a sampling of what is available, and the list is by no means complete.
For example, to specify row-level locking for the transaction in a SELECT statement, the following syntax may be used:
SELECT * FROM customer WITH (ROWLOCK)
There is a penalty to pay for the high granularity — it degrades performance as SQL Server allocates more resources for row-level locking operations.
| Note | In addition to the visual interface of the Enterprise manager, Microsoft SQL Server provides stored procedure sp_locks, which return information about all active locks on the system; sufficient privilege-levels are required. |
SQL Server deploys different locks at its own discretion based on cost decisions: the default is a row-level lock, which may escalate to a page-level lock, and in turn to a table-level lock, when a transaction exceeds its escalation threshold. This parameter is not configurable and is determined by SQL Server itself in each situation.
Dealing with deadlocks
The classic deadlock situation arises when two (or more) sessions are waiting to acquire a lock on a shared resource, and none of them can proceed because a second session also has a lock on some other resource that is required by the first session. Imagine a situation, in which Session 1 holds resource A, while trying to access resource B; at the same time Session 2 holds resource B while trying to access resource A.
Usually RDBMS resolves situations like this automatically by killing one of the processes and rolling back all the changes it may have made.
Oracle implements a sophisticated mechanism enforcing the rule "reader and writer processes cannot block each other." The idea behind this rule is to present each process with a consistent image of data without noncommitted changes. Nevertheless, deadlocks do occur in Oracle and usually are resolved by the RDBMS itself; in some rare cases, manual resolution — choosing the deadlock "victim" process — is required. The most common deadlock types are ORA-00060 (en queue deadlocks) and ORA-04020 (library cache deadlocks). It is possible to specify the NOWAIT clause or set up session timeouts to avoid deadlocks, some other techniques involve explicit locking and use of the isolation levels within the transaction. A deadlock may also be resolved manually through Oracle's interfaces.
IBM DB2 runs a background process, called Deadlock Detector, to find and resolve the deadlock situation. The session chosen as a deadlock victim is rolled back, and a special error is generated (SQLCODE-901, SQLSTATE 40001). The read-only process is a prime candidate for the deadlock victim, and beyond that, DB2 employs "least cost" criteria to select the session to be killed. If deadlocks ever become a problem, IBM recommends using system monitoring tools to collect information about the deadlock situations and either optimize the system or redesign any applications involved.
Microsoft SQL Server 2000 employs a proprietary algorithm for detecting deadlocks and resolves them in a way similar to that implemented by Oracle or DB2 UDB: deadlocks are resolved automatically or manually through the Enterprise Manager Console. It is possible to volunteer a session to become a deadlock victim by setting the DEADLOCK_PRIORITY parameter within that session (see paragraph about sessions earlier in the chapter).
SET DEADLOCK_PRIORITY LOW
Another way of dealing with the situation would be setting LOCK_TIMEOUT for the session. Setting the timeout means that the session will hold the resource under the lock no longer than a specified interval. Once the time set for locking expires, SQL Server returns an error and the transaction is rolled back. The resolution of the situation will be similar to that for every other RDBMS: handle the situation in which an error indicating a deadlock situation is returned (Error 1205 for SQL Server, SQLSTATE 40001) by re-running the transaction, redesigning the application to decrease or eliminate the deadlock possibility, and so on.
|
|
EAN: 2147483647
Pages: 208