Examples of using MySQL pessimistic locking and optimistic locking

Pessimistic Lock

Pessimistic lock, considers the data to be pessimistic. When we query the data, we add a lock. Prevent other threads from tampering until they get the lock.

For example, there is the following table. status=1 means that the order can be placed, and status=2 means that the order cannot be placed. If two users check status = 1 at the same time during the concurrent process, then logically both users can add new orders, but this will cause overselling of the product.

The following example

CREATE TABLE `goods` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) DEFAULT NULL,
 `status` tinyint(4) DEFAULT NULL,
 `version` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4
INSERT INTO demo.goods (id, name, status, version) VALUES (1, 'test', 1, 1);

session1 execution

set autocommit=0;
begin;
select *
from goods where id=1 and goods.status=1 for update;
update goods set status=2 where id=1;

session2 execution

begin;
select * from goods where id=1 for update;

At this time, session 2 is blocked because the lock is still in session 1, so the lock is always waiting. If session1 is not submitted, session2 will time out and disconnect after a certain period of time, and report

(1205, 'Lock wait timeout exceeded; try restarting transaction') error,

The specific lock waiting time can be controlled by setting the innodb_lock_wait_timeout parameter.

If the commit operation is executed in session1 at this time, session2 will obtain the query results and the lock will be given to session2.

We can also

show status like 'innodb_row_lock_%';

To further view the lock information.

Optimistic Locking

Optimistic locking is different from pessimistic locking. Optimistic locking is implemented through its own program rather than mySql itself.

Optimistic locking does not lock the query, and only checks the version number when updating.

For example, if we query the goods table and find that version is 1, then when updating this table, SQL will be

select * from goods where id=1;
update goods set status=2,version=version+1 where id=1 and version=1;

The version here is the version number at the time of query, and each change will result in version+1. If the version numbers do not match the update will not be successful.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.