MySQL database lock mechanism-MyISAM engine table lock and InnoDB row lock
Lock concept in MySQL
Different storage engines in Mysql support different lock mechanisms. For example, the MyISAM and MEMORY storage engines use table-level locks. BDB uses page locks and table-level locks. the InnoDB Storage engine supports row-level locks and table-level locks, row-level locks are used by default.
The lock features in Mysql3 are as follows:
Table-level locks: low overhead, lock blocks, no deadlocks, large lock granularity, the highest probability of lock conflicts, and the lowest concurrency.
Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock conflict, and the highest concurrency.
Page lock: there is a deadlock between the overhead and the lock bound between the table lock and the row lock. The lock granularity is between the table lock and the row lock, and the concurrency is normal.
MyISAM Table cable 1. query table-Level Lock contention
Analyze the system's above table lock contention by checking the table_locks_waited and table_locks_immediate status variables
The longer the table_locks_waited lock wait time, the more serious table-Level Lock contention.
2. lock mode mysql table lock has two modes: table read lock and table write lock)
Description 1. Read operations on the myISAM table do not block read requests from other users to the same table, but block write requests to the same table. 2. Write operations on the myISAM table will block read and write operations on the same table by other users. 3. myISAM tables are serialized between read and write operations and between write operations. The instance is as follows. Two sessions are opened. When t3 is locked for read, Session 2 can retrieve t3 data. When t3 is in write lock, Session 2 displays data only after it is unlocked (the retrieval time can be compared ).
3. Before executing a query, MyISAM automatically locks and unlocks the table. In general, you do not need to manually add or unlock the table, but sometimes you need to display the lock. For example, you can retrieve the number of data in table t1 and table t2 at a certain time point. Common Code: select count (t1.id1) as 'sum' from t1; select count (t2.id1) as 'sum' from t2;
In fact, this is not labeled. It is very likely that when you retrieve t1 at that time, the t2 data has changed, that is to say, the t1 and t2 data results you check are not at the same time point. The correct method is:
lock table t1 read, t2 read;select count(t1.id1) as 'sum' from t1;select count(t2.id1) as 'sum' from t2;unlock tables;
Of course, union can also be used to write:
SELECT COUNT(t1.`id1`) AS dadasum,'t1' AS tablenameFROM t1UNIONALL SELECT COUNT(t2.`id1`)AS dadasum ,'t2' AS tablenameFROM t2 ;
Note 1. When locking a table, if the keyword local is added, the myISAM table can be inserted concurrently. Eg: lock table t3 read local; 2. when you use locak tables to lock a table, you must lock all the involved tables at the same time, because after the lock, the current session cannot operate the table without the lock. 4. Concurrent Insertion problem myISAM storage engine has a system variable, concurrent_insert, which is used to control Concurrent Insertion behavior. The value can be 0, 1, 2. When concurrent_insert is 0, insertion is not allowed.
When concurrent_insert is set to 1, if mysql has no holes (the row is not deleted in the middle), when myISAM runs a process to read the table, another process inserts records from the end of the table, this is also the default mysql settings. When the value of concurrent_insert is 2, parallel insertion at the end of the table is allowed no matter whether there are holes in the MyISAM table.
5. myISAM lock scheduling problem the Read and Write locks of MyISAM storage engine are mutually exclusive. The read and write operation room is serial. If the Read and Write processes request the same table at the same time, Mysql will first obtain the lock for the write process. Not only that, even if the Read Request first reaches the lock wait queue and the write lock arrives, the write lock will be executed first. Because mysql writes requests more important than read requests. This is exactly why MyISAM is not suitable for applications that contain a large number of update operations and query operations. Adjustment Method: 1) by specifying the startup parameter low-priority-updates, the MyISAM engine gives the Read Request priority by default. 2) by executing set low_PRIORITY_UPDATES = 1, the priority of the update request is reduced. 3) Specify the LOW_PRIORITY attribute of the INSERT, UPDATE, and DELETE statements. InnoDB Lock 1. There are two biggest differences between InnoDB and MyISAM: 1). Support transactions 2). Use row-level locks 2. View InnoDB row lock contention
3. innodb row lock mode and locking method innoDB implements two types of row locks: Shared locks (S): Allow a transaction to read a row, prevents other transactions from obtaining exclusive locks for the same dataset. Exclusive lock (X): allows transactions that obtain exclusive locks to update data, and prevents other transactions from obtaining shared read locks and exclusive write locks for the same dataset. The first two types of batch table locks:
Intention to share the lock intention exclusive lock
If the lock mode of a transaction request is compatible with the current lock mode, innodb grants the requested lock to the transaction. If the two are not compatible, the transaction will wait for the lock to be released. The intention lock is automatically added by Innodb without user intervention. For UPDATE, DELETE, and INSERT statements, Innodb automatically adds an exclusive lock (X) to the involved dataset. Innodb does not apply any lock to normal SELECT statements.
Show add lock share lock (S): SELECT * FROM table_name WHERE .... lock in share mode exclusive LOCK (X): SELECT * FROM table_name WHERE .... for update. use select... the in share mode obtains the shared lock. It is mainly used when data dependency is required to confirm whether a row of record exists and ensure that no one updates or deletes the record. 4. innoDB row lock implementation method InnoDB row lock is implemented by locking the index items on the index. This is different from Oracle in MySQL, where the latter is implemented through data blocks, locks corresponding data rows. The implementation of InnoDB row locks means that innoDB uses row-level locks only when data is retrieved through the index conditions. Otherwise, InnoDB uses table locks, which should be noted during actual development. Example 1: Create a table t1 as follows:
CREATE TABLE `t1` ( `id1` int(5) DEFAULT NULL, `id2` int(3) unsigned zerofill NOT NULL DEFAULT '000') ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into t1 valuses(1,1),(2,2);
Because no index is created, when you add an index to the first session, you actually add a table index instead of a row index, because the second session remains in the waiting status when querying other information, the last timeout period can be queried only after the first session transaction is committed. (Set autocommit = 0 first) instance 2: Modify the data in Table t1 as follows:
Add index alter table t1 add index id1 (id1) to id1 );
This shows that mysql uses a row index. However, we need to note that
Obviously, the two sessions are not queried in the same row. Why does Session 2 still need to be queried after session 1 is submitted? The reason is that Mysql row locks apply to indexes rather than to records. Although indexes access different records, their indexes are the same and there will be conflicts, pay attention to this when designing databases. The conflict problem can be solved only by adding the field id2 and the index. This is one reason for the low efficiency of mysql.
Reference: MySQL database development, optimization, Management and Maintenance
