Cacher is the code snippet organizer for pro developers

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