Easily understand the principles of the MySQL database lock mechanism, and easily master mysql

Easily understand the principles of the MySQL database lock mechanism, and easily master mysql

1. show processlist: Check the database table status and whether the table is locked;

Kill id // kill the locked table

========================================================== ==============

Set autocommit = 0;

Select * from t1 where uid = 'xxxx' for update // row lock in the case of an index (such as uid); otherwise, table lock

Insert into t1 values (1, 'xxxxx ');

Commit;

========================================================== ==================

Lock tables t1 write | read;

Insert into t1 values (2, 'xxxxx'); // only insert

Unlock tables;

2. easily understand the MySQL database lock mechanism principles

In a table with frequent update and insert operations, a small amount of data runs well during testing. In actual operations, a deadlock may occur due to a large amount of data (0.21 million records, run show processlist to check whether the status of an update statement is Locked and that of a delete statement is Sending data. I checked the reference manual and sorted out the lock-related information so that I could record and track the solution to the problem:

MySQL 5.1 supports table-level locking for MyISAM and MEMORY tables, page-level locking for BDB tables, and row-level locking for InnoDB tables. In many cases, you can guess the type of lock the application uses based on the training, but it is generally difficult to say that a given lock type is better than the other. Everything depends on the application. Different parts of the application may require different lock types. To determine whether you want to use a row-level locking storage engine, you should check what the application is doing and what options and update statements are used together. For example, most Web applications execute many choices, but seldom delete them. They only update the value of the keyword and insert only a small number of specific tables. The basic MySQL MyISAM settings have been well adjusted.

In MySQL, table locks are not deadlocked when table-level locking is used for storage engines. This means that all necessary locks are requested immediately at the beginning of a query and the tables are always locked in the same order.

The table locking method for WRITE and MySQL is as follows:

◆ If there is no lock on the table, put a write lock on it.

◆ Otherwise, put the lock request in the write lock queue.

The locking method for READ and MySQL is as follows:

◆ If there is no write lock on the table, place a read lock on it.

◆ Otherwise, put the lock request in the read lock queue.

When a lock is released, the lock can be obtained by the threads in the write lock queue, followed by the threads in the read lock queue.

This means that if you have many updates on a table, the SELECT statement will wait until there are no more updates.

If the INSERT statement does not conflict with each other, you can use a hybrid parallel INSERT and SELECT statement for the MyISAM table without locking.

InnoDB uses row locking and BDB uses page locking. Both storage engines may have deadlocks. This is because InnoDB automatically acquires row locks during SQL statement processing, while BDB acquires page locks instead of at transaction startup.

Advantages of Row-level locking:

· There are only a few lock conflicts when different rows are accessed in many threads.

· Only a small number of changes are allowed during rollback.

· A single row can be locked for a long time.

Disadvantages of Row-level locking:

· More memory is occupied than page-level or table-level locking.

· When used in most tables, it is slower than page-level or table-level locking because you must obtain more locks.

· If you often perform group by operations on most data or scan the entire table frequently, it is much slower than other locks.

· With high-level locking, you can easily adjust applications by supporting different types of locking because the lock cost is lower than Row-level locking.

In the following cases, table locking takes precedence over page-level or row-level locking:

· Most of the table statements are used for reading.

· Read and update strict keywords. You can update or delete a row that can be extracted with a single read Keyword:

• UPDATE tbl_name SET column = value WHERE unique_key_col = key_value;

• Delete from tbl_name WHERE unique_key_col = key_value;

· SELECT combines parallel INSERT statements with few UPDATE or DELETE statements.

· There are many scan or group by operations on the entire table without any write operations.

Different from row-level or page-level locking options:

· Version (for example, the technology used to insert data in MySQL in parallel), which can be a write operation with many read operations at the same time. This indicates that the database or table supports different views of Data Dependencies, depending on the Access time. Other common terms are "time tracking", "Write replication", or "On-Demand replication ".

· On-Demand replication takes precedence over page-level or row-level locking in many cases. However, in the worst case, it may use more memory than using regular locking.

· In addition to row-level locking, you can use application-level locking, for example, using GET_LOCK () and RELEASE_LOCK () in MySQL (). These are creation locks, and they can only work in well-running applications.

To achieve the maximum locking speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB and BDB ). For InnoDB and bdb tables, if you use lock tables to explicitly lock tables, MySQL only uses table locking. If you do not use lock tables, because InnoDB uses automatic row-level locking and BDB uses page-level locking to ensure transaction isolation.

But for large tables, table locking is better for most applications than row locking, but there are some defects. Table locking allows many threads to read data from a table at the same time. However, if a thread wants to write data to a table, it must first obtain exclusive access. During the update, all other threads that want to access the table must wait until the update is complete.

Table updates are generally considered to be more important than table searches, so they are given a higher priority. This should ensure that the activity of updating a table cannot be "starved to death", even if the table has a heavy SELECT activity.

Table locking may cause problems in this case. For example, when the thread is waiting because the hard disk is full and there must be free space before the thread can process it. In this case, all threads that want to access the problematic table are also set to wait until more hard disk space is available.

The table is locked in the following situations:

· A customer sends a long-running query.

· Then, another customer updates the same table. The customer must wait until the SELECT operation is complete.

· Another customer sends another SELECT statement to the same table. Because UPDATE has a higher priority than SELECT, the SELECT statement waits for UPDATE to complete and waits for 1st SELECT statements to complete.

The following describes some methods to avoid or reduce the competition caused by table locking:

· Try to make the SELECT statement run faster. You may have to create some summary tables to do this.

· Start mysqld with -- low-priority-updates. This gives all statements that update (modify) a table with a lower priority than the SELECT statement. In this case, the 2nd SELECT statements in the previous case will be executed before the UPDATE statement, without waiting for 1st SELECT statements to complete.

· You can use the SET LOW_PRIORITY_UPDATES = 1 Statement to specify that all updates in a specific connection should have a low priority.

· The LOW_PRIORITY attribute can be used to give a specific INSERT, UPDATE, or DELETE statement with a lower priority.

· The HIGH_PRIORITY attribute can be used to give a specific SELECT statement with a higher priority.

· Specify a low value for the max_write_lock_count system variable to start mysqld to force MySQL to temporarily increase the priority of all SELECT statements waiting for a table after a specific number of inserts are completed. In this way, the READ lock is allowed after a certain number of WRITE locks.

· If you have questions about INSERT and SELECT, switch to the new MyISAM table because they support concurrent SELECT and INSERT.

· If you INSERT or delete a table in a hybrid manner, insert delayed will be of great help.

· If you have a problem using the SELECT and DELETE statements in combination with the same table, the delete limit option can be helpful.

· SQL _BUFFER_RESULT can be used for SELECT statements to shorten the table lock time.

· You can change the lock code in mysys/thr_lock.c to use a single queue. In this case, write locks and read locks have the same priority and will be helpful to some applications.

Here are some techniques related to table locking in MySQL:

· You can perform parallel operations if you do not need to check the selection of many rows in the same table and do not need to perform hybrid updates.

· You can use lock tables to increase the speed, because many updates in a LOCK are much faster than those that are not locked. It is also helpful to split the table content into several tables.

· If you encounter speed problems during table lock in MySQL, you can convert the table to InnoDB or BDB tables to improve performance.