MySQL lock table and unlock statement sharing

For MySQL, there are three levels of Lock: page level, table level, row level

The typical representative engine for the page level is BDB.
The typical representative engine for the table level is myisam,memory and the ISAM of a long time ago.
The typical representative engine for the row level is InnoDB.
-the most used in our actual application is the row lock.
The advantages of row-level locks are as follows:
1) Reduce the lock state when many connections make separate queries.
2), if an exception occurs, you can reduce the loss of data. Because you can roll back only one row or a few rows of data at a time.
The disadvantages of row-level locks are as follows:
1), which consumes more memory than page-level locks and table-level locks.
2), when querying than page-level locks and table-level locks require more I/O, so we often use the row-level lock in the write operation instead of the read operation.
3), easy to appear deadlock.
For a write lock as follows:
1), if the table is not locked, then write to lock it.
2), otherwise, put the request into the write lock queue.
For read locks, the following:
1), if the table does not have a write lock, then add a read lock.
2), otherwise, put the request in the read lock queue.
Of course, we can use low_priority and high_priority to change these behaviors in writing and reading operations respectively.

If you want to do a large number of insert and SELECT operations on a table, but parallel insertions are not possible, you can insert the records into the staging table and then periodically update the data in the staging table to the actual tables. This can be accomplished with the following command:

mysql> LOCK TABLES real_table Write, insert_table write;
Mysql> INSERT into real_table SELECT * from insert_table;
mysql> TRUNCATE TABLE insert_table;
Mysql> UNLOCK TABLES;
InnoDB use row-level locks, BDB use page-level locks. For the InnoDB and BDB storage engines, deadlocks can be generated. This is because InnoDB automatically captures row locks, and BDB captures page locks when executing SQL statements, rather than doing so at the beginning of a transaction.
The advantages of row-level locks are:

Reduce conflict locks when many threads request different records.
Reduce change data when a transaction is rolled back.
Making it possible to lock a single row of records for a long time.
The disadvantages of row-level locks are:

Consumes more memory than page-level and table-level locks.
When used in a large number of tables, it is slower than page-level and table-level locks because he needs to request more resources.
When you need to do a GROUP by operation on most data frequently or you need to scan the entire table frequently, it is significantly worse than other locks.
With higher-level locks, it is easier to support a variety of different types of applications, because the cost of such locks is much smaller than row-level locks.
Table-level locks are more advantageous than page-level and row-level locks in the following situations:

Many operations are read tables.
Read and update on Strictly conditional indexes, when updates or deletions can be read with a separate index:

UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
DELETE from Tbl_name WHERE unique_key_col=key_value;
SELECT and INSERT statements are executed concurrently, but there are very few UPDATE and DELETE statements.
Many scan tables and GROUP by operations on the whole table, but no tables are written.
The difference between a table-level lock and a row-level or page-level lock is also:
There will be a write and multiple read places at the same time to make the version (for example, concurrent insertions in MySQL). In other words, the database/table support different attempts based on the different points of access to the data at the beginning of the time. Other names are: time travel, write copy, or copy on demand.

Copy CodeThe code is as follows:
Execute SQL statement lock out stat_num table
$sql = "LOCK TABLES stat_num WRITE"; The write lock on the table, blocking all other MySQL query processes
$DatabaseHandler->execute ($sql);
Perform an update or write operation
$sql = "UPDATE stat_num SET ' correct_num ' = ' correct_num ' +1 WHERE stat_date= ' {$cur _date} '";
$DatabaseHandler->execute ($sql);
Performs an unlock SQL statement after all writes for the current request have been completed
$sql = "UNLOCK TABLES";
$DatabaseHandler->execute ($sql);

MySQL lock table and unlock statement sharing