mysql advanced (4) mysql select

select * for update

in mysql Note:

FOR UPDATE is only applicable to InnoDB and must be in the transaction block (BEGIN/COMMIT) to take effect.

Function

Lock the object selected by this statement. This prevents data inconsistency caused by modifying these objects elsewhere after selection. To ensure that records are not updated by other users during the execution of statistics (query),

you can use the For update clause for locking. In this way, other users cannot update, delete or lock these records before the lock is released.

Select daptno from dept Where deptno=25 For update;
If you use FOR UPDATE to lock the table, you must use commit to release the locked records.

Locks are divided into two categories: locking scope clause and locking behavior clause Locking scope clause: After select...for update, you can use the of clause to select the select Perform locking operations on specific data tables. By default, not using the of clause means locking all data tables in the select. Locking behavior clause: When we perform the for update operation, it is very different from ordinary select. Generally, the selection does not need to consider whether the data is locked, and at most reads the previous version based on the feature of multi-version consistent reading.
Rules The for UPDATE statement will lock the tuples in the query results. These tuples will not be operated by UPDATE, delete and for UPDATE of other transactions until this transaction is committed.

Application scenarios

So, when do you need to use for update? When business-level data is required to be exclusive, you can consider using for update. In scenarios, such as train ticket booking, the remaining tickets are displayed on the screen, and when the tickets are actually issued, it is necessary to re-confirm that the data has not been modified by other clients. Therefore, during this confirmation process, you can use for update. This is a unified solution problem and requires preparation in advance.

Since InnoDB defaults to Row-Level Lock, MySQL will execute Row lock only if the primary key is "explicitly" specified (only the selected key will be locked) Data example), otherwise MySQL will execute Table Lock (lock the entire data form).

Example 1

Let me give you a few examples:

select * from t for update 会等待行锁释放之后，返回查询结果。
select * from t for update nowait 不等待行锁释放，提示锁冲突，不返回结果
select * from t for update wait 5 等待5秒，若行锁仍未释放，则提示锁冲突，不返回结果
select * from t for update skip locked 查询返回查询结果，但忽略有行锁的记录

The syntax of the SELECT...FOR UPDATE statement is as follows:
　

　SELECT ... FOR UPDATE [OF column_list][WAIT n|NOWAIT][SKIP LOCKED];

Among them:
The OF clause is used to specify the column to be updated, that is, to lock a specific column on the row.
The WAIT clause specifies the number of seconds to wait for other users to release the lock to prevent indefinite waiting.

The advantages of the "Use FOR UPDATE WAIT" clause are as follows:
1. Prevents waiting indefinitely for locked rows; 2. Allows the application to have more control over the lock waiting time. .
　3 Very useful for interactive applications, because these users cannot wait indefinitely
　4 If skip locked is used, the locked rows can be skipped and the 'resource busy' exception report caused by wait n will not be reported

Example 2

Suppose there is a form products, which has two fields: id and name, and id is the primary key.

Example 1: (Explicitly specify the primary key, and there is this data, row lock)

SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

Example 2: (Explicitly specify the primary key, if there is no such data, there is no lock)

SELECT * FROM products WHERE id='-1' FOR UPDATE;

Example 2: (No primary key, table lock)

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

Example 3: (Unclear primary key, table lock )

SELECT * FROM products WHERE id<>&#39;3&#39; FOR UPDATE;

Example 4: (Unclear primary key, table lock)

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

Note 1: FOR UPDATE only Applicable to InnoDB and must be in the transaction block (BEGIN/COMMIT) to take effect.

Note 2: To test the locking situation, you can use MySQL's Command Mode to open two windows for testing.

The test in MySql 5.0 is indeed like this.

In addition: MyAsim only supports table-level locks, and InnerDB supports row-level locks.

The data with added (row-level lock/table-level lock) lock cannot be locked by other transactions, nor can it be modified (modified, deleted) by other transactions. When it is a table-level lock, regardless of whether the record is queried, The table will be locked.

In addition, if A and B both query the table ID but cannot query the record, A and B will not perform row locks on the query, but both A and B will acquire exclusive locks. At this time, A If you insert another record, it will be waiting because B already has the lock. At this time, B will insert the same data again and it will throw Deadlock found when trying to get lock; try restarting transaction and then release the lock. At this time, A will get The lock was inserted successfully.

Knowledge supplement

Lock is a very important concept in the database. It is mainly used to ensure the integrity and consistency of the database in a multi-user environment. We know that if multiple users can manipulate data in the same database at the same time, data inconsistency will occur. That is, if there are no locks and multiple users access a database at the same time, problems may occur when their transactions use the same data at the same time. These problems include: lost updates, dirty reads, non-repeatable reads and phantom reads:

1. The lost update problem occurs when two or more transactions select the same row and then update that row based on the originally selected value. Each transaction is unaware of the existence of other transactions. The last update will overwrite updates made by other transactions, which will cause data loss. For example, two editors produce electronic copies of the same document. Each editor makes changes to his or her copy independently and then saves the changed copy, overwriting the original document. The last editor to save a copy of his or her changes overwrites the changes made by the first editor. This problem can be avoided if the second editor cannot make changes until the first editor has finished.

2. Dirty reading means that when a transaction is accessing data and has modified the data, but the modification has not yet been submitted to the database, another transaction also accesses the data. , and then used this data. Because this data has not yet been committed, the data read by another transaction is dirty data, and the operations based on the dirty data may be incorrect. For example, an editor is making changes to an electronic document. During the change process, another editor makes a copy of the document (the copy contains all the changes made so far) and distributes it to the intended users. After this, the first editor decided that the changes made so far were wrong, deleted the edits, and saved the document. Documents distributed to users contain edits that no longer exist and shall be deemed to have never existed. This problem can be avoided if no one can read the changed document until the first editor finalizes the changes.

3. Non-repeatable read refers to reading the same data multiple times within a transaction. Before this transaction ends, another transaction also accesses the same data. Then, between the two reads of data in the first transaction, due to the modification of the second transaction, the data read twice by the first transaction may be different. In this way, the data read twice within a transaction is different, so it is called non-repeatable read. For example, an editor reads the same document twice, but between reads the author rewrites the document. When the editor reads the document a second time, the document has changed. Raw reads are not repeatable. This problem can be avoided if editors can only read the document after the author has all finished writing.

4． Phantom read refers to a phenomenon that occurs when transactions are not executed independently. For example, the first transaction modifies the data in a table, and this modification involves all data rows in the table. At the same time, the second transaction also modifies the data in this table. This modification inserts a row of new data into the table. Then, in the future, the user who operates the first transaction will find that there are still unmodified data rows in the table, as if a hallucination has occurred. For example, an editor changes a document submitted by an author, but when production merges their changes into the master copy of the document, it is discovered that the author has added new, unedited material to the document. This problem can be avoided if no one can add new material to the document until the editors and production department have finished working on the original document.

Therefore, the way to handle concurrent access by multiple users is to lock. Locks are a major means of preventing other transactions from accessing specified resource controls and achieving concurrency control. When a user locks an object in the database, other users can no longer access the object. The impact of locking on concurrent access is reflected in the granularity of the lock. In order to control locked resources, you should first understand the system's space management.

The above is the content of select in mysql advanced (4) mysql. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!