Session and Lock

Lock:参考如下网址:http://broadh2o.net/docs/database/oracle/oracleLocks.html

　　　　　　　　  http://docs.oracle.com/cd/E11882_01/server.112/e10592/ap_locks001.htm#SQLRF55502

　　　　　　　　　(Automatic Locks in DML Operations)

　　　　　　　　　http://docs.oracle.com/cd/E11882_01/server.112/e25789/consist.htm#CNCPT1339(数据库的事务和读一致性和Oracle的锁)

博客备份地址:http://www.cnblogs.com/caroline/archive/2012/04/20/2458733.html

 

     In multi-user systems, many users may update the same information at the same time. Locking allows only one user to update a particular data block; another person cannot modify the same data.

    The basic idea of locking is that when a user modifies data through a transaction, that data is locked by that transaction until the transaction is committed or rolled back. The lock is held until the transaction is complete - this known as data concurrency.(数据一致性)

   The second purpose of locking is to ensure that all processes can always access (read) the original data as they were at the time the query began (uncommited modification), This is known as read consistency.(读一致性)

   Although locks are vital to enforce database consistency, they can create performance problems. Every time one process issues a lock, another user may be shut out from processing the locked row or table. Oracle allows to lock whatever resources you need - a single row, many rows, an entire table, even many tables. But the larger the scope of the lock, the more processes you potentially shut out.(级别高的锁起作用)

Oracle提供了两种类别的锁:

Oracle provides two different levels of locking: Row Level Lock andTable Level Lock.

行级别和表级别。

Row-Level Locking：

    With a row-level locking strategy, each row within a table can be locked individually(单独锁定). Locked rows can be updated only by the locking process(锁定的行只能被当前锁定的进程进行数据的修改). All other rows in the table are still available for updating by other processes(其他的的(未被锁定的行仍然可以被其他的进程锁定)). Of course, other processes continue to be able to read any row in the table, including the one that is actually being updated. When other processes do read updated rows, they see only the old version of the row prior to update (via a rollback segment) until the changes are actually committed. This is known as a consistent read.

 When a process places a row level lock on a record, what really happens?

     First, a data manipulation language (DML) lock is placed over the row. This lock prevents other processes from updating (or locking) the row. This lock is released only when the locking process successfully commits the transaction to the database (i.e., makes the updates to that transaction permanent) or when the process is rolled back.
   Next, a data dictionary language (DDL) lock is placed over the table to prevent structural alterations to the table. For example, this type of lock keeps the DBA from being able to remove a table by issuing a DROP statement against the table. This lock is released only when the locking process successfully commits the transaction to the database or when the process is rolled back.

 Table-Level Locking：

    With table-level locking, the entire table is locked as an entity(作为一个整体被锁定). Once a process has locked a table, only that process can update (or lock) any row in the table(只有进行锁定的进程可以修改表的数据). None of the rows in the table are available for updating by any other process(任何行对于其他进行都是不可用的(update)). Of course, other processes continue to be able to read any row in the table(但是可以进行数据的读取), including the one that is actually being updated.

How does table-level locking work?

     The first DML operation that needs to update a row in a table obtains what's called a Row Share Exclusive lock over the entire table. All other query-only processes needing access to the table are informed that they must use the rollback information for the locking process. The lock is released only when the locking process successfully commits the transaction to the database or when the process is rolled back.

Releasing Locks:

     Many users believe that they are the only users on the system - at least the only ones who count. Unfortunately, this type of attitude is what causes locking problems. We've often observed applications that were completely stalled because one user decided to go to lunch without having committed his or her changes. Remember that all locking (row or table) will prevent other users from updating information. Every application has a handful of central, core tables. Inadvertently locking such tables can affect many other people in a system.

    Many users, and some programmers, don't understand that terminating a process does not always release locks(结束一个进程不一定可以进行锁的释放). Switching off your workstation before you go home does not always release locks. Locks are released only when changes are committed or rolled back. A user's action is the only thing that distinguishes between committing, aborting, and rolling back changes. Make it a priority to train your users to commit or roll back all outstanding changes before leaving their current screens.

Oracle锁的两种模式:

Oracle uses two modes of locking in a multi-user database:
    Exclusive(独占锁) lock mode (X) prevents the associates resource from being shared. (防止被关联的资源被共享)This lock mode is obtained to modify data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.(第一个获取锁定资源的事务也在在该锁被释放前，唯一可以修改该资源的事务。)
    Share(共享锁) lock mode (S) allows the associated resource to be shared,(允许被关联的资源共享) depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer (who needs an exclusive lock). Several transactions can acquire share locks on the same resource.

----待续