select…for update在mysql和oracle間鎖行為的比較

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  

  www.aiwalls.com  

    對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;  

被阻塞…  

  www.aiwalls.com  

    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  

 www.aiwalls.com  

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  

  www.aiwalls.com  

    對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 

 

發佈留言