the lock concept in MySQL
Different storage engines in MySQL support different locking mechanisms. For example, the MyISAM and memory storage engine with table-level locks, bdb with a page lock, also supports table-level locks, InnoDB storage engine supports both row-level locks, table-level locks, by default, row-level locks.
The MYSQL3 lock features are as follows:
Table-level locks: low overhead, lock block, no deadlock, lock granularity, lock collision is the highest probability, concurrency is the lowest.
Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock collisions, and highest concurrency.
Page Lock: A deadlock occurs between the overhead and lock bounds between a table lock and a row lock, and a locking granularity boundary between a table lock and a row lock, and concurrency is common.
MyISAM Watch1. Querying table-level lock contention
Analysis of table lock contention on the system by checking table_locks_waited and table_locks_immediate state variables
The longer the table_locks_waited lock wait time, the more severe table-level lock contention is present.
2. Lock modeThere are two modes of table lock for MySQL: Table shared read lock (tables read lock) and table exclusive write locks (table write lock)
describes a read operation on a 1.myISAM table that does not block other users from reading requests to the same table, but blocks write requests to the same table. a write operation on a 2.myISAM table blocks other users from reading and writing to the same table. 3.myISAM tables are serialized between read, write operations, and write operations. as an example, two sessions are opened, and session Two can retrieve T3 data when T3 is locked in a read lock. When T3 is in a write lock, session two only waits to be unlocked before the data can be displayed (compared to the retrieval time).
3. Add table lockMyISAM in the execution of the query, the table will automatically perform the locking, unlock operation, generally do not require the user to manually add, unlock, but sometimes also need to display lock. For example: Retrieving the amount of data in a t1,t2 table at a given moment. common code is as follows:Select COUNT (t1.id1) as ' sum ' from T1; Select COUNT (t2.id1) as ' sum ' from T2;
In fact, this is not labeled, it is possible that when you retrieve T1 at that point in time, T2 data has changed, that is, you check out the T1 and T2 data results are not at the same point in time. the correct approach is to:
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, you can also use union, so write:
Select count (t1. ' Id1 ') as Dadasum, ' T1 ' as Tablenamefrom t1unionall SELECT COUNT (t2. ' Id1 ') as Dadasum, ' T2 ' As Tablenamefrom T2;
Precautions1. When the table is locked, if the keyword local is added, it satisfies the concurrency insertion problem of the MyISAM table. Eg:lock table t3 read local;2. When you use Locak tables to lock a table, you must also lock all the tables involved, because after locking the current session, you cannot manipulate a table without locks. 4. Concurrent insertion IssuesThe MyISAM storage engine has a system variable, Concurrent_insert, that is specifically designed to control the concurrent insertion behavior, and the value can be 0,1,2. Concurrent_insert is 0, do not allow insertion
Concurrent_insert is 1, if MySQL is not empty (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, which is also the MySQL default setting. Concurrent_insert is 2, allowing for parallel insertion at the end of the table, regardless of whether the MyISAM table is empty.
5.myISAM Lock scheduling problemMyISAM Storage Engine read lock and write lock is mutually exclusive, read and write operation room serial, then if read and write two processes simultaneously request the same table, MySQL will let the write process first obtain lock. Not only that, even if the read request arrives at the lock waiting queue first, the write lock arrives, and the write lock executes first. Because MySQL is more important than a read request because of a write request. This is why MyISAM is not suitable for applications that contain a large number of update operations and query operations. Adjustment Method:1) enable the MyISAM engine to give priority to read requests by specifying the startup parameter Low-priority-updates2) Reduce the priority of update requests by executing set Low_priority_updates=1. 3) Specify the Low_priority property of the Insert, UPDATE, DELETE statement. InnoDB Lockthe maximum difference between 1.InnoDB and MyISAM is two points:1). Support Transactions2). Using row-level locks2. View InnoDB row lock contention
3.innodb row lock mode and lock methodInnoDB implements the first two types of row locks:shared Lock (S): Allows a transaction to read one line, preventing other transactions from acquiring an exclusive lock on the same data set. exclusive Lock (X): A transaction that allows an exclusive lock to update data, preventing other transactions from acquiring shared read and exclusive write locks of the same data set. first two kinds of intent table lock:
Intent sharing lock Intent 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 and, conversely, if the two are incompatible, the transaction waits for the lock to be released. Intent locks are innodb automatically and do not require user intervention. For UPDATE, DELETE, INSERT statements, InnoDB automatically adds an exclusive lock (X) to the data set involved, and InnoDB does not add any locks for the normal SELECT statement.
Show Add lockshared Lock (S): SELECT * FROM table_name WHERE .... LOCK in SHARE MODEexclusive Lock (X): SELECT * FROM table_name WHERE .... For UPDATE. use Select ... in share mode to obtain a shared lock, which is used primarily when data dependencies are required, to confirm that a row record exists, and to ensure that no one is update or delete the record. 4.InnoDB row Lock Implementation methodInnoDB row locks are implemented by locking the index entries on the index, which is different from Oracle, which is implemented by locking the corresponding data rows in the data block. InnoDB This type of row lock implementation is characterized by: only by indexing criteria to retrieve data, InnoDB use row-level locks, otherwise innodb will use table locks, in the actual development should be noted. Example One:Establish the T1 table as follows:
CREATE TABLE ' t1 ' ( ' id1 ' int (5) Default NULL, ' id2 ' int (3) unsigned zerofill not NULL default ' ") Engine=innod B DEFAULT Charset=utf8
<span style= "FONT-SIZE:18PX;" >insert into T1 valuses (a), (2,2);</span>
because there is no index created, when you add an index to the first session, you add the table index instead of the row index, because the second session waits until the other information is queried, and finally times out, until the first session transaction commits, before it can be queried. (set autocommit=0 is required first)Example Two:Modify the data in the T1 table above, the data is as follows
Add index to ID1 alter TABLE T1 add index ID1 (ID1);
As you can see, MySQL is using the row index at this point. but there's another one that needs our attention .
It is obvious that the two sessions are not querying the same row of records, why does session 2 still need to wait for session 1 to be committed before querying? or because the MySQL row lock is a lock on the index, not for the record plus locks, although the index access to different records, but their index is the same, there is a conflict, in the design of the database need to pay attention to this point. The above only resolves the conflict by id2 the field and adding an index. This is one of the reasons why MySQL is inefficient.
reference: MySQL database development, optimization and management maintenance
MySQL database lock mechanism MyISAM engine table lock and InnoDB row lock detailed
