mysql数据库死锁问题处理_table_locks_waited

1.查询是否锁表

show OPEN TABLES where In_use > 0;

解开表级锁：

UNLOCK TABLES 

—————————————————— 

事务锁处理：

1、查看当前进程

mysql> show processlist;

2、查看当前运行的事务

mysql> SELECT * FROM information_schema.INNODB_TRX;

3、当前出现的锁

mysql> SELECT * FROM information_schema.INNODB_LOCKs;

4、kill掉对应进程

kill  id
————————————————————

查询并组织查杀线程的语句 

SELECT
    concat('KILL ', id, ';'),
    command,
    time,
    state,
    info,
    USER,
    HOST,
    db
FROM
    information_schema. PROCESSLIST
WHERE
    USER = '3pl'
AND command <> 'sleep' 

导读：

Mysql数据库的行级锁和表级锁

mysql的锁是由具体的存储引擎实现的。因此像Mysql的默认引擎MyISAM和第三方插件引擎 InnoDB的锁实现机制是有区别的。 
Mysql有三种级别的锁定：表级锁定、页级锁定、行级锁定mysql

1、定义

每次锁定的是一行数据的锁机制就是行级别锁定(row-level)。行级锁定不是MySQL本身实现的锁定方式，而是由其余存储引擎本身所实现的sql

表级锁：每次锁定的是一张表的锁机制就是表级别锁定(table-level)。它是MySQL各存储引擎中粒度最大的锁定机制。数据库

2、优缺点

优势服务器

行级锁：锁粒度小，争用率低，并发高。session

表级锁：实现简单，开销小，获取锁释放锁的速度快，将整个表锁定，能够避免死锁的问题并发

缺点分布式

行级锁：实现复杂，开销大。加锁慢、容易出现死锁高并发

表级锁：锁的粒度大，争用率高，并发低性能

3、支持存储引擎

使用行级锁定的主要有InnoDB存储引擎，以及MySQL的分布式存储引擎NDBCluster优化

使用表级锁定的主要有MyISAM，MEMORY，CSV等一些非事务性存储引擎。

4、行级锁

行级锁的类型：

InnoDB的行级锁定的两种类型：共享锁和排他锁，而在锁定机制的实现过程当中为了让行级锁定和表级锁定共存，InnoDB也一样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。

意向锁的做用就是当一个事务在须要获取资源锁定的时候，若是遇到本身须要的资源已经被排他锁占用的时候，该事务能够须要锁定行的表上面添加一个合适的意向锁。若是本身须要一个共享锁，那么就在表上面添加一个意向共享锁。而若是本身须要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。

意向共享锁能够同时并存多个，可是意向排他锁同时只能有一个存在。因此，能够说InnoDB的锁定模式实际上能够分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX）

行级锁的兼容性： 

行级锁定实现方式

InnoDB行锁是经过给索引上的索引项加锁来实现的。因此，只有经过索引条件检索数据，InnoDB才使用行级锁，不然，InnoDB将使用表锁。其余注意事项：

• 在不经过索引条件查询的时候，InnoDB使用的是表锁，而不是行锁。 
• 因为MySQL的行锁是针对索引加的锁，不是针对记录加的锁，因此即便是访问不一样行的记录，若是使用了相同的索引键，也是会出现锁冲突的。
• 当表有多个索引的时候，不一样的事务可使用不一样的索引锁定不一样的行，另外，不管是使用主键索引、惟一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
• 即使在条件中使用了索引字段，但具体是否使用索引来检索数据是由MySQL经过判断不一样执行计划的代价来决定的，若是MySQL认为全表扫描效率更高，好比对一些很小的表，它就不会使用索引，这种状况下InnoDB将使用表锁，而不是行锁。所以，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

行级锁的加锁：

隐式加锁：

• InnoDB自动加意向锁。
• 对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；
• 对于普通SELECT语句，InnoDB不会加任何锁；

显示加锁：

• 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
• 排他锁（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE

用SELECT … IN SHARE MODE得到共享锁，主要用在须要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操做。

可是若是当前事务也须要对该记录进行更新操做，则颇有可能形成死锁，对于锁定行记录后须要进行更新操做的应用，应该使用SELECT… FOR UPDATE方式得到排他锁。

InnoDB如何加表锁：

在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，不然MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，由于UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。

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

间隙锁（Next-Key锁）

间隙锁定义：

nnodb的锁定规则是经过在指向数据记录的第一个索引键以前和最后一个索引键以后的空域空间上标记锁定信息而实现的。 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（这些记录并不存在）的“间隙”加锁。

间隙锁的缺点：

• 间隙锁有一个比较致命的弱点，就是当锁定一个范围键值以后，即便某些不存在的键值也会被无辜的锁定，而形成在锁定的时候没法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能形成很大的危害
• 当Query没法利用索引的时候， Innodb会放弃使用行级别锁定而改用表级别的锁定，形成并发性能的下降；
• 当Quuery使用的索引并不包含全部过滤条件的时候，数据检索使用到的索引键所指向的数据可能有部分并不属于该Query的结果集的行列，可是也会被锁定，由于间隙锁锁定的是一个范围，而不是具体的索引键；
• 当Query在使用索引定位数据的时候，若是使用的索引键同样但访问的数据行不一样的时候（索引只是过滤条件的一部分），同样会被锁定

间隙锁的做用：

• 防止幻读，以知足相关隔离级别的要求。
• 为了数据恢复和复制的须要。

注意

• 在实际应用开发中，尤为是并发插入比较多的应用，咱们要尽可能优化业务逻辑，尽可能使用相等条件来访问更新数据，避免使用范围条件。
• InnoDB除了经过范围条件加锁时使用间隙锁外，若是使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。

查看本数据库锁隔离级别

show VARIABLES like 'tx_isolation' 

设置数据库事务隔离级别

set tx_isolation='READ-COMMITTED' 

注意：

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

6、表级锁

表级锁的类型：

表级锁的两种类型：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

表级锁模式的兼容性：

对MyISAM表的读操做，不会阻塞其余用户对同一表的读请求，但会阻塞对同一表的写操做；
对MyISAM表的写操做，则会阻塞其余用户对同一表的读和写操做；
MyISAM表的读操做与写操做之间，以及写操做之间是串行的。当一个线程得到对一个表的写锁后，只有持有锁的线程能够对表进行更新操做。其余线程的读、写操做都会等待，直到锁被释放为止。

表级锁的加锁：

• 在执行查询语句（select）前，会自动给涉及的全部表加读锁
• 在执行更新操做（update、delete、insert等）前，会自动给涉及的表加写锁。这个过程并不须要用户干预，所以不须要直接用lock table命令给MyISAM表显式加锁

固然能够显示的加锁，以下：

显示加写锁：

// 当一个线程得到对一个表的写锁后，只有持有锁的线程能够对表进行更新操做。
// 其余线程的读、写操做都会等待，直到锁被释放为止。
// test表将会被锁住，另外一个线程执行select * from test where id = 3;将会一直等待，直到test表解锁
LOCK TABLE test WRITE; 

显示加读锁

// test表将会被锁住，另外一个线程执行select * from test where id = 3;不会等待
// 执行UPDATE test set name='peter' WHERE id = 4;将会一直等侍，直到test表解锁
LOCK table test READ;

查看表级锁争用状况

执行SQL:mysql> show status like ‘table%’;

mysql> show status like 'table%';
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| Table_locks_immediate      | 20708     |
| Table_locks_waited         | 0         |
+----------------------------+-----------+

Table_locks_immediate：产生表级锁定的次数； 
Table_locks_waited：出现表级锁定争用而发生等待的次数； 
若是Table_locks_waited状态值比较高，那么说明系统中表级锁定争用现象比较严重，就须要进一步分析为何会有较多的锁定资源争用了。

 

 

意向锁是表锁仍是行锁？

首先能够确定的是，意向锁是表级别锁。意向锁是表锁是有缘由的。

当咱们须要给一个加表锁的时候，咱们须要根据意向锁去判断表中有没有数据行被锁定，以肯定是否能加成功。若是意向锁是行锁，那么咱们就得遍历表中全部数据行来判断。若是意向锁是表锁，则咱们直接判断一次就知道表中是否有数据行被锁定了。  

//查看数据库事务锁最长等待时间

show global variables like 'innodb_lock_w%'; 

//设置数据库事务锁最长等待时间

SET GLOBAL innodb_lock_wait_timeout=120;