Select... Comparison of lock behavior between MySQL and Oracle for update

Environment:

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> select * from v$version where rownum=1;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64biSQL> !uname -aLinux 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

For MySQL, select for update must be in a transaction. when the transaction is committed, the lock is released. Therefore, you must add a begin, start transaction, or set autocommit = 0 in the experiment.

MySQL:

------------------ 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; blocked... mysql> Update t set n = 'F' where I = 2; blocked... mysql> alter table t drop index t_idx; blocked... mysql> Delete from t where I = 2; blocked...

ORACLE:

Revoke session_a --------------- SQL> select * from t where I = 1 for update; I n ---------- -------------------- 1 think big enough session_ B ------------- SQL> select * from t where I = 1 for update; blocked... SQL> Update t set n = 'think open' where I = 1; blocked... 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 specifiedsql> Delete from t where I = 1; blocked...

In MySQL, select... for update, add an X lock to the row record. Any other transaction that wants to add any lock to these rows will be blocked. This also conforms to the InnoDB row-Level Lock concept.

In Oracle, let's perform the next test:

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

For Oracle, when the select... for update, the get is the RX lock (lmode = 3). At the same time, through the TRC file, we can also find that the LCK is set to 1, that is, row-level locks are added at the same time.
The TRC excerpt is as follows:

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x000a.029.0000013b  0x008000dd.00c8.2b  C---    0  scn 0x0000.000911f40x02   0x0004.026.00000142  0x008000a3.00c7.04  --U-    1  fsc 0x0000.00091339.....tl: 5 fb: --H-FL-- lb: 0x2  cc: 1col  0: [ 1]  61