重庆思庄Oracle、Redhat认证学习论坛

标题: OCP课程31：管理Ⅰ之锁 [打印本页]

作者: stonebox 时间: 2016-1-27 14:45
标题: OCP课程31：管理Ⅰ之锁

课程目标：

描述锁机制及管理数据并发
监控和解决锁冲突

1、锁

在数据库允许会话修改数据之前，会话必须先锁定正在修改的数据。锁会使会话对数据进行独占控制，因此没有其他事务可以修改锁定的数据，直到锁被释放为止。

事务可以锁定单行数据，多行数据，甚至整个表。Oracle支持手动和自动锁定。自动获得的锁始终选择最低级别的锁，以尽量减少与其他事务潜在的冲突。

2、锁机制

锁机制的目的是提供最大程度的数据并发。修改数据的事务获取行的是级锁而不是块级或表级锁。对对象（如表移动）的修改获得对象级锁，而不是整个数据库或模式锁。

数据查询不需要锁，即使有人锁定数据查询也会成功。

当多个事务需要锁定相同资源时，第一个请求的事务会获取锁。其他事务需要等待第一个事务完成。队列机制是自动的，不需要管理员介入。

事务完成后（提交或者回滚）所有的锁被释放。如果事务失败，相同的后台进程，后台进程自动回滚失败的事务并释放该事务所持有的所有锁。

3、数据并发

锁机制默认为细粒度，行级锁模式。不同的事务可以在同一个表中更新不同的行，而不互相干扰。

虽然默认模式是在行级锁定，但如果需要的话，Oracle支持更高级别的锁：

SQL> lock table emp in exclusive mode;

Table(s) Locked.

这条语句表示，任何试图更新锁表中的行的任何其他事务都必须等待发出锁定请求的事务完成。有以下锁模式：

ROW SHARE：允许同时访问锁定的表，但禁止会话将整个表锁定为独占访问。
ROW EXCLUSIVE：与ROW SHARE相同，但同时禁止锁定为SHARE模式，当更新、插入或删除数据时，自动获得ROW EXCLUSIVE。ROW EXCLUSIVE锁允许多个读和一个写。
SHARE：允许同时查询，但禁止更新锁定的表。SHARE锁会请求（自动请求）在表中创建索引。然而，在线索引创建需要一个用于构建索引时使用的ROW SHARE锁。共享锁允许多个读，但不允许写。当删除或更新一个有子表外键参考的父表的时候会自动使用SHARE锁。
SHARE ROW EXCLUSIVE：用于查询整个表，允许其他人查询表中的行，但禁止其他人在SHARE模式或更新行时锁定该表。
EXCLUSIVE：允许查询锁定的表，但禁止任何其他活动。删除表就需要EXCLUSIVE锁。

象一个锁的任何请求一样，手动锁语句等待所有的会话释放他们的锁，这些会话或者已经有锁或先前已请求了锁。LOCK命令使用NOWAIT参数控制等待动作。

如果指定的表已经被另一个会话锁定，使用NOWAIT可以立即获取控制权：

SQL> lock table emp in share mode nowait;

lock table emp in share mode nowait

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

通常不需要手动锁定对象。自动锁定机制提供大多数应用程序所需的数据并发。Oracle建议避免使用手动锁，特别是在开发应用程序时。严重的性能问题经常发生在不必要的高级别锁定。

4、DML锁

每个DML事务获取2个锁：

行上面的EXCLUSIVE锁

表级（TM）上的ROW EXCLUSIVE（RX）。这可以防止在更新的时候其他会话锁定整个表（或删除或截断它）。这种模式也被称为subexclusive表锁（SX）。

表上的ROW EXCLUSIVE锁防止DDL命令在未提交事务阶段改变字典元数据。这将在整个事务期间保持字典的完整性读一致性。

例子：更新表的行，在另个一个会话删除这个表

SQL> update emp set salary=10000 where employee_id=100;

1 row updated.

SQL> drop table emp;

drop table emp

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

5、队列机制

锁请求自动排队。一旦获得锁的事务完成，队列中的下一个会话就会收到锁。

队列机制跟踪锁请求和请求的锁模式的次序。

已持有锁的会话可以请求其他锁而无需进入队列的末端。例如，假设一个会话在一个表中占有一个SHARE锁。会话可以请求将SHARE锁转换为EXCLUSIVE锁。如果没有其他事务在该表上具有EXCLUSIVE或SHARE锁，则持有该SHARE锁的会话被授予一个EXCLUSIVE锁，而不必在队列中等待。

6、锁冲突

锁冲突经常发生，但通常都可以通过时间和队列机制解决。在某些罕见的情况下，锁冲突可能需要管理员干预。在上图中，事务2在9:00:00获得一个行锁，没有提交。事务1在9:00:05想去更新相同的行，就需要等待事务2的行锁，一直等到16:30:01事务2提交后，事务1才得该行的行锁。

在这种情况下DBA必须检测和解决冲突。

7、锁冲突的可能原因

锁冲突最常见的原因是未提交更改，但也有一些其他可能的原因：

长时间运行的事务：许多应用程序使用批处理来执行批量更新。这些批处理作业通常是在非业务时间执行，但在某些情况下，他们可能没有完成或可能需要太长时间运行。当事务和批处理同时执行时，会经常出现锁冲突。
不必要的高锁定级别：不是所有的数据库都支持行级锁。某些数据库仍只能在页或表级锁定。开发者编写运行在许多不同的数据库的应用程序的时候，经常人为的高级别锁定，就使得Oracle数据库的行为类似于其他只能表级锁定的数据库系统。

8、检测锁冲突

用EM的阻塞会话页来定位锁冲突。冲突的锁请求按层次布局显示，在顶部显示持有锁的会话，下面显示等待该锁的会话。

对于冲突中的每一个会话，给出用户名、会话ID和会话已等待的秒数。点击会话ID可以看到实际的SQL语句。

自动数据库诊断监视器（ADDM）也能自动检测锁冲突。

9、解决锁冲突

为了解决锁冲突，持有该锁的会话必须释放它。让会话释放锁最好的方式是联系用户完成事务。

在紧急情况下，管理员可以通过单击“终止会话”按钮来终止该会话。如果会话被终止，则会话当前事务中的所有工作丢失（回滚）。一个会话被终止的用户必须重新登录并重做所有工作。

被终止会话的用户在下一次发出SQL语句的时候会收到以下错误：

ORA-03135: connection lost contact

10、使用SQL解决锁冲突

V$SESSION表包含所有连接会话的详细信息。blocking_session是阻断会话的会话ID。使用SID和SERIAL#终止会话。

例子：查询锁并终止会话

SQL> select sid,serial#,username from v$session where sid in (select blocking_session from v$session);

SID SERIAL# USERNAME

---------- ---------- ------------------------------

1 7 HR

SQL> alter system kill session '1,7' immediate;

System altered.

11、死锁

死锁是锁冲突的一个特殊例子。互相等待对方完成事务。

Oracle数据库自动检测死锁并终止错误的语句。对该错误的正确响应是提交或回滚，将释放该会话中的任何其他锁，以便其他会话可以继续它的事务。

在上图中，交易1必须提交或回滚。如果提交，则需要重新执行第二条语句。如果执行回滚，则需要重新执行这两条语句。

例子：死锁

会话1：

SQL> update employees set salary=salary*1.1 where employee_id=100;

1 row updated.

会话2：

SQL> update employees set manager_id=100 where employee_id=102;

1 row updated.

会话1：

SQL> update employees set salary=salary*1.1 where employee_id=102;

会话2：

SQL> update employees set manager_id=101 where employee_id=100;

会话1：

SQL> update employees set salary=salary*1.1 where employee_id=102;

update employees set salary=salary*1.1 where employee_id=102

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

回滚：

SQL> rollback;

Rollback complete.

会话2：

SQL> update employees set manager_id=101 where employee_id=100;

1 row updated.

12、相关习题

（1）User A executes the following command to drop a large table in your database:
SQL> DROP TABLE trans;
While the drop table operation is in progress; user B executes the following command on the same table;
SQL> DELETE FROM trans WHERE tr_type='SL';
Which statement is true regarding the DELETE command?
A.  It fails to delete the records because the records are locked in the SHARE mode
B.  It deletes the rows successfully because the table is locked in the SHARE mod
C.  It fails to delete the records because the table is locked in EXCLUSIVE mode
D.  It deletes the rows successfully because the table is locked in SHARE ROW EXCLUSIVE mode

答案：C

（2）View the Exhibit and examine the parameters. User A executes the following command to update the TRANS table:
SQL> UPDATE B.trans SET tr_amt=tr_amt+500 WHERE c_code='C005';
Before user A issues a COMMIT or ROLLBACK command, user B executes the following command on the TRANS table:
SQL> ALTER TABLE trans MODIFY (tr_type VARCHAR2(3));
What would happen in this scenario?

A.  The ALTER TABLE command modifies the column successfully
B.  The DDL operation gets higher priority and transaction for user A is rolled back
C.  The ALETER TABLE command waits indefinitely until user A ends the transaction
D.  The ALTER TABLE command fails after waiting for 60 seconds due to the resource being busy

答案：D

（3）The session of user SCOTT receives the following error after executing an UPDATE command on the EMP table:
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
On investigation, you find that a session opened by user JIM has a transaction that caused the deadlock.
Which two statements are true regarding the session of SCOTT in this scenario? (Choose two.)
A.  The session is terminated after receiving the error and JIM can continue with his transaction.
B.  SCOTT should perform a COMMIT or ROLLBACK to allow JIM to continue with his transaction.
C.  The session is rolled back after receiving the error and JIM can continue with his transaction.
D.  SCOTT has to reexecute the last command in the transaction after he commits the transaction.

答案：BD

（4）User SCOTT executes the following command on the EMP table but has not issued COMMIT,ROLLBACK, or any data definition language (DDL) command:
SQL> SELECT ename FROM emp
      2    WHERE job='CLERK' FOR UPDATE OF empno;
SCOTT has opened another session to work with the database instance.
Which three operations would wait when issued in SCOTT's second session? (Choose three.)
A.  LOCK TABLE emp IN SHARE MODE;
B.  LOCK TABLE emp IN EXCLUSIVE MODE;
C.  UPDATE emp SET sal=sal*1.2 WHERE job='MANAGER'
D.  INSERT INTO emp(empno,ename) VALUES (1289,'Harry');
E.  SELECT ename FROM emp WHERE job='CLERK' FOR UPDATE OF empno;

答案：ABE

（5）All  the  database  users  are  presently  connected  to  the  database  instance  and  working.  The HR user has opened three database sessions and executed the following command in one of his sessions:
SQL> UPDATE persons SET ccode='U031' WHERE ccode='U029';
123 rows updated.
SQL> DELETE FROM persons WHERE exp='Y';
3 rows deleted.
The SYS user opens a new session after HR executed the above commands.
Which sessions can see the effect of the UPDATE and DELETE commands?
A.  All sessions of the HR user only
B.  All sessions of the HR user and the SYS user
C.  The session of the HR user that executed the commands
D.  All the sessions for which the database users have access privilege to the PERSONS table

答案：C

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)