MySQL Exclusive and shared locks

　　See the code today see there is a select name from the user where id = 1 for update, a little crazy, not seen at all, can only say that they see less, it can only learn a bit. First do a basic knowledge (most of the documents are collated, if there is infringement also please inform):

Basic concepts of Locks
When multiple transactions for a resource, it is possible to cause data inconsistency, this time requires a mechanism constraints, and the data access order to ensure the consistency of database data, lock is one of the mechanisms. We can use the shopping malls in the fitting room to make a metaphor, shopping malls in every fitting room can be used by multiple consumers, so there may be multiple consumers at the same time to try clothes need to use the fitting room, this is a conflict, in order to avoid conflict, fitting room lock (in fact, after the door to get in), One of the test clothes of the person in the fitting room lock, the other customers can not open from the outside, can only wait for the customers inside, try to finish clothes, from the inside lock open, outside the talent can go in (online to find the metaphor, very image). But I want to be concurrency is embarrassing, haha.

Basic types of Locks
Operations on the database can be summarized into two types: Read and write.
When multiple transactions read an object at the same time, there is no conflict. simultaneous reading and writing, or simultaneous writing, can create conflicts. therefore, in order to improve the concurrency performance of the database, two kinds of locks are usually defined: shared and exclusive locks.

Shared Lock (also known as S-lock)
A shared lock (S) represents a read operation on the data. Therefore, multiple transactions can share a lock on an object at the same time. (If the door of the fitting room is not locked, the customer can go to visit at the same time)
Sql:select * from Ad_plan lock in share mode with shared lock

Usage Scenarios for shared locks
SELECT ... Lock in SHARE mode walks is the is lock (intent shared lock), that is, a shared lock is added to the qualifying rows, so that others can read the records, or continue to add the is lock, but cannot modify these records until you complete the lock process ( The completed scenarios are: The commit of the transaction, the rollback of the transaction, or the direct lock wait timeout).
SELECT ... The application of the LOCK in SHARE mode is suitable for two tables when there is a relationship between the write operation, take the MySQL Official document example, a table is a child table, one is the parent table, assuming that a column of the child table child_id mapped to the parent table C_ child_id column, then from the business point of view, at this point I directly insert a child_id=100 record to the child table is at risk, because the first insert may delete this c_child_id=100 record in the parent table, Then there is a risk of inconsistency in business data. The correct method is to execute the SELECT * from the parent where c_child_id=100 lock in share mode, lock the parent table for this record, and then execute INSERT into child (child_id) v Alues (100) there would be no such problem.

Exclusive Lock (Exclusive lock, also called X Lock)

　　An exclusive lock is also called a write lock (X).

An exclusive lock indicates that the data is written. If a transaction has an exclusive lock on the object, the other transaction cannot add any more locks to it. (a customer locks the fitting room from the inside, and the other customer wants to use the fitting room, only to wait for the lock to open from the inside)
Sql:select * from Ad_plan for update that generates an exclusive lock; see, for update appears, so for update is an exclusive lock, up knowledge.
Usage scenarios for exclusive locks:

　　Use scenario One: quantity of goods ordered
　But if it is a scenario of the same table, for example, the e-commerce system calculates the remaining quantity of a commodity, it is necessary to confirm the quantity of the goods >=1 before the order is generated, and the quantity of the goods should be reduced by 1 after the order is generated.
1 Select amount from product where product_name= ' XX ';
2 Update product set amount=amount-1 where product_name= ' XX ';

Obviously 1 of the practice is problematic, because if the 1 query out of amount is 1, but then just the other session also bought the goods and produced an order, then amount becomes 0, then the second step to execute there is a problem. It is also unreasonable to use lock in share mode, because when two sessions lock the row record at the same time, when the two session is re-update, it will inevitably result in a deadlock that causes the transaction to be rolled back. The following are examples of operations (chronological)

　　Use scenario One: The state of a data table

If there is a table to record the status of a commodity, in order to change the process, the status of the order is constantly changing, and the process of change will certainly have concurrency problems, and many times there is interaction with other systems, there will be compensation situation, so the likelihood of concurrency is very large, Compensation or in order to increase the likelihood of the success of State modification, 2 changes in the state of the situation also has, the landlord encountered this situation, really fucked. So I see this for update notation.

1 Update order Set status = 1 where product_id = ' 1 ';　　

　　　　2 Insert Order_flow (...) value (...) in the * * * * .....)

In this case it is possible that the status of the order has been updated, but to compensate for these additional messages the status is updated to be processed or multiple streams are inserted (the likelihood of multiple streams is large, the kind of state may compensate for lag). This time you can use SELECT ... from the order where order_id = ' 1 ' for update, first lock the table to modify the state, so that no one else to operate, and then put the water back into the first, and then update the state, perfect. But the performance is very slow after the lock, worried about the performance impact, and there may be a deadlock situation, and then I modified to add a unique index to the water table, so that the insertion of water error is already processed records. This will not present a performance issue.

　By contrast, the lock in share mode applies to the consistency requirements of two tables when there is a business relationship, and for update applies to conformance requirements when manipulating the same table.

MySQL Exclusive and shared locks