Oracle死锁案例分析

191次阅读

共计 3834 个字符，预计需要花费 10 分钟才能阅读完成。

问题背景描述：

发生 Oracle 死锁的多个进程执行的都是同一个存储过程，大概代码及顺序如下：

–1. 首先通过主键 order_no 锁住一条订单
select t.* from order t where t.order_no=’order_no’ for update;
–2. 其次通过主键 channel_id 锁住一个渠道
select t.* from channel t where t.channel_id=’channel_id’ for update;
–3. 然后通过主键 order_no 对订单表数据进行修改
update order t set t.order_status=0,t.finish_time=sysdate where t.order_no=’order_no’;
commit;

死锁情况描述
session A
– 正在执行语句 3，他处于 enq: TX – allocate ITL entry 等待
update order t set t.order_status=0,t.finish_time=sysdate where t.order_no=’orderno_a’;

session B
– 正在执行语句 2，他处于 enq: TX – row lock contention 等待
select t.* from channel t where t.channel_id=’ch1′ for update;

session C
正在执行语句 2，他处于 enq: TX – row lock contention 等待
select t.* from channel t where t.channel_id=’ch1′ for update;

可能还会有更多的 session 处于执行语句 2，并等待 enq: TX – row lock contention 的情况，这里暂时只列 3 个 session，其实 2 个也够了，也能形成，只是概率很低。

等待链
A 被 C 堵塞，C 被 B 堵塞，B 被 A 堵塞
等待链分析：
A 执行到语句 3 了，说明主键为 orderno_a 的 order 数据行锁和 ch1 的 channel 数据行锁已经获取到了，而其余的 B 和 C 只能等待该 ch1 数据的行锁释放。
B 和 C 都执行到语句 2 了，说明他们都获取到了各自的 order 数据行锁，且数据不是 orderno_a 锁代表的数据。这点毋庸置疑。
疑问：A，B，C 操作的都是不同的订单数据行，且都获取到了各自的行锁的，为什么在表 order 上，还会发生 A 被 C 堵塞呢。

要知道为什么有这个疑问，就要先明白，在 A 执行 order 的 for update 时是已经获取了 itl 资源的，所以在后来真正 update 数据时是不应该存在这个等待的 enq: TX – allocate ITL entry，因为他已经获取这个资源了。

死锁分析
要分析这个死锁就要明白等待事件 enq: TX – allocate ITL entry 所代表的资源 itl 事务槽的含义。itl 事务槽是数据块头中用来标记事务的记录。在这里有个重点是数据块。想一想，如果事务跨数据块了会怎样。这就是这个死锁的关键点。当然不同表的事务肯定跨数据块了，一个事务即使修改一个表的多条数据也可能跨块了。这里的情况是，order 表上事务都是通过主键来操作的，对于一条数据，要跨越数据块，行迁移或者行连接会有这种情况。

简单说下这两种情况
行迁移一般是 update 后经常出现，比如一个 err_mesg 字段，初期只有 10 个字符，后面 update 为 1000 个字符，如果这个时候原数据块找不下了，他就会找另外的数据块来存放，而原数据块上放一个新数据块的 dba（data block address），指向新的数据块，如下图：行连接一般是 insert 时出现的，比如一条数据非常大，大到一个块装不下了，oracle 会拆分成多个块来存放。可以通过创建块尺寸小的表空间来测试。

Oracle 死锁案例分析

到此处，要明白 itl 是数据块上的资源，即使是同一个事务中，如果事务跨数据块了，当他要修改这个数据块时，他也需要重新再次在这个新块上申请 itl 资源，也就是我这里死锁中，假设 orderno_a 数据 rowid 指向的块为 dba_1，行迁移中指向的块为 dba_2, 在最开始 for update 时获取的是块 dba_1 中的 itl 资源，当最后真正 update 数据时，为了保护操作，需要获取 dba_2 上的 itl 资源。而此时，其余的很多 session，比如 B，C……N 等等 session 将块 dba_2 上的 itl 资源耗尽了，那么 session A 就处于等待数据块 dba_2 上的 itl 资源的状态，对应于 enq: TX – allocate ITL entry。而其他 session 将等待 session A 释放渠道表数据的锁。完成了锁的闭环

到此死锁分析完毕。

可以使用以下代码来做简单的测试

– 创建 order 表，将 PCTFREE 置为 0，INITRANS 置为 1create table t_order(mesg varchar2(4000)) PCTFREE 0 INITRANS 1;
– 创建 channel 表
create table t_channel(id NUMBER)；
– 准备数据，对于 order 表，至少要有两个块有数据
– 第一个块的数据，有三条，即 a,b,c
insert into t_order select rpad(‘a’,3000,’a’) from dual;
insert into t_itl select rpad(‘b’,1000,’b’) from dual;
insert into t_order select rpad(‘c’,3000,’c’) from dual;
– 更改数据 b，此时第一个块装不下，将会发生行迁移
update t_order set mesg=(select rpad(‘b’,3000,’b’) from dual) where mesg like ‘b%’;
– 可以使用以下语句分析行迁移的表，只用作测试，在线生产慎用, 可以 dump 第一个数据块找到，迁移到哪一个 dba 去了
create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date
);
analyze table t_order list chained rows;
select * from CHAINED_ROWS;
– 继续插入数据，将迁移后的数据块数据增加，方便之后 for update 时消耗这个块的 itl 资源
– 通常情况，下面插入的数据就是放在 b 数据迁移后的数据块的
insert into t_order select rpad(‘d’,1000,’d’) from dual;
insert into t_order select rpad(‘f’,6000,’f’) from dual;
insert into t_order select rpad(‘g’,300,’g’) from dual;
insert into t_order select rpad(‘h’,100,’h’) from dual;
/* 开始模拟死锁 */
–t1 时刻
–session A
select * from t_order where mesg like ‘b%’ for update;
select * from t_channel where id=1 for update;
–t2 时刻
–session B
select * from t_order where mesg like ‘d%’ for update;
select * from t_channel where id=1 for update;– 等待 session A 释放
– 其余 session
select * from t_order where mesg like ‘f%’ for update;
select * from t_channel where id=1 for update;– 加入该条数据的行锁等待
select * from t_order where mesg like ‘g%’ for update;
select * from t_channel where id=1 for update;– 加入该条数据的行锁等待
…..
/* 如果这些数据不在 b 所在的块，可以通过设置 where 条件为以下内容来指定更改 b 迁移后的块
where DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) = ‘block_no’
and DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) = 1;
– 此时 session B 与其余 session 将 t_order 的第二个块，即 d,f,g,h 数据所在的块的 itl 耗尽
–t3 时刻
–session A 去更改 t_order 的数据
update t_order t set t.mesg=’bbbbb’ where t.mesg like ‘b%’;
– 此时会等待 session B 及其他 session 释放 itl 资源，而 session B 及其他 session 又在等待 session A 释放 channel 的锁
– 形成了互相等待，闭环，死锁形成