By default, mysql locks table-level commands are not commonly used. mysql uses table-level locks by default. If InnoDB Storage engine is enabled, the database supports row-level locks. 2. Check whether the database supports the innodb command SHOWvariableslikehave _ %. The other command SHOWENGINES; is more accurate. You can use
By default, mysql locks table-level commands are not commonly used. mysql uses table-level locks by default. If InnoDB Storage engine is enabled, the database supports row-level locks. 2. Check whether the database supports the innodb command SHOW variables like have _ %; the other command show engines; is more accurate. You can use
Mysql uses table-Level Lock commands by default.
1. mysql uses table-level locks by default. If InnoDB Storage engine is enabled, the database supports row-level locks.
2. Check whether the database supports innodb commands.
SHOW variables like "have _ % ";
The other command show engines; is more accurate.
You can use show status to view the detailed information of the mysql database;
From: http://blog.csdn.net/tigernorth/article/details/7948539
Row-level locks of INNODB are divided into two types: Shared locks and exclusive locks. The shared lock allows a transaction to read a row of records and does not allow any thread to modify the Row Records. The exclusive lock allows the current transaction to delete or update a row of records. Other threads cannot operate on this record.
Row-level locks of INNODB are divided into two types: Shared locks and exclusive locks. The shared lock allows a transaction to read a row of records and does not allow any thread to modify the Row Records. The exclusive lock allows the current transaction to delete or update a row of records. Other threads cannot operate on this record.
Shared lock:
Usage: SELECT... lock in share mode;
MySQL adds a shared lock to each row in the query result set.
Lock application prerequisites:Currently, no thread uses the exclusive lock for any row in the result set. Otherwise, the application will be blocked.
Operation restrictions:
Table of Operation restrictions on lock records using a shared lock thread and a non-shared lock thread
Thread |
Read operation |
Write operation |
Apply for a shared lock |
Exclusive lock Application |
Share lock |
Readable |
Writeable/writeable (error Reported) |
Available |
Available |
Share lock not used |
Readable |
Non-writable (blocking) |
Available |
Cannot apply (blocking) |
1. The shared lock thread can read the lock record. Other threads can also read the lock record, and the data read by these two threads belongs to the same version.
2. for write operations, the thread that uses the shared lock should be discussed in detail. When only the current thread uses the shared lock for a specified record, the thread can write the record (including update and delete ), this is because before the write operation, the thread applies for an exclusive lock to the record before the write operation. When other threads also use a shared lock for the record, the write operation is not allowed, the system prompts an error. The write operation is not allowed for threads that use shared locks for lock records. The write operation is blocked.
3. The shared lock process can apply for a shared lock for the lock record again. The system does not report an error, but the operation itself does not make much sense. Other threads can also apply for a shared lock for the lock record.
4. The shared lock process can apply for exclusive locks for its lock records. Other processes cannot apply for exclusive locks for lock records, and the application will be blocked.
Exclusive lock:
Usage:SELECT... for update;
MySQL adds an exclusive lock to each row in the query result set. In transaction operations, any update or delete operation on the record will automatically add an exclusive lock.
Lock application prerequisites:Currently, no thread uses exclusive or shared locks for any row in the result set. Otherwise, the application will be blocked.
Operation restrictions:
Use exclusive lock threads and do not use exclusive lock threads to limit the operation of lock records
Thread |
Read operation |
Write operation |
Apply for a shared lock |
Exclusive lock Application |
Use exclusive lock |
Readable (new version) |
Writable |
Available |
Available |
Do not use exclusive lock |
Readable (earlier version) |
Non-writable (blocking) |
Cannot apply (blocking) |
Cannot apply (blocking) |
1. the exclusive lock thread can be used to read the lock record and read the latest version of the current transaction. For threads that do not use the exclusive lock, the read operation can also be performed, this feature is consistent non-locked read. That is, for the same record, the database records multiple versions, and the update operations in the transaction are reflected in the new version. The old version will provide read operations to other threads.
2. Use the exclusive lock thread to write the lock records. For threads that do not use the exclusive lock, write operations on the lock records are not allowed, and requests are blocked.
3. the exclusive lock process can apply for a shared lock for its lock records. However, after applying for a shared lock, the thread does not release the original exclusive lock. Therefore, this record shows the exclusive lock nature; other threads cannot apply for a shared lock on the locked record, and the request will be blocked.
4. The exclusive lock process can apply for exclusive locks for its lock records (which does not actually make any sense). Other processes cannot apply for exclusive locks for the lock records, and the application will be blocked.