什么是锁?
- 锁是计算机协调多个进程或线程并发访问某一资源的机制;
- 在数据库中,除传统的计算资源(如CPU、RAM、I/O、等)的争用外,数据也是一种供许多用户共享的资源;
- 如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素;
- 锁对数据库极其重要,也更复杂。
表锁(偏读)
- 偏向MYISAM存储引擎,开销小,加锁快,无死锁,锁定粒度大,发生锁冲突的概率最高,并发度最低;
- 手动增加表锁:
lock table 表名 read(write);
- 查看表上加过的锁:
show open tables;
- 释放表锁:
unlock tables;
tips:
MYISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁。
读锁会阻塞写,不阻塞读;写锁会阻塞读+写。
可通过检查table_locks_waited和table_locks_immediate状态变量分析系统上的表锁定;
table_locks_waited:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1),值越高表明存在着严重的表级锁争用情况;
table_locks_immediate:产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁值加1.
tips:MYISAM的读写锁调度是写优先,不适合做写为主表的引擎;写锁后。其他线程不能做任何操作,大量的更新会使查询很难得到锁,可能造成永远阻塞。
行锁(偏写)
- 偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁,;锁定粒度最小,发生锁冲突的概率最低,并发度最高。
- InnoDB不同于MYISAM的是:
1.支持事务(TRANSACTION);
2.采用了行级锁。
事务及其ACID属性:
事务是由一组SQL语句组成的逻辑处理单元;
1.原子性(Atomicity):事务是一个原子操作单元,其对数据的修改,要么全部执行,要么全部不执行;
2.一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态;所有相关的事务规则都必须应用于事务的修改,以保持数据的完整性;事务结束后,所有的内部数据结构(如B+Tree索引/双向链表)也都必须是正确的;
3.隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行,意味着事务处理过程的中间状态对外部不可见;
4.持久性(Durable):事务完成后,对于数据的修改是永久性的。
并发事务处理带来的问题有哪些?
1.更新丢失(Lost Update)
2.脏读(Dirty Reads)
3.不可重复读(Non-Repeatable Reads)
4.幻读(Phantom Reads)
tips:MYSQL默认是可重复读(Repeatable Reads);
行锁是独立的,互不相同的行之间互不干扰
注意:索引失效可能会使行锁变表锁,尤其varchar类型必须加’’;
间隙锁及危害
- 当用范围条件检索数据时,InnoDB会给符合条件的已有的数据记录的索引项加锁,对于键值在条件范围内但不存在的记录称为“间隙(GAP)”,InnoDB也会对这种“间隙”加锁,这种锁机制称为“间隙锁(Next-Key)”
- 锁定的时候无法插入锁定键值范围内的任何数据。
eg:
session1:
update test_innodb_lock set b=a*20 where a>1 and a<6;
session2:
insert into test_innodb_lock values(2,'100'); //即使2不存在,但属于1-5范围内,便会被锁定,产生阻塞,不能插入
session1:
commit;
session2等待阻塞完成,成功插入
如何锁定一行?
select xxx for update锁定一行后,其他的操作会被阻塞,只有锁定行的会话提交commit才会继续其他操作,需要等待。
总结: MYISAM适合读锁(比如APP大量浏览数据),InnoDB适合写锁(比如APP搜索很多关键字并进行交易)。