How MySQL locks a single table
Copy Code code as follows:
Mysql>lock table Userstat Read;
Mysql>unlock tables;
The typical page-level representative engine is BDB.
The typical representative engine for the table level is myisam,memory and ISAM, a long time ago.
The typical line-level representative engine is InnoDB.
-the most used in our actual application is the row lock.
The advantages of row-level locks are as follows:
1. Reduce lock state when many connections have different queries.
2), if there is an exception, you can reduce the loss of data. Because you can roll back only one row or a few lines of data at a time.
The disadvantages of row-level locks are as follows:
1), more memory than page-level locks and table-level locks.
2), the query is more than page-level locks and table-level locks need more I/O, so we often use row-level locks in the write operation rather than read operations.
3), easy to appear deadlock.
For write locks as follows:
1), if the table is not locked, then write lock on it.
2), otherwise, put the request into the write lock queue.
For read locks as follows:
1), if the table does not write lock, then add a read lock.
2), otherwise, put the request into the read lock queue.
Of course, we can use low_priority and high_priority to change these behaviors in both write and read operations.
If you want to do a large number of insert and SELECT operations on a table, but parallel inserts are not possible, you can insert records into a temporary table, and then periodically update the data in the temporary table to the actual tables. You can implement 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 InnoDB and BDB storage engines, deadlocks can occur. 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 conflicting locks when many threads request different records.
Reduces data change when a transaction rolls 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 locks 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.
It is significantly worse than other locks when you need to frequently make GROUP by operations on most of the data or if you need to scan the entire table frequently.
With higher-level locks, it is easier to support a variety of different types of applications because the locks 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 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;
The SELECT and INSERT statements execute concurrently, but there are few UPDATE and DELETE statements.
Many scan tables and GROUP by operations on the entire table, but there are no write tables.
The difference between a table-level lock and a row-level lock or page-level lock is also:
A version (for example, concurrent inserts in MySQL) will be available for both write and multiple reads. That is, the database/table supports various attempts based on the differences in the time point at which data is started. Other names include: Time travel, write replication, or copy on demand.
Copy Code code as follows:
Execute SQL statement lock off Stat_num table
$sql = "LOCK TABLES stat_num WRITE"; Table write lock, 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);
Execute the UNLOCK SQL statement after all the write operations for the current request have been completed
$sql = "UNLOCK TABLES";
$DatabaseHandler->execute ($sql);
