当前位置 : 首页 » 文章分类 :  开发  »  MySQL-InnoDB 存储引擎

MySQL-InnoDB 存储引擎

MySQL InnoDB 存储引擎、事务相关笔记

Chapter 14 The InnoDB Storage Engine
https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html


InnoDB 启动参数与系统变量

14.15 InnoDB Startup Options and System Variables
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

MySQL 全局系统变量在 5.1.7 节
5.1.7 Server System Variables
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html


innodb_lock_wait_timeout(50秒)

show VARIABLES like '%innodb_lock_wait_timeout%';
该参数控制 Innodb 行锁等待的超时时间,单位为秒,该实例该参数的默认值为 50(秒)

默认50秒, 我们生产系统设置成了 120 秒 set innodb_lock_wait_timeout=120;

事务超过此时间会报错
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout


innodb_buffer_pool_size(128MB)

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

启动参数:--innodb-buffer-pool-size=#
默认值:134217728 (128M)
最小值:5242880
64 位系统中可配置的最大值为 2^64-1
32 位系统中可配置的最大值为 2^32-1
可动态调整:是

系统变量 innodb_buffer_pool_size 指定 InnoDB 引擎的 buffer pool 大小,单位字节。
InnoDB 使用 buffer pool 内存来缓存表和索引数据。

innodb_buffer_pool_size 大于等于 1GB 时,将 buffer pool 划分为多个实例(Buffer Pool Instance)可进一步提高性能,实例个数用系统变量 innodb_buffer_pool_instances 设置。
每个 Buffer Pool Instance 都有自己的锁,信号量,物理块(Buffer chunks)以及逻辑链表(List)。即各个 instance 之间没有竞争关系,可以并发读取与写入。
所有 instance 的物理块(Buffer chunks)在数据库启动的时候被分配,直到数据库关闭内存才予以释放。MySQL 使用 mmap 分配 Buffer Pool, 但是都是虚拟内存,在 top 命令中占用 VIRT 这一列,而不是 RES 这一列,只有相应的内存被真正使用到了,才会被统计到 RES 中,从而提高内存使用率。这就是为什么常常看到 MySQL 一启动就被分配了很多的 VIRT, 而 RES 却是慢慢涨上来的原因。

做数据库压测时启动了一个 innodb_buffer_pool_size=50G 的 MySQL,刚起动时 top 看内存占用如下:

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
98063 mysql     20   0   55.1g  15.5g  17096 S   0.3  2.0   0:26.35 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

用 sysbench oltp_read_only 压几次后,看到 MySQL 的 RES 已经有 40G 了。

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
98063 mysql     20   0   63.1g  40.9g  17696 S   0.3  5.4 125:32.95 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

InnoDB buffer pool 的扩缩容是以 块(chunks) 为单位进行的,块的大小由变量 innodb_buffer_pool_chunk_size 来定义,默认值为 128 MB.

innodb_buffer_pool_size 必须是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的整数倍。如果将 innodb_buffer_pool_size 改为一个非 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 整数倍的值,buffer pool 大小也会自动调整 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的整数倍。

innodb_buffer_pool_size 的大小可动态配置而无需重启 MySQL 服务器。Innodb_buffer_pool_resize_status 状态变量可反映 buffer pool 在线扩缩容的状态。

5.7 中默认 134217728 字节 即 134217728/1024/1024=128M
可在 my.cnf 中永久配置 innodb_buffer_pool_size=8G 后重启

在 Mysql 5.7 版本之前,调整 innodb_buffer_pool_size 大小必须在 my.cnf 配置里修改,然后重启 mysql 进程才可以生效。
如今到了 Mysql5.7 版本,就可以直接动态调整这个参数,方便了很多。

14.8.3.1 Configuring InnoDB Buffer Pool Size
https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html

[玩转MySQL之十]InnoDB Buffer Pool详解
https://zhuanlan.zhihu.com/p/65811829


MySQL InnoDB 数据预热

MySQL 的 InnoDB 引擎有 InnoDB Buffer Pool, 系统变量 innodb_buffer_pool_size 设置 buffer pool 大小,值越大,可以放到内存的数据越多,而大多数的项目都会有热点数据的存在,当热点数据经过 LRU 算法进入到 buffer pool 之后,读磁盘的次数减少,读的都是内存,速度是最快的。

数据库重启后 buffer pool 中是空的,热点数据被清空,此时 MySQL 就只能从磁盘中读取数据到内存中,所以刚启动后一段时间内性能较差。Buffer Pool中数据从无到业务频繁使用热数据的过程称之为预热。让应用系统自身慢慢通过 SQL 给 InnoDB Buffer Pool 预热成本很高,如果遇到高峰期极有可能带来一场性能灾难,业务卡顿不能顺利运营。所以,需要人工触发 buffer pool 的预热。

MySQL 5.6 引入了数据预热机制。
innodb_buffer_pool_dump_at_shutdown 和 innodb_buffer_pool_load_at_startup 两个参数控制了预热,不过默认都是关闭的,需要开启。MySQL 5.7 则是默认开启。

MySQL 5.7 数据预热
在 MySQL 5.7 版本中引入将 Innodb Buffer 中数据备份和回复的新特性,具体原理是将 Buffer pool 中 LRU 链表上存放的 spaceid 和 page id 存储到文件中,在数据库恢复时,按照该文件中存放的 spaceid 和 page id 数据对应的页面加载到 buffer pool 中,快速预热内存,提升查询性能。

相关参数:
innodb_buffer_pool_dump_now : 立刻做一次buffer pool LRU dump
innodb_buffer_pool_dump_at_shutdown : 在正常关闭实例时做一次转储
innodb_buffer_pool_load_at_startup : 在启动实例时读入转储文件中记录的Page
innodb_buffer_pool_load_now : 立即做一次转储文件读入
innodb_buffer_pool_load_abort:立刻中断LOAD操作
innodb_buffer_pool_dump_pct : 表示转储每个Buffer pool instance的LRU上最热的page的百分比。通过设置该参数可以减少转储的page数。

参数 innodb_buffer_pool_dump_pct 在 MySQL 5.7.2 版本中引入,在 MySQL 5.7.6 及之前版本中默认值为100,而在MySQL 5.7.7及之后版本中默认值为25

在MySQL 5.6.21和MySQL 5.7.5之前的版本中,由于使用同步方式读取page到Buffer pool中,效率较低,后期版本采用异步方式加载,能明显提升加载速度。


innodb_buffer_pool_chunk_size(128MB)

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_chunk_size

启动参数:--innodb-buffer-pool-chunk-size=#
默认值:134217728 (128MB)
最小值:1048576
最大值:innodb_buffer_pool_size / innodb_buffer_pool_instances

InnoDB buffer pool 的扩缩容是以 块(chunks) 为单位进行的。
变量 innodb_buffer_pool_chunk_size 定义了 InnoDB buffer pool 扩缩容操作时 块(chunks) 的大小。
innodb_buffer_pool_chunk_size 可以以 1MB(1048576 bytes) 为单位增减配置。

如果 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 大于 innodb_buffer_pool_size, InnoDB buffer pool 初始化时会将 innodb_buffer_pool_chunk_size 设为 innodb_buffer_pool_size / innodb_buffer_pool_instances

innodb_buffer_pool_size 必须等于 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 或是其整数倍。如果修改 innodb_buffer_pool_chunk_size, innodb_buffer_pool_size 会自动调整为 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的整数倍。

所以调整 innodb_buffer_pool_chunk_size 时务必小心,因为这可能引起 innodb_buffer_pool_size 的变化。


innodb_buffer_pool_instances(8)

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances

启动参数:--innodb-buffer-pool-instances=#
默认值:8(当 innodb_buffer_pool_size < 1GB 时默认值为 1)
最小值:1
最大值:64

InnoDB buffer pool 实例个数。
对于大于 1GB 的 buffer pool 来说,将其划分为不同实例可提高并发。
缓存页(page) 进行存取时,会根据哈希算法先映射到具体的 buffer 实例, 来实现不同 buffer 实例之间的隔离。

只有当 innodb_buffer_pool_size 大于等于 1GB 时,这个选项才起作用。
尽量合理的设置 innodb_buffer_pool_instancesinnodb_buffer_pool_size 的大小,使得每个 buffer 实例的大小都大于 1GB.

除 Windows 平台外,当 innodb_buffer_pool_size 大于等于 1GB 时,默认值为 8, 否则默认值为 1.


innodb_page_size(16KB) 页大小

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_size

启动参数:--innodb-page-size=#
默认值:16384
可取值: 4096 8192 16384 32768 65536

变量 innodb_page_size 指定 InnoDB tablespaces 的页大小,默认单位是 bytes.
可以用 bytes 或 KB 来指定,例如 16KB 可表示为 16384, 16KB, 16k.
innodb_page_size 必须在初始化 MySQL 实例之前配置,并且 MySQL 启动后无法再变。

MySQL 5.5 及之前,InnoDB 页大小是固定的 16KB.
从 MySQL 5.6 开始,可通过 innodb_page_size 变量将页大小配置为 4KB, 8KB 或 16KB
从 MySQL 5.7.6 开始,支持配置为 32KB 或 64 KB.


innodb_max_dirty_pages_pct(75) 最大脏页百分比

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct

命令行参数:--innodb-max-dirty-pages-pct=#
默认值:75

InnoDB buffer pool 的最大脏页百分比,默认值 75%.
InnoDB 会尝试 flush 数据以保证 buffer pool 中的脏页比例不超过 innodb_max_dirty_pages_pct


innodb_max_dirty_pages_pct_lwm(0) 脏页低水位

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct_lwm

命令行参数:--innodb-max-dirty-pages-pct-lwm=#
默认值:0

InnoDB buffer pool 中脏页的 低水位(low water mark) 百分比阈值,超过此阈值时会开启 预flush 以便控制脏页比例。
默认值 0 禁用 预flush 功能。
innodb_max_dirty_pages_pct_lwm 必须低于 innodb_max_dirty_pages_pct


innodb_flush_neighbors(1) 脏页临近刷新

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_neighbors

命令行参数:--innodb-flush-neighbors=#
默认值:1
可配置的值:0, 1, 2

将 buffer pool 中的脏页 flush 到磁盘时,时是否将同一 tablespace 中的其他脏页也刷入磁盘。
flush 指的是将 buffer pool 中的 脏页(dirty page) 写入磁盘上的数据文件。

设为 0 表示禁用此功能,相邻的脏页不会被同时刷入磁盘。
默认值 1 会将同一 tablespace 中连续的相邻脏页刷入磁盘。
设为 2 时会将同一 tablespace 中的所有脏页同时刷入磁盘。

如果数据是存储在传统的 HDD 机械硬盘上,相比于多次 flush 单独的脏页,同时 flush 相邻的脏页可大大减少 I/O 开销,因为会减少磁盘寻道时间。
如果数据是存储在 SSD 固态硬盘上,磁盘寻道时间不是主要 I/O 开销,可关闭此配置项。


MySQL默认事务隔离级别

14.7.2.1 Transaction Isolation Levels
https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

MySQL使用可重复读作为默认隔离级别的原因
https://www.cnblogs.com/vinchen/archive/2012/11/19/2777919.html

MySQL一致性读分析

[MySQL] 一致性读分析
https://www.cnblogs.com/renolei/p/5910060.html


show engine innodb status 查看innodb死锁日志

show engine innodb status;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-11-05 19:07:44 2b6bcb03f700
*** (1) TRANSACTION:
TRANSACTION 67059742790, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 462 lock struct(s), heap size 63016, 4 row lock(s)
MySQL thread id 39706604, OS thread handle 0x2b6ba0f91700, query id 168182824647 10.128.18.185 uds_prod_w updating
UPDATE relation_table SET relation = 0 WHERE account1_id =55735549 AND account2_id = 1800241143
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 536 page no 65409 n bits 264 index `PRIMARY` of table `relation_table` trx table locks 1 total table locks 2  trx id 67059742790 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0
*** (2) TRANSACTION:
TRANSACTION 67059742791, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
641 lock struct(s), heap size 95784, 3 row lock(s)
MySQL thread id 39707153, OS thread handle 0x2b6bcb03f700, query id 168182824649 10.128.19.96 uds_prod_w updating
UPDATE relation_table SET relation = 0 WHERE account1_id =55735549 AND account2_id = 126475
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 536 page no 65409 n bits 264 index `PRIMARY` of table `relation_table` trx table locks 1 total table locks 2  trx id 67059742791 lock_mode X locks rec but not gap lock hold time 0 wait time before grant 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 536 page no 29223 n bits 792 index `account1_id` of table `relation_table` trx table locks 1 total table locks 2  trx id 67059742791 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0
*** WE ROLL BACK TRANSACTION (1)

如何查看MySQL数据库的死锁日志
http://825635381.iteye.com/blog/2339503


table metadata lock

metadata lock 是为了保护 database objects (包括 表结构、存储过程、触发器等)而设计的。

下面的 MDL 操作需要获取 metadata lock
创建、删除索引。
修改表结构。
表维护操作(optimize table、repair table等)。
删除表。
获取表上表级写锁 (lock table tab_name write)。

那 metadata lock 和事务有什么关系呢?
在事务中,当它需要访问一个 database object 时都需要先获得其 metadata lock,在事务结束后才会释放 metadata lock。这样做有几个目的:

第一是为了进一步保证事务的一致性,比如我在事务 A 中对某一行记录进行了更新,我的事务现在还没有提交,但是这个时候另外一个会话2要修改表名,如果事务 A 持有了 metadata lock,那么这时候另一个会话2将无法修改,show processlist 会发现它在 Waiting for table metadata lock 。直到事务 A 提交或回滚后,才能获得 metadata lock 修改成功。如果我们没有 metadata lock 的机制,那么会话2 就可以直接修改表名,这样当事务 A 由于其它原因需要回滚的时候,就回滚不了,因为表名被修改了,这样导致数据不一致。

第二是为了解决 binlog 同步的一个 bug,这个和上面的原因一样。binlog 的操作是基于事务的提交顺序的。事务 A 还未提交,另一个会话删除了相关表,这样 binlog 先记录的是删除表的操作,从库执行的顺序就不对了。

Waiting for table metadata lock

MySQL在进行alter table等DDL操作时,有时会出现 Waiting for table metadata lock 的等待场景。
而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)都无法进行,因为他们也会在Opening tables的阶段进入到Waiting for table metadata lock的锁等待队列。
如果是产品环境的核心表出现了这样的锁等待队列,就会造成灾难性的后果。

造成alter table产生Waiting for table metadata lock的原因其实很简单,一般是以下几个简单的场景:

场景一:长事物运行,阻塞DDL,继而阻塞所有同表的后续操作
通过show processlist可以看到TableA上有正在进行的操作(包括读),此时alter table语句无法获取到metadata 独占锁,会进行等待。
处理方法: kill 掉 DDL所在的session.

场景二:未提交事物,阻塞DDL,继而阻塞所有同表的后续操作
通过show processlist看不到TableA上有任何操作,但实际上存在有未提交的事务,可以在 information_schema.innodb_trx中查看到。在事务没有完成之前,TableA上的锁不会释放,alter table同样获取不到metadata的独占锁。
处理方法:通过 select * from information_schema.innodb_trx, 找到未提交事物的sid, 然后 kill 掉,让其回滚。

Waiting for table metadata lock 案例分析

添加索引的过程中,有查询请求,查询请求等待 table metadata lock 超时。

MySQLQueryInterruptedException

Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLQueryInterruptedException: Query execution was interrupted

Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLQueryInterruptedException: Query execution was interrupted
; uncategorized SQLException; SQL state [70100]; error code [1317]; Query execution was interrupted; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLQueryInterruptedException: Query execution was interrupted, StackTrace: [org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89), org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81), org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81), org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75), org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447), com.sun.proxy.$Proxy149.selectList(Unknown Source), org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:231), org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:128), org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:68), org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53), com.sun.proxy.$Proxy172.queryUserIdByStatusAndRole(Unknown Source), com.nio.uds.watson.service.sales.LeadsDistributeService.queryUserIdByStatusAndRole(LeadsDistributeService.java:573),


mysql行锁与synchronized死锁案例排查

表结构

create table user (
    id bigint auto_increment primary key,
    mobile varchar(20) not null comment '手机号',
  constraint mobile unique (mobile)
);

事务的隔离级别为Spring默认的读已提交READ_COMMITTED,传播属性为默认的PROPAGATION_REQUIRED即有则加入无则新建。

代码结构如下,这是个创建 user 的功能,入口有2个,一个来自 http 接口调用,一个收听 kafka 消息创建。最终都通过 insertUser 方法插入user表。

// http 创建 user 接口
@Transactional
public void httpCreateUser(request) {
  if(select no user) {
    userService.insertUser(request);  // @1
  }
  ... ...
  userService.insertUser(request);  // @2
  ... ...
}

// kafka 消息处理
@Tranactional
public void consumerCreateUser(request) {
  ... ...
  if(select no user) {
    userService.insertUser(request);  // @3
  }
  ... ...
}

private void insertUser(request) {
  // 从全局 static lock map 中根据 mobile 获取唯一的锁对象
  Object lockObject = getLockByString(request.getMbile());
  synchronized(lockObject) {
    userService.insertUser(request);
  }
}

问题:
生产环境出现事务死锁,造成后续大量请求超时
org.springframework.dao.CannotAcquireLockException: ### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction|

原因:
死锁,mysq 行锁 和 java synchronized 对象锁互相死锁。
经分析发现造成死锁的过程如下,在同一台server上,依次按如下时序:
1、线程1 从 http request 请求创建用户,进入 httpCreateUser, 开启事务, 在 @1 处调用 insertUser, 插入新user,同时也锁住了 mobile 所在的行。
2、线程2 消费创建新 user 的 kafka 消息,进入 consumerCreateUser, 开启事务, 此时由于 线程1 的事务还没提交,线程2 中查不到,认为此手机号的用户还没创建,在 @3 处调用 insertUser, synchronized 加锁成功,但 insert sql执行时需要等待 mysql 行锁
3、线程1 不管什么原因在 @2 处又调用了一次 insertUser, 且调用之前没先select查一下,在 insertUser 方法中 synchronized 加锁不成功,因为同一个手机号的 object 锁已经被 线程2 占有了。
线程1占着 mysq 行锁等待 synchronized 对象锁, 线程2占着 synchronized 对象锁等待 mysq 行锁,造成死锁。

其实,线程2 在 mysq 行锁等待超时后(mysql默认50秒,我们生产系统设置成了120秒,innodb_lock_wait_timeout 变量设置),可以抛异常并自动释放 synchronized 对象锁,结束执行。然后 线程1 拿到 synchronized 对象锁再次insert,mysql报重复key异常,结束执行。是可以自动解开死锁的,之所以没有自动解开,是因为后续又进来了n多个重试请求,要求insert这个手机号的user,并且 synchronized 是非公平锁,后续来的活跃请求会插队到 线程1 前面,也就是 线程2 即使抛异常结束后 线程1 也拿不到 synchronized 锁,然后就一直等着也不commit事务,一直锁着这个手机号对应的数据行。然后后续所有的插入请求都会等待 innodb_lock_wait_timeout 时间后再返回,产生雪崩效应。
可以本地开两个session试下
session 1 中,手动开启事务,插入一个原来没有的手机号到user, 先不 commit

mysql > begin;
mysql > insert into user(mobile) values('98762427151');

此时,session 2 中还查不到这个手机号(默认隔离级别RR或RC都看不到),插入同一个手机号的user(手动或自动提交事务都行),会等待锁超时时间(默认50秒)后,给出 Lock wait timeout exceeded 错误,因为mysql锁住了这个手机号所在的行。

mysql > select * from user where mobile='98762427151';
Empty set (0.000 sec)
mysql > insert into user(mobile) values('98762427151');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

事务内加分布式锁导致重复数据案例排查

以如下关系表为例

create table student_school_table
(
    student_id bigint null,
    school_id bigint null
);

业务要求是一个学生只能有唯一的一所学校,即 student_school_table 表中 每个 student_id 最多对应一个 school_id,但表上忘了给 student_id 加唯一索引。

后端代码中,学生学校信息的更新是放在一个大事务中的,伪代码如下

@Transactional(isolation = Isolation.READ_COMMITTED)
public void updateStudentInfo(Request request) {
  // 更新学生基本信息
  updateBaseInfo(request);

  // 更新地址信息
  updateAddress(request);

  // 创建或更新学校信息
  upsertSchool(request);

  // 更新家庭信息
  updateFamilyInfo(request);
}

事务的隔离级别为Spring默认的读已提交READ_COMMITTED,传播属性为默认的PROPAGATION_REQUIRED即有则加入无则新建。

更新学校信息放在 upsertSchool 方法中,为了达到“一个学生最多只有一所学校”的目的,upsert 学校时加了分布式锁,保证对同一个学生的学校信息更新只能串行进行。伪代码如下:

public void upsertSchool(Request request) {
  try {
    // 加分布式锁,锁 key 是 student_id
    lock(student_id);
    // select 查询 student_id 是否已有学校信息,没有的话插入,已有的话更新
    ... ...
  } catch (Exception exception) {
    // log.error
  } finally {
    unlock();
  }
}

结果还是出现了 student_school_table 表中 一个 student_id 有两个 学校信息的情况。
查日志分析出问题的过程如下:
0、前提: id为1 学生目前还没有学校信息。
1、两台服务器 server1 和 server2 上同时进来更新 学生1 的信息的请求,server1请求将 学生1 的学校更新为100,server2请求将 学生1 的学校更新为200
2、两台服务器上几乎同时进入 updateSchool 方法,server1先加分布式锁成功,select发现 学生1 无学校信息,插入一条记录:(学生1, 学校100),但事务还未提交,因为要等到整个updateStudentInfo方法结束才提交。updateSchool 方法结束后释放分布式锁。
3、刚才 server1 执行 updateSchool 方法时 server2 一直在等待分布式锁,等 server1 释放后,server2 获得锁,select 发现 学生1 无学校信息,插入一条记录:(学生1, 学校200),这里有非常关键的一点:由于事务隔离级别为读已提交,在server1提交事务前,server2查库时是看不到server1写进去的(学生1, 学校100)这条记录的,所以会认为学生1还没有学校信息
4、由于数据库也没给 student_school_table 的 student_id 字段加唯一索引,就出现了 学生1 有两条学校记录的情况。

解决方法:
1、给 student_school_table 的 student_id 字段加唯一索引。
2、事务内加分布式锁导致多线程间数据不可见,所以可以改为把分布式锁加到 updateStudentInfo 方法外,在锁内提交事务。


数据库磁盘空间不足导致事务超时排查案例

java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

异常表现

azkaban web界面无法上传新任务也无法删除和修改任何任务属性,看后台日志都是等待一段时间后抛出锁等待超时异常,比如:

java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction Query: INSERT INTO project_versions (project_id, version, upload_time, uploader, file_type, file_name, md5, num_chunks, resource_id) values (?,?,?,?,?,?,?,?,?) Parameters: [56, 7, 1544775799795, azkaban, zip, sync-user-identity-1.0.0-SNAPSHOT-sync-user-identity.zip, [-27, 52, 115, 62, -67, 107, 55, 49, 95, -107, -41, 27, -81, 90, 22, 115], 0, null]

原因与解决

查看 information_schema.INNODB_TRX 表,发现有好多卡住的事务,不知道为什么。
在mysql命令行 kill trx_mysql_thread_id 也不起作用。
找DBA帮看下,发现事务卡住是因为azkaban数据库所在的服务器磁盘满了,写不进去。
DBA紧急给做了磁盘扩容,马上就好了。

所以有时候真的无法想象会出现什么异常,谁能想到是数据库服务器磁盘满了导致写不进去,事务卡住,又引起了后来事务的锁等待超时,最终表现为web界面无法进行任何写入操作。

关于MySQL的lock wait timeout exceeded解决方案
https://segmentfault.com/a/1190000015314171


交叉索引导致死锁案例排查

分析

表结构如下:

create table relation_table
(
    id bigint auto_increment primary key,
    account1_id bigint null,
    account2_id bigint null,
    relation smallint(6) not null,
    create_time datetime default CURRENT_TIMESTAMP not null,
    update_time timestamp default CURRENT_TIMESTAMP not null,
    constraint relation_fk_1 foreign key (account1_id) references account (account_id),
    constraint relation_fk_2 foreign key (account2_id) references account (account_id)
);

业务上来说,是两个用户同时对一个用户更新关系类型,比如 Account A 和 Account B 同时更新和 Account C 的关系。
反应在表上就是,表中已有:
(A, C)
(B, C)
然后又同时更新(A, C) 和 (B, C)

代码层面,对 account1_id-account2_id 这个key加了redis分布式锁,所以同时写 account1 和 account2 都相同的,分布式锁层面就会拦截住,但这两个sql account2不同,分布式锁锁不住。

explain 下这条sql语句:

MariaDB [uds]> explain UPDATE relation_table SET relation = 0 WHERE account1_id =55735549 AND account2_id = 126475;
+------+-------------+-----------------------+-------------+-------------------------+-------------------------+---------+------+------+-------------------------------------------------------+
| id   | select_type | table                 | type        | possible_keys           | key                     | key_len | ref  | rows | Extra                                                 |
+------+-------------+-----------------------+-------------+-------------------------+-------------------------+---------+------+------+-------------------------------------------------------+
|    1 | SIMPLE      | relation_table        | index_merge | account1_id,account2_id | account1_id,account2_id | 9,9     | NULL |    1 | Using intersect(account1_id,account2_id); Using where |
+------+-------------+-----------------------+-------------+-------------------------+-------------------------+---------+------+------+-------------------------------------------------------+
1 row in set (0.009 sec)

同事帮看了下,看到explain的type是index_merge,感觉像是走交叉索引导致的死锁。
这个表在 account1 和 account2 上都有外键,外键默认会创建索引,所以这条sql会同时使用2个索引来加锁。
上网搜了下,有好多类似情况,大体上能确定原因就是这个。

InnoDB行锁是通过给索引上的索引项加锁来实现的,InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!
在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。
1、在不通过索引条件查询的时候,InnoDB 确实使用的是表锁,而不是行锁。
2、由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行 的记录,但是如果是使用相同的索引键,是会出现锁冲突的。应用设计的时候要注意这一点。
3、当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论 是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。
4、即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同 执行计划的代价来决定的,如果 MySQL 认为全表扫 效率更高,比如对一些很小的表,它 就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。因此,在分析锁冲突时, 别忘了检查 SQL 的执行计划,以确认是否真正使用了索引。

MySQL中的行级锁,表级锁,页级锁
http://www.hollischuang.com/archives/914

index_merge引发的死锁排查
https://www.cnblogs.com/cchust/p/6464188.html

index merge 引起的死锁分析
http://seanlook.com/2017/03/11/mysql-index_merge-deadlock/

解决

解决方法:加联合索引
添加联合索引 account_key(account1_id,account2_id),这样以来查询会选择这一个索引,至于 idx_sid 这个单列索引还需不需要,看业务场景。

创建联合索引:

create index idx_account1_2 on relation_table (account1_id, account2_id);

创建之后再explain下这个SQL:

MariaDB [uds]> explain UPDATE relation_table SET relation = 0 WHERE account1_id =55735549 AND account2_id = 126475;
+------+-------------+-----------------------+---------+-----------------------------+-------------------------+---------+------+------+-------------+
| id   | select_type | table                 | type    | possible_keys               | key                     | key_len | ref  | rows | Extra       |
+------+-------------+-----------------------+---------+-----------------------------+-------------------------+---------+------+------+-------------+
|    1 | SIMPLE      | relation_table        | range   | account2_id,idx_account1_2  | idx_account1_2          | 18      | NULL |    1 | Using where |
+------+-------------+-----------------------+---------+-----------------------------+-------------------------+---------+------+------+-------------+
1 row in set (0.009 sec)

index_merge 变为 range

但我在test环境上,创建联合索引后,explain 还是 index_merge,DBA在生产上explain是range,说索引跟数据量关系很大,就算结构一样,数据量不一样的表,走的索引也不太一样的

MySQL死锁日志

show engine innodb status;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-11-05 19:07:44 2b6bcb03f700
*** (1) TRANSACTION:
TRANSACTION 67059742790, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 462 lock struct(s), heap size 63016, 4 row lock(s)
MySQL thread id 39706604, OS thread handle 0x2b6ba0f91700, query id 168182824647 10.128.18.185 uds_prod_w updating
UPDATE relation_table SET relation = 0 WHERE account1_id =55735549 AND account2_id = 1800241143
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 536 page no 65409 n bits 264 index `PRIMARY` of table `relation_table` trx table locks 1 total table locks 2  trx id 67059742790 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0
*** (2) TRANSACTION:
TRANSACTION 67059742791, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
641 lock struct(s), heap size 95784, 3 row lock(s)
MySQL thread id 39707153, OS thread handle 0x2b6bcb03f700, query id 168182824649 10.128.19.96 uds_prod_w updating
UPDATE relation_table SET relation = 0 WHERE account1_id =55735549 AND account2_id = 126475
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 536 page no 65409 n bits 264 index `PRIMARY` of table `uds`.`relation_table` trx table locks 1 total table locks 2  trx id 67059742791 lock_mode X locks rec but not gap lock hold time 0 wait time before grant 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 536 page no 29223 n bits 792 index `account1_id` of table `uds`.`relation_table` trx table locks 1 total table locks 2  trx id 67059742791 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0
*** WE ROLL BACK TRANSACTION (1)

服务端异常信息

[Handled] unknown exception
org.springframework.dao.DeadlockLoserDataAccessException:
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may involve com.masikkk.mapper.MyRelationMapper.updateAppUserRelation-Inline
### The error occurred while setting parameters
### SQL: UPDATE relation_table SET relation = ? WHERE account1_id =? AND account2_id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; SQL []; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:263) ~[spring-jdbc-4.3.16.RELEASE.jar!/:4.3.16.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.3.16.RELEASE.jar!/:4.3.16.RELEASE]
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75) ~[mybatis-spring-1.3.0.jar!/:1.3.0]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447) ~[mybatis-spring-1.3.0.jar!/:1.3.0]
    at com.sun.proxy.$Proxy120.update(Unknown Source) ~[?:?]
    at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:295) ~[mybatis-spring-1.3.0.jar!/:1.3.0]
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:59) ~[mybatis-3.4.0.jar!/:3.4.0]
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53) ~[mybatis-3.4.0.jar!/:3.4.0]
    at com.sun.proxy.$Proxy137.updateAppUserRelation(Unknown Source) ~[?:?]

上一篇 TiDB

下一篇 Spring-Data-Redis

阅读
评论
7.1k
阅读预计30分钟
创建日期 2018-11-06
修改日期 2021-07-29
类别

页面信息

location:
protocol:
host:
hostname:
origin:
pathname:
href:
document:
referrer:
navigator:
platform:
userAgent:

评论