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

Source: Internet
Author: User


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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.