(16) Table locking and transaction control in mysql

(16) Table locking and transaction control in mysql
Overview

MySQL supports three lock levels: Page, table, and row. The MyISAM and MEMORY storage engines use table-level locking. The BDB storage engine uses page-level locking, but also supports table-level locking; the InnoDB Storage engine supports both row-level locking and table-level locking, but row-level locks are used by default. By default, table locks and row locks are automatically obtained without additional commands. The three lock levels have the following advantages and disadvantages:

Table-level locks: low overhead, fast locking, no deadlock, large lock granularity, the highest probability of lock conflict, the lowest concurrency. Page lock: overhead and lock time are between table locks and row locks. Deadlocks may occur. The lock granularity is between table locks and row locks, with a general concurrency. Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock conflict, and the highest concurrency. Table lock1. Syntax

# Lock tables tb_name1 [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} tb_name2 [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE }... # Release the table to lock the unlock tables;

Lock tables can lock tables used for the current thread (session. If it is locked by another thread, the current thread will wait until it can get all the locks. Unlock tables releases any lock obtained by the current thread (session. Read (READ lock/shared lock): When the table does not have a WRITE lock, the read lock is executed. In this status, the current thread cannot be modified (insert, update, delete ), the modification operations of other threads enter the queue. When the current thread releases the lock, the modifications of other threads are executed. Read local: The difference between read local and read is that read local allows non-conflicting INSERT statements (INSERT at the same time) to be executed when the lock is kept ). However, if you are planning to operate on database files outside MySQL while you are still locked, you cannot use read local. For InnoDB tables, read local is the same as read. Write (write/exclusive lock): All Access (read/write) of other users is completely blocked except that the current user is allowed to read and modify the locked table. Note that when the current thread executes the WRITE operation, it will be canceled even if the READ is not canceled. Low_priority write: write with lower priority. By default, write has a higher priority than read. if the low_priority write of the current thread is in the queue and another thread transfers a read before execution, low_priority write continues to wait. innoDB row locks are implemented by locking the index items on the index. This is different from Oracle in MySQL. The latter is implemented by locking the corresponding data rows in the data block. The implementation of InnoDB row locks means that InnoDB uses row-level locks only when data is retrieved through index conditions. Otherwise, InnoDB uses table locks! Concurrent insert: in principle, when a data table has a read lock, other processes cannot update the table. However, under certain conditions, the MyISAM table also supports concurrent query and insert operations. The MyISAM storage engine has a system variable concurrent_insert, which is used to control its Concurrent Insertion behavior. The values can be 0, 1, or 2, respectively.
Concurrent inserts are not allowed when concurrent_insert is set to 0. When concurrent_insert is set to 1, if the MyISAM table does not have holes (that is, the rows in the middle of the table are not deleted), MyISAM allows a process to read the table at the same time, another process inserts records from the end of the table. This is also the default setting of MySQL. When concurrent_insert is set to 2, records can be inserted concurrently at the end of the table regardless of whether there are holes in the MyISAM table. 2. Example

This is an example of getting and releasing a table lock. Session1 and session2 indicate two sessions opened at the same time (connection/thread). Each row in the table indicates the running status of the two sessions at the same time. The following example is also in the same format.


Transaction Control

MySQL supports local transactions through set autocommit, start transaction, commit, rollback, and other statements.

1. Syntax

START TRANSACTION |BEGIN [WORK]COMMIT [WORK][AND [NO] CHAIN] [[NO] RELEASE]ROLLBACK [WORK][AND [NO] CHAIN] [[NO] RELEASE]SET AUTOCOMMIT={0|1}

By default, MySQL is automatically committed. To submit and roll back a transaction through explicit Commit and Rollback, you need to use clear transaction control commands to start the transaction. Start transaction or begin statement to start a new transaction. Commit and rollback are used to submit or roll back transactions. The chain and release clauses are used to define the operations after the transaction is committed or rolled back. The chain immediately starts a new thing and has the same isolation level as the previous transaction, release will disconnect from the client. Set autocommit can modify the submission method of the current connection. If set autocommit = 0 is set, all the transactions after the set need to be committed or rolled back using clear commands. 2. Example

Example 1
Use start transaction and commit and chain.


<喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> VcD4NCjxwPjxjb2RlIGNsYXNzPQ = "language-SQL hljs"> Example 2
When a table is locked, use the start transaction command to start a new transaction, which causes the implicit unlock tables to be executed.


Example 3
In a transaction, you can define a savepoint to specify a part of the transaction to be rolled back, but not a part of the transaction to be committed. For complex applications, you can define multiple savepoints to roll back different savepoints when different conditions are met. Note that if savepoint with the same name is defined, the savepoint defined later will overwrite the previous definition. For a savepoint that is no longer needed, you can use the release savepoint command to delete the savepoint. The deleted savepoint cannot run the rollback to savepoint command.