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

bitsCN.com

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 

 

bitsCN.com