About the lock in MySQL

Depending on the MySQL storage engine, the supported locks are different.

Myisam,memory, supports table-level locks.

InnoDB, row-level and table-level locks are supported, and row-level locks are the default.

Table-level lock to lock the entire table. Lock table Fast. There is no deadlock. More conflicts.

Row-level lock, which locks the specified row. The lock table is slow. A deadlock may occur. Less conflict.

About Deadlocks:

There are 2 data x, y in the table. There are two people who want to modify both of these data. and a modify x first, then modify Y. b Modify Y first, and then edit X.

These two people do the same thing at the same time.

Table-level Lock: Whether a first execution or B first execution, will first lock the table, another person can only wait, know the first person operation is complete. So the data is definitely safe.

Row-level Lock: The transaction is turned on at the same time, a modified x,b y. At this point a wants to modify Y, but B wants to change the x, but they are taking the data that the other person wants to manipulate, and the deadlock is generated. I can only think of letting them do it in order to avoid deadlocks.

Table-Level Locks:

Table shared read lock (table read lock) and table exclusive write lock (table write lock)

A thread performs a retrieval of SQL, all the tables involved are added to the shared read lock, and the other threads can only read the tables.

A thread executes a delete and add to SQL, the table involved is added to the exclusive write lock, other threads to read the table or modify the table will need to wait for the previous thread to end.

Table-level lock and lock is not required for human control. Read locks are automatically added when SQL retrieval is performed. Write locks are automatically added when adding or deleting changes to SQL.

Row-level Locks:

Shared lock: Row lock.

Exclusive lock: Row lock.

Intent Shared Lock: Table lock.

Intention to pat him lock: Table lock.

You can add a shared lock or intent shared lock to a row that has a shared lock. But you can't give a row with a shared lock plus a pat on his lock or intent to pat him on the lock.

A row of data on a pat lock, you can not give this line of data and arbitrary lock. Only wait until the lock is released.

compatible compatible

request lock mode     is compatible with current lock mode	X	IX	S	is
x	conflict	conflict	conflict	conflict
ix	conflict	compatible	conflict
s	conflict	conflict	compatible	compatible
is	conflict	compatible	compatible

If the lock mode of a transaction request is compatible with the current lock, INNODB grants the requested lock to the transaction and, conversely, if the two are incompatible, the transaction waits for the lock to be released.
The intent lock is innodb automatically and does not require user intervention. For update, Delete, and INSERT statements, InnoDB automatically adds an exclusive lock (X) to the data set involved, and InnoDB does not add any locks for the normal SELECT statement, and the transaction can be displayed to the recordset with shared or exclusive locks.

Shared Lock (S): SELECT * FROM table_name WHERE ... Lock in SHARE mode exclusive lock (X): SELECT * FROM table_name WHERE ... For UPDATE

Use SELECT ... In SHARE mode, a shared lock is used primarily to confirm the existence of a row of records when data dependencies are needed, and to ensure that no one is doing an update or delete operation on the record.
However, if the current transaction also requires an update to the record, it is most likely to cause deadlocks, and for applications that require an update operation after locking the row records, you should use SELECT ... The for Update method obtains an exclusive lock.
2.InnoDB row Lock Implementation method
InnoDB a row lock is achieved by locking the index entry on the index, only if the data is retrieved by the index criteria, InnoDB uses row-level locks, otherwise innodb will use a table lock
In practice, it is important to pay special attention to this feature of the InnoDB row lock, otherwise, it may lead to a lot of lock conflicts, which can affect the concurrency performance. Here are some practical examples to illustrate.
(1) The InnoDB does use a table lock instead of a row lock when querying without an index condition.
(2) Since the MySQL row lock is for the index plus lock, not for the record plus lock, so although it is access to the record, but if you use the same index key, there will be a lock conflict.
(3) When a table has multiple indexes, different transactions can use different indexes to lock different rows, and InnoDB uses row locks to lock the data, whether it is using a primary key index, a unique index, or a normal index.
(4) Even if the index field is used in the condition, but whether the index is used to retrieve the data is determined by MySQL by judging the cost of different execution plans, if MySQL thinks that the full table scan is more efficient, such as for some small tables, it will not use the index, in which case InnoDB will use the table lock. Instead of a row lock. Therefore, when parsing a lock conflict, don't forget to check the SQL execution plan to verify that the index is actually used.
3. Clearance Lock (Next-key Lock)
When we retrieve data with a range condition rather than an equal condition, and request a shared or exclusive lock, InnoDB locks the index entry for the qualifying existing data record;
For records in which the key value is within the range but does not exist, called "gap", InnoDB will also lock the "gap", which is called the Gap Lock (Next-key lock).
Cases:
If there are only 101 records in the EMP table, the value of the Empid is,..., 100,101 respectively, the following SQL:

Mysql> SELECT * from emp where empid > + for update;

is a retrieval of a range condition, innodb not only locks the records that meet the conditional Empid value of 101, but also locks the "gap" of Empid greater than 101 (which do not exist).
InnoDB the purpose of using clearance locks:
(1) To prevent Phantom reading to meet the requirements of the relevant isolation level. For the above example, if a gap lock is not used, if the other transaction inserts any record with empid greater than 100, then this transaction will occur if the above statement is executed again;
(2) In order to meet the needs of its recovery and replication.
Obviously, when using range criteria to retrieve and lock a record, even if some nonexistent key value is also locked by an innocent, it is not possible to insert any data in the Lock key value range when locked. In some scenarios this can be very damaging to performance.
In addition to the negative impact of the gap lock on the performance of InnoDB, there are several other major performance pitfalls in how the lock is implemented by indexing:
(1) When query cannot take advantage of the index, InnoDB discards the use of row-level locking instead of table-level locking, resulting in lower concurrency performance;
(2) When the index used by query does not contain all the filters, the index key used by the data retrieval may have some parts that are not part of the query's result set, but will also be locked because the gap lock is locked by a range, not a specific index key;
(3) When query uses the index to locate data, it is locked if the same index key is used but the data row accessed is different (the index is only part of the filter).
Therefore, in the actual application development, especially the concurrent inserting more and more applications, we should try to optimize the business logic, try to use equal conditions to access the updated data, to avoid the use of scope conditions.
It is also necessary to note that, in addition to using the clearance lock when the InnoDB is locked by the range condition, the InnoDB will also use a gap lock if the equality condition is used to request a lock for a record that does not exist.

About the lock in MySQL