SQL Bible [Electronic resources] نسخه متنی

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.

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.

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).

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.

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.

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.