mysql中锁的详解

1.锁是计算机协调多个进程或线程并发访问某一资源的机制。

2.在数据库中,除了传统的计算资源(如,cpu,RAM,I/O等)的争用以外,数据也是一种提供许多用户共享的资源。

如何保证数据并发访问的一致性,有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素,从这个角度来说锁对数据库显得尤为重要也更加复杂。

1、从对数据库操作的类型进行分类

1.1、读锁 (共享锁) : 针对同一份数据,多个读操作可以同时进行而不会互相影响

1.2、写锁(排它锁) : 当前写操作没有完成之前,它会阻止其它写锁和读锁。

2、从对数据库操作的粒度分类

1、表锁（偏读）

MyISAM 在执行查询语句(select) 前,会自动给涉及的所有表加读锁,在执行增删改查操作前,会自动给涉及的表写写锁。

表共享读锁 (Table Read Lock)

表独占写锁(Table Write Lock)

所以对 MyIsam 表进行操作,会有以下情况:

1. 对 MyIsam 表的读操作(加读锁),不会阻塞其它进程对同一表的读请求,只有当读锁释放后(unlock tables),才会执行其它进行的操作。

2. 对Myisam 表的写操作(加写锁),会阻塞其它进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进行的读写操作。

简而言之就是读写会阻塞写但是不会阻塞读,但是写锁则会把读和写都阻塞

2、行锁（偏写）

1、事务及ACID属性

• 原子性：事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生

• 一致性：事务必须使数据库从一个一致性状态切换到另一个一致性状态

• 隔离性：事务的隔离性是指事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰

• 持久性：一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该有任何影响

2、并发事物处理带有的问题

• 更新丢失：当俩个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其它事务的存在,就会发生丢失更新问题--最后的更新覆盖了其它事务所作的更新操作。例如,俩个程序员修改了同一java文件,没个程序员独立地更改其副本,然后保存更改后的副本,这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所作的更改。如果在一个程序员完成提交事物之前,另一个程序员不能访问同一文件,则可避免这个问题。

• 脏读：对于俩个事务 T1 ,T2 T1读取了已经被T2 更新但还没有被提交的字段之后,若T2 回滚,T1读取的内容就是临时且无效的。

• 不可重复读：对于俩个事务 T1 ,T2 T1读取了一个字段,然后T2更新了该字段之后,T1再次读取同一字段,值就不同了。

• 幻读：对于俩个事务 T1,T2 ,T1 从一个表中读取了一个字段,然后T2在该表中插入了一些新的行,之后,如果T1再次读取同一个表,就会多出几行。脏读和幻读区别：1.脏读读到了T2里面的修改了的数据；2.幻读读到了T2里面的新增数据

3、数据库的隔离级别

• repeatable read（读未提交的数据）（RR）出现幻读，不会出现脏读和不可重复读

• read commited（读已提交的数据）出现幻读和不可重复读，不会出现脏读

• read uncommited（可重复读）出现脏读、幻读不可重复读

• serializable（串行化）都不会出现脏读民、幻读和不可重复读

4、测试

4.1、要先关闭数据库的自动提交

默认是开启的
查询命令是：show variables like '%autocommit%';
关闭命令：set autocommit=0;

4.2、连接客户端a和客户端b

5、无索引行锁升级为表锁

由于varchar使用单引号引起的行锁变为表锁

6、间隙锁

6.1、如何产生

a客户端使用范围update，b客户端插入一个范围之内没有的值，b就会发生阻塞

7、如何锁定某一行

手动锁定某行,在修改某行的过程中,别人无法进行修改当前行号的数据,知道commit进行提交后,别人才能修改

8、总结

1.InnoDB 存储引擎由于实现了行级锁定,虽然锁的机制的实现方面所带来的性能损耗可能比表级锁定会更高一些,但是在整体并发处理能力方面要远远优于 MyISAM 的表级锁定,当系统并发量较高的时候,InnnoD的整体性能和MyIsam 相比就会有比较明显的优势。

2.但是 InnoDB 的行锁同样也很脆弱的一面,当我们使用不当的时候会让 InnoDB 的整体性能不仅不能比 MyISAM 高,甚至会更差。

9、优化建议

• 尽可能让所有检索都通过索引来完成，避免无索引，行锁升级为表锁

• 合理设计索引，尽量缩小锁的范围

• 尽可能较小检索索引，避免间隙锁

• 尽量控制事务大小，减少锁定资源量和事务长度

• 尽可能低级别事务隔离