-------------------锁及事务的高级使用--------------------
锁是数据库用来控制共享资源并发访问的机制。
锁用于保护正在被修改的数据
直到提交或回滚了事务之后,其他用户才可以更新数据
一致性 - 一次只允许一个用户修改数据
完整性 - 为所有用户提供正确的数据。如果一个用户进行了修改并保存,所做的修改将反映给所有用户
并行性 -允许多个用户访问同一数据
----------------------行级锁
行级锁:对正在被修改的行进行锁定。其他用户可以访问除被锁定的行以外的行
行级锁是一种排他锁,防止其他事务修改此行
在使用以下语句时,Oracle会自动应用行级锁:
INSERT
UPDATE
DELETE
SELECT … FOR UPDATE
SELECT … FOR UPDATE语句允许用户一次锁定多条记录进行更新
使用COMMIT或ROLLBACK语句释放锁
SELECT … FOR UPDATE语法:
SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT];
说明: OF 子句用于指定即将更新的列,即锁定行上的特定列。 WAIT 子句指定等待其他用户释放锁的秒数,防止无限期的等待
SQL> SELECT * FROM order_master WHERE vencode=’V002’
FOR UPDATE OF odate, del_date;
SQL> UPDATE order_master SET del_date=’28-8月-05’
WHERE vencode=’V002’;
SQL> COMMIT;
SQL> SELECT * FROM order_master WHERE vencode=’V002’
FOR UPDATE WAIT 5;
SQL> SELECT * FROM order_master WHERE vencode=’V002’
FOR UPDATE NOWAIT;
“使用FOR UPDATE WAIT”子句的优点如下:
1.防止无限期地等待被锁定的行;
2.允许应用程序中对锁的等待时间进行更多的控制
3.对于交互式应用程序非常有用,因为这些用户不 能等待不确定
4.若使用了skip locked,则可以越过锁定的行,不会报告由wait n 引发的‘资源忙’异常报告
封锁问题
A:用户1检索一行数据,并准备修改
B:用户2检索到相同的一行数据
C:用户2删除了这一行,并提交
D:用户1修改那一行,并提交,结果程序报错,该行不存在
处理方法:用户1: select * from tab for update
insert update delete 与 select ..for update 都是行级锁
----------------------表级锁
表级锁:锁定整个表,限制其他用户对表的访问。
使用命令显示地锁定表,应用表级锁的语法是:
LOCK TABLE table_name IN mode MODE;
行共享 (ROW SHARE) – 禁止排他锁定表
行排他 (ROW EXCLUSIVE) – 禁止使用排他锁和共享锁
共享锁 (SHARE)
锁定表,仅允许其他用户查询表中的行
禁止其他用户插入、更新和删除行
多个用户可以同时在同一个表上应用此锁
共享行排他(SHARE ROW EXCLUSIVE) – 比共享锁更多的限制,禁止使用共享锁及更高的锁
排他(EXCLUSIVE) – 限制最强的表锁,仅允许其他用户查询该表的行。禁止修改和锁定表
当一个事物在进行DML操作时,所在的事务会对所操作的表加表级锁,以保证在这个事务过程中表不被改变。
RS (Row share table lock)
以下的操作会产生RS lock
Select * from tab for update.
LOCK TABLE table IN ROW SHARE MODE;
RS不允许的操作:
LOCK TABLE table IN EXCLUSIVE MODE;
RX (Row Exclusive Table Locks)
以下的操作会产生RS lock
1:INSERT,UPDATE ,DELETE
2:LOCK TABLE table IN ROW EXCLUSIVE MODE;
RX不允许的操作
LOCK TABLE table IN SHARE MODE;
LOCK TABLE table IN SHARE EXCLUSIVE MODE;
LOCK TABLE table IN EXCLUSIVE MODE;
S (Share Table Locks )
以下操作产生S lock.
LOCK TABLE table IN SHARE MODE;
S不允许的操作
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE table IN EXCLUSIVE MODE;
LOCK TABLE table IN ROW EXCLUSIVE MODE;
SRX (Share Row Exclusive Table Locks)
以下操作产生SRX LOCK
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;
SRX不允许的操作.
LOCK TABLE table IN SHARE MODE;
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE; LOCK TABLE table IN ROW EXCLUSIVE MODE;
LOCK TABLE table IN EXCLUSIVE MODE;
X( Exclusive Table Locks )
以下操作产生X LOCK
LOCK TABLE table IN EXCLUSIVE MODE;
X不允许的操作
不允许所有的操作
--0:none
--1:null 空
Select,有时会在v$locked_object出现。
--2:Row-S 行共享(RS):共享表锁,sub share
2级锁有:Select for update,Lock For Update,Lock Row Share
select for update当对话使用for update子串打开一个游标时,所有返回集中的数据行都将处于行级(Row-X)独占式锁定,其他对象只能查询这些数据行,不能进行update、delete或select for update操作。
--3:Row-X 行独占(RX):用于行的修改,sub exclusive
3级锁有:Insert, Update, Delete, Lock Row Exclusive
没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。
--4:Share 共享锁(S):阻止其他DML操作,share
4级锁有:Create Index, Lock Share
locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。
00054, 00000, "resource busy and acquire with NOWAIT specified"
// *Cause: Resource interested is busy.
// *Action: Retry if necessary.
--5:S/Row-X 共享行独占(SRX):阻止其他事务操作,share/sub exclusive级锁有:Lock Share Row Exclusive
具体来讲有主外键约束时update / delete ... ; 可能会产生4,5的锁。
--6:exclusive 独占(X):独立访问使用,exclusive
6级锁有:Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive
死锁
当两个事务相互等待对方释放资源时,就会形成死锁
Oracle会自动检测死锁,并通过结束其中的一个事务来解决死锁
死锁现象:
1.用户1对表A进行update,没有提交
2.用户2对表B进行update,没有提交
3.如果用户2此时对A表作update,则会发生阻塞锁现象
4.如果用户1此时对B表作update,则产生死锁现象
----------------------事务简介
事务:就是一个完整的逻辑工作单元,由多个操作组成,所以操作要么全部成功,要么全部失败。
事务特点:ACID
原子性,一致性,隔离型,持久性
数据库事务:
由多个SQL语句组成的一个逻辑工作单元,所以有SQL都必须同时执行成功,只要其中一个执行失败,则所有执行过的SQL自动全部回滚;
----------------------基本的事务处理
在oracle中commit,rollback,savepoint这三条语句可以完 成基本的事务管理功能:
开始事务:
transation.begintransation
oracle:执行第一个SQL事务开始
执行SQL语句
提交事务
自动提交
回滚事务
事务是最小的工作单元,作为一个整体进行工作
保证事务的整体成功或失败,称为事务控制
用于事务控制的语句有:
COMMIT - 提交并结束事务处理
当向数据库发出 commit 语句,那该事务就被终结了,并且:
事务完成的所有工作永久化;
其它事务可以看到此事务所作的修改;
事务所需要执行的所有加锁(lock)处理被释放。
ROLLBACK - 撤销事务中已完成的工作
当向数据库发出 rollback 语句,那该事务就被终结了,并且:
事务完成的所有工作被取消(undo);
事务所需要执行的所有加锁(lock)处理被释放。
SAVEPOINT – 标记事务中可以回滚的点
rollback 会撤消整个事务,使用 savepoint 可以做到部分撤消事务。
SQL> UPDATE order_master
SET del_date = ‘30-8月-05’
WHERE orderno <= ’o002’;
SQL> SAVEPOINT mark1;
SQL> DELETE FROM order_master WHERE orderno = ‘o002’;
SQL> SAVEPOINT mark2;
SQL> ROLLBACK TO SAVEPOINT mark1;
SQL> COMMIT;
----------------------Oracle中DDL语句对事务的影响
在Oracle中,执行DDL语句(如Create Table、Create View等)时,会在执行之前自动发出一个Commit命令,并在随后发出一个Commit或者Rollback命令,也就是说,DDL会象如下伪码一样执行:
Commit;
DDL_Statement;
If (Error) then
Rollback;
Else
Commit;
End if;
select * from emp;
update emp set empno = 8000 where empno=7900;
savepoint my_savepoint1;
delete emp where empno = 8000;
savepoint my_savepoint2;
rollback to savepoint my_savepoint1;
update emp set empno = 7900 where empno=8000;
commit;
update emp set empno = 8000 where empno=7900;
rollback;
PL/SQL:锁及事务的高级使用
最新推荐文章于 2023-06-04 11:58:55 发布