The difference between optimistic lock and pessimistic lock

the difference between optimistic lock and pessimistic lock

Reference:
http://www.hollischuang.com/archives/934
http://m.blog.csdn.net/article/details?id=26004335 optimistic Lock

In the relational database management system, optimistic concurrency control (also known as "optimistic lock", optimistic concurrency control, abbreviated "OCC") is a concurrency controlling method. It assumes that concurrent transactions of multiple users do not interact with each other during processing, and that each transaction can process the part of the data that affects them without creating a lock. Before submitting the data update, each transaction checks to see if there are other transactions and changes to the data after the transaction has been read. If other transactions are updated, the transaction being committed is rolled back. Optimistic transaction control was first proposed by Kongxiang Heavy (H.t.kung) professor. phase of optimistic concurrency control

Optimistic concurrency-controlled transactions include the following phases:
1. READ: Transaction reads data into the cache, when the system assigns a timestamp to the transaction.
2. Verify: After the execution of the transaction, commit. At this point, all transactions are synchronously validated, and if the data read by the transaction is modified by another transaction after reading, a conflict occurs and the transaction is interrupted and rolled back.
3. Write: After the verification phase, the updated data is written to the database.

Optimistic concurrency control is mostly used in environments where data contention is small and less conflict, where the cost of occasionally rolling back transactions is lower than the cost of locking data when reading data, and thus can achieve higher throughput than other concurrent control methods.

In contrast to pessimistic locks, optimistic locks do not use the lock mechanism provided by the database when processing the database. The general way to implement optimistic locks is to record data versions.

The data version, which is an additional version identifier for the data. When reading the data, the value of the version ID is read together, the data is updated every time, and the version identity is updated. When we submit the update, we judge that the current version information of the corresponding record in the database table is compared with the version ID that was first taken out, if the current version number of the database table is equal to the version ID value that was first taken out, it is updated, otherwise it is considered to be out-of-date data.

There are two ways to implement a data version, the first is to use a version number, and the second is to use a timestamp. Use version number to implement optimistic lock

When you use the version number, you can specify a version number when the data is initialized, and each update operation on the data performs a +1 operation on the version number. and determine if the current version number is the latest version number of the data. use version number to implement optimistic lock

When you use the version number, you can specify a version number when the data is initialized, and each update operation on the data performs a +1 operation on the version number. and determine if the current version number is the latest version number of the data.

1. Query out the Product information
Select (status,status,version) from T_goods where Id=#{id}
2. Generate orders based on product information
3. Modify the status of the item to 2
Update T_goods
set status=2,version=version+1
where Id=#{id} and version=#{version};

Advantages and Disadvantages

Optimistic concurrency control believes that the probability of data competition between transactions (race) is relatively small, so do it as directly as possible until committed to lock, so no locks and deadlocks are generated. However, if you do this directly, it is possible to encounter unpredictable results, such as two transactions have read a row of the database, after the modification to write back to the database, then encountered a problem. Pessimistic lock

In the relational database management system, pessimistic concurrency control (also known as "pessimistic lock", pessimistic concurrency control, abbreviated "PCC") is a concurrency controlling method. It can prevent a transaction from modifying data in a way that affects other users. If a transaction performs an operation that reads a lock on a row of data, the other transaction can perform the operation that conflicts with the lock only if the transaction releases the lock.

Pessimistic concurrency control is primarily used for data contention environments, and the cost of using locks to protect data when concurrent conflicts occur is lower than the cost of rolling back transactions. Use

Pessimistic lock used in MySQL InnoDB

To use pessimistic locks, we must turn off the automatic submission property of the MySQL database because mysql defaults to the autocommit mode , which means that MySQL will immediately submit the results when you perform an update operation. Set autocommit=0;

#0. Start a transaction
begin;/begin Work;/start transaction; (You can choose one of the three)
#1. Query for product information
Select status from T_goods where id=1 for update;
#2. Generate order
INSERT into t_orders (id,goods_id) VALUES (null,1) based on commodity information;
#3. Modify commodity status is 2
update t_goods set status=2;
#4. Commit;/commit work for the submission of services
;

In the query above, we used the select...for update method, so that the pessimistic lock was implemented by opening the exclusive lock. At this point in the T_goods table, the data ID 1 is locked, and other transactions must wait for this transaction to be committed before they can be executed. This allows us to ensure that the current data is not modified by other transactions.

As we mentioned above, using Select...for Update will lock the data, but we need to pay attention to some level of lock,MySQL innodb default row-level lock . row-level locks are indexed, and if a SQL statement does not use an index, row-level locks are used, and table-level locks are used to lock the entire table, which requires attention. Advantages and disadvantages

Pessimistic concurrency control is actually a conservative strategy of "lock and Access first", which provides a guarantee for the security of data processing. But in terms of efficiency, the process of locking the database creates additional overhead, there is also an increase in the chance of deadlocks, and there is no need to use locks in read-only transactions because there is no conflict, and it is not necessary to do so, which can only increase the system load, but also reduce parallelism, and if a transaction locks a row of data, Other transactions must wait for the transaction to be processed before they can handle the summary of rows

Optimistic locks apply to the multiple-read application type, which can improve throughput, as if the database provides optimistic locks that are actually provided like write_condition wit. Conversely, if a conflict occurs frequently, the top application will continue to retry, thus reducing performance, so it is more appropriate to use pessimistic locks in this case.