9.1数据并发处理
默认情况下,锁定机制采用细粒度行级锁定模式。不同的事务处理可更新同一表内不同的行,彼此互不干扰。
尽管默认模式是行级锁定,但Oracle DB 也允许根据需要在更高级别执行手动锁定:
SQL> LOCK TABLE employees INEXCLUSIVE MODE;
Table(s) Locked.
使用以上语句时,其他任何尝试更新锁定表中行的事务处理都必须等待,直到发出锁定请求的事务处理完成为止。
9.2几种锁机制
ROW SHARE
允许对锁定的表进行并发访问,但禁止在会话中锁定整个表进行独占访问。
ROW EXCLUSIVE
与ROW SHARE 相同,但是同时禁止以SHARE模式锁定。更新、插入或删除数据时会自动获取ROW EXCLUSIVE 锁。ROW EXCLUSIVE 锁允许多个进程执行读取,但只允许一个进程执行写入。
SHARE
允许并发查询,但禁止更新锁定的表。需要有SHARE锁才能创建表的索引,创建时会自动请求该锁。但是,创建联机索引的操作在建立索引时需要有ROW SHARE锁。
共享锁允许多个进程进行读取,但不允许执行写入。删除或更新某个父表中的行,并且其子表在该父表上具有外键约束条件时,也会以透明方式使用共享锁。
SHARE ROW EXCLUSIVE
用于查询整个表,允许其他人查询表中的行,但禁止其他人在SHARE模式下锁定表或更新行。
EXCLUSIVE
允许查询锁定表,禁止对锁定表执行任何其它活动。需要有EXCLUSIVE 锁才能删除表。
与任何锁定请求一样,手动锁定语句会一直等待,直到已经持有锁(或先前请求锁定)的所有会话释放锁为止。LOCK 命令可接受用于控制等待行为的特殊参数NOWAIT 。NOWAIT 会立即将控制权交给你,即使指定的表已经被另一会话锁定:
SQL> LOCK TABLE hr.employees INSHARE MODE NOWAIT;
LOCK TABLE hr.employees IN SHARE MODENOWAIT
*
ERROR at line 1:
ORA-00054: resource busy and acquirewith NOWAIT specified
通常不必手动锁定对象。自动锁定机制提供了大多数应用程序所需的数据并发处理能力。
Oracle 建议尽量不要使用手动锁定,特别是在开发应用程序时。使用不必要的高锁定级别时,经常会出现严重的性能问题。
DML 锁
每个DML 事务处理必须获得两个锁:
针对正在更新的一行或多行的EXCLUSIVE 行锁
针对包含这些行的表的ROW EXCLUSIVE(RX) 模式下的表锁(TM)。
这可避免在进行更改时另一会话锁定整个表(可能会删除或截断表)。这种模式也称为子排它表锁(SX)。
对表执行ROW EXCLUSIVE 锁定时,会禁止DDL 命令在未提交的事务处理进行到一半时更改字典元数据。这样便可在事务处理的有效期内保持字典完整性和读取一致性。
9.3入队机制
入队机制用于跟踪:
• 等待锁的会话
• 请求的锁模式
• 会话请求锁的顺序
锁定请求自动排队。只要持有某个锁的事务处理一完成,队列中的下一个会话就接收该锁。入队机制会跟踪请求锁的顺序及请求的锁模式。
已经持有锁的会话可请求转换锁,而不必排到队尾。例如,假定某个会话对表持有SHARE锁。该会话可以请求将SHARE锁转换为EXCLUSIVE 锁。如果没有其它事务处理已经对表持有EXCLUSIVE 或SHARE锁,则持有SHARE锁的会话就会被授予EXCLUSIVE 锁,而不必重新在队列中等待。
注:等待入队的进程分为两类:没有共享所有权的等待进程,以及有共享所有权、但没有选择升级锁级别的等待进程。第二类等待进程称为转换进程,这类进程的优先级始终高于正常等待进程,即使其等待时间较短。
9.4锁冲突
锁冲突经常发生,但通常会随着时间流逝通过入队机制得到解决。只有极少数情况下,锁冲突可能需要管理员干预。如上图所示,事务处理2 在9:00:00 获取了对某一行的锁且忘记了提交,从而留下了锁。事务处理1 在9:00:05 尝试更新整个表,因此需要锁定所有行。但事务处理2 会阻塞事务处理1,直到16:30:01 事务处理2 提交为止。
这种情况下,用户要尝试执行事务处理1,就一定要与管理员联系以获得帮助,DBA 必须检测冲突并解决冲突。
9.4.1锁冲突的可能原因
9.4.1.1长时间运行事务处理
许多应用程序使用批处理来执行批量更新。这些批作业通常会安排在没有用户活动或者用户活动少时执行,但是,有些情况下,批作业在用户活动少的期间内没有完成或要占用过长的时间来运行。同时执行事务处理和批处理时通常会发生锁冲突。
9.4.1.2 不必要的高锁定级别
并不是所有数据库都支持行级锁定(Oracle 在1988 年的发行版6 中添加了对行级锁定的支持)。某些数据库仍然在页级或表级上进行锁定。开发人员在编写要在许多不同数据库上运行的应用程序时,会人为地使用高锁定级别,以便使Oracle DB 与功能较少的数据库系统的操作方式相同。如果开发人员不熟悉Oracle,有时也会以高于Oracle DB 要求的锁定级别编写代码,其实这是不必要的。
9.4.2检测锁冲突
在“Performance (性能)”页上选择“Blocking Sessions(阻塞会话)”。
单击“Session ID (会话ID )”链接,查看关于锁定会话的信息,包括实际SQL 语句。
使用Enterprise Manager 中的“Blocking Sessions(阻塞会话)”页可找出锁冲突。有冲突的锁定请求以分层布局的形式显示,其中持有锁的会话位于顶部,下面是排队请求锁的所有会话。
对于冲突中涉及的每个会话,会显示用户名、会话ID和会话已等待的秒数。选择会话 ID 可查看会话当前正在执行或请求的实际SQL 语句。
自动数据库诊断监视器(ADDM) 还会自动检测锁冲突,并且会就低效的锁定趋势提出建议。
9.4.3解决锁冲突
为了解决锁冲突,应该:
1、提交或回退持有锁的会话
2、终止持有锁的会话(在紧急情况下)
要解决锁冲突,持有锁的会话必须释放锁。让会话释放锁的最好方式是与用户联系,要求用户完成事务处理。
紧急情况下,管理员可以通过单击“KillSession (终止会话)”按钮来终止持有锁的会话。
select sid from v$mystat whererownum=1;
select userenv('sid') from dual;
ALTER SYSTEM KILL SESSION '130,651' IMMEDIATE
请记住,终止会话后,当前事务处理中的所有工作都会丢失(回退)。会话被终止的用户必须再次登录,然后重做被终止的会话自上次提交以来所做的所有工作。
如果用户的会话已终止,用户下次尝试发出SQL 语句时会收到以下错误:
ORA-03135: connection lostcontact
scott@TEST0924> update emp setsal=sal+100 where empno=7369;
ERROR:
ORA-03114: not connected to ORACLE
update emp set sal=sal+100 whereempno=7369
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 24763
Session ID: 130 Serial number: 651
注:如果会话出现空闲超时,PMON 会话检测程序会自动终止会话,这可以使用概要文件或资源管理器来完成。
9.4.4使用SQL 解决锁冲突
可以使用SQL 语句来确定阻塞会话并终止该会话。
sys@TEST0924> select SID, SERIAL#,USERNAME from V$SESSION where SID in (select BLOCKING_SESSION from V$SESSION);
SID SERIAL# USERNAME
---------- ----------------------------------------
96 675 SCOTT
sys@TEST0924> alter system kill session '96,675' immediate;
System altered.
与在Enterprise Manager 中执行的大多数其它任务一样,会话操作也可以通过发出SQL 语句来完成。V$SESSION 表包含所有已连接会话的详细信息。BLOCKING_SESSION 中的值是阻塞会话的会话ID。如果查询SID 和SERIAL#(其中SID 与阻塞会话ID 相匹配),就会得到执行kill session操作所需的信息。
注:可以使用数据库资源管理器自动注销阻塞其它会话的空闲会话。
9.5死锁
死锁是锁冲突的一种特殊情况。两个或更多会话等待已被其中另一会话锁定的数据时,就会发生死锁。因为每个会话都在等待另一个会话释放锁,所以任何一个会话都不能完成事务处理,也就不能解决冲突。
Oracle DB 会自动检测死锁并终止发生错误的语句。面对这种错误的适当做法是执行提交或回退,这样做会释放该会话中的其它所有锁,以便其它会话可继续完成其事务处理。
在示例中,事务处理1 必须提交或回退,才能更正检测到的死锁错误。如果执行提交,则必须重新提交第二次更新才能完成事务处理。如果执行回退,则必须同时重新提交这两个语句才能完成事务处理