1 什么是行锁，为什么会发生行锁？
当一条sql语句更新或删除一行数据时，事务只获得这一行的锁，获得的这个锁就是行锁。锁这一行的是某一个事务，而不是会话。oracle的规则是这样的，写一行数据库数据时加行锁，读一行数据不对行枷锁，写阻塞写，写不阻塞读，读不阻塞读和写。
当多个事务同时更新（包括删除） 统一数据时（表或索引），就会发生行锁竞争，在Oracle等待事件里就是enq: TX - row lock contention enq，enq时队列的意思，在oracle数据库里同锁是一个意思。
发生行锁竞争一个最常见的场合时多个会话同时更新或删除同一表的同一条数据，或者是会话更新一行数据的同时其它会话正在删除数据，反过来也一样。
表上有唯一索引时，插入数据有时也会导致行锁，这主要时由于插入数据时唯一索引的值引起的冲突，产生唯一索引的值的方法不正确，是表设计时唯一索引的选择不正确的结果。
产生行锁竞争的另一个常见的场景是位图索引，位图索引的一个值对应表中的多个值，虽然我们在表中更新的是不同的值，在位图索引中对应的是一个值，这样即使我们更新的表中的不同的值也会导致行锁竞争。这也是位图索引在oltp事务中被禁止使用的原因。
2 发生行锁时如何分析诊断？
数据库发生行锁会导致怎样的后果。受到直接影响的发生行锁竞争的会话及应用，行锁竞争直接导致会话被阻塞，应用得不到响应。行锁竞争也会消耗大量的计算资源，从而影响整个数据库的性能。
oracle数据库有多种工具可以用于行锁的诊断分析，如常用的awr报告及ash报告，也可以使用视图来对行锁进行分析。下面的是实验模仿一个最简单的行锁场景，对行锁进行分析及处理。
2.1 数据准备
先创建一个简单的表，插入几行数据，表的主键在这里不是必须的。
SQL> create table test(id int primary key, name varchar2(20),salary number);
Table created.
SQL> insert into test values (1, 'zhangsan', 2000);
1 row created.
SQL> insert into test values (2, 'lisi', 2500);
1 row created.
SQL> insert into test values (3, 'wangwu', 2100);
1 row created.
2.2 模仿行锁
在第一个会话中执行以下操作
检查以下autocommit是否关闭，如果没有关闭，需要关闭后在执行sql语句。关闭的命令是：set autocommit off;
SQL> show auto
autocommit OFF
autocommit已关闭
SQL> update test set salary=2000 where id=1;
1 row updated.
另开一个会话,关闭自动提交后更新同一行数据
SQL> show autocommit;
autocommit OFF
SQL> update test set salary=2200 where id=1;
这个会话卡住了
2.3 行锁分析
发生行锁时，数据库中会有被锁定的对象，这个可以从视图 v$locked_object查询到。
SQL> select object_id ,session_id, locked_mode from v$locked_object;
OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- -----------
76280 378 3
76280 471 3
对象76280被会话378，和471 同时锁定，查询dba_objects 视图，可以知道被锁定的对象正是update语句更新的表格test。
另一个视图v$lock可以看到数据库中锁的详细信息，该视图的type列的值是锁类型，用oracle的术语说是2字母的资源标识符，用户类型的有三个TM - DML enqueue（数据操作语言锁），
TX - Transaction enqueue（事务锁），UL - User supplied，其它的是系统锁。
SQL> select SID,TYPE,ID1,ID2,LMODE,REQUEST,BLOCK from v$lock where lmode in (3,6);
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
399 RT 1 0 6 0 0
401 TS 65539 1 3 0 0
393 KD 0 0 6 0 0
401 TS 196611 1 3 0 0
471 TX 655363 994 6 0 1
378 TM 76280 0 3 0 0
471 TM 76280 0 3 0 0
7 rows selected.
有一个视图dba进程会查询，这就是v$session视图，这个视图中可以看到会话及阻塞此会话的会话，等待事件等许多有用的信息。
SQL> select sid, BLOCKING_SESSION,BLOCKING_SESSION_STATUS,command, event from v$session where BLOCKING_SESSION is not null;
SID BLOCKING_SESSION BLOCKING_SE COMMAND EVENT
---------- ---------------- ----------- ---------- ----------------------------------------------------------------
378 471 VALID 6 enq: TX - row lock contention
从上面可以看到会话378被会话471阻塞，等待事件是enq: TX - row lock contention，如果我们想知道行锁竞争发生在哪个表的那行数据，v$session会话也提供了这个信息，用下面语句可以查询到：
SQL> select event,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK# ,ROW_WAIT_ROW# from v$session where BLOCKING_SESSION is not null;
EVENT ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------------------------------------------------------------- ------------- -------------- --------------- -------------
enq: TX - row lock contention 76280 12 134 0
从语句的输入可以看到，行锁竞争发生的等待对象id是76280，正好是更新语句的对象id，数据文件id是12，块id是134，行号是0
查询dba _objects对象可以获得更新的表的名称。
SQL> select OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from dba_objects where DATA_OBJECT_ID=76280
OWNER OBJECT_NAM OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- ---------- ---------- -------------- -----------------------
TEST TEST 76280 76280 TABLE
要想查询到行锁竞争发生在表中的哪一行，需要用到一个oracle的工具包dbms_rowid，这个工具包可以获得表中一行数据的对象id，文件号，块号，行id，运行下面的语句可以查询到行锁竞争发生在表的哪一行。
SQL> select id ,
rowid,
dbms_rowid.rowid_object(rowid) "object",
dbms_rowid.rowid_relative_fno(rowid) "file",
dbms_rowid.rowid_block_number(rowid) "block",
dbms_rowid.rowid_row_number(rowid) "row"
from test.test where dbms_rowid.rowid_row_number(rowid)=0; 6 7
ID ROWID object file block row
---------- ------------------ ---------- ---------- ---------- ----------
1 AAASn4AAMAAAACGAAA 76280 12 134 0
可以看到行锁竞争发生在test表中id为1 的行，这一行的信息和v$session表中的信息是一致的。在实际的行锁分析中，知道行锁竞争发生的具体信息有时十分有用，可以让我们快速找到导致行锁的应用，特别是多个应用执行同一语句的时候或者一个应用给多个业务部门使用时。
2.4 行锁处理
发生行锁时处理的一般办法是找到阻塞的会话，在数据库或操作系统里杀死阻塞的对话。
SQL> select sid, serial# from v$session where sid in ( select distinct blocking_session from v$session);
SID SERIAL#
---------- ----------
1 36638
在数据库里用下面语句杀掉会话
SQL> alter system kill session '1,36638';
System altered.
数据库的告警日志里有下面信息
2022-08-05T09:25:36.879896+08:00
ORCLPDB1(3):KILL SESSION for sid=(1, 36638):
ORCLPDB1(3): Reason = alter system kill session
ORCLPDB1(3): Mode = KILL SOFT -/-/-
ORCLPDB1(3): Requestor = USER (orapid = 70, ospid = 25353, inst = 1)
ORCLPDB1(3): Owner = Process: USER (orapid = 71, ospid = 25414)
ORCLPDB1(3): User = oracle
ORCLPDB1(3): Program = sqlplus@iZ2ze0t8khaprrpfvmevjiZ (TNS V1-V3)
ORCLPDB1(3): Result = ORA-0
kill掉的会话已经从数据库里退出登录
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 25414
Session ID: 1 Serial number: 36638
如果在数据库里无法杀掉阻塞会话，可以查到会话的操作系统pid后，使用kill或kill -9命令杀掉操作系统进程
SQL> select a.sid, b.spid from v$session a , v$process b
where a.paddr=b.addr
and a.sid in (select distinct blocking_session from v$session);
SID SPID
---------- ------------------------
1 25534
也可以用下面的语句生成操作系统下kill会话的命令，
SQL> select 'kill -9 ' || b.spid ||';' kill_session from
v$session a , v$process b
where a.paddr=b.addr
and a.sid in
(select distinct blocking_session from v$session);
KILL_SESSION
---------------------------------
kill -9 25534;
转到操作系统下直接运行上面的命令
[oracle@iZ2ze0t8khaprrpfvmevjiZ ~]$ kill -9 25534;
被杀到的会话状态如下所示：
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 25534
Session ID: 1 Serial number: 57400
数据库告警日志里面没有操作系统下kill 进程的信息。
3 死锁及死锁的诊断处理
3.1 Oracle对于死锁的定义及处理
Oracle官方对死锁的定义是这样的
A deadlock is a situation in which two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work. Oracle Database automatically detects deadlocks and resolves them by rolling back one statement involved in the deadlock, releasing one set of the conflicting row locks.
The database returns a corresponding message to the transaction that undergoes statement-level rollback. The statement rolled back belongs to the transaction that detects the deadlock. Usually, the signalled transaction should be rolled back explicitly, but it can retry the rolled-back statement after waiting.
从上面可以看出，Oracle自动检测死锁，并回滚涉及到死锁的一个语句。数据库返回相应的信息给经历语句级回滚的事务。回滚的语句属于检测到死锁的事务。
3.2 模拟一个死锁
--session 1
查看一下会话的sid
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
425
关闭自动提交
SQL> set autocommit off;
SQL> show autoc
autocommit OFF
main session作为监控会话
SQL> select a.sid, b.pid from v$session a ,v$process b where a.paddr=b.addr and a.sid=425;
SID PID
---------- ----------
425 64
再打开另一个会话，作为会话2
--session2
SQL> set autocommit off;
SQL> show autoc
autocommit OFF
按照顺序再会话1和2执行下面语句
--session 1
SQL> update test set salary=465;
--session2
SQL> update test2 set salary=473;
--session1
SQL> update test2 set salary=466;
--session2
SQL> update test set salary=474;
10秒之后，会话1报一下错误
update test2 set salary=466
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
3.3 死锁的诊断
数据库的告警日志中含有一下信息
2022-08-01T14:48:39.788730+08:00
ORCLPDB1(3):ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_13275.trc.
从告警日志中可以看到，更多的信息可以再会话进程的跟踪文件中看到
[oracle@iZ2ze0t8khaprrpfvmevjiZ trace]$ more /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_13275.trc
[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 serial process session holds waits serial
TX-000A0003-000004B0-79F3F24D-00000000 64 425 X 42850 65 456 X 46887
TX-00070006-0000049A-79F3F24D-00000000 65 456 X 46887 64 425 X 42850
----- Information for waiting sessions -----
Session 425:
sid: 425 ser: 42850 audsid: 9310003 user: 109/TEST
pdb: 3/ORCLPDB1
flags: (0x41) USR/- flags2: (0x40009) -/-/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-/-
pid: 64 O/S info: user: oracle, term: UNKNOWN, ospid: 13275
image: oracle@iZ2ze0t8khaprrpfvmevjiZ client details:
O/S info: user: oracle, term: pts/1, ospid: 13273
machine: iZ2ze0t8khaprrpfvmevjiZ program: sqlplus@iZ2ze0t8khaprrpfvmevjiZ (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update test2 set salary=463
Session 456:
sid: 456 ser: 46887 audsid: 9340002 user: 109/TEST
pdb: 3/ORCLPDB1
flags: (0x41) USR/- flags2: (0x40009) -/-/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-/-
pid: 65 O/S info: user: oracle, term: UNKNOWN, ospid: 13432
image: oracle@iZ2ze0t8khaprrpfvmevjiZ client details:
O/S info: user: oracle, term: pts/2, ospid: 13430
machine: iZ2ze0t8khaprrpfvmevjiZ program: sqlplus@iZ2ze0t8khaprrpfvmevjiZ (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update test set salary=471
进程的跟踪文件中可以看到死锁的详细信息，如发生死锁的资源（这里时数据库里一行数据，每个资源的持有者，等待者，发生死锁时正在执行的语句等）。
值得注意的是，Oracle在处理死锁问题时，只是做语句级的回滚，并不退出事务和会话，事务仍然需要提交或回滚。
