Oracle锁的示例

最新推荐文章于 2023-12-26 13:37:12 发布

猿界零零七

最新推荐文章于 2023-12-26 13:37:12 发布

阅读量1.3k

点赞数 1

分类专栏：知识总结文章标签： oracle

本文链接：https://blog.youkuaiyun.com/Flychuer/article/details/121820379

版权

知识总结专栏收录该内容

26 篇文章

订阅专栏

锁是解决事务隔离性的一种内部机制，有关锁的详细介绍，请参考：https://blog.youkuaiyun.com/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;

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

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;

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