悲观锁大多数情况下依靠数据库的锁机制实现,以保证操作最大程度的独占性。
乐观锁相对悲观锁而言,乐观锁机制采取了更加宽松的加锁机制,大多是基于数据版本记录机制实现。
读取出数据时,将此版本号一同读出,之后更新时,对此版本号加一。此时,将提交数据的版本数据与
数据库表对应记录的当前版本信息进行比对,如果提交的数据版本号大于数据库表当前版本号,则予以更新,
否则认为是过期数据。
死锁是指两个或两个以上的进程在执行过程中,由于竞争资源或者由于彼此通信而造成的一种阻塞的现象,
若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待
的进程称为死锁进程。
---悲观锁:
--会话1:查询id为2的记录并进行锁定:
10:24:05 SCOTT@ORA11GR2_s1> select * from t_lock where id=2 and type=0 for update nowait;
ID TYPE
---------- ----------
2 0
10:24:19 SCOTT@ORA11GR2_s1>
-- 会话2:查询id为2的记录,此时查询报错:
10:21:00 SCOTT@ORA11GR2_s2> select * from t_lock where id=2 and type=0 for update nowait;
select * from t_lock where id=2 and type=0 for update nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
10:26:18 SCOTT@ORA11GR2_s2>
-- 会话1:对id为2的记录进行更新:
10:24:19 SCOTT@ORA11GR2_s1> update t_lock set type=1 where id=2 and type=0;
1 row updated.
10:27:16 SCOTT@ORA11GR2_s1>commit;
Commit complete.
10:27:20 SCOTT@ORA11GR2_s1>select * from t_lock where id=2;
ID TYPE
---------- ----------
2 1
10:27:27 SCOTT@ORA11GR2_s1>
-- 会话2:再次更新id为2的记录,由于已经将id为2的type已经变为1,所以避免了更新丢失:
10:30:35 SCOTT@ORA11GR2_s2>select * from t_lock where id=2 and type=0 for update nowait;
no rows selected
10:30:39 SCOTT@ORA11GR2_s2>
--- 乐观锁:
-- 会话1:查询id为3的伪列ora_rowscn的值:
10:30:01 SCOTT@ORA11GR2_s1>select id,type,ora_rowscn from t_lock where id=3;
ID TYPE ORA_ROWSCN
---------- ---------- ----------
3 0 933259
10:37:40 SCOTT@ORA11GR2_s1>
--会话2:查询id为3的伪列ora_rowscn的值:
10:30:39 SCOTT@ORA11GR2_s2>select id,type,ora_rowscn from t_lock where id=3;
ID TYPE ORA_ROWSCN
---------- ---------- ----------
3 0 933259
10:39:18 SCOTT@ORA11GR2_s2>
--会话1:更新id为3的type为1:
10:37:40 SCOTT@ORA11GR2_s1>update t_lock set type=1 where ora_rowscn=933259 and id=3;
1 row updated.
10:40:18 SCOTT@ORA11GR2_s1>commit;
Commit complete.
10:40:26 SCOTT@ORA11GR2_s1>
-- 会话2:更新id为3的type为1:
10:39:18 SCOTT@ORA11GR2_s2>update t_lock set type=1 where ora_rowscn=933259 and id=3;
0 rows updated.
10:40:48 SCOTT@ORA11GR2_s2>
--- 死锁:
--创建测试表:
10:58:28 SCOTT@ORA11GR2_s1>create table t_lock_1(id number(2),name varchar2(15));
Table created.
10:59:09 SCOTT@ORA11GR2_s1>create table t_lock_2 as select * from t_lock_1;
Table created.
10:59:27 SCOTT@ORA11GR2_s1>insert into t_lock_1 values(1,'liubei');
1 row created.
10:59:45 SCOTT@ORA11GR2_s1>insert into t_lock_2 values(1,'guanyu');
1 row created.
11:00:01 SCOTT@ORA11GR2_s1>commit;
Commit complete.
11:00:02 SCOTT@ORA11GR2_s1>select * from t_lock_1;
ID NAME
---------- ---------------
1 liubei
11:00:10 SCOTT@ORA11GR2_s1>select * from t_lock_2;
ID NAME
---------- ---------------
1 guanyu
11:00:17 SCOTT@ORA11GR2_s1>
-- 会话1:更新表t_lock_1的id字段为1的name为“liuxuande”,不提交:
11:00:52 SCOTT@ORA11GR2_s1>update t_lock_1 set name='liuxuande' where id=1;
1 row updated.
11:01:13 SCOTT@ORA11GR2_s1>
-- 会话2:更新表t_lock_2的id字段为1的name为“关云长”,不提交
11:01:22 SCOTT@ORA11GR2_s2>update t_lock_2 set name='guanyunchang' where id=1;
1 row updated.
11:01:47 SCOTT@ORA11GR2_s2>
-- 会话1:更新表t_lock_2的id字段为1的name为“guanyunchang”,此时挂起状态:
11:01:13 SCOTT@ORA11GR2_s1>update t_lock_2 set name='guanyunchang' where id=1;
-- 会话2:更新表t_lock_1的id字段为1的name为“liuxuande”,此时挂起状态:
11:01:47 SCOTT@ORA11GR2_s2>update t_lock_1 set name='liuxuande' where id=1;
-- 会话1:此时回到会话1,出现死锁错误:
11:01:13 SCOTT@ORA11GR2_s1>update t_lock_2 set name='guanyunchang' where id=1;
update t_lock_2 set name='guanyunchang' where id=1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
11:02:41 SCOTT@ORA11GR2_s1>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31392094/viewspace-2130864/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2130864/