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