A simple way to master the relevant principles of MySQL database lock mechanism

1,show processlist View the status of the table in the database, whether it is locked;

Kill ID//kills locked table

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

Set autocommit=0;

SELECT * from t1 where uid= ' xxxx ' for update//is a row lock in the case of an index (e.g. UID), otherwise it is a 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 "Easy to grasp the MySQL database lock mechanism related principles"

In a table where update and insert operations are frequent, a small amount of data is tested well, and in actual operation, due to the large amount of data (210,000 records), there is a deadlock phenomenon, which is viewed with show processlist, You can see that an UPDATE statement state is locked, and a DELETE statement state is sending data. Check out the reference manual to sort out the lock-related information so that you can record and track the resolution of the problem:

MySQL 5.1 supports table-level locking of MyISAM and Memory tables, page-level locking of BDB tables, row-level locking of InnoDB tables. In many cases, it is best to guess which type of lock the application uses based on training, but it is generally difficult to say that one given lock type is better than the other. Depending on the application, different parts of the application may require different lock types. To determine if you want to use a row-level locked storage engine, you should look at what the application does and what kind of selection and UPDATE statements to use. For example, most Web applications perform many choices, but rarely delete them, only the values of the keywords are updated, and only a small number of specific tables are inserted. The basic MySQL MyISAM settings have been well tuned.

In MySQL for a storage engine that uses table-level locking, the table locks without deadlock. This is managed by always requesting all necessary locks at the beginning of a query and always locking the table in the same order.

The table locking method used for Write,mysql is as follows:

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

Otherwise, the lock request is placed in the write lock queue.

The locking method used for Read,mysql is as follows:

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

Otherwise, the lock request is placed in the read lock queue.

When a lock is released, the lock can be obtained by the thread in the write lock queue, and then the thread in the lock queue is read.

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

If the INSERT statement does not conflict, you can freely mix parallel insert and SELECT statements for the MyISAM table without locking.

InnoDB uses row locking, BDB uses page locking. There may be deadlocks for both of these storage engines. This is because, during the processing of SQL statements, InnoDB automatically obtains row locks, and BDB obtains page locks instead of being obtained when a transaction is started.

Advantages of row-level locking:

· There are only a few locking conflicts when accessing different rows in many threads.

· There are only a few changes when rolling back.

· You can lock a single row for a long time.

Disadvantages of row-level locking:

· Consumes more memory than page-level or table-level locking.

· When used in most of the table, it is slower than page-level or table-level locking because you have to get more locks.

· If you frequently perform GROUP by operations on most data or you must scan the entire table frequently, it is significantly slower than other locks.

· With high-level locking, you can easily adjust your application by supporting different types of locking, because its lock cost is less than row-level locking.

Table locking takes precedence over page-level or row-level locking in the following cases:

· Most of the statements in the table are used for reading.

· With strict keywords read and updated, 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, and there is no write operation.

Options that are different from row-level or page-level locking:

· Version (for example, the technique used in MySQL for parallel insertions), which can be a write operation with many read operations. This database or table supports different views of data dependencies, depending on when the access begins. Other common terms are "time tracking," "Write Replication," or "copy on Demand."

· 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 locks.

· In addition to row-level locking, you can use application-level locking, such as Get_lock () and Release_lock () in MySQL. These are recommended locks that work only in well-run applications.

To achieve the highest lock speed, MySQL uses table locking (rather than page, row, or column locking) for all storage engines except InnoDB and BDB. For InnoDB and BDB tables, if you explicitly lock the table with lock TABLES, MySQL uses only 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, for most applications, table locking is better than row locking, but there is a partial flaw. Table locking enables many threads to read from one table at the same time, but if a thread wants to write to the 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 more important than table retrievals, so they are given a higher priority. This should ensure that an activity that updates a table cannot "starve" even if there is a heavy select activity on the table.

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

Table locking also has problems in the following situations:

· A customer issues a long-running query.

· A second customer then updates the same table. The customer must wait until the select is complete.

· Another customer issues a second SELECT statement on the same table. Because the update is higher than the select priority, the SELECT statement waits for the update to complete and waits for the 1th select to complete.

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

· An attempt was made to run the SELECT statement faster. You may have to create some summary (summary) tables to do this.

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

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

· You can use the Low_priority property to give a lower priority to a specific insert, UPDATE, or DELETE statement.

· You can use the High_priority property to give a higher priority to a particular SELECT statement.

· 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 complete. This allows a read lock to be given after a certain number of write locks.

· If you have questions about insert combined with SELECT, switch to using the new MyISAM table because they support concurrent select and insert.

· If you mix inserts and deletes on the same table, insert delayed will be a great help.

· The limit option for DELETE can help if you are having problems mixing the same table with SELECT and DELETE statements.

· Using Sql_buffer_result with the SELECT statement can help 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, the write lock and read lock will have the same priority, and some applications will be helpful.

Here are some tips related to table locking in MySQL:

· If you do not mix updates with a selection that requires you to check many rows in the same table, you can do this in parallel.

· You can use the lock TABLES to increase speed because many updates in one lock are much faster than updates that are not locked. Splitting the contents of a table into several tables can also be useful.

· If you experience speed problems with table locking in MySQL, you can improve performance by converting tables to InnoDB or BDB tables.

A simple way to master the relevant principles of MySQL database lock mechanism