Mysql SELECT FOR UPDATE

Mysql select for update MySQL use SELECT... for update confirmation before transaction writing take MySQL's InnoDB as an example. The preset Tansaction isolation level is repeatable read, and the select read lock can be divided into two methods: www.2cto.com SELECT... lock in share modeselect... for update, when the Transaction (Transaction) is selected to the same data table, it must wait FOR other Transaction data to be committed (Commit) before execution. The major difference is that lock in share mode can easily cause deadlocks when a transaction wants to Update the same form. To put it simply, if you want to UPDATE the same form after SELECT, you 'd better use SELECT... UPDATE. For example, assume that the product form products contains a quantity that stores the quantity of commodities. before the order is established, you must first determine whether the quantity of the quantity commodities is sufficient (quantity> 0 ), then update the quantity to 1. Unsafe practice: SELECT quantity FROM products WHERE id = 3; UPDATE products SET quantity = 1 WHERE id = 3; why not? Www.2cto.com may not be a problem in a few cases, but a large amount of data access may be a problem. If we need to deduct the inventory only when the quantity is greater than 0, assuming that the quantity read by the program in the first line of SELECT is 2, it seems that the number is correct, however, when MySQL is preparing to UPDATE, some people may have deducted the inventory to 0, but the program did not know how to UPDATE the wrong one. Therefore, the transaction mechanism must be used to ensure that the data read and submitted is correct. So we can test in MySQL as follows: (Note 1) set autocommit = 0; begin work; SELECT quantity FROM products WHERE id = 3 for update; ========================================================== ===at this time, the data of id = 3 in products data is locked (note 3 ), other transactions must wait until the transaction is committed before executing SELECT * FROM products WHERE id = 3 for update (note 2). This ensures that the number read by quantity in other transactions is correct. ========================================================== === UPDATE products SET quantity = '1' WHERE id = 3; commit work; ========================================================== === submit (Commit) write data to the database, and products is unlocked. Note 1: BEGIN/COMMIT is the start and end point of the transaction. You can use more than two MySQL Command windows to observe the locking status. NOTE 2: During the transaction, only SELECT... for update or lock in share mode, the same data will be executed only after other transactions are completed. Generally, SELECT... this is not affected. NOTE 3: Because InnoDB defaults to Row-level Lock, you can refer to this article for data column locking. Note 4: Do not use the lock tables command for InnoDB forms. If you have to use the lock tables command, read the official instructions on InnoDB to avoid frequent system deadlocks. MySQL SELECT... for update, Row Lock and Table Lock are described above in SELECT... the usage of for update, but the Lock data is a distinction, you have to pay attention to it. Because InnoDB defaults to Row-Level Lock, MySQL will only execute Row lock (only Lock selected data) If a specified Primary Key is specified explicitly ), otherwise, MySQL will execute Table Lock (Lock the entire data form ). For example, suppose www.2cto.com has a form named products, which contains two columns: id and name. id is the primary key. Example 1: (the primary key is explicitly specified and the row lock exists) SELECT * FROM products WHERE id = '3' for update; Example 2: (specify the primary key explicitly, if this data is not found, no lock) SELECT * FROM products WHERE id = '-1' for update; Example 2: (no primary key, table lock) SELECT * FROM products WHERE name = 'mouse 'for update; Example 3: (table lock) SELECT * FROM products WHERE id <> '3' for update; example 4: (table lock) SELECT * FROM products WHERE id LIKE '3' for update; Note 1: for update only applies to InnoDB And must be in the transaction block (BEGIN/COMMIT. NOTE 2: To test the locking status, you can use the Command Mode of MySQL to open two windows for testing.