oracle lock 03 - lock mode and conversion and escalation and duration

Lock Modes

Oracle Database automatically uses the lowest applicable level of restrictiveness to provide the highest degree of data concurrency yet also provide fail-safe data integrity. The less restrictive the level, the more available the data is for access by other users. Conversely, the more restrictive the level, the more limited other transactions are in the types of locks that they can acquire.

Oracle Database uses two modes of locking in a multiuser database:

1. Exclusive lock mode

This mode prevents the associated resource from being shared. A transaction obtains an exclusive lock when it modifies data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.

2.Share lock mode

This mode 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.

Assume that a transaction uses a SELECT ... FOR UPDATE statement to select a single table row. The transaction acquires an exclusive row lock and a row share table lock. The row lock allows other sessions to modify any rowsother than the locked row, while the table lock prevents sessions from altering the structure of the table. Thus, the database permits as many statements as possible to execute.

Lock Conversion(转化) and Escalation（扩大、升级）

Oracle Database performs lock conversion as necessary. In lock conversion, the database automatically converts a table lock of lower restrictiveness to one of higher restrictiveness.

For example, suppose a transaction issues a SELECT ... FORUPDATE for an employee and later updates the locked row. In this case, the database automatically converts the row share table lock to a row exclusive table lock. A transaction holds exclusive row locks for all rows inserted, updated, or deleted within the transaction. Because row locks are acquired at the highest degree of restrictiveness, no lock conversion is required or performed.

Lock conversion is different from lock escalation, which occurs when numerous locks are held at one level of granularity (for example, rows) and a database raises the locks to a higher level of granularity (for example, table). If a user locks many rows in a table, then some databases(某些数据库) automatically escalate the row locks to a single table. The number of locks decreases, but the restrictiveness of what is locked increases.

Oracle Database never escalates locks. Lock escalation greatly increases the likelihood of deadlocks. Assume that a system is trying to escalate locks on behalf of transaction 1 but cannot because of the locks held by transaction 2. A deadlock is created if transaction 2 also requires lock escalation of the same data before it can proceed.

Lock Duration(持续)

Oracle Database automatically releases a lock when some event occurs so that the transaction no longer requires the resource. In most cases, the database holds locks acquired by statements within a transaction for the duration of the transaction. These locks prevent destructive interference such as dirty reads, lost updates, and destructiveDDL from concurrent transactions.

Oracle Database releases all locks acquired by the statements within a transaction when it commits or rolls back. Oracle Database also releases locks acquired after asavepoint  when rolling back to the savepoint. However, only transactions not waiting for the previously locked resources can acquire locks on the now available resources. Waiting transactions continue to wait until after the original transaction commits or rolls back completely.

 

实验：验证Oracle Database also releases locks acquired after a savepoint when rolling back to the savepoint.

创建测试表

SQL> create table t1 (id number,name varchar2(32));

Table created.

SQL> insert into t1 values (1,'aa');

1 row created.

SQL> commit;

Commit complete.

SQL> create table t2 as select * from t1;

Table created.

SQL> savepoint svpt_t1;  --定义一个保存点

Savepoint created.

SQL> update t1 set name='t1aa' where id=1;

1 row updated.

--查看当前系统中哪些对象被上锁

SQL> select t1.OS_USER_NAME,t1.ORACLE_USERNAME,t1.SESSION_ID,t2.object_name from v$locked_object t1,dba_objects t2 where t1.object_id=t2.object_id;

OS_USER_NAME                   ORACLE_USERNAME                SESSION_ID OBJECT_NAME
------------------------------            --------------------------------                 --------               --------------------
oracle                                             SCOTT                                      39                       T1

SQL> savepoint svpt_t2;  --还是同一个会话，再定义一个保存点

Savepoint created.

SQL> update t2 set name='t2aa' where id=1;

1 row updated.

--此时，查看系统中有哪些对象被上锁

SQL> select t1.OS_USER_NAME,t1.ORACLE_USERNAME,t1.SESSION_ID,t2.object_name from v$locked_object t1,dba_objects t2 where t1.object_id=t2.object_id;

OS_USER_NAME                   ORACLE_USERNAME                SESSION_ID OBJECT_NAME
------------------------------ ------------------------------ ---------- --------------------
oracle                         SCOTT                                  39 T2
oracle                         SCOTT                                  39 T1
发现，会话39一共锁了两个对象(T1,T2)

此时，我们回滚到保存点svpt_t2,看看加载对象T2上的锁会不会释放

SQL> rollback to svpt_t2;

Rollback complete.

SQL> select t1.OS_USER_NAME,t1.ORACLE_USERNAME,t1.SESSION_ID,t2.object_name from v$locked_object t1,dba_objects t2 where t1.object_id=t2.object_id;

OS_USER_NAME                   ORACLE_USERNAME                SESSION_ID OBJECT_NAME
------------------------------ ------------------------------ ---------- --------------------
oracle                         SCOTT                                  39 T1
发现，加载表T2上的锁已被释放。