lock

本文深入解析Oracle数据库中的锁机制,包括不同类型的锁及其应用场景,并通过实际案例演示如何处理阻塞和死锁问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

oracle中锁的概念十分重要,对于其他关系型数据库更不用说,有的数据库发出select语句都要加锁,但是oracle有独立的undo(可从undo里读取修改过的数据)所以不加锁。
下面介绍oracle里的几种锁模式:
锁代码 锁模式名称 锁模式缩写 锁模式别名 锁级别
0 none none  none
1 null null null 表级锁
2 ROW-S SS RS 表级锁
3 ROW-X SX RX 表级锁
4 Share S S 表级锁
5 S/ROW-X SSX SRX 表级锁
6 Exclusive X X 表/行级锁
锁代码说明:
0:none 
1:null 空 
2:Row-S 行共享(RS):共享表锁 (row  share)
3:Row-X 行专用(RX):用于行的修改 (row exclusive)
4:Share 共享锁(S):阻止其他DML操作(share) 
5:S/Row-X 共享行专用(SRX):阻止其他事务操作 (share row exclusive)
6:exclusive 专用(X):排它锁,最高级锁,独立访问使用 (exclusive)
(数字越大锁级别越高, 影响的操作越多) 
TX表示的是行级锁,TM表示的是表级锁。
下面是关于summary  of  table locks:
Sql Statement  Mode of table lock RS RX S SRX X
select * from table .... none Y Y Y Y Y
insert into table .... RX Y Y N N N
update table .... RX Y* Y* N N N
delete from table .... RX Y* Y* N N N
select ... from table for update of RS Y* Y* Y* Y* N
lock table table in row share mode RS Y Y Y Y N
lock table table in row execlusive RX Y Y N N N
lock table table in share mode S Y N Y N N
lock table table in share row execlusive mode SRX Y N N N N
lock table table in execlusive mode X N N N N N
Y(YES)  N(NO) 
Y*:if no conficting row locks are held by another transaction.otherwise,waits occur.(如果没有冲突行锁是被另一个事务持有。否则,等待出现)
对于锁的概念理解很重要,尤其在写应用程序的时候。
-bash-3.2$ lsb_release -a
Description: Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Release: 5.5
Codename: Carthage
SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> show user;
USER 为 "HR"
SQL> create table t(id number,name varchar2(10));
表已创建。
SQL> insert into t values(1,'diy');
已创建 1 行。
SQL> insert into t values(2,'os');
已创建 1 行。
SQL> COMMIT;
提交完成。
block(阻塞):

SQL> update t set name='d' where id=1;

已更新 1 行。

SQL> select distinct sid from v$mystat;


       SID
----------
21


在另一个session里:
SQL> select distinct sid from v$mystat;


       SID
----------
19


SQL> update t set name='o' where id=1;

此时会一直等待,知道第一个事务结束(commit或rollback),这是block,不是死锁!

我们分析这个过程:

SQL> show user;
USER 为 "SYS"
SQL> select * from v$lock where sid in(19,21) order by sid;


ADDR KADDR SID TY        ID1  ID2   LMODE    REQUEST CTIME    BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
38687638 38687664  19 AE        100      0         4     0                810          0
38687ED4 38687F00  19 TX     262175  1888  0     6               230           0
004CA6B0 004CA6E0  19 TM      76714    0       3     0                230          0
004CA6B0 004CA6E0  21 TM      76714    0         3    0               240          0
37FBACC0 37FBAD00  21 TX     262175  1888   6   0               240           1(锁定了一个事务)
38687720 3868774C  21   AE        100     0           4    0               3730         0

已选择6行。 

注意AE:Edition Lock,是11g新增加的锁类型,这是一个会话锁,只要有会话就会有一个锁。
此时session号为21的先更新id=1这一行,获得了一个TM(RX)锁,又获得了TX(X)锁;
session号为22的也获得了一个TM锁(RX),但是和上面的TM兼容,所以此时没有阻塞,
但是由于行锁并不和上面的行锁兼容,所以没有获得行锁X,从上面的LMODE可以看出。


我们可以通过下面两个视图分析数据库中被锁的对象:
SQL> select * from v$locked_object;
 XIDUSN    XIDSLOT  XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME      OS_USER_NAME     PROCESS      LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------ -----------
0    0       0      76714    19 HR      oracle     6130 3
4   31    1888      76714    21 HR      oracle     5022 3
SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_ID=76714;


OWNER       OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE     CREATED    LAST_DDL_TIME  TIMESTAMP       STATUS  T G S  NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- -------------- -------------- ------------------- ------- - - - ---------- ------------------------------
HR                             T                         76714          76714 TABLE               22-4月 -15     22-4月 -15     2015-04-22:12:39:06 VALID   N N N          1

我们在重建索引时,为了不影响系统性能,往往:
alter  index   index_name rebuild online;
但是我们为什么不:alter  index   index_name rebuild
下面简单操作示范:
SQL> create table ttt as select * from dba_objects;
表已创建。
SQL> select count(*) from dba_objects;


  COUNT(*)
----------
     72746
SQL> create index index_id on TTT(OBJECT_ID);


索引已创建。

SQL> set autotrace traceonly;
SQL> SELECT * FROM TTT;


已选择72746行。




执行计划
----------------------------------------------------------
Plan hash value: 774701505


--------------------------------------------------------------------------
| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  | | 51569 |    10M|   283   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| TTT  | 51569 |    10M|   283   (1)| 00:00:04 |
--------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)




统计信息
----------------------------------------------------------
308  recursive calls
 0  db block gets
       5909  consistent gets
       1035  physical reads
 0  redo size
    8067725  bytes sent via SQL*Net to client
      53755  bytes received via SQL*Net from client
       4851  SQL*Net roundtrips to/from client
 0  sorts (memory)
 0  sorts (disk)
      72746  rows processed

SQL> alter index index_id rebuild;


索引已更改。
SQL> SELECT * FROM TTT;


已选择72746行。




执行计划
----------------------------------------------------------
Plan hash value: 774701505


--------------------------------------------------------------------------
| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  | | 51569 |    10M|   283   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| TTT  | 51569 |    10M|   283   (1)| 00:00:04 |
--------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)




统计信息
----------------------------------------------------------
36  recursive calls
 0  db block gets
       5886  consistent gets
 0  physical reads
 0  redo size
    8067725  bytes sent via SQL*Net to client
      53755  bytes received via SQL*Net from client
       4851  SQL*Net roundtrips to/from client
 0  sorts (memory)
 0  sorts (disk)
      72746  rows processed

SQL> alter index index_id rebuild online;


索引已更改。


SQL> SELECT * FROM TTT;


已选择72746行。




执行计划
----------------------------------------------------------
Plan hash value: 774701505


--------------------------------------------------------------------------
| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  | | 51569 |    10M|   283   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| TTT  | 51569 |    10M|   283   (1)| 00:00:04 |
--------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)




统计信息
----------------------------------------------------------
 5  recursive calls
 0  db block gets
       5879  consistent gets
 0  physical reads
 0  redo size
    8067725  bytes sent via SQL*Net to client
      53755  bytes received via SQL*Net from client
       4851  SQL*Net roundtrips to/from client
 0  sorts (memory)
 0  sorts (disk)
      72746  rows processed

两种扫描方式都是全表扫描,都会发生排序(sort操作)但是rebulid online操作比rebulid性能更好,从逻辑读次数可知。
rebulid操作会阻塞dml操作,而online操作不会(online操作降低了锁级别)
deadlock:
SQL>show user;
USER 为 "HR"
SQL> select * from t;
ID NAME
---------- ----------
1 diy
2 os
SQL> SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
21
SQL> update t set name='d' where id=1;
已更新 1 行。




SQL> SHOW USER;
USER 为 "HR"
SQL> select * from t;
ID NAME
---------- ----------
1 diy
2 os
SQL> SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
19
SQL> update t set name='s' where id=2;
已更新 1 行。



SQL> SHOW USER;
USER 为 "HR"
SQL> select distinct sid from v$mystat;
  SID
----------
21
SQL> update t set name='y' where id=2;
update t set name='y' where id=2
       *
第 1 行出现错误:
ORA-00060: 等待资源时检测到死锁



SQL> show user;
USER 为 "HR"
SQL> select distinct sid from v$mystat;
SID
----------
19
SQL> update t set name='s' where id=1;

上述顺序按操作顺序排列。
告警日志里(alert)有警告:
Wed Apr 22 14:43:05 2015
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl3939/orcl3939/trace/orcl3939_ora_5024.trc.
查看转储文件:
.......
.......
*** 2015-04-22 14:43:04.053
DEADLOCK DETECTED ( ORA-00060 )
 
[Transaction Deadlock]
 
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
 
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00030014-000009b7        24      21     X             32      19           X
TX-0006000d-00000a27        32      19     X             24      21           X
 
session 21: DID 0001-0018-00000019 session 19: DID 0001-0020-00000014 
session 19: DID 0001-0020-00000014 session 21: DID 0001-0018-00000019 
 
Rows waited on:
  Session 21: obj - rowid = 00012BAA - AAASuqAAEAAABuvAAB
.........
.........
上面的内容是不是太详细了!哪个session,rowid都告诉我们了!可以更加深入研究死锁。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29876893/viewspace-1591254/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/29876893/viewspace-1591254/

### Record Lock 的概念与工作原理 #### 什么是 Record Lock? Record Lock 是一种数据库锁机制,用于锁定具体的行数据。它确保在事务隔离级别下,其他事务无法对被锁定的行进行修改或删除操作,从而保证数据的一致性和完整性[^3]。 #### 工作原理 Record Lock 锁定的是索引记录本身,而不是记录之间的间隙。当一个事务对某一行执行更新、删除或带有 `FOR UPDATE` 或 `LOCK IN SHARE MODE` 的查询时,数据库会对该行加 Record Lock[^1]。这种锁可以防止其他事务在同一行上执行冲突的操作。 例如,在 MySQL 的 InnoDB 存储引擎中,如果事务 A 执行以下 SQL 语句: ```sql SELECT * FROM table WHERE id = 5 FOR UPDATE; ``` 此时,数据库会为 `id = 5` 的行加上 Record Lock,阻止其他事务对该行进行修改或删除操作。 #### 使用场景 Record Lock 主要用于以下场景: - **更新操作**:当事务需要更新某一行的数据时,可以通过 Record Lock 防止其他事务同时修改同一行。 - **删除操作**:当事务需要删除某一行的数据时,可以通过 Record Lock 确保其他事务不会在删除过程中修改该行。 - **一致性读取**:在某些隔离级别下(如可重复读),通过 Record Lock 可以避免脏读和不可重复读问题[^3]。 #### 示例代码 以下是一个简单的示例,展示如何在 MySQL 中使用 Record Lock: ```sql -- 假设表 t 中有以下数据:id = {1, 2, 3} START TRANSACTION; -- 对 id = 2 的行加锁 SELECT * FROM t WHERE id = 2 FOR UPDATE; -- 此时,另一个事务尝试更新 id = 2 的行会失败 UPDATE t SET column = 'new_value' WHERE id = 2; ``` 在这个例子中,`SELECT ... FOR UPDATE` 语句对 `id = 2` 的行加了 Record Lock,因此另一个事务无法对该行进行更新操作。 #### Record Lock 与其他锁的关系 - **Next-Key Lock**:Next-Key Lock 是 Record Lock 和 Gap Lock 的组合,用于更精细地控制并发访问[^1]。 - **Gap Lock**:Gap Lock 锁定的是索引记录之间的间隙,而 Record Lock 锁定的是具体的行。 - **意向锁**:意向锁是表级锁,用于表明事务是否有意向对表中的行加锁,但与 Record Lock 不直接冲突[^2]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值