Oracle-24-锁机制

最新推荐文章于 2023-05-21 18:08:04 发布

原创最新推荐文章于 2023-05-21 18:08:04 发布 · 327 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#锁机制

Oracle 专栏收录该内容

38 篇文章

订阅专栏

本文详细介绍了Oracle数据库中的DDL锁和DML锁的工作原理。DDL锁用于保护数据结构及对象完整性，例如在表结构变更时；而DML锁则是在事务中产生的，用于保证并发数据的一致性。通过实例演示了如何查看锁的状态、如何触发锁以及解决锁争用问题。

DDL锁：保护数据结构，保护对象的完整性，也叫字典锁。

当我们想要向表中增加一列，要求我们先要锁定表的结构，然后增加一个新的列。

select table_name,table_lock from user_tables;

TABLE_NAME                     TABLE_LO
------------------------------ --------
DEPT                           ENABLED
EMP                            ENABLED
BONUS                          ENABLED
SALGRADE                       ENABLED

Elapsed: 00:00:00.11

创建一张临时表

create table e01 as select * from emp;

select table_name,table_lock from user_tables;

TABLE_NAME                     TABLE_LO
------------------------------ --------
DEPT                           ENABLED
EMP                            ENABLED
BONUS                          ENABLED
SALGRADE                       ENABLED
E01                            ENABLED

Elapsed: 00:00:00.02

alter table e01 disable table lock;

select table_name,table_lock from user_tables;

TABLE_NAME                     TABLE_LO
------------------------------ --------
DEPT                           ENABLED
EMP                            ENABLED
BONUS                          ENABLED
SALGRADE                       ENABLED
E01                            DISABLED

Elapsed: 00:00:00.03

现在表锁处于DISABLE状态

下面我们对表做一些操作

truncate table e01;

truncate table e01
               *
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for E01


Elapsed: 00:00:00.03

drop table e01;

drop table e01
           *
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for E01


Elapsed: 00:00:00.12

我们把锁打开

alter table e01 enable table lock;

select table_name,table_lock from user_tables;

TABLE_NAME                     TABLE_LO
------------------------------ --------
DEPT                           ENABLED
EMP                            ENABLED
BONUS                          ENABLED
SALGRADE                       ENABLED
E01                            ENABLED

Elapsed: 00:00:00.02

truncate table e01;

Table truncated.

Elapsed: 00:00:00.09

DML锁：在事务中产生的，为了保证并发数据一致性的锁，存在于行头，叫做行级锁

用sys用户授予scott用户查看视图权限

grant select on v_$mystat to scott;

查看当前会话的sid

select sid from v$mystat where rownum=1;

       SID
----------
        38

Elapsed: 00:00:00.00

在sys用户下查看该会话有哪些锁

select * from v$lock where sid=38;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000009705A0A8 000000009705A100         38 AE        100          0          4          0       1822     0
000000009705A248 000000009705A2A0         38 TO      65927          1          3          0        425     0

Elapsed: 00:00:00.03

SCOTT用户模拟会话产生锁

insert into e01 select * from emp;

SYS用户查看锁的状态

select * from v$lock where sid=38;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000009705A0A8 000000009705A100         38 AE        100          0          4          0       2014     0
000000009705A248 000000009705A2A0         38 TO      65927          1          3          0        617     0
00007FAEC3237828 00007FAEC3237888         38 TM      74754          0          3          0         52     0
0000000096383280 00000000963832F8         38 TX     786441          6          6          0         48     0

Elapsed: 00:00:00.00

我们队SCOTT用户事务进行提交

commit;

再查看锁的状态

select * from v$lock where sid=38;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000009705A0A8 000000009705A100         38 AE        100          0          4          0       2112     0
000000009705A248 000000009705A2A0         38 TO      65927          1          3          0        715     0

Elapsed: 00:00:00.00

Oracle是自动管理锁的资源的，在不同时间对不同对象操作就会产生不同的锁，如果两个会话同时修改一张表的同一行信息，会出现锁的征用问题，他们会以队列的形式进行排队处理

我们开启两个SCOTT用户的会话同时执行一个SQL

update e01 set sal=sal+1 where empno=7369;

我们用SYS用户来看下锁队列的情况

@?/rdbms/admin/utllockt

drop table lock_holders
           *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.02
drop   table dba_locks_temp
             *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.03

1 row created.

Elapsed: 00:00:00.00

Commit complete.

Elapsed: 00:00:00.01

Table dropped.

Elapsed: 00:00:00.03

1 row created.

Elapsed: 00:00:00.00

Commit complete.

Elapsed: 00:00:00.00

WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED MODE_HELD      LOCK_ID1          LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
36                None
   38             Transaction       Exclusive      Exclusive      1310747           6

Elapsed: 00:00:00.00

Table dropped.

Elapsed: 00:00:00.03

如果出现两个会话交叉入队，都在等待另一方把锁放开，Oracle会中断其中的一个会话，这种情况叫死锁。

我们手工将处于资源等待的会话杀掉

select sid,serial# from v$session where sid=38;

       SID    SERIAL#
---------- ----------
        38          6

Elapsed: 00:00:00.00

alter system kill session '38,6' immediate;