Oracle11g中的锁
1 锁的概念
锁是数据库用来控制共享资源并发访问的机制。锁用于保护正在被修改的数据,直到提交或回滚了事务之后,其他用户才可以更新数据。
锁的优点:
一致性:一次只允许一个用户修改数据。
完整性:为所有用户提供正确的数据。如果一个用户进行了修改并保存,所做的修改将反映给所有用户。
并行性:允许多个用户访问同一数据。
对于 DML 操作
行锁(TX)只有一种。
表锁(TM)共有五种:分别是 RS、RX、S、SRX、X。
2 五种 TM 表锁的含义
![](https://i-blog.csdnimg.cn/blog_migrate/0e437bffe061277a91428c634fd684d2.png)
ROW SHARE行共享
(RS):允许其他用户同时更新其他行,允许其他用户同时加共享锁,不允许有独占(排他性质)的锁。
ROW EXCLUSIVE行排他
(RX):允许其他用户同时更新其他行,只允许其他用户同时加行共享锁或者行排他锁。
SHARE共享
( S):不允许其他用户同时更新任何行,只允许其他用户同时加共享锁或者行共享锁。
SHARE ROW EXCLUSIVE共享行排他
(SRX):不允许其他用户同时更新其他行,只允许其他用户同时加行共享锁。
EXCLUSIVE排他
(X):其他用户禁止更新任何行,禁止其他用户同时加任何排他锁。
sql语句 | 加锁模式 | 许可其他用户的加锁模式 |
---|---|---|
select * from table_name | 无 | RS, RX, S, SRX, X |
insert, update, delete (DML操作) | RX | RS, RX |
select * from table_name for update | RX | RS, RX |
lock table table_name in row share mode | RS | RS, RX, S, SRX |
lock table table_name in row exclusive mode | RX | RS, RX |
lock table table_name in share mode | S | RS, S |
lock table table_name in share row exclusive mode | SRX | RS |
lock table table_name in exclusive mode | X | 无 |
3 锁的实践
create table MICHAEL_GOODS_TEST_DOC (
goodsid NUMBER(10) not null,
goodsname VARCHAR2(100),
gspflag NUMBER(1),
factoryid NUMBER(10),
gspcategoryid NUMBER(10),
usestatus NUMBER(2),
inputmanid NUMBER(10),
credate DATE
);
alter table MICHAEL_GOODS_TEST_DOC
add constraint MICHAEL_GOODS_TEST_DOC_PK primary key (GOODSID);
insert into michael_goods_test_doc (GOODSID, GOODSNAME, GSPFLAG, FACTORYID, GSPCATEGORYID, USESTATUS, INPUTMANID, CREDATE)
values (22, '苹果1', 0, 21, 104, 1, 1, to_date('28-03-2022', 'dd-mm-yyyy'));
insert into michael_goods_test_doc (GOODSID, GOODSNAME, GSPFLAG, FACTORYID, GSPCATEGORYID, USESTATUS, INPUTMANID, CREDATE)
values (23, '桃子1', 1, 22, 106, 1, 1, to_date('28-03-2022', 'dd-mm-yyyy'));
insert into michael_goods_test_doc (GOODSID, GOODSNAME, GSPFLAG, FACTORYID, GSPCATEGORYID, USESTATUS, INPUTMANID, CREDATE)
values (24, '香蕉1', 1, 23, 104, 0, 1, to_date('28-03-2022', 'dd-mm-yyyy'));
insert into michael_goods_test_doc (GOODSID, GOODSNAME, GSPFLAG, FACTORYID, GSPCATEGORYID, USESTATUS, INPUTMANID, CREDATE)
values (25, '榴莲1', 0, 24, 105, 1, 1, to_date('28-03-2022', 'dd-mm-yyyy'));
insert into michael_goods_test_doc (GOODSID, GOODSNAME, GSPFLAG, FACTORYID, GSPCATEGORYID, USESTATUS, INPUTMANID, CREDATE)
values (26, '西瓜1', 1, 25, 106, 0, 1, to_date('28-03-2022', 'dd-mm-yyyy'));
insert into michael_goods_test_doc (GOODSID, GOODSNAME, GSPFLAG, FACTORYID, GSPCATEGORYID, USESTATUS, INPUTMANID, CREDATE)
values (27, '柠檬1', 0, 26, 105, 1, 1, to_date('28-03-2022', 'dd-mm-yyyy'));
3.1.0 自动加锁
做DML操作时,如 insert update delete ,以及 select…for update 由 oracle 自动完成加锁。
3.1.1 明确指定[主键/索引/字段],查有数据,ROW_LOCK
![](https://i-blog.csdnimg.cn/blog_migrate/acf50a119917b676eb4bd6492ee978a0.png)
在非主键的情况下,Orcale也是 ROW_LOCK
SELECT * FROM michael_goods_test_doc t WHERE t.factoryid=21 FOR UPDATE;
SELECT * FROM michael_goods_test_doc t WHERE t.factoryid=22 FOR UPDATE;
3.1.2 明确指定[主键/索引/字段],查无数据,NO_LOCK
![](https://i-blog.csdnimg.cn/blog_migrate/c561e72f937ecf27d5d5ab9741a29824.png)
3.1.3 无条件查询,TABLE_LOCK
![](https://i-blog.csdnimg.cn/blog_migrate/b754d13c9acf7bf607a5516ae85b2d18.png)
3.2.0 手动加锁
用lock命令以显式的方式加锁
lock table 表名 in exclusive mode. mode.(一般限于后三种表锁
观察锁的动态视图 v$lock
观察锁的静态视图 dba_locks
select * from v$lock;
select * from dba_locks where session_id=666;
锁的类型:
1)行级锁 ROW LEVEL LOCK
对正在被修改的行进行锁定。其他用户可以访问除被锁定的行以外的行
1> 行级锁是一种排他锁,防止其他事务修改此行;在使用以下语句时,Oracle会自动应用行级锁:
INSERT,UPDATE,DELETE,SELECT … FOR UPDATE
2> SELECT … FOR UPDATE语句允许用户一次锁定多条记录进行更新
使用COMMIT或ROLLBACK语句释放锁.
SELECT … FOR UPDATE语法:
SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT];
SQL> SELECT * FROM emp WHERE sal=1000
FOR UPDATE;
SQL> UPDATE emp SET sal = 3000
WHERE sal =1000;
SQL> COMMIT;
SQL> SELECT * FROM scott.emp WHERE sal=1000
FOR UPDATE WAIT 5;
SQL> SELECT * FROM scott.emp WHERE sal=1000
FOR UPDATE NOWAIT ;
2)表级锁
锁定整个表,限制其他用户对表的访问。
使用命令显示地锁定表,应用表级锁的语法是:
LOCK TABLE table_name IN mode MODE;
- 行共享 (ROW SHARE) – 禁止排他锁定表
- 行排他(ROW EXCLUSIVE) – 禁止使用排他锁和共享锁
- 共享锁(SHARE)
1> 锁定表,仅允许其他用户查询表中的行。
2> 禁止其他用户插入、更新和删除行。
3> 多个用户可以同时在同一个表上应用此锁。 - 共享行排他(SHARE ROW EXCLUSIVE) – 比共享锁更多的限制,禁止使用共享锁及更高的锁
- 排他(EXCLUSIVE) – 限制最强的表锁,仅允许其他用户查询该表的行。禁止修改和锁定表
死锁
- 当两个事务相互等待对方释放资源时,就会形成死锁。
- Oracle会自动检测死锁,并通过结束其中的一个事务来解决死锁。
4 锁的查询与释放
--锁表查询SQL
select *
from v$lock
where type in ('TX','TM');
--锁表查询SQL
select a.sid,b.sql_text,a.serial#
from v$session a, v$sql b
where a.prev_sql_id=b.sql_id and a.sid=666;
--锁表查询SQL
select sid,blocking_session,username,event,serial#
from v$session
where blocking_session_status='VALID';
--释放SESSION SQL
ALTER SYSTEM KILL SESSION 'sid,serial#';
--锁表查询SQL gbase?
SELECT object_name, machine, s.sid, s.serial#
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid;
--释放SESSION SQL: gbase?
--alter system kill session 'sid, serial#';
ALTER system kill session '235, 49711';
更详细的信息,可以从多个视图得出,如:
v$session
,v$process
,v$sql
,v$locked
,v$sqlarea
等等。
阻塞(排队) 从 OEM 里看的更清楚 OEM ---->Performance ---->Additional Monitoring Links -->Blocking Sessions 或(Instance Locks)