select for update与select for nowait
来源:互联网 发布:网络热词及解释 编辑:程序博客网 时间:2024/06/05 14:53
Select For Update行级锁定
for update 和 for update nowait的区别是,别的事务要对这个表进行写操作时,是等待一段时间还是马上就被数据库系统拒绝而返回
Oracle的SelectForUpdate语句可以实现在读取数据后马上锁定相关资源,防止被其他session修改数据的目的。也就是我们常常谈到的“悲观锁定”(现实应用开发中,使用悲观锁定的情况少之又少,也许是因为乐观锁定的实现更加灵活和便捷的缘故)。
for update 和 for update nowait的区别是,别的事务要对这个表进行写操作时,是等待一段时间还是马上就被数据库系统拒绝而返回
这个小文儿做一个小小的实验,来看看Select For Update语句实现的行级锁定
1.创建实验表table_sfu,并初始化三条数据
sec@ora10g> create table table_sfu (a number);
Table created.
sec@ora10g> insert into table_sfu values (1);
1 row created.
sec@ora10g> insert into table_sfu values (2);
1 row created.
sec@ora10g> insert into table_sfu values (3);
1 row created.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select * from table_sfu;
A
----------
1
2
3
2.使用Select For Update语句得到第一条数据
sec@ora10g> select * from table_sfu where a = 1 for update;
A
----------
1
3.查看一下现在系统中的锁定情况,152会话(即上面语句所在的会话)获得了一个TX锁和一个TM锁了,锁定的表就是TABLE_SFU
sec@ora10g> @lock
lock lock
holder holder lock lock request blocked
username sessid SERIAL# type id1 id2 mode mode BLOCK sessid
-------- ------- ------- ---- ------ ---- ---- ------- ----- -------
SEC 152 14985 TM 15396 0 3 0 0
SEC 152 14985 TX 327722 1790 6 0 0
164 1 TS 3 1 3 0 0
165 1 CF 0 0 2 0 0
165 1 RS 25 1 2 0 0
165 1 XR 4 0 1 0 0
166 1 RT 1 0 6 0 0
7 rows selected.
sec@ora10g> col OWNER for a6
sec@ora10g> col OBJECT_NAME for a10
sec@ora10g> select OWNER,OBJECT_NAME,OBJECT_ID,OBJECT_TYPE from dba_objects where object_id = '15396';
OWNER OBJECT_NAM OBJECT_ID OBJECT_TYPE
------ ---------- ---------- -------------------
SEC TABLE_SFU 15396 TABLE
4.另外新打开一个session,执行以下修改任务
sec@ora10g> update table_sfu set a = 100 where a = 1;
OK,效果出现了,这里出现了“锁等待”现象,原因就是因为在第一个session中使用Select For Update语句锁定了第一行数据,不允许其他的session对它修改。
5.这时系统中锁定情况如下,可以看到第一个session(session id是152)会话锁定了第二个session(session id是145)会话的事务
sec@ora10g> @lock
lock lock
holder holder lock lock request blocked
username sessid SERIAL# type id1 id2 mode mode BLOCK sessid
-------- ------- ------- ---- ------ ---- ---- ------- ----- -------
SEC 145 11388 TM 15396 0 3 0 0
SEC 152 14985 TM 15396 0 3 0 0
SEC 152 14985 TX 327722 1790 6 0 1 145
164 1 TS 3 1 3 0 0
165 1 CF 0 0 2 0 0
165 1 RS 25 1 2 0 0
165 1 XR 4 0 1 0 0
166 1 RT 1 0 6 0 0
8 rows selected.
6.因为仅仅是锁定了第一条数据,所以其他记录可以顺利的进行修改,如下
sec@ora10g> update table_sfu set a = 200 where a = 2;
1 row updated.
sec@ora10g> commit;
Commit complete.
7.解锁方式:commit或rollback后即完成锁定的接触
8.反过来思考一下,如果Select For Update与要锁定的行已经在其他session中完成了修改,再执行回出现什么效果呢?这个很显然,同样的会出现“锁等待”的现象,不过我想强调的是,这里可以使用nowait和wait选项来进行“探测”待锁定行是否可被锁定
实验效果如下:
第一个session:
sec@ora10g> update table_sfu set a = 100 where a = 1;
1 row updated.
第二个session:
sec@ora10g> select * from table_sfu where a = 1 for update;
此处是“锁等待”效果
sec@ora10g> select * from table_sfu where a = 1 for update nowait;
select * from table_sfu where a = 1 for update nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
这里提示了错误,原因就是已经“探测”到该行已经被别的事务锁定,这里无法对其进行锁定操作。
sec@ora10g> select * from table_sfu where a = 1 for update wait 3;
select * from table_sfu where a = 1 for update wait 3
*
ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired
这里提示的错误内容与上面的一样,不过这里wait 3表示,我等你三秒的时间,如果三秒过后还无法锁定资源,就报错
1.创建实验表table_sfu,并初始化三条数据
sec@ora10g> create table table_sfu (a number);
Table created.
sec@ora10g> insert into table_sfu values (1);
1 row created.
sec@ora10g> insert into table_sfu values (2);
1 row created.
sec@ora10g> insert into table_sfu values (3);
1 row created.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select * from table_sfu;
A
----------
1
2
3
2.使用Select For Update语句得到第一条数据
sec@ora10g> select * from table_sfu where a = 1 for update;
A
----------
1
3.查看一下现在系统中的锁定情况,152会话(即上面语句所在的会话)获得了一个TX锁和一个TM锁了,锁定的表就是TABLE_SFU
sec@ora10g> @lock
lock lock
holder holder lock lock request blocked
username sessid SERIAL# type id1 id2 mode mode BLOCK sessid
-------- ------- ------- ---- ------ ---- ---- ------- ----- -------
SEC 152 14985 TM 15396 0 3 0 0
SEC 152 14985 TX 327722 1790 6 0 0
164 1 TS 3 1 3 0 0
165 1 CF 0 0 2 0 0
165 1 RS 25 1 2 0 0
165 1 XR 4 0 1 0 0
166 1 RT 1 0 6 0 0
7 rows selected.
sec@ora10g> col OWNER for a6
sec@ora10g> col OBJECT_NAME for a10
sec@ora10g> select OWNER,OBJECT_NAME,OBJECT_ID,OBJECT_TYPE from dba_objects where object_id = '15396';
OWNER OBJECT_NAM OBJECT_ID OBJECT_TYPE
------ ---------- ---------- -------------------
SEC TABLE_SFU 15396 TABLE
4.另外新打开一个session,执行以下修改任务
sec@ora10g> update table_sfu set a = 100 where a = 1;
OK,效果出现了,这里出现了“锁等待”现象,原因就是因为在第一个session中使用Select For Update语句锁定了第一行数据,不允许其他的session对它修改。
5.这时系统中锁定情况如下,可以看到第一个session(session id是152)会话锁定了第二个session(session id是145)会话的事务
sec@ora10g> @lock
lock lock
holder holder lock lock request blocked
username sessid SERIAL# type id1 id2 mode mode BLOCK sessid
-------- ------- ------- ---- ------ ---- ---- ------- ----- -------
SEC 145 11388 TM 15396 0 3 0 0
SEC 152 14985 TM 15396 0 3 0 0
SEC 152 14985 TX 327722 1790 6 0 1 145
164 1 TS 3 1 3 0 0
165 1 CF 0 0 2 0 0
165 1 RS 25 1 2 0 0
165 1 XR 4 0 1 0 0
166 1 RT 1 0 6 0 0
8 rows selected.
6.因为仅仅是锁定了第一条数据,所以其他记录可以顺利的进行修改,如下
sec@ora10g> update table_sfu set a = 200 where a = 2;
1 row updated.
sec@ora10g> commit;
Commit complete.
7.解锁方式:commit或rollback后即完成锁定的接触
8.反过来思考一下,如果Select For Update与要锁定的行已经在其他session中完成了修改,再执行回出现什么效果呢?这个很显然,同样的会出现“锁等待”的现象,不过我想强调的是,这里可以使用nowait和wait选项来进行“探测”待锁定行是否可被锁定
实验效果如下:
第一个session:
sec@ora10g> update table_sfu set a = 100 where a = 1;
1 row updated.
第二个session:
sec@ora10g> select * from table_sfu where a = 1 for update;
此处是“锁等待”效果
sec@ora10g> select * from table_sfu where a = 1 for update nowait;
select * from table_sfu where a = 1 for update nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
这里提示了错误,原因就是已经“探测”到该行已经被别的事务锁定,这里无法对其进行锁定操作。
sec@ora10g> select * from table_sfu where a = 1 for update wait 3;
select * from table_sfu where a = 1 for update wait 3
*
ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired
这里提示的错误内容与上面的一样,不过这里wait 3表示,我等你三秒的时间,如果三秒过后还无法锁定资源,就报错
- select for update与select for nowait
- select for update nowait 与 select for update 区别
- select for update nowait 与 select for update
- select for update nowait 与 select for update 区别
- select for update nowait 与 select for update 区别
- select for update nowait 与 select for update 区别
- oracle-select for update nowait 与 select for update 差别
- select for update nowait 与 select for update 区别
- select ... for update, select ... for update nowait
- Postgre中的 select for update 和 select for update nowait
- SELECT ... FOR UPDATE(WAIT/NOWAIT)命令与ORACLE锁
- SELECT ... FOR UPDATE(WAIT/NOWAIT)命令与ORACLE锁
- Select for update, nowait, skip locked
- select for update和select for update wait和select for update nowait的区别
- No NOWAIT Option in MySQL SELECT FOR UPDATE
- for update、for update nowait、select t.*,t.rowid from table的区别
- oracle select语句中,不加for update和加 for update和for update nowait区别
- select for update行锁
- 【每天一道算法题】恍如隔世
- Datatable 列expression使用
- I MISS YOU
- Linux网络编程必看书籍推荐
- hdu 2066 Dijkstra 最短路应用
- select for update与select for nowait
- uva10564
- Practical Netty (2) CS模式下的Echo及String与ChannelBuffer的转化
- Ubuntu 12.04 telnet exit method
- 用post还是用get
- SQL--数据库触发器
- [leetcode] Decode Ways (!!)
- VC案例教学之一:改造轮子1:用for代替……
- 浅谈并行编程语言 Unified Parallel C