锁--阻塞和死锁

最新推荐文章于 2022-08-15 22:49:00 发布

ciqmm402244

最新推荐文章于 2022-08-15 22:49:00 发布

阅读量248

点赞数

3. 阻塞

定义：如果一个会话持有某个资源的锁，而另一个会话在请求这个资源，就会出现阻塞（blocking）。这样一来，请求的会话会被阻塞，它会“挂起”，直至持有锁的会话放弃锁定的资源。
数据库中有5条常见的DML语句可能会阻塞，具体是：INSERT 、UPDATE、DELETE、MERGE和SELECT FOR UPDATE。
阻塞的INSERT

1> 阻塞原因：由于主键或惟一性约束的存在，如果有两个会话试图用同样的值插入一行，则其中的一个会话会被阻塞；由于可能多个表通过引用完整性约束相互链接，对子表的插入可能会阻塞，因为它所依赖的父表正在创建或删除。

2> 解决方法：1）使用一个序列来生成主键/惟一列值；2）如果无法使用序列，则使用手工锁，这里的手工锁通过内置的DBMS_LOCK包来实现。

3> 手工锁方法：
原理：创建一个带主键的表,还有一个触发器,它会防止两个(或更

多)会话同时插入相同的值。这个触发器使用 DBMS_UTILITY.GET_ HASH_VALUE 来计算主键的散列值,得到一个 0~1 073 741 823 之间的数(这也是 Oracle 允许我们使用的锁 ID 号的范围) 。在这个例子中,我选择了一个大小为1 024 的散列表, 这说明我们会把主键散列到 1 024 个不同的锁ID 。然后使用DBMS_LOCK.REQUEST 根据这个 ID 分配一个排他锁(也称独占锁,exclusive lock) 。一次只有一个会话能做这个工作,所以,如果有人想用相同的主键值向表中插入一条记录,这个人的锁请求就会失败(并且会产生 resource busy(资源忙)错误) 。
例：

1）首先创建表

create table demo(x int primary key);

2）创建触发器

create or replace trigger demo_bifer
before insert on demo
for each row
declare
l_lock_id number;
resource_busy exception;
pragma exception_init(resource_busy,-54);
begin
l_lock_id :=
dbms_utility.get_hash_value(to_char(:new.x),0,1024);
if (dbms_lock.request
(id => l_lock_id,
lockmode => dbms_lock.x_mode,
timeout => 0,
release_on_commit => TRUE) <> 0)
then
raise resource_busy;
end if;
end;
/
3）在两个单独的会话中执行下面的插入
insert into demo values(1);
1 row created.
第一个会话会成功，但第二个会话得到以下错误：
insert into demo values(1);
insert into demo values(1)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at "ORACLE.DEMO_BIFER", line 14
ORA-04088: error during execution of trigger 'ORACLE.DEMO_BIFER'

阻塞的Merge、Update和Delete

1> 如果出现UPDATE或DELETE阻塞，说明代码中可能存在丢失更新问题。解决方法：使用SELECT FOR UPDATE NOWAIT。

2> 由于MERGE只是INSERT、UPDATE和DELETE的组合，所以可以同时使用解决INSERT阻塞和解决UPDATE、DELETE阻塞的方法解决MERGE阻塞。

4.死锁

定义：如果你有两个会话，每个会话都持有另一个会话想要的资源，此时就会出现死锁（deadlock）。
例：数据库中有两个表A和B，每个表只有一行，在会话A中更新A，并在会话B中更新B。现在我想在会话B中更新A，就会阻塞（因为会话A已经锁定了这一行）；如果再回到会话A，试图更新表B，就会导致死锁。此时，会话B中对表A的更新可能回滚，得到以下错误：

update A set x=x+1

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

但此时更新表B的会话A还阻塞着，说明Oracle并没有回滚会话B的整个事务，只回滚与死锁有关的某条语句，因此会话B仍然锁定着表B中的行。
导致死锁的两大原因

1> 外键未加索引

2> 表上的位图索引遭到并发更新
外键未加索引可能导致出现死锁的情况

1> 如果更新了父表的主键，由于外键上没有索引，所以子表会被锁住。

2> 如果删除了父表中的一行，整个子表也会被锁住（由于外键上没有索引）。

注：这些全表锁都是短期的，它们仅在DML操作期间存在，而不是整个事务期间都存在。但是这些全表锁还是可能（而且确实会)导致死锁问题。
例：

1> 删除父表的一行

create table p(x int primary key);

insert into p values(1);

insert into p values(2);

create table c(x int references p);

insert into c values(2);

commit;

insert into c values(2);

1 row created.

此时，在另一个会话中，删除父表的一行，就会被阻塞。

delete from p where x=1;

结论：如果删除p表中的一行，则在DML操作期间，子表c就会被锁定，这样可以避免事务期间对c执行其他更新(当然，这里有一个前提，即没有人在修改c，如果确实已经有人在修改c，删除会等待)。此时就会出现阻塞和死锁问题。通过锁定整个表c，数据库的并发性就会大幅下降，以至于没有人能够修改c中的任何内容。另外，出现死锁的可能性则增大了。

2> 更新父表的主键

create table p(x int primary key);

insert into p values(1);

insert into p values(2);

create table c(x int references p);

commit;

在一个会话中，向子表c插入一行

insert into c values(2);

此时，在另一个会话中，更新父表的主键就会被阻塞

update p set x=x+1 where x=2;
未加索引的外键可能带来的其他问题（除了前面介绍的全表锁）：

1> 如果有ON DELETE CASCADE，而且没有对子表加索引，那么删除父表中的每一行时都会对子表做一个全表扫描。

2> 从父表查询子表，例如假设表dept是表emp的父表，且emp没有加外键索引，频繁运行以下查询，会发现没有索引会使查询速度变慢。

select * from dept, emp

where emp.deptno = dept.deptno and dept.deptno = :X;
满足以下条件时不需要加索引

1> 没有从父表删除行。

2> 没有更新父表的惟一键/主键值（当心工具有时会无意地更新主键！如Oracle Forms）。

3> 没有从父表联接子表（如DEPT联接到EMP）。

注：如果满足上述全部3个条件，则不需要对外键加索引。否则，就要当心未加索引的后果。

5.锁升级

定义：出现锁升级（lock escalation）时，系统会降低锁的粒度。例如，数据库系统可以把一个表的100个行级锁变成一个表级锁。
Oracle不会升级锁，从来不会！
Oracle会执行锁转换（lock conversion）或锁提升（lock promotion）。
Oracle会尽可能在最低级别锁定（即限制最少的锁），如果必要，会把这个锁转换为一个更受限的级别。例如：用FOR UPDATE字句从表中选择一行，就会创建两个锁。一个锁放在所选行上（这是一个排他锁：任何人都不能以独占模式锁定这一行）。另一个锁是ROW SHARE TABLE锁，放在表本身上，该锁能防止其他会话放置一个排他锁，譬如能相应防止这些会话改变表的结构，但其他会话可以修改这个表中的任何其他行，而不会有冲突。
在Oracle中，1个锁的开销和1000000各锁的开销是一样的，都没有开销。