Mysql Pessimistic lock

Reprint: http://chenzhou123520.iteye.com/blog/1860954

Pessimistic Lock Introduction:

Pessimistic locking, as its name implies, is a conservative attitude to the data being modified by the outside world (including other transactions currently in the system, as well as transactions from external systems), so that the data is locked during the entire data processing process. Pessimistic lock implementation, often rely on the database provided by the lock mechanism (also only the database layer provides a lock mechanism to truly guarantee the exclusivity of data access, otherwise, even in this system to implement the locking mechanism, there is no guarantee that the external system will not modify the data).

examples of usage scenarios : MySQL InnoDB as an example

The Product goods table has a field status,status of 1 for the product is not under order, status is 2 for the product has been orders, then we have to order a product to ensure that the product status is 1. Suppose the product has an ID of 1.

1 if the lock is not used, then the operation is as follows:

1. Check the product information select status from T_goods where id=1;//2. Generate orders based on product information insert into t_orders (id,goods_id) values (null,1);//3. Repair Change the product status to 2update t_goods set status=2;

This scenario is likely to cause problems in high concurrent access scenarios.

As mentioned earlier, only if the goods status is 1 o'clock to order the goods, the first step above, the item status of the query is 1. However, when we perform the third update operation, it is possible for others to first change the goods status to 2 for the order, but we do not know that the data has been modified, which may cause the same item to be put on the order 2 times, which makes the data inconsistent. So it's not safe to say this way.

2 Use pessimistic lock to achieve:

In the above scene, the product information from the query out to modify, in the middle there is a process of processing orders, the use of pessimistic locking principle is that when we query out goods information after the current data lock, until we modify and then unlock. So in this process, because the goods is locked, there will be no third party to modify it.

Note: To use pessimistic locks, we must turn off the auto-commit property of the MySQL database because MySQL uses the autocommit mode by default, which means that when you perform an update operation, MySQL will immediately submit the results.

We can use the command to set MySQL to non-autocommit mode:

Set autocommit=0;

After setting up the autocommit, we can execute our normal business. Specific as follows:

0. Commencement of business Begin;/begin Work;/start transaction; (You can choose one of the three)//1. Check the product information select status from T_goods where id=1 for update;//2. Generate orders based on product information insert into t_orders (id,goods_id) VA Lues (null,1);//3. Modify the product status to 2update T_goods set status=2;//4. Commit the transaction commit;/commit work;

Note: The above begin/commit is the start and end of the transaction because we closed the MySQL autocommit in the previous step, so we need to manually control the commit of the transaction.

The first step above we performed a query operation: Select status from T_goods where id=1 for update;

Unlike normal queries, we use the Select...for Update method, which enables pessimistic locking through the database. At this point in the T_goods table, the data with ID 1 is locked, and the other transaction must wait for the transaction to be committed before it can be executed. This allows us to ensure that the current data is not modified by other transactions.

Note: It is important to note that in a transaction, only select ... For UPDATE or lock in SHARE MODE the same pen data will wait for other transactions to finish before executing, general Select ... is not affected by this.

Take the example above when I perform the select status from T_goods where id=1 for update; I am in another transaction if the select status from T_goods where id=1 for update is executed again, then the second transaction waits for the first transaction to commit, at which point the second query is in a blocked state. But if I was executing the select status from T_goods where Id=1 in the second transaction, the data would be queried normally and not affected by the first transaction.

Added: MySQL select...for update's Row lock and table lock

As we mentioned above, using Select...for Update will lock the data, but we need to pay attention to some lock levels, MySQL InnoDB default row-level lock, so only "explicitly" to specify the primary key, MySQL will execute row lock ( Lock only the selected data), or MySQL will execute table lock (lock the entire data form).

To illustrate:

database table T_goods, including Id,status,name three fields, ID as the primary key, the database is recorded as follows:

Mysql> SELECT * from t_goods;+----+--------+------+| ID | Status | Name |+----+--------+------+|  1 |      1 | Props | |  2 |      1 | Equip |+----+--------+------+2 rows in setmysql>

Note: In order to test the database lock, I use two console to simulate different transaction operations, represented by Console1, Console2, respectively.

Example 1: (explicitly specifying a primary key and having this data, row lock)

Console1: The result was queried, but the data was locked

Mysql> SELECT * from T_goods where id=1 for update;+----+--------+------+| ID | Status | Name |+----+--------+------+|  1 |      1 | Props |+----+--------+------+1 row in setmysql>

Console2: Query is blocked

Mysql> SELECT * from T_goods where id=1 for update;

Console2: If Console1 is not submitted for a long time, it will report an error

Mysql> SELECT * from T_goods where id=1 for update; ERROR 1205:lock wait timeout exceeded; Try restarting transaction

Example 2: (explicitly specify the primary key, if the data is not found, no lock)

Console1: The query result is empty

Mysql> SELECT * from T_goods where id=3 for update; Empty Set

Console2: The query result is empty, the query is non-blocking, indicating that Console1 does not lock the data

Mysql> SELECT * from T_goods where id=3 for update; Empty Set

Example 3: (No primary key, table lock)

Console1: Query name= props data, query normal

Mysql> SELECT * from t_goods where name= ' props ' for update;+----+--------+------+| ID | Status | Name |+----+--------+------+|  1 |      1 | Props |+----+--------+------+1 row in setmysql>

Console2: Query name= equipment data, query blocking, instructions Console1 to lock the watch

Mysql> SELECT * from t_goods where name= ' equipment ' for update;

Console2: If Console1 is not committed for a long time, the query returns empty

Mysql> SELECT * from t_goods where name= ' equipment ' for update; Query OK,-1 rows affected

Example 4: (primary key ambiguous, table lock)

Console1: Query OK

Mysql> begin; Query OK, 0 rows affectedmysql> select * from T_goods where id>0 for update;+----+--------+------+| ID | Status | Name |+----+--------+------+|  1 |      1 | Props | |  2 |      1 | Equip |+----+--------+------+2 rows in setmysql>

Console2: The query was blocked, indicating that Console1 locked the watch.

Mysql> SELECT * from T_goods where id>1 for update;

Example 5: (primary key ambiguous, table lock)

Console1:

Mysql> begin; Query OK, 0 rows affectedmysql> select * from T_goods where id<>1 for update;+----+--------+------+| ID | Status | Name |+----+--------+------+|  2 |      1 | Equip |+----+--------+------+1 row in setmysql>

Console2: The query was blocked, indicating that Console1 locked the watch.

SELECT * from T_goods where id<>2 for update;

Console1: Commit Transaction

Mysql> commit; Query OK, 0 rows affected

Console2 query results are normal after CONSOLE2:CONSOLE1 transaction commits

Mysql> SELECT * from T_goods where id<>2 for update;+----+--------+------+| ID | Status | Name |+----+--------+------+|  1 |      1 | Props |+----+--------+------+1 row in setmysql>

The above is an example of the impact of the database primary key on the MySQL lock level, and it is important to note that the use of indexes also affects the locking level of the database in addition to the primary key.

Example:

We modify the T_goods table to create an index to the Status field

Change the status of data with ID 2 to 2, at which time the data in the table is:

Mysql> SELECT * from t_goods;+----+--------+------+| ID | Status | Name |+----+--------+------+|  1 |      1 | Props | |  2 |      2 | Equip |+----+--------+------+2 rows in setmysql>

Example 6: (explicitly specified index, and there is this data, row lock)

Console1:

Mysql> SELECT * from T_goods where Status=1 for update;+----+--------+------+| ID | Status | Name |+----+--------+------+|  1 |      1 | Props |+----+--------+------+1 row in setmysql>

Console2: Blocking when querying Status=1 data, returning empty after timeout, indicating that the data is locked by Console1

Mysql> SELECT * from T_goods where status=1 for update; Query OK,-1 rows affected

Console2: Query status=2 data, can normal query, indicating that Console1 only locked the line, unlocked the table

Mysql> SELECT * from T_goods where status=2 for update;+----+--------+------+| ID | Status | Name |+----+--------+------+|  2 |      2 | Equip |+----+--------+------+1 row in setmysql>

Example 7: (explicitly specified index, no lock if no data is found)

Console1: Querying status=3 data, returning empty data

Mysql> SELECT * from T_goods where status=3 for update; Empty Set

Console2: Querying status=3 data, returning empty data

Mysql> SELECT * from T_goods where status=3 for update; Empty Set

Mysql Pessimistic lock