LOCK modes in SQL
Modos de bloqueo en SQL From: http://etutorials.org/SQL/SQL+Bible+Oracle/Part+III+Data+Manipulation+and+Transaction+Control/Chapter+7+Sessions+Transactions+and+Locks/Understanding+Locks/#sthash.Tl0mychR.b8SOuYTz.dpuf
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.
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).