Comparison of select... for update locks between mysql and oracle

Select... comparison environment of for update lock behavior between mysql and 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.2cto.com for mysql, select for update must be in one transaction. When the transaction commit, the lock is released. Therefore, you must add a begin, start transaction, or set autocommit = 0 in the experiment. 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) mysq L> 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... www.2cto.com oracle: [SQL] Explain session_A --------------- SQL> select * from t where I = 1 for update; I n ---------- ------------------ 1 think big begin session_ B ----------------- SQL> selec T * 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 specified www.2cto.com SQL> delete from t where I = 1; blocked... in mysql, select... for update adds 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 another test: [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.2cto.com on 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: [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