数据库锁定机制简单来说,就是数据库为了保证数据的一致性,而使各种共享资源在被并发访问变得有序所设计的一种规则
表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低;
行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高;
页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
适用:从锁的角度来说,表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。
MyISAM表级锁的锁模式:
None | 读锁 | 写锁 | |
表共享读锁 | 兼容 | 兼容 | 冲突 |
表独占写锁 | 兼容 | 冲突 | 冲突 |
读操作不会因为不同进程访问而发生阻塞,但是不能执行更新插入命令。lock table tableName read
写操作会阻止其他用户对同一表的读操作和写操作 lock table tableName write
加锁
MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。
read 和 read local 之间的区别就是 后者允许在锁定被保持时,执行非冲突的inset语句同时插入
concurrent_insert ,专门用来控制其并非插入行为。
concurrent_insert = 0 不允许并发插入;concurrent_insert =1 如果表中没有被删除的行,MyISAM允许在一个进程读表的同时在尾部插入记录;concurrent_insert =2 无论是否删除,都允许在尾部并发插入记录
MyISAM表锁的优化
(1)查询表级锁争用情况
MySQL内部有两组专门的状态变量记录系统内部锁资源争用情况:
mysql> show status like 'table%';
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| Table_locks_immediate | 100 |
| Table_locks_waited | 11 |
+----------------------------+---------+
这里有两个状态变量记录MySQL内部表级锁定的情况,两个变量说明如下:
Table_locks_immediate:产生表级锁定的次数;
Table_locks_waited:出现表级锁定争用而发生等待的次数;
两个状态值都是从系统启动后开始记录,出现一次对应的事件则数量加1。如果这里的Table_locks_waited状态值比较高,那么说明系统中表级锁定争用现象比较严重,就需要进一步分析为什么会有较多的锁定资源争用了。
(2)减少锁定时间,尽量让查询的时间尽可能的短
(3)concurrent_insert 特性
(4)根据实际情况设置读写的优先级别。 通过设置 low_priority_updates =1(写操作比读操作优先级低)
INNODB行级锁
共享锁(S) | 排它锁(X) | 意向共享锁(IS) | 意向排它锁(IX) | |
共享锁(S) | 兼容 | 冲突 | 兼容 | 冲突 |
排它锁(X) | 冲突 | 冲突 | 冲突 | 冲突 |
意向共享锁(IS) | 兼容 | 冲突 | 兼容 | 兼容 |
意向排它锁(IX) | 冲突 | 冲突 | 兼容 | 兼容 |
如果一个事务请求的锁模式与当前的锁兼容,InnoDB就将请求的锁授予该事务;反之,如果两者不兼容,该事务就要等待锁释放。
考虑这个例子:事务A锁住了表中的一行,让这一行只能读,不能写。之后,事务B申请整个表的写锁。如果事务B申请成功,那么理论上它就能修改表中的任意一行,这与A持有的行锁是冲突的。数据库需要避免这种冲突,就是说要让B的申请被阻塞,直到A释放了行锁。数据库要怎么判断这个冲突呢?step1:判断表是否已被其他事务用表锁锁表step2:判断表中的每一行是否已被行锁锁住。注意step2,这样的判断方法效率实在不高,因为需要遍历整个表。
于是就有了意向锁。在意向锁存在的情况下,事务A必须先申请表的意向共享锁,成功后再申请一行的行锁。在意向锁存在的情况下,上面的判断可以改成step1:不变step2:发现表上有意向共享锁,说明表中有些行被共享行锁锁住了,因此,事务B申请表的写锁会被阻塞。注意:申请意向锁的动作是数据库完成的,就是说,事务A申请一行的行锁的时候,数据库会自动先开始申请表的意向锁,不需要我们程序员使用代码来申请。
使用共享锁和和排它锁必须满足的条件:
1、set autocommit = 0;2、表引擎支持事务3、使用begin或 start transaction 开启事务
获取innodb行级锁争用情况
隔离级别越高,Innodb处理SQL语句时所采用的一致性和需求的锁是不同的。
show status like'%innodb_row_lock%' 获取详细记录。
innodb行级锁实现方法
在不通过索引条件查询的时候,此时使用的是表锁而不是行锁。
添加索引的命令:
create index index_name on table_name(id);
间隙锁
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;
InnoDB除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁。
死锁
一般情况下,如果innodb存储引擎发生了死锁状况,通常是一个事务释放锁并回滚,另一事务获得锁,继续完成事务。在innodb发现死锁时,会计算出两个事务各自插入、更新或者删除的数据量来判定两个事务的大小,让较小的事务回滚,让较大的事务继续事务。但在涉及外部锁、或涉及表锁的情况下,innodb并不能完全自动检测死锁,此时需要设置锁等待时间-- innodb——lock_wait_timeout来解决
innodb行级锁优化
1、控制事务大小,减少锁定资源量和锁定时间长度。
2、避免因为无法通过索引加锁而使用表级锁
3、减少使用范围数据检索,造成锁定多余记录
4、在业务允许的情况下,尽量使用隔离级别较低的事务隔离,以减少因为事务隔离级别锁带来的附加成本。