mysql锁机制共享锁和排他锁 oracle共享锁和排他锁

转载

mob6454cc7c8b2e 2023-10-11 21:10:54

文章标签 mysql锁机制共享锁和排他锁 oracle 锁的引用 SQL 数据库共享锁 文章分类 MySQL 数据库

一、ORACLE锁分类

1.按用户与系统划分，可以分为自动锁与显示锁

自动锁：

当进行一项数据库操作时，缺省情况下，系统自动为此数据库操作获得所有有必要的锁。显示锁：

某些情况下，需要用户显示的锁定数据库操作要用到的数据，才能使数据库操作执行得更好，显示锁是用户为数据库对象设定的。

2、按锁级别划分，可分为共享锁与排它锁

共享锁：共享锁使一个事务对特定数据库资源进行共享访问，另一事务也可对此资源进行访问或获得相同共享锁。共享锁为事务提供高并发性，但如拙劣的事务设计加共享锁容易造成死锁或数据更新丢失。

排它锁：事务设置排它锁后，该事务单独获得此资源，另一事务不能在此事务提交之前获得相同对象的共享锁或排它锁。

3、按锁保护的内容分类oracle提供多粒度封锁机制，按保护对象来分，据此又可以分为：

(1)dml锁又称数据锁，用来保护数据的完整性和一致性。

DML锁的目的在于保证并发情况下的数据完整性，在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X 等多种模式，在数据库中用0－6来表示，不同的SQL操作产生不同类型的TM锁。

Oracle的TM锁类型

锁模式锁描述解释SQL操作

0none

1NULL空Select

2SS(Row-S)行级共享锁，其他对象只能查询这些数据行Select for update、Lock for update、Lock row share

3SX(Row-X)行级排它锁，在提交前不允许做DML操作Insert、Update、Delete、Lock row share

4S(Share)共享锁Create index、Lock share

5SSX(S/Row-X)共享行级排它锁Lock share row exclusive

6X(Exclusive)排它锁Alter table、Drop able、Drop index、Truncate table、Lock exclusive

这五种模式的TM锁的兼容关系如下表所示(√表示互相兼容的请求；×表示互相不兼容的请求；N/A表示没有锁定请求)：-SXSSSXSSXN/A

S√×√××√

X×××××√

SS√×√√√√

SX××√√×√

SSX××√××√

N/A√√√√√√

在数据行上只有X锁(排他锁)。在Oracle数据库中，当一个事务首次发起一个DML语句(Insert、Update、Delete)或使用SELECT…FOR UPDATE语句进行查询时，它即获得一个TX(事务)锁，直至该事务结束(执行COMMIT或ROLLBACK操作)时，该锁才被释放。所以，一个TX锁可以对应多个被该事务锁定的数据行。当两个或多个会话在表的同一条记录上执行 DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放其他会话才可以加锁。当Oracle数据库发生TX锁等待时，如果不及时处理常常会引起Oracle数据库挂起或导致死锁的发生，产生ORA-60的错误。这些现象都会对实际应用产生极大的危害，如长时间未响应，大量事务失败等。

(2)ddl锁，dictionary locks 字典锁，用来保护数据对象的结构，如table，index的定义。

DDL锁又可以分为：排它DDL锁、共享DDL锁、分析锁

排它DDL锁

创建、修改、删除一个数据库对象的DDL语句获得操作对象的排它锁。如使用alter table语句时，为了维护数据的完成性、一致性、合法性，该事务获得一排它DDL锁。

共享DDL锁

需在数据库对象之间建立相互依赖关系的DDL语句通常需共享获得DDL锁。

如创建一个包，该包中的过程与函数引用了不同的数据库表，当编译此包时，该事务就获得了引用表的共享DDL锁。

分析锁

ORACLE使用共享池存储分析与优化过的SQL语句及PL/SQL程序，使运行相同语句的应用速度更快。一个在共享池中缓存的对象获得它所引用数据库对象的分析锁。分析锁是一种独特的DDL锁类型，ORACLE使用它追踪共享池对象及它所引用数据库对象之间的依赖关系。当一个事务修改或删除了共享池持有分析锁的数据库对象时，ORACLE使共享池中的对象作废，下次在引用这条SQL/PLSQL语句时，ORACLE重新分析编译此语句。

(3)内部锁和闩 (internal locks andlatchs) 用来保护数据库内部结构，如sga内存结构。

内部闩锁：这是ORACLE中的一种特殊锁，用于顺序访问内部系统结构。当事务需向缓冲区写入信息时，为了使用此块内存区域，ORACLE首先必须取得这块内存区域的闩锁，才能向此块内存写入信息。

二、锁等待测试

1、模式锁等待

在session1中创建表test1,插入一条数据，然后在session1中更新test1表数据不提交。

SQL>create table test1 ( n int );
Tablecreated.
SQL>insert into test1 values ( 1 );
1 rowcreated.
SQL>commit;
Commitcomplete.
SQL>select * from test1;
N
----------
1
SQL>update test1 set n=2;
1 rowupdated.
SQL>

在session2中更新test1表数据也不进行提交。SQL>update test1 set n=6;

因为session1中更新语句未提交，因此session2中更新语句一直处于等待状态，该状态为锁等待状态。

2、检测数据库存在的锁

SQL>SELECT s.sid,
2 s.serial#,
3 s.username,
4 o.object_name,
5 o.object_type,
6 decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
7 s.osuser,
8 s.program,
9 s.machine,
10 s.terminal,
11 s.logon_time
12 FROMv$locked_object lo, dba_objects o, v$session s, v$lock l
13 WHERE lo.object_id = o.object_id and lo.session_id = s.sid andl.sid=s.sid
14 ORDER BY sid, s.serial#
15 ;
SID SERIAL# USERNAME OBJECT_NAME OBJECT_TYPE LOCK_LEVEL OSUSER PROGRAM MACHINE TERMINAL LOGON_TIME
-------------------- ---------- ------------ -------------- ---------- -------------------------------------- ------------ --------- -----------
138 3874 JASON TEST1 TABLE TABLE LOCK oracle sqlplus@server (TNS V1-V3) server pts/1 2013-8-30 1
138 3874 JASON TEST1 TABLE ROW LOCK oracle sqlplus@server (TNS V1-V3) server pts/1 2013-8-30 1
147 3476 JASON TEST1 TABLE ROW LOCK oracle sqlplus@server (TNS V1-V3) server pts/0 2013-8-30 1
147 3476 JASON TEST1 TABLE TABLE LOCK oracle sqlplus@server (TNS V1-V3) server pts/0 2013-8-30 1
SQL>

3、查看谁阻止了谁

SQL>select /*+rule*/
2 (select username from v$session where sid = a.sid) username,
3 a.sid,
4 (select serial# from v$session where sid = a.sid) serial#,
5 a.type,
6 a.id1,
7 a.id2,
8 a.lmode,
9 a.request,
10 a.block,
11 b.sid blocking_sid
12 fromv$lock a,
13 ( select * from v$lock
14 where request > 0
15 and type <> 'MR'
16 ) b
17 where a.id1 = b.id1(+)
18 and a.id2 = b.id2(+)
19 and a.lmode > 0
20 and a.type <> 'MR'
21 order by username,a.sid,serial#,a.type
22 ;
USERNAME SID SERIAL# TYPE ID1 ID2 LMODE REQUEST BLOCK BLOCKING_SID
------------------------ ---- ---------- ---------- ------- ---------- ----------------------
JASON 138 3874 TM 74609 0 3 0 0
JASON 147 3476 TM 74609 0 3 0 0
JASON 147 3476 TX 262163 10887 6 0 1 138
164 1 TS 3 1 3 0 0
165 1 CF 0 0 2 0 0
165 1 RS 25 1 2 0 0
165 1 XR 4 0 1 0 0
166 1 RT 1 0 6 0 0
8rows selected
SQL>

根据以上结果可以看出，会话147占有行级锁，阻塞了会话138,导致session 138无法更新完成。找到147会话提交或者进行回滚。否则根据如下不步骤解除锁等待。

第一步：查看数据库中存在的锁SELECTs.sid,

s.serial#,
s.username,
o.object_name,
o.object_type,
decode(l.type, 'TM', 'TABLE LOCK', 'TX','ROW LOCK', NULL) LOCK_LEVEL,
s.osuser,
s.program,
s.machine,
s.terminal,
s.logon_time
FROMv$locked_object lo, dba_objects o, v$session s, v$lock l
WHERElo.object_id = o.object_id and lo.session_id = s.sid and l.sid=s.sid
ORDERBY sid, s.serial#

第二步：查看谁阻止了谁select/*+rule*/

(select username from v$session where sid= a.sid) username,
a.sid,
(select serial# from v$session where sid =a.sid) serial#,
a.type,
a.id1,
a.id2,
a.lmode,
a.request,
a.block,
b.sid blocking_sid
fromv$lock a,
( select * from v$lock
where request > 0
and type <> 'MR'
) b
wherea.id1 = b.id1(+)
and a.id2 = b.id2(+)
and a.lmode > 0
and a.type <> 'MR'
orderby username,a.sid,serial#,a.type

第三步：第三步：删除锁表的会话altersystem kill session 'sid,serial#'

若出现ora-00031:sessionmarked for kill在oracle中无法杀掉的锁继续如下步骤

第四步：通过会话的sid号找出spid号(在操作系统层面的进程号)selectspid, osuser, s.program

fromv$session s,v$process p
wheres.paddr=p.addr and s.sid=sid_number (sid_number是上面的sid)

第五步：杀死进程kill-9 进程号

三、相关视图

V$LOCK

Thisview lists the locks currently held by the Oracle Database and outstandingrequests for a lock or latch.ColumnDatatypeDescriptionADDRRAW(4 | 8)Address of lock state object

KADDRRAW(4 | 8)Address of lock

SIDNUMBERIdentifier for session holding or acquiring the lock

TYPEVARCHAR2(2)Type of user or system lock

The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:

TM- DML enqueue

TX- Transaction enqueue

UL- User supplied

The locks on the system types are held for extremely short periods of time. The system type locks are listed in.

ID1NUMBERLock identifier #1 (depends on type)

ID2NUMBERLock identifier #2 (depends on type)

LMODENUMBERLock mode in which the session holds the lock:0- none

1- null (NULL)

2- row-S (SS)

3- row-X (SX)

4- share (S)

5- S/Row-X (SSX)

6- exclusive (X)

REQUESTNUMBERLock mode in which the process requests the lock:0- none

1- null (NULL)

2- row-S (SS)

3- row-X (SX)

4- share (S)

5- S/Row-X (SSX)

6- exclusive (X)

CTIMENUMBERTime since current mode was granted

BLOCKNUMBERA value of either 0 or 1, depending on whether or not the lock in question is the blocker.

V$LOCKED_OBJECT

This view lists all locksacquired by every transaction on the system. It shows which sessions areholding DML locks (that is, TM-type enqueues) on what objects and in what mode.ColumnDatatypeDescriptionXIDUSNNUMBERUndo segment number

XIDSLOTNUMBERSlot number

XIDSQNNUMBERSequence number

OBJECT_IDNUMBERObject ID being locked

SESSION_IDNUMBERSession ID

ORACLE_USERNAMEVARCHAR2(30)Oracle user name

OS_USER_NAMEVARCHAR2(30)OS user name

PROCESSVARCHAR2(12)OS process ID

LOCKED_MODENUMBERLock mode

v$lock

SID会话的sid，可以和v$session关联

TYPE区分该锁保护对象的类型，如tm，tx，rt，mr等

ID1锁标识1，详细见下说明

ID2锁标识2，详细见下说明

LMODE锁模式，LMODE取值0,1,2,3,4,5,6, 数字越大锁级别越高, 影响的操作越多。

REQUEST申请的锁模式，同lmode

CTIME已持有或者等待锁的时间

BLOCK是否阻塞其他会话锁申请 1:阻塞 0:不阻塞

关于ID1、ID2，TYPE取值不同其含义也有所不同：TYPEID1ID2

TM被修改表的标识(object_id)0

TX以十进制数值表示该事务所占用的回滚段号与该事务在该回滚段的事务表(Transaction table)中所占用的槽号(slot number，可理解为记录号)。其组成形式为：0xRRRRSSSS ( RRRR = RBS number, SSSS = slot )。以十进制数值表示环绕(wrap)次数，即该槽(slot)被重用的次数；

v$locked_object

XIDUSN undosegment number，可以和v$transaction关联。

XIDSLOT undoslot number。

XIDSQN序列号

OBJECT_ID被锁定对象的object_id ，可以和dba_objects关联。

SESSION_ID持有该锁的session_id，可以和v$session关联。

ORACLE_USERNAME持有该锁的oracle帐号

OS_USER_NAME持有该锁的操作系统帐号

PROCESS操作系统的进程号，可以和v$process关联

LOCKED_MODE锁模式，含义同v$lock.lmode

V$session

如果某个session由于某些行被其他会话锁定而阻塞，则该视图中的下面四个字段列出了这些行所属对象的相关信息

ROW_WAIT_FILE# 等待的行所在的文件号

ROW_WAIT_OBJ#等待的行所属的object_id

ROW_WAIT_BLOCK# 等待的行所属的block

ROW_WAIT_ROW#等待的行在blcok中的位置

通过如下语句可以查出被锁会话的相关信息

selectROW_WAIT_FILE#,ROW_WAIT_OBJ#,ROW_WAIT_ROW#,ROW_WAIT_BLOCK# FROM V$SESSION where sid=blocking_sid

四、死锁测试

死锁产生的四个必要条件

1)Mutual exclusion(互斥)：资源不能被共享，只能由一个进程使用。

2)Hold and wait(请求并保持)：已经得到资源的进程可以再次申请新的资源。

3)No pre-emption(不可剥夺)：已经分配的资源不能从相应的进程中被强制地剥夺。

4)Circular wait(循环等待条件)：系统中若干进程组成环路，该环路中每个进程都在等待相邻进程正占用的资源。

测试死锁

第一步：首先在会话134中创建T1表并插入一条数据并提交。SQL>create table T1 (n int);

Tablecreated.
SQL>insert into T1 values(1);
1 rowcreated.
SQL>commit;
Commitcomplete.
SQL>

第二步：接下来在会话141中创建T2表并插入一条数据并提交。SQL>create table T2 (n int);

Tablecreated.
SQL>insert into T2 values(2);
1 rowcreated.
SQL>commit;
Commitcomplete.
SQL>

第三步：

切换窗口到会话134中更新T1表数据不提交。SQL>update T1 set n=100 where n=1;

1 rowupdated.

SQL>

同样在会话141中更新表T2表数据不提交。SQL>update T2 set n=200 where n=2;

1 rowupdated.

SQL>

第四步：

切换窗口回到会话134中执行更新T2表数据，更新语句处于锁等待状态中。SQL>update T2 set n=400 where n=2;

打开新窗口通过查看锁等待语句可以发现锁等待，如下：

SQL>select /*+rule*/
2 (select username from v$session where sid = a.sid) username,
3 a.sid,
4 (select serial# from v$session where sid = a.sid) serial#,
5 a.type,
6 a.id1,
7 a.id2,
8 a.lmode,
9 a.request,
10 a.block,
11 b.sid blocking_sid
12 fromv$lock a,
13 ( select * from v$lock
14 where request > 0
15 and type <> 'MR'
16 ) b
17 where a.id1 = b.id1(+)
18 and a.id2 = b.id2(+)
19 and a.lmode > 0
20 and a.type <> 'MR'
21 order by username,a.sid,serial#,a.type
22 ;
USERNAME SID SERIAL# TYPE ID1 ID2 LMODE REQUEST BLOCK BLOCKING_SID
------------------------- ---- ---------- ---------- ---------- ---------- ----------------------
JASON 134 593 TM 74619 0 3 0 0
JASON 134 593 TM 74618 0 3 0 0
JASON 134 593 TX 131117 10937 6 0 0
JASON 141 5406 TM 74619 0 3 0 0
JASON 141 5406 TX 393252 10954 6 0 1 134
164 1 TS 3 1 3 0 0
165 1 CF 0 0 2 0 0
165 1 RS 25 1 2 0 0
165 1 XR 4 0 1 0 0
166 1 RT 1 0 6 0 0
10rows selected
SQL>

第五步：

切换窗口到在会话141中执行更新T1表数据。SQL>update T1 set n=300 where n=1;

更新语句处于锁等待状态，这时观察会话134窗口将出现如下信息;

SQL>update T2 set n=400 where n=2;
updateT2 set n=400 where n=2
*
ERRORat line 1:
ORA-00060:deadlock detected while waiting for resource
SQL>

查看数据警告日志及trc信息将看到警告信息：

警告日志信息：

ORA-00060:Deadlock detected. More info in file /home/oracle/orabase/admin/ORACLE/udump/oracle_ora_32577.trc.
trc中的信息如下：DEADLOCKDETECTED
[TransactionDeadlock]
CurrentSQL statement for this session:
updateT1 set n=400 where n=2
Thefollowing deadlock is not an ORACLE error. It is a
deadlockdue to user error in the design of an application
orfrom issuing incorrect ad-hoc SQL. The following
informationmay aid in determining the deadlock:
Deadlockgraph:
---------Blocker(s)-------- ---------Waiter(s)---------
ResourceName process session holdswaits process session holds waits
TX-0002002d-00002ab9 21 134 X 22 141 X
TX-00060024-00002aca 22 141 X 21 134 X
session134: DID 0001-0015-00000098 session141: DID 0001-0016-00000079
session141: DID 0001-0016-00000079 session134: DID 0001-0015-00000098
Rowswaited on:
Session141: obj - rowid = 0001237A - AAASN6AAGAAACeFAAA
(dictionary objn - 74618, file - 6, block - 10117,slot - 0)
Session134: obj - rowid = 0001237B - AAASN7AAGAAACeOAAA
(dictionary objn - 74619, file - 6, block -10126, slot - 0)
Informationon the OTHER waiting sessions:
Session141:
pid=22 serial=5406 audsid=81728 user:55/JASON
O/S info: user: oracle, term: pts/1, ospid:32730, machine: server
program: sqlplus@server (TNS V1-V3)
application name: SQL*Plus, hashvalue=3669949024
Current SQL Statement:
update T1 set n=400 where n=2
Endof information on OTHER waiting sessions.

本文整理自网络及实验测试，参考链接http://space.itpub.net/519536！

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。