以前很讨厌Oracle数据库,做DML(insert/update/delete)操作还要commit,但深入理解Oracle数据库的锁机制后,才认识到Oracle数据库的优秀和commit/rollback的合理性。Oracle数据库对并发操作的支持做得非常好,但如果不合理利用,后果同样很严重。
Oracle数据库的锁粒度上分为表级锁和行级锁,但实际上通常考虑行级锁就可以了。表级锁应用在建立表,修改表结构,建立索引,删除表等操作上,这是重大操作,一般的后台不会提供这样的权限。因此本文只谈行级锁。
行级锁仅有一个锁类型,就是TX锁。TX锁的T代表transaction,表面上一个TX锁对应一条记录,但实际上整个DML操作无论做多少遍,只要没commit,加在记录上的TX锁状态就不会改变。X代表排斥(exclusive),表级锁X不允许没持有权限的session(会话)进行任何相关表(包括表中记录)的读和写操作,但TX锁允许没持有权限的session读取记录,这涉及到Oracle副本机制,实际上session读取到的是原始记录,而非副本中的修改记录。
TX锁权限的获得方法有:
1.DML操作(insert/update/delete)
2.使用SQL语句:select *** from TableName (where ***) for update nowait;
不要小看第二种方法。
session释放当前持有的全部TX锁权限的方法有:commit和rollback。commit是合并副本到本体,rollback是抛弃副本。
启动两个Oracle SQL Plus窗口,这将是两个不同的session,可以用来模拟两个客户的行为。用这两个窗口做下面的实验,将会得出一些重要的认识。
Oracle数据库的副本机制
1.表的操作create/drop不会产生副本,倘若操作成功,作用是立刻反映到本体上。因此create和drop操作事实上无需补上commit
session甲:
create table test(
id number(5) primary key,
value NVARCHAR2(10));
session乙:select * from test;
session乙没有错误产生。
session甲:drop table test;
session乙:select * from test;
session乙将发生错误。
重新在session甲创建test表,继续下面的实验。
Oracle数据库的sequence
Oracle中的主键不会自动递增,需要手动创建sequence,插入新记录时使用sequence的nextval
2.sequence和事务无关,不需要锁。
session甲:create sequence test_seq increment by 1 start with 1 maxvalue 99999;
session甲:commit;
session甲:insert into test values(test_seq.nextval, 'test');
session甲:select * from test;
ID VALUE
---------- --------------------
1 test
session乙:insert into test values(test_seq.nextval, 'test2');
session乙:select * from test;
ID VALUE
---------- --------------------
2 test2
session甲和session乙都commit后,select * from test的结果是
ID VALUE
---------- --------------------
1 test
2 test2
Oracle数据库的TX锁机制和副本机制
3.Oracle数据库的记录差异会保存到副本中(借用一下网游的名词),每个session对记录的修改都会产生各自的副本,只有在commit后,副本才会合并到本体中。
session乙:delete from test where id=2;
session乙:select * from test;
ID VALUE
---------- --------------------
1 test
session甲:select * from test;
ID VALUE
---------- --------------------
1 test
2 test2
session乙:commit;
session甲:select * from test;
ID VALUE
---------- --------------------
1 test
4.仅有差异化会保存到副本,也即只有持有TX权限的记录的修改结果会保存到副本。一个session commit后,差异化合并到本体,另外的session的select仍然会发生变化,也即session对记录的select是同时根据本体和当前持有的副本。
session乙:insert into test values(test_seq.nextval, 'test3');
session甲:update test set value='test111' where id=1;
session乙:select * from test;
ID VALUE
---------- --------------------
1 test
3 test3
session甲:commit;
session乙:select * from test;
ID VALUE
---------- --------------------
1 test111
3 test3
session甲:insert into test values(test_seq.nextval, 'test4');
session甲:commit;
session乙:select * from test;
ID VALUE
---------- --------------------
1 test111
3 test3
4 test4
session甲:select * from test;
ID VALUE
---------- --------------------
1 test111
4 test4
session乙:commit;
session甲:select * from test;
ID VALUE
---------- --------------------
1 test111
3 test3
4 test4
5.一个记录的TX权限被session乙取得后,session甲对这个记录的DML操作会进入等待状态,直到TX锁被释放,session甲才能获得这个记录的TX权限,这是Oracle数据库解决操作冲突的机制。
session乙:update test set value='test1' where id=1;
session甲:update test set value='test1111' where id=1;
session甲会进入等待状态
session乙:commit;
session甲会执行SQL语句(同时获得了TX锁权限)
session甲:commit;
session乙:delete from test where id=1;
session甲:update test set value='test' where id=1;
session甲会进入等待状态
session乙:commit;
session甲会执行SQL语句,但要修改的记录已经不存在,所以影响了0条记录
session甲:commit;
(是否执行commit已经没所谓,但在商业程序中,在DML操作后一定要commit,不管是否记录数为0,DML操作如果失败,则执行rollback)
6.第5条中的DML操作包括insert,对存在primary key insert具有相同key值的记录,同样会产生TX权限冲突。
session乙:insert into test values(0, 'test0');
session甲:insert into test values(0, 'test000');
session甲会进入等待状态
session乙:commit;
session甲会执行SQL语句,但产生了错误
session甲:rollback;
7.程序设计不合理将会产生死锁
下面的操作请谨慎进行,余不负责解决导致的后果
session甲:update test set value='test33' where id=3;
session乙:update test set value='test44' where id=4;
session甲:update test set value='test444' where id=4;
session乙:update test set value='test333' where id=3;
因为都没执行commit,session甲和session乙都将进入等待对方释放锁权限的状态,是为死锁
终止这两个session都不能释放id=3和id=4这两条记录的锁状态,因为锁状态是保存在Oracle数据库的数据表中(请搜索Google大神解决也同时学习一下)
合理使用Oracle数据库的锁权限,解决锁冲突
因为DML操作是自动获得TX权限,很多人会被这表面蒙蔽,从而写程序时不留意导致死锁。
如何合理解决锁冲突?
8.不能滥用高等级锁
TX锁的粒度是单条记录,是粒度很细的锁,倘若如果觉得解决锁冲突很麻烦而使用高等级的锁,如表级锁X(X是完全排斥的锁,不允许没持有权限的session做任何操作),死锁是没有了,但这是滥用。越高级的锁,所影响的范围越大,虽然拿使用X来做比喻,但使用X是极端的行为,普通的后台管理严禁使用X锁,后台管理之间的完全排斥不说,前台比如一个购物系统,顾客就不能购买任何东西。
9.那是否应该在一条DML操作后立刻进行commit?
这还是不值得推荐的做法。因为DML/commit操作会在数据库服务器产生和注销副本,商业系统面临的并发数不是小数目,频繁产生和注销副本会加重服务器负担。
10.将DML操作缓存在本地程序内存中是一个很好的做法,批量执行之后才commit,可以节省服务器开销。
11.但缓存方法的执行时间间隔就算再小,如果并发度很高,还是有死锁的危险。因此后台管理程序应该提供终止长时间没响应的锁的功能。
12.如何在缓存方法的基础上彻底泯灭死锁的危险?答案就是使用select for update nowait;
for update nowait仅对select操作有效,DML无法使用for update nowait。虽然DML操作会自动申请TX锁,但倘若锁已经被别人获得,DML操作会进行等待状态,这是死锁的根源所在。在DML操作前,首先使用select *** from TableName (where ***) for update nowait申请TX权限,如果申请失败,会返回异常,不会进入等待状态,只要select语句中的where条件子句和DML的条件子句一样,就能保证所影响的记录范围不会扩大。使用try…catch…语句捕捉异常,可以确保事务执行的正确性。事实上select for update nowait如果成功,已经为session建立了相关记录的副本,这对服务器的开销影响不大,因为DML最终还是要使用副本中的这些记录。
注意:insert操作倘若可能造成冲突,比如第6条,在操作前也应当使用select for update nowait(session甲就不会进入等待状态),倘若没有冲突危险,比如使用sequence.nextval作为primary key的值,不需要在操作前select for update nowait。
最后请思考一下下面的问题(深入理解select for update nowait):
session甲:select * from test where id=4 for update nowait;
session乙:update test set value='test44' where id=4;
session乙仍然会进入等待状态。为什么?
评论