search
HomeDatabaseMysql TutorialWhat does Mysql row-level lock mean?

The first thing we need to know is that mysql locks are implemented by a specific storage engine. Therefore, there are differences in the lock implementation mechanisms of MySQL's default engine MyISAM and the third-party plug-in engine InnoDB.

What does Mysql row-level lock mean?

Mysql has three levels of locking: table-level locking, page-level locking, row-level locking

1. Definition

Every time The locking mechanism that locks a row of data is row-level locking (row-level). Row-level locking is not a locking method implemented by MySQL itself, but is implemented by other storage engines

2. Advantages and Disadvantages

1. Advantages

  • Due to the small lock granularity, the contention rate is low and concurrency is high.

2. Disadvantages

  • It is complicated to implement and expensive.

  • Locking is slow and deadlock is prone to occur

3. Support storage engine

  • Use row-level locking The main ones are the InnoDB storage engine and the MySQL distributed storage engine NDBCluster

4. Row-level lock types

InnoDB’s row-level locks are also divided into two types: shared locks and exclusive locks. In order to allow row-level locks and table-level locks to coexist during the implementation of the locking mechanism, InnoDB also uses the concept of intention locks (table-level locks), and there are intention shared locks and intention exclusive locks. Two kinds.

The function of intention lock is that when a transaction needs to obtain a resource lock, if the resource it needs is already occupied by an exclusive lock, the transaction can add a suitable row to the table that needs to lock the row. Intention lock. If you need a shared lock, add an intention shared lock on the table. If you need to add an exclusive lock on a certain row (or some rows), first add an intention exclusive lock on the table.

Multiple intention shared locks can exist at the same time, but only one intention exclusive lock can exist at the same time. Therefore, it can be said that InnoDB's locking mode can actually be divided into four types: shared lock (S), exclusive lock (X), intention shared lock (IS) and intention exclusive lock (IX)

Lock mode Compatibility:

What does Mysql row-level lock mean?

5. Row-level locking implementation method

InnoDB row lock is implemented by locking the index entry on the index. Therefore, InnoDB uses row-level locks only when data is retrieved through index conditions. Otherwise, InnoDB will use table locks. Other notes:

  • When querying without index conditions, InnoDB uses table locks instead of row locks.

  • Since MySQL's row lock is a lock for the index, not for the record, even if you access records in different rows, if the same index key is used, it will A lock conflict occurs.

  • When a table has multiple indexes, different transactions can use different indexes to lock different rows. In addition, whether using primary key index, unique index or ordinary index, InnoDB will Use row locks to lock data.

  • Even if an index field is used in the condition, whether to use the index to retrieve data is determined by MySQL by judging the cost of different execution plans. If MySQL considers the full table scan efficiency Higher, for example, for some very small tables, it will not use indexes. In this case, InnoDB will use table locks instead of row locks. Therefore, when analyzing lock conflicts, don't forget to check the SQL execution plan to confirm whether the index is actually used.

Implicit locking:

  • InnoDB automatically adds intention locks.

  • For UPDATE, DELETE and INSERT statements, InnoDB will automatically add exclusive locks (X) to the involved data sets;

  • For ordinary SELECT statements , InnoDB will not add any locks;

Display locks:

  • Shared lock (S): SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

  • Exclusive lock (X): SELECT * FROM table_name WHERE ... FOR UPDATE

Use SELECT ... IN SHARE MODE Obtaining a shared lock is mainly used to confirm whether a certain row of records exists when data dependencies are required, and to ensure that no one performs UPDATE or DELETE operations on this record.

However, if the current transaction also needs to update the record, it is likely to cause a deadlock. For applications that need to update the row record after locking it, the SELECT... FOR UPDATE method should be used to obtain an exclusive lock.

How to lock the table in InnoDB:

When using LOCK TABLES to lock the InnoDB table, be careful to set AUTOCOMMIT to 0, otherwise MySQL will not lock the table; before the transaction ends , do not use UNLOCK TABLES to release table locks, because UNLOCK TABLES will implicitly commit the transaction; COMMIT or ROLLBACK cannot release table-level locks added with LOCK TABLES, and UNLOCK TABLES must be used to release the table lock.

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

Since we all use table locks, why not choose the MyISAM engine!

六、间隙锁(Next-Key锁)

1. 间隙锁定义:

Innodb的锁定规则是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。 Innodb的这种锁定实现方式被称为“ NEXT-KEY locking” (间隙锁),因为Query执行过程中通过范围查找的话,它会锁定整个范围内所有的索引键值,即使这个键值并不存在。

例:假如emp表中只有101条记录,其empid的值分别是 1,2,…,100,101,下面的SQL:

mysql> select * from emp where empid > 100 for update;

是一个范围条件的检索,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁。

2. 间隙锁的缺点:

  • 间隙锁有一个比较致命的弱点,就是当锁定一个范围键值之后,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

  • 当Query无法利用索引的时候, Innodb会放弃使用行级别锁定而改用表级别的锁定,造成并发性能的降低;

  • 当Quuery使用的索引并不包含所有过滤条件的时候,数据检索使用到的索引键所指向的数据可能有部分并不属于该Query的结果集的行列,但是也会被锁定,因为间隙锁锁定的是一个范围,而不是具体的索引键;

  • 当Query在使用索引定位数据的时候,如果使用的索引键一样但访问的数据行不同的时候(索引只是过滤条件的一部分),一样会被锁定

3 . 间隙锁的作用:

  • 防止幻读,以满足相关隔离级别的要求。

  • 为了数据恢复和复制的需要。

4. 注意

  • 在实际应用开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。

  • InnoDB除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁。

七、查看行级锁争用情况

执行SQL:mysql> show status like 'InnoDB_row_lock%';

mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+| Variable_name                 | Value |
+-------------------------------+-------+| InnoDB_row_lock_current_waits | 0     |
| InnoDB_row_lock_time          | 0     |
| InnoDB_row_lock_time_avg      | 0     |
| InnoDB_row_lock_time_max      | 0     |
| InnoDB_row_lock_waits         | 0     |+-------------------------------+-------+

如果发现锁争用比较严重,还可以通过设置InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等,并分析锁争用的原因。如:

设置监视器:mysql> create table InnoDB_monitor(a INT) engine=InnoDB;

查看:mysql> show engine InnoDB status;

停止查看:mysql> drop table InnoDB_monitor;

具体参考:InnoDB Monitor

八、死锁

什么是死锁:你等我释放锁,我等你释放锁就会形成死锁。

如何发现死锁: 在InnoDB的事务管理和锁定机制中,有专门检测死锁的机制,会在系统中产生死锁之后的很短时间内就检测到该死锁的存在

解决办法:

  • 回滚较小的那个事务

  • 在REPEATABLE-READ隔离级别下,如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁,在没有符合该条件记录情况下,两个线程都会加锁成功。程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成READ COMMITTED,就可避免问题。

判断事务大小:事务各自插入、更新或者删除的数据量

注意:

  • 当产生死锁的场景中涉及到不止InnoDB存储引擎的时候,InnoDB是没办法检测到该死锁的,这时候就只能通过锁定超时限制参数InnoDB_lock_wait_timeout来解决。

九、优化行级锁定

InnoDB存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些,但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候,InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是,InnoDB的行级锁定同样也有其脆弱的一面,当我们使用不当的时候,可能会让InnoDB的整体性能表现不仅不能比MyISAM高,甚至可能会更差。

(1)要想合理利用InnoDB的行级锁定,做到扬长避短,我们必须做好以下工作:

  • Let all data retrieval be completed through the index as much as possible, so as to prevent InnoDB from being upgraded to table-level locking because it cannot be locked through the index key;

  • Design the index reasonably so that InnoDB can be as accurate as possible when locking the index key, narrow the locking scope as much as possible, and avoid unnecessary locking that affects the execution of other Query;

  • Reduce range-based data retrieval filter conditions as much as possible to avoid locking records that should not be locked due to the negative impact of gap locks;

  • Try to control the size of transactions and reduce locks The amount of resources and locking time length;

  • If the business environment permits, try to use a lower level of transaction isolation to reduce the additional costs of MySQL implementing the transaction isolation level. cost.

(2) Due to InnoDB’s row-level locking and transactional nature, deadlocks will definitely occur. Here are some commonly used tips to reduce the probability of deadlocks:

  • In similar business modules, try to access in the same access sequence to prevent deadlock;

  • In the same transaction, try to do as much as possible to all resources required for one lock to reduce the probability of deadlock;

  • For business parts that are very prone to deadlock, you can try to upgrade the locking granularity and use table-level locking to Reduce the probability of deadlock.

Related recommendations: "mysql tutorial"

The above is the detailed content of What does Mysql row-level lock mean?. For more information, please follow other related articles on the PHP Chinese website!

Statement
This article is reproduced at:CSDN. If there is any infringement, please contact admin@php.cn delete
图文详解mysql架构原理图文详解mysql架构原理May 17, 2022 pm 05:54 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于架构原理的相关内容,MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层,下面一起来看一下,希望对大家有帮助。

mysql怎么替换换行符mysql怎么替换换行符Apr 18, 2022 pm 03:14 PM

在mysql中,可以利用char()和REPLACE()函数来替换换行符;REPLACE()函数可以用新字符串替换列中的换行符,而换行符可使用“char(13)”来表示,语法为“replace(字段名,char(13),'新字符串') ”。

mysql的msi与zip版本有什么区别mysql的msi与zip版本有什么区别May 16, 2022 pm 04:33 PM

mysql的msi与zip版本的区别:1、zip包含的安装程序是一种主动安装,而msi包含的是被installer所用的安装文件以提交请求的方式安装;2、zip是一种数据压缩和文档存储的文件格式,msi是微软格式的安装包。

mysql怎么去掉第一个字符mysql怎么去掉第一个字符May 19, 2022 am 10:21 AM

方法:1、利用right函数,语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”;2、利用substring函数,语法为“select substring(指定字段,2)..”。

mysql怎么将varchar转换为int类型mysql怎么将varchar转换为int类型May 12, 2022 pm 04:51 PM

转换方法:1、利用cast函数,语法“select * from 表名 order by cast(字段名 as SIGNED)”;2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。

MySQL复制技术之异步复制和半同步复制MySQL复制技术之异步复制和半同步复制Apr 25, 2022 pm 07:21 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于MySQL复制技术的相关问题,包括了异步复制、半同步复制等等内容,下面一起来看一下,希望对大家有帮助。

带你把MySQL索引吃透了带你把MySQL索引吃透了Apr 22, 2022 am 11:48 AM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了mysql高级篇的一些问题,包括了索引是什么、索引底层实现等等问题,下面一起来看一下,希望对大家有帮助。

mysql怎么判断是否是数字类型mysql怎么判断是否是数字类型May 16, 2022 am 10:09 AM

在mysql中,可以利用REGEXP运算符判断数据是否是数字类型,语法为“String REGEXP '[^0-9.]'”;该运算符是正则表达式的缩写,若数据字符中含有数字时,返回的结果是true,反之返回的结果是false。

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!