Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of locking in MySQL, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

1. Pseudo transaction

Transactions in InnoDB and BDB table environments have been introduced earlier, which is the only table type of ACID rule transactions that MySQL inherently supports. The old MySQL table types, still in use in many environments where MySQL is installed, do not support transactions, but MySQL still enables users to implement transactions in their original form through table locking. Pseudo transactions are introduced here, and some basic indicators for performing secure transactions on non-transaction tables are provided. Www.2cto.com

two。 Lock and unlock

Because MyISAM (and other older MySQL tables) do not support COMMIT and ROLLBACK syntax in InnoDB format, each change is immediately saved on disk. As mentioned earlier, this is fine in a single-user environment, but it can cause a lot of problems in a multi-user environment. Because it cannot create transactions to isolate changes made by users from changes made by other users. In this case, the only way to ensure that different users can see consistent data is to enforce it: prevent other users from accessing the changing table (by locking the table) during the change, and allow access only after the change is complete.

The InnoDB and BDB tables, which support row-level and page-level locking, have been discussed earlier to secure simultaneous transactions. However, the MyISAM table type does not support these locking mechanisms. Therefore, table locking should be explicitly set to avoid simultaneous transactions invading each other's space.

MySQL provides a LOCK TABLES statement to lock the current thread's table. The syntax format is as follows: LOCK TABLES

Tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

Description:

Table locking supports the following types of locking.

● READ: read lock to ensure that the user can read the table, but cannot modify the table. The addition of LOCAL allows users to make non-conflicting INSERT statements after the table is locked, which is only applicable to tables of type MyISAM.

● WRITE: write lock, only the user who locks the table can modify the table, and other users cannot access the table. Add LOW_PRIORITY to allow other users to read the table, but cannot modify it.

When users use a locked table multiple times in a query, they need to define an alias for the table with the AS clause when locking the table, and alias represents the alias of the table. Www.2cto.com

3. Table locking is only used to prevent improper reads and writes by other clients.

Clients that keep locks (even read locks) can perform surface-level operations, such as DROP TABLE.

The following points need to be noted when using table locking on a transaction table:

(1) all transactions are implicitly committed when the table is locked, and all table locks are implicitly unlocked at the start of a transaction, such as START TRANSACTION.

(2) in the transaction table, the system variable AUTOCOMMIT must be set to 0. Otherwise, MySQL releases the table lock immediately after calling LOCK TABLES, and it is easy to form a deadlock.

Example: set a read-only lock on the XS table: LOCK TABLES XS READ

Note: LOCK TABLES can also lock multiple tables at the same time, separated by commas.

Example: set a write lock on the KC table: LOCK TABLES KC WRITE

After locking the table, you can use the UNLOCK TABLES command to unlock it. The syntax format is: UNLOCK TABLES

The UNLOCK TABLES command does not need to indicate the name of the unlocked table. MySQL automatically unlocks all tables that were previously locked by LOCK TABLES. When the user publishes another LOCK TABLES, or when the connection to the server is closed, all tables locked by the current user are implicitly unlocked.

4. When users access the database concurrently, locking is needed to ensure transaction integrity and database consistency, which is the main means to realize database concurrency control. Locking prevents users from reading data that is being changed by other users, and prevents multiple users from changing the same data at the same time. If locking is not used, the data in the database may be logically incorrect, and queries to the data may produce unexpected results. Specifically, locking prevents the loss of updates, dirty reads, unrepeatable reads, and phantom reads.

Missing updates (lost update) means that when two or more transactions select the same row and then update the row based on the values originally selected, because each transaction is unaware of the existence of other transactions, the last update will overwrite updates made by other transactions, resulting in data loss. Www.2cto.com

Dirty read means that one transaction is accessing the data while another transaction is updating the data but has not yet committed, and the dirty read problem occurs, that is, the data read by the first transaction is "dirty" (incorrect) data, which may cause errors.

The unrepeatable read problem occurs when a transaction accesses the same row multiple times and reads different data each time. Non-repeatable reads are similar to dirty reads because the transaction is also reading data that other transactions are changing. When one transaction accesses the data, another transaction also accesses the data and modifies it, so it occurs that the data read by the first transaction is different from that read by the second transaction due to the modification of the data by the second transaction. this is non-repeatable.

The phantom read problem occurs when a transaction performs an insert or delete operation on a row that falls within the scope of the row that the transaction is reading. The range of rows read by the transaction for the first time shows that one of the rows no longer exists in the second or subsequent read because the row has been deleted by another transaction. Similarly, due to the insert operation of other transactions, the second or subsequent read of the transaction shows that a row no longer exists in the original read.

Thank you for reading this article carefully. I hope the article "example Analysis locked in MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.