mysql表锁、行锁

mysql表锁、行锁

 

 

一、表锁：

偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生冲突的概率最高，并发度最低。

查看表上加锁状态 ： SHOW OPEN TABLES

查看存在的表锁： Show OPEN TABLES where In_use > 0; 

In_use = 1 表示被锁

添加表锁命令：

读锁(共享锁)：   LOCK TABLE   表名    READ;

写锁（排他锁）：  LOCK TABLE   表名    WRITE;

解锁： UNLOCK TABLES;

 

读锁： 

加了读锁后当前表被锁定，当前事务可以进行读操作，但是无法进行插入或者修改操作，当前事务无法操作其他没有被锁定的表。

其他事务可以对被锁定的表的读操作，但是进行插入或者修改会一直等待锁的释放， 并且可以操作其他未被锁定的表。

写锁：

当前事务对锁定表的  查询  +  修改 + 新增  操作都可以执行。

其他事务对锁定表的查询  +  修改 + 新增  操作都阻塞，只有当上一个事务释放锁后才继续执行。

总结：

对于MyISAM表进行操作会有如下情况：

1： 对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求，只有当读锁释放后，才会执行对其他进程的写操作。

2：对MyISAM表的写操作（加写锁），会阻塞其他进程的读和写操作，只有当写操作释放后，才会执行其他进程的读写操作。

 简而言之读锁不会阻塞读，但是会阻塞写，而写锁会阻塞读和写。

所以：对于MyISAM引擎适合用于读多写少的表，因为加写锁后，其他进程都不能进行操作，大量的  修改 + 新增  会使得  查询  很难得到锁，从而造成永久阻塞。

表锁分析： 

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

命令： show status like 'table%';

table_locks_waited:  出现表级锁争用而发生等待的次数，此值越高说明表级锁争用越严重。

table_locks_immediate： 产生表级锁定的次数。

 

二、行锁

偏向于InnoDB引擎，开销大，加锁慢；会出现死锁；锁粒度最小，发生冲突的概率最低，并发度也最高。

InnoDB与MyISAM最大的不同有两点：1：InnnoDB支持事务； 2：采用了行级锁。

当两个或多个事务操作同一行的数据修改时，先获得锁的事务在没有提交事务之前，其他的事务都是被阻塞的状态，只有前一个事务提交后，后面的事务才会继续执行。

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

命令： show status like  'innodb_row_lock%';

 Innodb_row_lock_current_waits: 当前正在等待锁定的数量。

 Innodb_row_lock_time： 从系统启动到现在锁定的总时长。

Innodb_row_lock_time_avg：每次等待锁花费的平均时间。

Innodb_row_lock_time_max： 从系统启动到现在等待最长的时间。

Innodb_row_lock_waits：系统启动到现在总共等待的次数。

 

其他锁：

1：什么是间隙锁？

     当我们用范围条件而不是相等条件检索数据时，InnoDB会对符合条件的已有数据的索引项加锁，对于键值在范围内，但是并不存在的数据叫做间隙，但是InnoDB也会对这种数据加锁，叫做间隙锁。

比如 条件是 where id > 1 and id < 5 如果并不存在id=2的这条记录，另一个事务在插入一条id=2的sql时会一直阻塞，直到上一个事务已经提交。

 

2：如何锁定一行数据？

SELECT * FROM your_table WHERE id = 1 FOR UPDATE