MySQL支持多种存储引擎,不同的存储引擎对应着不同的锁机制,用来适应不同的业务需求。
MySQL有三种锁:
1、表级锁:开销小、加锁快;不会出现死锁;锁定粒度大,发生锁冲突概率最高,并发度低。(MyISAM引擎,MEMORY引擎)
表级锁适合已查询为主,只有少量按索引更新的数据的应用。
2、行级锁:开销大、加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度高。(InnoDB引擎)
行级锁更适合有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,对并发性要求较高的应用。
3、页级锁:开销和加锁介于表级锁和行级锁之间,会出现死锁;锁定粒度也介于两者之间,并发度一般。
详细说一下每个引擎对应锁机制的基本使用
1、MyISAM表锁
锁兼容
读锁(S) | 写锁(X) | |
读锁(S) | 兼容 | 不兼容 |
写锁(X) | 不兼容 | 不兼容 |
加写锁:lock table table_name write;
加读锁:lock table table_naem1,table_name2 read local;
释放锁:unlock tables;
写锁阻塞例子:
当前Session加 写锁,释放锁之前,其他Session读锁住的表,会等待锁释放后才能读。
读阻塞例子:
1)当前Session 加读锁后,不能读取没有锁定的表。
2)当前Session插入和更新锁定的表会报错。
3)其他Session可以更新查询未锁定的表,但不能对锁定的表进行操作。
还有一点要特别注意:
一次锁定所有用到的表,同一表在SQL中出现多次,就要通过SQL语句中相同别名锁定多少次。
2、InnoDB行锁
InnoDB与MyISAM不同的有两点:
1)支持事务 2)采用行级锁
一、事务的概述和讲解
事务的四个属性:
1)原子性:事务是一个原子操作单元,对数据修改,要不全部执行,或都不执行。
2)一致性:开始和完成时,数据必须保持一致(索引)。
3)隔离性:互相隔离,不同Session或不同事务互不影响。
4)持久性:事务后,对数据的修改时永久的
但是事务在并发中也会有相应的弊端:
1)更新数据的丢失:A、B两个事务,对同一事务进行修改,不加锁的情况,后一个Session更新会将前一个Session更新覆盖。
2)脏读:A对数据进行修改,如果不做锁处理,B拿取到的脏数据,同时进行了处理,就会产生未提交的数据依赖。
3)幻读:A以相同条件检索两次,第二次发现其他插入满足其检索条件。
二、InnoDB锁讲解
InnoDB 有两种行锁和两种表锁
两种行锁:
1)排他锁(X):允许获得该锁的事务更新数据,阻止其他事务取得相同数据集的共享锁和排他锁。
2)共享锁 (S) :允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
两种表锁:
1)意向共享锁(IS):给数据加共享锁,要先获得该表的IS锁。
2)意向排他锁(IX):加排他锁前,要先获得该表的IX锁。
加行锁方式:
共享锁:Select ......... from talbe_name lock in share mode;
排他锁:Select ........... from table_name for update;
InnoDB锁兼容性
X | IX | S | IS | |
X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
IX | 不兼容 | 兼容 | 不兼容 | 兼容 |
S | 不兼容 | 不兼容 | 兼容 | |
IS | 不兼容 | 兼容 | 兼容 | 兼容 |