SQL lock table and unlock mysql statements

SQL lock table and unlock mysql tutorial statements

For MySQL, there are three lock levels: page-level, table-level, and row-level.

A typical page-level engine is BDB.
A typical table-level engine is MyISAM, MEMORY, and ISAM, which was a long time ago.
A typical line-level engine is INNODB.

-Row locks are the most commonly used in actual applications.
Row-level locks have the following advantages:
1) Reduce the LOCK status when multiple connections perform different queries separately.
2) if an exception occurs, data loss can be reduced. Because only one or several rows of data can be rolled back at a time.
The disadvantages of Row-level locks are as follows:
1) the page-Level Lock and table-Level Lock occupy more memory.
2) query requires more I/O than page-level locks and table-level locks. Therefore, we often use row-level locks for write operations rather than read operations.
3) deadlock is prone.

Write locks are as follows:
1) if the table is not locked, write the table to lock it.
2) Otherwise, put the request into the write lock queue.
Read locks are as follows:
1) if no write lock is applied to the table, 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 actions in write and read operations.

If you want to perform a large number of INSERT and SELECT operations on a table, but parallel INSERT is not possible, you can INSERT records into the temporary table, then, the data in the temporary table is updated to the actual table on a regular basis. You can use 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 row-level locks and BDB uses page-level locks. For InnoDB and BDB storage engines, deadlocks may occur. This is because InnoDB will automatically capture row locks and BDB will capture page locks when executing SQL statements, rather than doing so at the beginning of the transaction.

Row-level locks have the following advantages:

Reduce the conflict lock when multiple threads request different records.

Reduce data changes during transaction rollback.

It is possible to lock a single row of records for a long time.

Row-level locks have the following Disadvantages:

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 they need to request more resources.

When you need to frequently perform group by operations on most data or frequently scan the entire table, it is obviously worse than other locks.

Using Higher-Level locks makes it easier to support different types of applications, because the cost of such locks is much lower than that of Row-level locks.

Table-level locks are superior to page-level locks and row-level locks in the following situations:

Many operations are read tables.

Read and update indexes with strict conditions. When updating or deleting indexes, you can use a separate index to read them:

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 the entire table, but there is no write table.

Table-level locks differ from row-level locks or page-level locks in the following ways:

Make the version of one write and multiple reads (for example, concurrent inserts in MySQL ). That is to say, database tutorials/Tables support different attempts based on the time points at which data access starts. Other names include time travel, write replication, or on-demand replication.

// Execute the SQL statement to lock the stat_num table

$ SQL = "LOCK TABLES stat_num WRITE"; // The WRITE LOCK on the table blocks all other mysql query processes.
$ DatabaseHandler-> exeCute ($ SQL );

// Perform update or write operations

$ 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 write operations of the current request are completed

$ SQL = "UNLOCK TABLES ";
$ DatabaseHandler-> exeCute ($ SQL );