select...forupdate在mysql和oracle间锁行为的比较_MySQL

select...for update在mysql和oracle间锁行为的比较

环境：

[sql]

mysql> show variables like '%storage_engine%';

+----------------+--------+

| Variable_name | Value |

+----------------+--------+

| storage_engine | InnoDB |

+----------------+--------+

1 row in set (0.00 sec)

mysql> select version();

+-----------+

| version() |

+-----------+

| 5.1.52 |

+-----------+

1 row in set (0.06 sec)

[sql]

SQL> select * from v$version where rownum=1;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

SQL> !uname -a

Linux Think 2.6.32-220.el6.x86_64 #1 SMP Wed Nov 9 08:03:13 EST 2011 x86_64 x86_64 x86_64 GNU/Linux

对mysql而言，select for update必须在一个事务中，当事务commit，锁也就释放了。因此，在实验时，务必加个begin、start transaction 或者 set autocommit=0。

mysql：

[sql]

------------------sesson_A---------------:

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where i=2 for update;

+---+------+

| i | n |

+---+------+

| 2 | b

|

+---+------+

1 row in set (0.00 sec)

------------------session_B---------------:

mysql> select * from t;

+---+------+

| i | n |

+---+------+

| 2 | b

|

| 3 | c

|

+---+------+

2 rows in set (0.00 sec)

mysql> select * from t where i=2 for update;

被阻塞...

mysql> update t set n='f' where i=2;

被阻塞...

mysql> alter table t drop index t_idx;

被阻塞...

mysql> delete from t where i=2;

被阻塞...

oracle：

[sql]

-----------------------session_A---------------

SQL> select * from t where i=1 for update;

I N

---------- --------------------

1 think big

-----------------------session_B---------------

SQL> select * from t where i=1 for update;

被阻塞...

SQL> update t set n='think open' where i=1;

被阻塞...

SQL> create index t_idx on t(i);

create index t_idx on t(i)

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

SQL> delete from t where i=1;

被阻塞...

于mysql，select ... for update 对行记录加个X锁。其他任何事务想在这些行上加任何锁都会被阻塞。这也符合InnoDB行级锁的概念。

在oracle中，我们再做下一个测试：

[sql]

-----------session_A-------------

SQL> select * from t for update;

A

-----

a

--------------session_B-------------

SQL> select sid,type,lmode from v$lock where sid=159;

SID TY LMODE

---------- -- ----------

159 TM 3

159 TX 6

对oracle，当发出select ... for update的时候、得到的是RX锁（lmode=3）,同时通过trc文件，我们还可以发现，Lck被置为1，也也就是同时被加上了行级锁。

trc部分摘录如下：

[sql]

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.029.0000013b 0x008000dd.00c8.2b C--- 0 scn 0x0000.000911f4

0x02 0x0004.026.00000142 0x008000a3.00c7.04 --U- 1 fsc 0x0000.00091339

.....

tl: 5 fb: --H-FL-- lb: 0x2 cc: 1

col 0: [ 1] 61