MySQL experience 10-2-locking _ MySQL

MySQL experience 10-2-lock bitsCN. comMySQL 10-2-lock 1. transactions in the InnoDB and BDB table environments have been introduced before the pseudo transactions. this is the only table type of ACID rule transactions that MySQL naturally supports. The old MySQL table types are still in use in many environments where MySQL is installed. they do not support transactions, but MySQL still allows you to implement original transactions through table locking. This section describes pseudo transactions and provides some basic metrics for executing security transactions on non-transaction tables. 2. lock and unlock because MyISAM (and other old MySQL tables) does not support the COMMIT and ROLLBACK syntax in InnoDB format, every database change is immediately saved on the disk. As mentioned above, in a single user environment, this is no problem, but in a multi-user environment, it will lead to many problems. Because it cannot create transactions to isolate user changes from those made by other users. In this case, the only way to ensure that different users can see consistent data is to force the method: during the change process, other users are prevented from accessing the changing table (by locking the table ), access is allowed only after the change is complete. We have discussed InnoDB and BDB tables. they support row-level and page-level locking to ensure the security of transactions executed simultaneously. However, the MyISAM table type does not support these locking mechanisms. Therefore, we need to explicitly set table locks to avoid space infringement by transactions at the same time. MySQL provides the lock tables statement to LOCK the table of the current thread. the syntax format is AS follows: lock tables tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} Description: table locks support the following types. ● READ: the READ lock ensures that the user can READ the table but cannot modify the table. After adding LOCAL, you can execute non-conflicting INSERT statements after the table is locked. this statement is only applicable to tables of the MyISAM type. ● WRITE: WRITE lock. only users who lock the table can modify the table. other users cannot access the table. Add LOW_PRIORITY to allow other users to read the table, but cannot modify it. When you use a locked table multiple times in a query, you must use the AS clause to define an alias for the table when locking the table. alias indicates the table alias. 3. table locking is only used to prevent improper reading and writing by other clients. Clients that keep locked (even read locked) can perform surface-level operations, such as drop table. Pay attention to the following points when locking a TRANSACTION table: (1) all transactions are implicitly committed when locking the table, such as start transaction when starting a TRANSACTION, all table locks are implicitly unlocked. (2) in the transaction table, the AUTOCOMMIT value of the system variable must be set to 0. Otherwise, MySQL releases the table LOCK immediately after calling lock tables, and easily forms a deadlock. For 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. For 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. Syntax format: unlock tables; the unlock tables command does not need to specify the name of the table to be unlocked. MySQL automatically unlocks all TABLES locked through lock tables. When a user releases another lock tables, or when the connection to the server is closed, all the TABLES locked by the current user are implicitly unlocked. 4. when a user accesses the database concurrently, locking is required to ensure transaction integrity and database consistency. it is the main means to achieve database concurrency control. Locking prevents users from reading data being changed by other users, and prevents multiple users from changing the same data at the same time. If the lock is not used, the data in the database may be logically incorrect and unexpected results may be generated for data queries. Specifically, locking can prevent loss of updates, dirty reads, non-repeated reads, and phantom reads. Lost update indicates that when two or more transactions select the same row and update the row based on the originally selected value, because each transaction does not know the existence of other transactions, therefore, the final update will overwrite the updates made by other firms, which will lead to data loss. Dirty read refers to the dirty read problem that occurs when a transaction is accessing data while other transactions are updating the data but have not yet committed it, that is, the data read by the first transaction is "dirty" (incorrect) data, which may cause errors. When a transaction accesses the same row multiple times and reads different data each time, unrepeatable read occurs. Repeatable reads are similar to dirty reads because the transaction is also reading the data being changed by other transactions. When a transaction accesses the data, another transaction also accesses the data and modifies it, therefore, the data read by the first transaction is different because the second transaction modifies the data. This means that the data cannot be read repeatedly. A phantom read problem occurs when a transaction inserts or deletes a row that is within the range of the row being read by a transaction. The row range for the first read of the transaction shows that one row no longer exists in the second read or subsequent read because the row has been deleted by other transactions. Similarly, due to the insert operation of other transactions, the second read or subsequent read of the transaction shows that a row does not exist in the original read. Author tianyazaiheruanbitsCN.com