About MySQL transaction row lock for update to implement the write lock feature

Example 1:

In e-commerce, there is often a small inventory, the purchase of a very large number of people, how to ensure that the volume of goods will not be purchased multiple times.

In fact, it is very simple to use the transaction +for update can be resolved. (for update only for InnoDB)

We all know that the for update is actually a shared lock and can be read. But how does it not be read when executed?

To put it simply: assuming the inventory is now 1, there is now A and B purchased simultaneously

Open a transaction first

Begin

Select Stock from good where id=1 for update;//query Good table the number of stock in a product

Check out, in the program to determine whether the stock is 0 (what language do you use, it is not my concern)

Finally in the execution

Update good set stock=stock-1 where id=1

Finally in

Commit

But this time B is also the Select stock from good where id=1 for update; Note: For update cannot be omitted. This time it will appear to be locked and cannot be read.

So this ensures that the remaining quantity of the goods is 1 consistent.

Example 2:

Because the InnoDB preset is Row-level lock, MySQL executes row lock (only the selected data sample) only if the specified primary key is "clear", otherwise MySQL will execute table lock (lock the entire data form).
As an example:
Suppose there is a form of products with ID and name two fields, ID is the primary key.
Example 1: (explicitly specify the primary key, and there is this information, row lock)
SELECT * FROM Products WHERE id= ' 3 ' for UPDATE;
SELECT * FROM Products WHERE id= ' 3 ' and type=1 for UPDATE;

Example 2: (explicitly specify the primary key, if the information 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: (primary key ambiguous, table lock)
SELECT * FROM Products WHERE id<> ' 3 ' for UPDATE;

Example 4: (primary key ambiguous, table lock)
SELECT * from the products WHERE ID like ' 3 ' for UPDATE;

Note 1:for Update applies only to InnoDB and must be in the transaction block (Begin/commit) to take effect.
NOTE 2: To test the condition of the lock, you can use the command Mode of MySQL and open two windows to do the test.

Testing in MySQL 5.0 is exactly like this.
In addition: Myasim only supports table-level locks, INNERDB supports row-level locks
Data that has been added (row-level lock/table-level Lock) locks cannot be locked by other transactions or modified (modified, deleted) by other transactions
is a table-level lock, the table is locked regardless of whether the record is queried

In addition, if both A and B query the table ID but not the records, A and b do not have a row lock on the query, but a and B will get an exclusive lock, when a and then insert a record will be waiting because B already has a lock, then B and then insert the same data will throw deadlock Found when trying to get lock; Try restarting transaction then releases the lock, at which point a lock is obtained and the insert succeeds


The above describes the select ... The use of for UPDATE, but the lock data is a discriminant, you have to pay attention to. Because the InnoDB preset is Row-level lock, MySQL executes row lock (only the selected data is locked) only if the specified primary key is "clear", otherwise MySQL will execute table lock (lock the entire data form).


Note: The NOWAIT keyword was used previously in Orcale, which is also possible in MySQL, but the result is not. The reason is: the current builtin version of InnoDB does not support nowait syntax

About MySQL transaction row lock for update to implement the write lock feature