How to implement row locks, table locks and deadlocks in Mysql lock mechanism

1. What is a Mysql lock? What types of locks are there?

Lock definition:
The same resource can only be accessed by one thread at the same time
In the database, except for traditional computing resources (such as CPU, In addition to contention for I/O, etc.), data is also a resource shared by many users. How to ensure the consistency and effectiveness of concurrent access to data is a problem that all databases must solve. Lock conflicts are also an important factor affecting the performance of concurrent access to databases.

The most commonly used optimistic lock is the version record of data to reflect version, which is actually an identifier.

For example:update test set a=a-1 where id=100 and a> 0; The corresponding version is the a field, and it does not necessarily require that there be a field called version. There is this field, and when this condition is met, it will be triggered

Lock classification:
From the data Type classification of operations (read or write)
Read lock (shared lock): For the same data, multiple read operations can be performed at the same time without affecting each other.
Write lock (exclusive lock): Before the current write operation is completed, it will block other write locks and read locks.

From the granularity of data operations
Table-level lock: Table-level lock is the lock with the largest granularity in MySQL, which means that the entire table of the current operation is added. Lock (MyISAM engine defaults to table-level locks and only supports table-level locks). For example, if you update a piece of data in a 100,000 table, other transactions will be excluded before this update commits the transaction, and the granularity is very large.
Row-level lock: Row-level lock is the most granular lock in Mysql, which means that only the row currently operated is locked (It is based on the index, so once a certain lock is locked If the operation does not use an index, then the lock will degenerate into a table lock)
Page-level lock: Page-level lock is a lock in MySQL with a locking granularity between row-level locks and table-level locks. It is a one-time lock. A group of adjacent records

Distribution from a concurrency perspective--In fact, optimistic locking and pessimistic locking are just ideas
Pessimistic locking: For data that is blocked by the outside world (including this system) It takes a conservative (pessimistic) attitude towards modifications to other current transactions, as well as transactions from external systems, and therefore keeps the data in a locked state during the entire data processing process.
Optimistic lock: Optimistic lock assumes that data will generally not cause conflicts, so when the data is submitted for update, the conflict of the data will be officially detected. If a conflict is found, an error message will be returned. Retry the business

Other locks:
Gap lock: In conditional queries, such as: where id>100, InnoDB will give the index of existing data records that meet the conditions Item locking; for records whose key values ​​are within the condition range but do not exist, it is called "gap". The purpose of the gap is to prevent phantom reading
Intention lock: Intention lock is divided into intention shared lock (IS) And intention exclusive lock (IX), the purpose of intention lock is to indicate that a transaction is or will lock a row in a table

2. The difference between row lock and table lock

Table-level lock is the lock with the largest locking granularity in MySQL, which means locking the entire table of the current operation. It is simple to implement. The most commonly used MYISAM and INNODB support table-level locking.
Features: Low overhead, fast locking; no deadlocks; large locking granularity, the highest probability of lock conflicts, and the lowest concurrency.

Row-level lock is the most fine-grained lock in Mysql, which means that only the row of the current operation is locked. Row-level locks can greatly reduce conflicts in database operations. Its locking granularity is the smallest, but the locking overhead is also the largest.
Features: high overhead, slow locking; deadlocks may occur; the locking granularity is the smallest, the probability of lock conflicts is the lowest, and the concurrency is the highest
Usage: InnoDB row lock is to lock the index item on the index To achieve this, InnoDB will use row-level locks only when retrieving data through index conditions. Otherwise, InnoDB will use table locks

In the following update statement, b is a general field and not an index column. , then the row-level lock will be changed to table-level lock at this time.

update from test set a=100 where b='100';

Now let’s take a practical example to see how innnodb uses row locks.

Data in the current table:

First open two session windows, and then set the mysql transaction level to the non-commit level:

Session one window:

Session two window:

其中会话2的update一直都在Running中，一直到超时结束，或者会话1提交事务后才会Running结束。

可以通过show VARIABLES like "%innodb_lock_wait_timeout%" 查询当前mysql设置的锁超时时间,默认是50秒。

可以通过set innodb_lock_wait_timeout = 60; 设置锁的超时时间。

只有在第一个会话提交后，第二个会话的更新语句才能成功执行。这代表了innodb用了锁。

那怎么确定是用了行锁呢？

总结:会话一更新id=125的时候，给这条数据add lock了，那么在会话2中再次更新id=125的时候，这条数据是locked中的。这个lock加的是id=125这条记录。证明默认情况下id=125这条记录会加上行锁，除了这条记录之外的其它记录都可以成功地操作。

三、InnoDB死锁概念和死锁案例

发生死锁是因为多个事务相互持有和请求锁，并形成了一个循环依赖关系。多个事务同时锁定同一个资源时，也会产生死锁。在一个事务系统中，死锁是确切存在并且是不能完全避免的。

自动检测事务死锁并回滚一个事务，同时返回错误信息的功能由InnoDB自动实现。它根据某种机制来选择那个最简单（代价最小）的事务来进行回滚

死锁场景一之select for update:

产生场景：两个transaction都有两个select for update，transaction a先锁记录1，再锁记录2；而transaction b先锁记录2，再锁记录1

写锁：for update，读锁：for my share mode show engine innodb status

验证下死锁的场景:

第一步更新会话一:

start TRANSACTION;
select * from wnn_test where a=199 for update;

第二步更新会话二:

start TRANSACTION;
select * from wnn_test where a=101 for update;

第三步更新会话一:

select * from wnn_test where a=101 for update;

第四步更新会话二;

select * from wnn_test where a=199 for update;

在更新到第三步和第四步的时候，已经发生了死锁。

来看下执行的日志:

show engine innodb status;最后一个锁的时间，锁的表，引起锁的语句。其中session1被锁 14秒(ACTIVE 14)，session 2被锁了10秒(Active 10)

死锁场景二之两个update

产生场景：两个transaction都有两个update，transaction a先更新记录1，再更新记录2；而transaction b先更新记录2，再更新记录1

 产生日志：

 注意：仔细查看上面2个例子可以发现一个现象，当2条资源锁住后，再执行第三个会执行成功，但是第四个会提示死锁。在mysql5.7中，执行第三个的时候就会一直在Running状态了，本博文使用的是mysql8.0 ，其中 有这个参数 innodb_deadlock_detect 可以用于控制 InnoDB 是否执行死锁检测，当启用了死锁检测时（默认设置），InnoDB 自动执行事务的死锁检测，并且回滚一个或多个事务以解决死锁。InnoDB 尝试回滚更小的事务，事务的大小由它所插入、更新或者删除的数据行数决定。

 那么这个innodb_deadlock_detect参数，到底要不要启用呢？

对于高并发的系统，当大量线程等待同一个锁时，死锁检测可能会导致性能的下降。此时，如果禁用死锁检测，而改为依靠参数 innodb_lock_wait_timeout 执行发生死锁时的事务回滚可能会更加高效。
通常来说，应该启用死锁检测，并且在应用程序中尽量避免产生死锁，同时对死锁进行相应的处理，例如重新开始事务。

Only when you confirm that deadlock detection affects the performance of the system and disabling deadlock detection will not have negative effects, you can try turning off the innodb_deadlock_detect option. In addition, If InnoDB deadlock detection is disabled, the value of the parameter innodb_lock_wait_timeout needs to be adjusted to meet actual needs.

4. How to avoid deadlock during program development

The essence of locks is that resources compete with each other and wait for each other. It is often the case that two (or more) Sessions are locked. The order is inconsistent

How to effectively avoid:

In the program, when operating multiple tables, try to access them in the same order (avoid Forming a waiting loop)

When operating single table data in batches, sort the data first (to avoid forming a waiting loop) A thread id: 1, 10, 20 are added in order Lock B thread id: 20,10,1 This is easy to lock.

If possible, turn large transactions into small transactions, or even not open the transaction select for update==>insert==>update = insert into update on duplicate key

In order to avoid table locks, it is recommended to use indexes to access data as much as possible and avoid operations without where conditions, because using indexes can record row locks without causing table locks

Use equal value query instead of range query to query data, hit records, and avoid the impact of gap lock on concurrency 1, 10, 20 Equivalent where id in (1,10,20) Range query id>1 and id

Avoid running multiple scripts that read and write the same table at the same time. Pay special attention to statements that lock and operate large amounts of data; we often have some timing scripts , to avoid them running at the same point in time

The above is the detailed content of How to implement row locks, table locks and deadlocks in Mysql lock mechanism. For more information, please follow other related articles on the PHP Chinese website!