MySQL database lock mechanism

Optimistic concurrency control and pessimistic concurrency control are the main methods used in concurrency control. Optimistic locking and pessimistic locking are not only used in relational databases, but also in Hibernate, Memcache and other related concepts.

Pessimistic lock: Also known as Pessimistic Concurrency control, globalization Concurrency Controller, or PCC. Pessimistic locking refers to the process of data processing, so that the data is locked, generally using the database lock mechanism to achieve. Set autocommit=0; set autocommit=0; MySQL uses autocommit mode by default, which means that when you perform an update, MySQL automatically commits the result.

//0. Start transaction begin; /begin work; /start transaction; Select status from t_goods where id=1 for update; select * from t_goods where id=1 for update; Insert into T_orders (id,goods_id) values (null,1); Update t_goods set status=2; //4. Commit transaction commit; /commit work;Copy the code

This example uses select… The for Update mode locks the data, thus enabling exclusive locking

Advantages and disadvantages of pessimistic lock: Pessimistic concurrency control (pessimistic lock) adopts the conservative strategy of “lock first and divide later”, which provides a security guarantee for data processing. However, in terms of efficiency, locking mechanism incurs additional overhead and increases the chance of deadlock.

Optimistic locking: In contrast to pessimistic locking, optimistic locking is achieved by recording the version of data. Add a version identifier to the data. When reading the data, the version identifier is read together and the version identifier is updated without updating the data once.

Advantages and disadvantages of optimistic locking: Optimistic locking considers that the probability of a transaction competing directly is very small and is locked at commit time, so there is no deadlock. However, if two transactions are reading a row of the database at the same time, then the disadvantage of optimistic locking will be found.

An exclusive locck is also called A write lock. If transaction T locks A exclusively, no other transaction can lock A. Transactions granted exclusive locks can both read and write data.

SELECT… FOR UPDATE

A share lock is also called A read lock. If transaction T adds A share lock to A, other transactions can only add the share lock to A. Transactions that are allowed to share locks can only read data, not write data.

SELECT… LOCK IN SHARE MODE;