mysql lock table and unlock statement sharing

The typical page-level engine is BDB.
The typical engines for the table level are MyISAM,MEMORY, and, long ago, ISAM.
The typical row-level engine is INNODB.
- the thing we use most in practice is row locking.

The advantages of row-level locks are as follows:

1) reduce the state of LOCK when many connections make different queries.
2) if there is an exception, the loss of data can be reduced. Because you can roll back only one row or a few rows of small amounts of data at a time.
The disadvantages of row-level locks are as follows:
1) it takes up more memory than page-level locks and table-level locks.
2) there are more I/O required for queries than for page-level locks and table-level locks, so we often use row-level locks for write operations instead of read operations.
3) deadlock is easy to occur.
The write lock is as follows:
1) if the table is not locked, then write to it to lock.
2) otherwise, put the request in the write lock queue.
For read locking:
1) if the table does not add a write lock, then add a read lock.
2) otherwise, put the request on the read lock queue.
Of course, we can use low_priority and high_priority to change these behaviors for write and read operations, respectively.

If you want to do a lot of INSERT and SELECT operations on one table, but parallel insertion is not possible, you can insert the records into the temporary table and then periodically update the data in the temporary table to the actual table. You can do this 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 USES line-level locks, BDB USES page-level locks. For the InnoDB and BDB storage engines, deadlocks are possible. This is because InnoDB automatically captures the row lock, and BDB captures the page lock when the SQL statement is executed, rather than at the beginning of the transaction.
The advantages of row-level locks are:

Reduces clashing locks when many threads request different records.
Reduces changes to data when transactions are rolled back.
Makes it possible to lock a single line of records for a long time.
The disadvantages of row-level locks are:

It consumes more memory than page-level locks and table-level locks.
When used in a large number of tables, it is slower than page-level locks and table-level locks because it requires more resources to be requested.
When you need to do GROUP BY operations on most of the data frequently, or if you need to scan the entire table frequently, it is obviously worse than other locks.
With higher-level locks, it is easier to support different types of applications because they are much less expensive than row-level locks.
Table-level locks are superior to page-level and row-level locks in the following situations:

Many operations are table reads.
Reads and updates on a strictly conditional index when updates or deletions can be read using 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;
The SELECT and INSERT statements are executed concurrently, but there are only a few UPDATE and DELETE statements.
Many scan tables and GROUP BY operations on full tables, but no write tables.
Another difference between a table-level lock and a row-level or page-level lock is that:
Versioning will have 1 write and multiple reads at the same time (for example, concurrent insertion in MySQL). That is, the database/table supports various attempts to access the data based on the point in time at which it was first accessed. Other names are: time travel, write copy, or copy as needed.

 

// perform SQL statements   The lock off stat_num table  

$sql = "LOCK TABLES stat_num WRITE"; // The table WRITE Lock, block everything else mysql The query process  

$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); 

// When all of the currently requested writes are completed, unlocking is performed sql statements  

$sql = "UNLOCK TABLES"; 

$DatabaseHandler->exeCute($sql);