mysql锁表，MVCC以及基础知识、锁、隔离级别、协议

加锁分析：http://www.cnblogs.com/zhaoyl/p/4121010.html 

转载：http://blog.csdn.net/yonghumingbuzhidao/article/details/8330795

此测试环境

Mysql 5.5 基于innodb 引擎

[sql] view plain copy

1. insert into  table1 values select  … from table2 ….  

此种方法，会锁table2

[sql] view plain copy

1. delete table1  from table1 inner join table2  on table1.id=table2.id  …  

此种方法，会锁table2

[sql] view plain copy

1. update tabel1,table2 set table1.name=’feie’ where table1.id=table2.id  

此种方法，会锁table2

[sql] view plain copy

1. update tabel1,table2 set table1.name=’feie’ where table1.id=table2.id and table1.id=1;  

此种方法，会锁table2.id=1的记录

-----------------------------------------第二次编辑分割线--------------------------------------

假设kid 是表table 的 一个索引字段 且值不唯一
1.如果kid 有多个值为12的记录那么：
update table  set name=’feie’ where kid=12;  
会锁表
2.如果kid有唯一的值为1的记录那么：
update table  set name=’feie’ where kid=1;  
不会锁
总结：用索引字段做为条件进行修改时， 是否表锁的取决于这个索引字段能否确定记录唯一，当索引值对应记录不唯一，会进行锁表，相反则行锁。

如果有两个delete

kid1 与 kid2是索引字段
语句1 delete from table where  kid1=1 and kid2=2;
语句2 delete from table where  kid1=1 and kid2=3;
这样的两个delete 是不会锁表的
语句1 delete from table where  kid1=1 and kid2=2;
语句2 delete from table where  kid1=1 ;
这样的两个delete 会锁表
总结：同一个表，如果进行删除操作时，尽量让删除条件统一，否则会相互影响造成锁表

Mysql中的MVCC：参考   http://blog.csdn.net/chen77716/article/details/6742128#comments

个人总结：

即MVCC一致性度是在事务启动时，获取当前活跃事务列表。

然后对该事务中的select来说，其可以读取到在该事务之前已经提交的并且不再活跃事务列表中的的记录。

而对于在其之后提交的，或者在活跃事务列表中的，则需要从其UNDO log中获取一个可用的历史版本。

 

如果某个事务1在更新某行（排他锁），则其他事物2无论是读还是写本行都必须等待；

这句话应该是错误的

事务2如果是只读的事务不会等待，读得到的是事务1未提交的行快照版本

简单的select操作，属于快照读，不加锁

数据库的四种隔离级别：

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

√: 可能出现    ×:不会出现

                                   脏读   不可重复读  幻读

Read uncommitted  √               √           √

Read committed       ×              √             √

Repeatable read       ×              ×            √

Serializable                 ×              ×           ×

 

多事物并发的隔离级别.....................

 

Read uncommitted :读未提交

公司发工资了，领导把5000元打到singo的账号上，但是该事务并未提交，而singo正好去查看账户，发现工资已经到账，是5000元整，非常高兴。可是不幸的是，领导发现发给singo的工资金额不对，是2000元，于是迅速回滚了事务，修改金额后，将事务提交，最后singo实际的工资只有2000元，singo空欢喜一场。

出现上述情况，即我们所说的脏读，两个并发的事务，“事务A：领导给singo发工资”、“事务B：singo查询工资账户”，事务B读取了事务A尚未提交的数据。

当隔离级别设置为Read uncommitted时，就可能出现脏读，如何避免脏读

 

Read committed :比如不能读取其它已由其它事务修改但是没有提交的数据.

singo拿着工资卡去消费，系统读取到卡里确实有2000元，而此时她的老婆也正好在网上转账，把singo工资卡的2000元转到另一账户，并在singo之前提交了事务，当singo扣款时，系统检查到singo的工资卡已经没有钱，扣款失败，singo十分纳闷，明明卡里有钱，为何......

出现上述情况，即我们所说的不可重复读，两个并发的事务，“事务A：singo消费”、“事务B：singo的老婆网上转账”，事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。

当隔离级别设置为Read committed时，避免了脏读，但是可能会造成不可重复读。

大多数数据库的默认级别就是Read committed，比如Sql Server , Oracle

 

Repeatable read :比如不能读取其它已由其它事务修改但是没有提交的数据，不允许其它事务在当前事务完成修改之前修改由当前事务读取的数据。Mysql的默认隔离级别。

 

Serializable:比如不能读取其它已由其它事务修改但是没有提交的数据，不允许其它事务在当前事务完成修改之前修改由当前事务读取的数据，不允许其它事务在当前事务完成修改之前插入新的行。

 

mysql-Innodb事务隔离级别-repeatable read详解

http://blog.csdn.net/dong976209075/article/details/8802778  

相关概念：

mysql中MyISAM锁机制：http://www.cnblogs.com/donknap/archive/2010/01/27/1657373.html

排它锁和共享锁：

并发一致性问题的解决办法

1 封锁（Locking）
    封锁是实现并发控制的一个非常重要的技术。所谓封锁就是事务T在对某个数据对象例如表、记录等操作之前，先向系统发出请求，对其加锁。加锁后事务T就对该 数据对象有了一定的控制，在事务T释放它的锁之前，其它的事务不能更新此数据对象。 基本的封锁类型有两种：排它锁（Exclusive locks简记为X锁）和共享锁（Share locks简记为S锁）。
    排它锁又称为写锁。若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。这就保证了其它事务在T释放A上的锁之前不能再读取和修改A。
    共享锁又称为读锁。若事务T对数据对象A加上S锁，则其它事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其它事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

 

悲观所和乐观所:

悲观锁(Pessimistic Lock), 顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

 

乐观锁(Optimistic Lock), 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

 

两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

封锁协议：

                                       X锁        S锁         一致性性保证

                        操作结束  事务结束      操作结束  事务结束        不丢失    不读脏   可重复读

                          时释放    时释放            时释放    时释放                    修改     数据

一级封锁协议                    √                                                                      √

二级封锁协议                     √                          √                                          √         √

三级封锁协议                      √                                       √                            √         √        √

 

在 运用X锁和S锁这两种基本封锁，对数据对象加锁时，还需要约定一些规则，例如应何时申请X锁或S锁、持锁时间、何时释放等。我们称这些规则为封锁协议 （Locking Protocol）。对封锁方式规定不同的规则，就形成了各种不同的封锁协议。下面介绍三级封锁协议。三级封锁协议分别在不同程度上解决了丢失的修改、不 可重复读和读"脏"数据等不一致性问题，为并发操作的正确调度提供一定的保证。下面只给出三级封锁协议的定义，不再做过多探讨。
    1 级封锁协议是：事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放。事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）。1级封锁协议可防止丢失修改，并保证事务T是可恢复的。在1级封锁协议中，如果仅仅是读数据不对其进行修改，是不需要加锁的，所以它不能保证可重复读和不 读"脏"数据。
    2级封锁协议是：1级封锁协议加上事务T在读取数据R之前必须先对其加S锁，读完后即可释放S锁。2级封锁协议除防止了丢失修改，还可进一步防止读"脏"数据。
    3级封锁协议是：1级封锁协议加上事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。3级封锁协议除防止了丢失修改和不读'脏'数据外，还进一步防止了不可重复读。

 

参考内容：

你要看一下关注点在哪里，一级封锁协议的关注点是事务修改的数据，二级和三级是事务读取的数据

所有的数据库都支持一级、二级封锁协议，至于三级封锁协议就不一定了，大名鼎鼎的Orcle好称最快查询

速度是牺牲三级封锁协议来完成的，但是它也支持三级封锁协议，只是用回退段来实现。所以Orcle如果

回退段满了，就不支持三级封锁协议了，会报快照太旧。而DB2就在数据库级别

实现了三级封锁协议。

在一个事务中

BEGIN TRANSACTION；

Update T set A='bb' where Id=1000

-->0

Selelct * form T where Id=1000

-->1

Sleep(1000)

COMMIT；

-->2

一级封锁协议在-->0起作用，直到事务完成释放，如果此时另一进程也要修改T表Id=1000的数据，则需要等待

二级封锁协议在-->1处就释放锁了，如果此时另一进程查询也查询Selelct * form T where Id=1000，则查询立刻返回结果

如果此时数据库加的是三级封锁协议，此时另一进程查询也查询Selelct * form T where Id=1000，则需要等待，直到-->2处才返回结果

三级封锁协议虽然避免了不可重复读，但是会造成查询的效率变低。我个人猜想Oralce就是当初设计时认为用到三级封锁协议的情况不多，所以在数据库级别不支持三级封锁协议，让查询效率最大化。然后用回退段来间接支持三级封锁协议的情况