mysql 锁机制

MySQL锁概述

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MySQL这3种锁的特性可大致归纳如下:

开销、加锁速度、死锁、粒度、并发性能

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

注：InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。

事务（Transaction）及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

事务（Transaction）及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性（Consistent）：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。一致性是为了保证数据的完整性。
隔离性（Isolation）：隔离性是指当多个用户并发操作数据库，比如操作同一张表，数据库为每一个用户开启的事务，不能被其他的事务所干扰或者影响，事务之间是彼此独立的。
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务处理带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

更新丢失（Lost Update）：两个事务同时更新一行数据，最后一个事务的更新会覆盖掉第一个事务的更新，从而导致第一个事务更新的数据丢失，这是由于没有加锁造成的；
脏读（Dirty Reads）：当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
不可重复读（Non-Repeatable Reads）：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
幻读（Phantom Reads）：
一、指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，并且已提交，当之前的事务再次读取该范围的记录时，查询结果发生变化，产生幻读。InnoDB存储引擎通过多版本并发控制（MVCC）解决了一部分幻读的问题。
二、幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。
参考：
理解mysql中的MVCC多版本并发控制，某些场景会出现幻读
 脏读，不可重复读，幻读是如何发生的?
mysql 幻读的详解、实例及解决办法

注：幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）；不可重复读重点在于update和delete，而幻读的重点在于insert。

数据库事务的隔离级别

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted 、Read committed 、Repeatable read 、Serializable ，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

Read UnCommitted(读未提交)
最低的隔离级别。一个事务可以读取另一个事务并未提交的更新结果。
Read Committed(读提交)
大部分数据库采用的默认隔离级别。一个事务的更新操作结果只有在该事务提交之后，另一个事务才可以的读取到同一笔数据更新后的结果(解决脏读问题)。
Repeatable Read(重复读)
mysql的默认级别。整个事务过程中，对同一笔数据的读取结果是相同的，不管其他事务是否在对共享数据进行更新，也不管更新提交与否(解决脏读、不可重复读问题)。
Serializable(序列化)
最高隔离级别。所有事务操作依次顺序执行。注意这会导致并发度下降，性能最差。通常会用其他并发级别加上相应的并发锁机制来取代它(解决脏读、不可重复读、幻读问题)。

见下图：
数据库事务的隔离级别

参考：
查询mysql事务隔离级别
 MySQL事务隔离级别的实现原理

示例

# 测试表
mysql> desc test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)      | YES  | UNI | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

脏读

/*设置隔离级别为read uncommitted;*/
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)

// 事务一
mysql> begin;  // 步骤一，开始一个事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;  // 步骤二，查询当前表的数据
Empty set (0.00 sec)

mysql> select * from test;  // 步骤六，再次查询可看到事务二未提交的数据（脏读）
+----+------+
| id | name |
+----+------+
|  1 | wm   |
+----+------+
1 row in set (0.00 sec)

mysql> select * from test;  // 步骤九，再次查询事务二回滚后的数据，发现数据已撤销
Empty set (0.00 sec)

mysql> commit；
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

// 事务二
mysql> begin;  // 步骤三，开启另一个事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test set name='wm';  // 步骤四，向test表中插入一个数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;  // 步骤五，查询插入结果
+----+------+
| id | name |
+----+------+
|  1 | wm   |
+----+------+
1 row in set (0.00 sec)

mysql> rollback;  // 步骤七，事务二回滚
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;  // 步骤八，查询回滚后的数据，已撤销
Empty set (0.00 sec)

不可重复读

1
2
3

/*设置隔离级别为read committed;*/
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

// 事务一
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  2 | wm   |
+----+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

1 2	// 事务二 insert into test set name="wm";

幻读

# 隔离级别
mysql> select @@global.tx_isolation,  @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+

# 事务一
mysql> begin;  # 第一步
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where id=100;  # 第二步
Empty set (0.00 sec)

mysql> insert into test values(104, "my"); # 第六步
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'  # (这里产生了幻读现象)

mysql> select * from test where id=100;
Empty set (0.00 sec)

# 事务二
mysql> begin;  # 第三步
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(100, "my");  # 第四步
Query OK, 1 row affected (0.00 sec)

mysql> commit;  # 第五步
Query OK, 0 rows affected (0.01 sec)

数据库锁

总的来说，InnoDB共有七种类型的锁：

共享/排它锁(Shared and Exclusive Locks)
意向锁(Intention Locks)
记录锁(Record Locks)
间隙锁(Gap Locks)
临键锁(Next-key Locks)
插入意向锁(Insert Intention Locks)
自增锁(Auto-inc Locks)

参考：https://www.cnblogs.com/volcano-liu/p/9890832.html
MySQL中锁详解（行锁、表锁、页锁、悲观锁、乐观锁等）MySQL中锁详解（行锁、表锁、页锁、悲观锁、乐观锁等）

mysql锁使用查询

查询表级锁争用情况

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

MySQL [ts_model]> show status like "table%";
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 710   |
| Table_locks_waited         | 0     |
+----------------------------+-------+

# Table_locks_immediate 指的是能够立即获得表级锁的次数
# Table_locks_waited 指的是不能立即获取表级锁而需要等待的次数

注：如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些。

查询InnoDB行锁争用情况

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

MySQL [ts_model]> 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_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过解锁:

第一种(show processlist;)，找到锁进程，kill id;

MySQL [ts_model]> show processlist;
+--------+------+---------------------+-------------------+---------+-------+----------+------------------+
| Id     | User | Host                | db                | Command | Time  | State    | Info             |
+--------+------+---------------------+-------------------+---------+-------+----------+------------------+
| 412414 | root | 43.254.54.249:37446 | ts_model          | Query   |     0 | starting | show processlist |
| 412415 | root | 43.254.54.249:37448 | ts_model          | Sleep   | 14360 |          | NULL             |
| 412425 | root | 43.254.54.249:37494 | smartant_saas_dev | Sleep   |  7130 |          | NULL             |
+--------+------+---------------------+-------------------+---------+-------+----------+------------------+

第二种(mysql>UNLOCK TABLES;)

扩展

MySQL存储引擎

查看MySQL的存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

查看MySQL当前默认的存储引擎

mysql> show variables like "%storage_engine%";
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)

查看MySQL数据表的存储引擎

mysql> show create table test\G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=10004 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

MySQL开启事务

MySql事务开始begin和start transcation的区别

事务的开始
begin或 start transaction 都是显式开启一个事务；
事务的提交
commit 或 commit work 都是等价的；
事务回滚
rollback 或 rollback work 也是等价的

mysql中set autocommit=0与start transaction区别

set autocommit=0指事务非自动提交，自此句执行以后，每个SQL语句或者语句块所在的事务都需要显示”commit”才能提交事务。

1、不管autocommit是1还是0，START TRANSACTION 后，只有当commit数据才会生效，ROLLBACK后就会回滚。
2、当autocommit 为 0 时，不管有没有START TRANSACTION。只有当commit数据才会生效，ROLLBACK后就会回滚。
3、如果autocommit 为1 ，并且没有START TRANSACTION。调用ROLLBACK是没有用的。即便设置了SAVEPOINT

查询mysql事务隔离级别

查看当前会话隔离级别

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

查看系统当前隔离级别

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

设置当前会话隔离级别

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

/*
* 可选参数
* read uncommitted
* read committed
* repeatable read
* serializable
*/

设置系统当前隔离级别

mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.01 sec)

/*
* 可选参数
* read uncommitted
* read committed
* repeatable read
* serializable
*/

MySQL关于table_lock_wait和table_lock_immediate_MySQL

参考：https://www.php.cn/mysql-tutorials-69154.html

for update的作用和用法

参考：https://www.cnblogs.com/banma/p/11797560.html