MySQL从入门到应用 第十天

1. MySQL锁

    1.1 锁的分类

        从对数据操作的粒度分 ：1） 表锁：操作时，会锁定整个表。 2） 行锁：操作时，会锁定当前操作行。

        从对数据操作的类型分： 1） 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。2） 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

    1.2 各个存储引擎对锁的支持

存储引擎	表级锁	行级锁	页面锁
MyISAM	支持	不支持	不支持
InnoDB	支持	支持	不支持
MEMORY	支持	不支持	不支持
BDB	支持	不支持	支持

    1.3 三种锁的特点

锁类型	特点
表级锁	偏向 MyISAM 存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高 , 并发度最低。
行级锁	偏向 InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低 , 并发度也最高。
页面锁	开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

     1.4 MyISAM 的表锁

                             

        结论：

            1 ） 对 MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

            2 ） 对 MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；

        简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

        1.4.1 查看锁的争用情况

            1）show open tables； In_user : 表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。

            2） show status like 'Table_locks%';  Table_locks_immediate ： 指的是能够立即获得表级锁的次数，每立即获取锁，值加1。 Table_locks_waited ： 指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加1，此值高说明存在着较为严重的表级锁争用情况。

     1.5 InnoDB 行锁

             InnoDB 实现了以下两种类型的行锁。

                 共享锁（S ）：又称为读锁，简称 S 锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

                 排他锁（ X ）：又称为写锁，简称 X 锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

            对于 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

   结论:在获取到表行锁排他锁的连接上,对该行数据可读可写，阻塞其他连接对该行数据的读写；在获取到表行锁共享锁的连接上，对该行数据只可读不可写，其他连接对该行数据也值可读不可写

        1.5.1 行锁失效自动升级表锁：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。

        1.5.2 间隙锁：当我们用范围条件，而不是使用相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据进行加锁； 对于键值在条件范围内但并不存在的记录，叫做 "间隙（GAP）" ， InnoDB也会对这个 "间隙" 加锁，这种锁机制就是所谓的 间隙锁（Next-Key锁）。

        1.5.3 行锁争用情况：

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