Oracle锁的示例

目录

一、准备工作

1.1、测试表

1.2、查看锁

1.3、查看会话ID

1.4、锁模式

二、DML操作

2.1、INSERT操作

2.1.1、单个会话插入

2.1.2、多个会话插入

2.1.3、带索引多会话插入

2.1.4、带有主键的多会话插入

2.2、UPDATE操作

2.2.1、多会话修改不同行数据

2.2.2、多会话修改相同行数据

2.3、FOR UPDATE操作

2.4、DELETE操作

2.4.1、多会话删除不同行数据

2.4.2、多会话删除相同行数据

三、DDL操作

3.1、创建索引

3.2、删除索引

四、主动加锁

4.1、锁模式为2的锁

4.2、锁模式为3的锁

4.3、锁模式为4的锁

4.4、锁模式为5的锁

4.5、锁模式为6的锁

---

锁是解决事务隔离性的一种内部机制，有关锁的详细介绍，请参考：https://blog.csdn.net/Flychuer/article/details/120766168

本篇主要用一些示例来解释锁。

版本：Red Hat Linux 6、Oracle 11.2

工具：PL/SQL Developer

说明：为了方便查看锁，本篇采用了大批量操作数据，可根据个人机器性能，调整操作数据的条数。

一、准备工作

1.1、测试表

创建一张测试表CST_TRAN

CREATE TABLE CST_TRAN
(
CST_NO NUMBER,
TRAN_DATE VARCHAR2(8),
TRAN_AMT NUMBER(19,3)
);

1.2、查看锁

接下来的所有示例中，都要使用此SQL代码查看锁的信息。

SELECT L.SID         "会话ID",
       S.SERIAL#     "会话序列号",
       P.SPID        "会话进程号",
       L.TYPE        "锁类型",
       S.USERNAME    "所属用户",
       S.MACHINE     "客户端",
       O.OBJECT_NAME "被锁对象",
       O.OBJECT_TYPE "被锁对象类型",
       L.CTIME       "被锁时间(S)",
       L.LMODE       "锁模式",
       L.REQUEST     "请求资源锁模式",    
       L.ID1,
       L.ID2,
       L.BLOCK,
       Q.SQL_TEXT    "执行SQL",
       S.ROW_WAIT_OBJ# "等待被锁定对象",
       S.ROW_WAIT_FILE#,
       S.ROW_WAIT_BLOCK#,
       S.ROW_WAIT_ROW#
  FROM V$SESSION S
  INNER JOIN V$LOCK L
  ON L.SID = S.SID
  LEFT JOIN V$PROCESS P
  ON S.PADDR = P.ADDR
  LEFT JOIN V$SQL Q
  ON Q.SQL_ID = S.SQL_ID
  LEFT JOIN V$LOCKED_OBJECT LO
  ON LO.SESSION_ID = S.SID
  LEFT JOIN DBA_OBJECTS O
  ON O.OBJECT_ID = LO.OBJECT_ID
 WHERE S.SCHEMA# <> 0
   AND L.TYPE IN ('TX', 'TM')
   AND S.USERNAME = 'CHF';

1.3、查看会话ID

因为我们在测试时，使用的时PL/SQL Developer工具，需要开多个窗口执行SQL语句，每个窗口都有一个会话ID，可以通过如下语句查看本窗口SID

SELECT SID FROM V$MYSTAT WHERE ROWNUM=1;

1.4、锁模式

锁模式	锁描述	解释	SQL操作
0	None
1	NULL	空	Select
2	SS (Row-S)	行级共享锁，其他对象只能查询这些数据行；是锁的类型中限制最少的锁，也是在表的并发程度中使用最多的	Select for update、Lock for update、Lock row share
3	SX (Row-X)	行级排它锁，在提交前不允许做DML操作	Insert、Update、 Delete、Lock row share
4	S(Share)	共享锁	Create index、Lock share
5	SSX (Share-Row-X)	共享行级排它锁	Lock share row exclusive
6	X(Exclusive)	排它锁	Alter table、Drop able、Drop index、Truncate table 、Lock exclusive

二、DML操作

2.1、INSERT操作

2.1.1、单个会话插入

测试代码一、语句如下

INSERT INTO CST_TRAN
SELECT ROUND(DBMS_RANDOM.VALUE(1,10000))+10000,
       TO_CHAR(SYSDATE - DBMS_RANDOM.VALUE(1,1000),'yyyymmdd'),
       ROUND(DBMS_RANDOM.VALUE(1,999999999),3)
FROM DUAL
CONNECT BY LEVEL<=1000000;

执行过程中查看锁信息

可以看出，insert时，共产生两个锁，TX类型的排它锁和TM类型的行级排它锁

2.1.2、多个会话插入

打开两个窗口分别执行测试代码一中的代码，注意要同时执行，执行过程中查看锁信息

可以看出，每个会话都有两个锁，分别是TX类型的排它锁和TM类型的行级排它锁。但是先执行的会话不会阻塞后执行的会话，因为此时的插入操作没有共享的行数据。

2.1.3、带索引多会话插入

先清空表，再为CST_TRAN创建索引

CREATE INDEX IDX_CST_TRAN_01 ON CST_TRAN(CST_NO);

打开两个窗口，分别向 CST_TRAN 中插入CST_NO相同的客户

INSERT INTO CST_TRAN
SELECT ROUND(DBMS_RANDOM.VALUE(1,10000))+10000,
       TO_CHAR(SYSDATE - DBMS_RANDOM.VALUE(1,1000),'yyyymmdd'),
       ROUND(DBMS_RANDOM.VALUE(1,999999999),3)
FROM DUAL
CONNECT BY LEVEL<=1000000;

在代码执行中查看锁信息：

可以看出，每个会话都有两个锁，分别是TX类型的排它锁和TM类型的行级排它锁。但是先执行的会话不会阻塞后执行的会话，因为此时的插入操作没有共享的行数据。 

2.1.4、带有主键的多会话插入

先清空表并删除2.1.3中索引，再为CST_TRAN创建主键

ALTER TABLE CST_TRAN ADD CONSTRAINT PK_CST_TRAN PRIMARY KEY (CST_NO);

打开两个窗口，分别向 CST_TRAN 中插入CST_NO相同的客户

INSERT INTO CST_TRAN
SELECT level + 10000000,
       TO_CHAR(SYSDATE - DBMS_RANDOM.VALUE(1,1000),'yyyymmdd'),
       ROUND(DBMS_RANDOM.VALUE(1,999999999),3)
FROM DUAL
CONNECT BY LEVEL<=1000000;

在执行过程中查看锁信息：

可以看出会话125被会话141阻塞，对比红色两条数据发现。。。。。。

2.2、UPDATE操作

2.2.1、多会话修改不同行数据

开启两个会话，分别执行以下两条语句

UPDATE CST_TRAN T SET T.TRAN_AMT = 500 WHERE T.CST_NO <=10200000;

UPDATE CST_TRAN T SET T.TRAN_AMT = 500 WHERE T.CST_NO =10300000;

查看锁信息

可以看出两条语句执行互补干扰，因为它们锁定的是不同的行。

2.2.2、多会话修改相同行数据

开启两个会话，执行相同的语句

UPDATE CST_TRAN T SET T.TRAN_AMT = 500 WHERE T.CST_NO <=10200000;

查看锁信息

可以看出会话125请求TX的模式为6的锁被141阻塞，因为141持有这个对象的TX的模式为6的锁。

2.3、FOR UPDATE操作

锁信息与UPDATE类似，大家可以自己动手试试。

2.4、DELETE操作

2.4.1、多会话删除不同行数据

开启两个窗口，分别执行如下语句

DELETE FROM CST_TRAN T WHERE T.CST_NO =10200000;

DELETE FROM CST_TRAN T WHERE T.CST_NO =10300000;

 查看锁信息

可以看出两条语句执行互补干扰，因为它们锁定的是不同的行。

2.4.2、多会话删除相同行数据

开启两个会话，执行相同的语句

DELETE FROM CST_TRAN T；

查看锁信息

可以看出会话125请求TX的模式为6的锁被141阻塞，因为141持有这个对象的TX的模式为6的锁。

三、DDL操作

3.1、创建索引

执行语句

CREATE INDEX IDX_CST_TRAN_01 ON CST_TRAN(CST_NO);

查看锁信息

我自己也凌乱了，没搞明白为啥这么多锁。 后续补充，希望大佬提宝贵意见。。。。。。

3.2、删除索引

我的天呢，执行太快，没法实现。

四、主动加锁

4.1、锁模式为2的锁

LOCK TABLE CST_TRAN IN SHARE UPDATE MODE;
LOCK TABLE CST_TRAN IN ROW SHARE MODE;

查看锁信息

4.2、锁模式为3的锁

LOCK TABLE CST_TRAN IN ROW EXCLUSIVE MODE;

查看锁信息

4.3、锁模式为4的锁

LOCK TABLE CST_TRAN IN SHARE MODE;

查看锁信息

4.4、锁模式为5的锁

LOCK TABLE CST_TRAN IN SHARE ROW EXCLUSIVE MODE;

查看锁信息

4.5、锁模式为6的锁

LOCK TABLE CST_TRAN IN EXCLUSIVE MODE;

查看锁信息