MySQL高级_锁

分类：

从对数据操作的粒度分 ：

1） 表锁：操作时，会锁定整个表。MyISAM默认。

2） 行锁：操作时，会锁定当前操作行。InnoDB默认。

从对数据操作的类型分：

1） 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

2） 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

MyISAM 如何 加/解 表锁?

MyISAM （加锁的过程一般不需要用户干预）
在执行 SELECT前，会自动给涉及的所有表加读锁。
在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁。

-- 加读锁 (table_name :表名)： 
lock table table_name read;

-- 加写锁 (table_name :表名)： 
lock table table_name write；

读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。

-- 释放锁(读锁，解锁都可以)
 unlock tables 

查看MyISAM 锁的争用情况

-- 查看数据库的表
show open tables

In_user : 表当前被查询使用的次数（说明该表正在加锁）。如果该数为零，则表是打开的，但是当前没有被使用。

Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。

-- 查看表的锁定情况
show status like 'Table_locks%'

Table_locks_immediate ： 指的是能够立即获得表级锁的次数，每立即获取锁，值加1。

Table_locks_waited ： 指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加1，此值高说明存在着较为严重的表级锁争用情况。

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

-- 关闭数据库事务自动提交
set autocommit = 0

InnoDB 虽然默认是行锁， 但是有时无索引行锁可以升级为表锁。

查看InnoDB 行锁争用情况

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: 系统启动后到现在总共等待的次数

当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。