本文共 18423 字,大约阅读时间需要 61 分钟。
ACID特性。
多个事务读取可能会遇到以下问题:
SQL标准定义了四个隔离级别:
InnoDB在可重复读隔离级别下是如何避免幻读的?
innodb的默认事务隔离级别是rr(可重复读)。它的实现技术是mvcc。基于版本的控制协议。该技术不仅可以保证innodb的可重复读,而且可以防止幻读。但是它防止的是快照读,也就是读取的数据虽然是一致的,但是数据是历史数据。如何做到保证数据是一致的(也就是一个事务,其内部读取对应某一个数据的时候,数据都是一样的),同时读取的数据是最新的数据。innodb提供了一个间隙锁的技术。也就是结合grap锁与行锁,达到最终目的。当使用索引进行插入的时候,innodb会将当前的节点和上一个节点加锁。这样当进行select的时候,就不允许加x锁。那么在进行该事务的时候,读取的就是最新的数据
间隙锁(Gap Lock )
**Gap Lock的唯一目的就是阻止其他事务插入到间隙中。**Gap Lock可以同时存在,不同的事务可以同时获取相同的Gap Lock,并不会互相冲突。
next-key Lock
Record Lock+Gap Lock,如果一个事务在记录R上的某个索引有共享/互斥锁,也会对其前面一个范围加锁
根据索引会形成一个个左开右闭的一个区间,根据查询的条件其所在的区间,并且包括其后的区间。
间隙锁的目的是为了防止幻读,其主要通过两个方面实现这个目的:
(1)防止间隙内有新数据被插入 (2)防止已存在的数据,更新成间隙内的数据
为什么说InnoDB的可重复读避免了“幻读”是不准确的?
首先看看什么是幻读
幻读。解决了不重复读,保证了同一个事务里,查询的结果都是事务开始时的状态(一致性)。但是,如果另一个事务同时提交了新数据,本事务再更新时,就会“惊奇的”发现了这些新数据,貌似之前读到的数据是“鬼影”一样的幻觉。在mysql中,提供了两种事务隔离技术,**第一个是mvcc,第二个是next-key技术。**这个在使用不同的语句的时候可以动态选择。不加lock inshare mode之类的快照读就使用mvcc。否则 当前读使用next-key。mvcc的优势是不加锁,并发性高。缺点是不是实时数据。next-key的优势是获取实时数据,但是需要加锁。
因为默认的查询语句是快照读,而要避免幻读需要使用当前读。可以通过使用当前读来规避幻读问题。
当前读和快照读
- **快照读:**最简单的select操作,属于快照读,不加锁 select * from table where id = 1;
- **当前读:**特殊的读与增删改操作,属于当前读,会读取数据库原本的数据,加锁 select * from table where xxx lock in share mode; (共享锁) select * from table where xxx for update; insert into table values() update table set xxx where xxx delete form table where xxx
以上语句,都属于当前读,读取记录的最新版本。并且,读取之后,还需要保证其他并发事务不能修改当前记录,对读取记录加锁。其中,除了第一条语句,对读取记录加S锁 (共享锁)外,其他的操作,都加的是X锁 (排它锁)。
实例:
在数据库中存在user表并且有4条数据
1.select快照读(照片):
当你执行select *之后,在A与B事务中都会返回4条一样的数据,这是不用想的,当执行select的时候,innodb默认会执行快照读,相当于就是给你目前的状态找了一张照片,以后执行select 的时候就会返回当前照片里面的数据,当其他事务提交了也对你不造成影响,和你没关系,这就实现了可重复读了,那这个照片是什么时候生成的呢?不是开启事务的时候,是当你第一次执行select的时候,也就是说,当A开启了事务,然后没有执行任何操作,这时候B insert了一条数据然后commit,这时候A执行 select,那么返回的数据中就会有B添加的那条数据……之后无论再有其他事务commit都没有关系,因为照片已经生成了,而且不会再生成了,以后都会参考这张照片。
update、insert、delete 当前读
当你执行这几个操作的时候默认会执行当前读,也就是会读取最新的记录,也就是别的事务提交的数据你也可以看到,这样很好理解啊,假设你要update一个记录,另一个事务已经delete这条数据并且commit了,这样不是会产生冲突吗,所以你update的时候肯定要知道最新的信息啊。在这里介绍一下update的过程吧,首先会执行当前读,然后把返回的数据加锁,之后执行update。加锁是防止别的事务在这个时候对这条记录做什么,默认加的是排他锁,也就是你读都不可以,这样就可以保证数据不会出错了。但注意一点,就算你这里加了写锁,别的事务也还是能访问的,是不是很奇怪?数据库采取了一致性非锁定读,别的事务会去读取一个快照数据。
innodb默认隔离级别是RR, 是通过MVVC来实现了,读方式有两种,执行select的时候是快照读,其余是当前读,所以,mvvc不能根本上解决幻读的情况MVCC
MySQL InnoDB存储引擎 ,实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) (注:与MVCC相对的,是基于锁的并发控制,Lock-Based Concurrency Control)。MVCC最大的好处:读不加锁,读写不冲突。 在MVCC并发控制中,读操作可以分成两类:快照读 (snapshot read)与当前读 (current read)。快照读,读取的是记录的可见版本 (有可能是历史版本),不用加锁。当前读,读取的是记录的最新版本,并且,当前读返回的记录,都会加上锁,保证其他事务不会再并发修改这条记录。
在mysql中,提供了两种事务隔离技术,第一个是mvcc,第二个是next-key技术。这个在使用不同的语句的时候可以动态选择。不加lock inshare mode之类的就使用mvcc。否则使用next-key。mvcc的优势是不加锁,并发性高。缺点是不是实时数据。next-key的优势是获取实时数据,但是需要加锁。同时需要注意几点:1.事务的快照时间点是以第一个select来确认的。所以即便事务先开始。但是select在后面的事务的update之类的语句后进行,那么它是可以获取后面的事务的对应的数据。2.mysql中数据的存放还是会通过版本记录一系列的历史数据,这样,可以根据版本查找数据。
四种隔离级别(由低到高)
Read Uncommitted读未提交:可以看到其它事务未提交的内容;
Read Commited读已提交:可以看到其它事务已提交的内容;
Repeatable Read可重复读:事务开始时和事务结束时读到的数据完全相同;
Serializable串行:事务必须逐步执行,后来的会排队。
隔离级别下的问题
前提条件:同时开启A和B两个事务;
脏读:
事务查询id为1的数据,num字段为1
B事务将id为1的数据num更新为2,但未commit 事务查询id为1的数据,num字段变为2 B回滚,则A读到的数据为脏数据不可重复读:
事务查询id为1的数据,num字段为1
B事务将id为1的数据num更新为2,commit 事务查询id为1的数据,num字段变为2 A事务前后两次读到的同一条记录num字段不同,不可重复读幻读:
A事务查询id >=1 and id <=3的数据,得到id=1和id=3两条数据(注意:没有id=2的数据)
B事务插入id=2的数据 A事务再查询id >=1 and id <=3的数据,发现多了一条id=2的数据,即两次查询数据的行数不同各种隔离模式下会出现的问题
读未提交:脏读、不可重复读、幻读;
读已提交:不可重复读、幻读;
可重复读:幻读;
间隙锁(gap-key)
在一个事务中select for update查询某条记录,会锁定该条记录的前后空行,什么叫空行呢,看个例子就知道了
id num
1 2 2 4 3 6 4 7 执行select * from table where num = 4 for updateinsert into table (’’, 3)和insert into table (’’, 5)都会被阻塞,即4前后的空行都无法插入
next-key
由于gay-key只能锁定记录之间的间隙,但是我们上面查询num=4的行也不能被更改,索引该行也会被加行锁,此时这种既能加行锁,又能加间隙锁的,称之为next-key。
MVCC
事务A:查询num>=2 and num<=4的记录,但是不加for update!不加for update!不加for update!
事务B:insert into table (’’, 3),不会被阻塞,不会被阻塞,不会被阻塞!然后commit 事务A:查询num>=2 and num<=4的记录,和之前查询相同 事务A:提交 事务A:查询num>=2 and num<=4的记录,可以查询到num=3的记录,此时返回3条结果 原理:假如事务A在开启的时候版本号为2,当更改或者插入数据后,该条记录的版本号+1,也就是说事务B插入num=3的记录的版本号为3,事务A在提交前的所有select都只能查询到版本号<=2的记录,也就是说不会产生上面说的幻读。MVCC和next-key
其实,innodb采用next-key + MVCC去解决幻读问题的:
在查询加for update时,会用next-key解决幻读问题,新的insert和update会阻塞
在查询不加for update时,会用MVCC解决幻读问题,新的insert和update不会阻塞事务
ACID;
隔离级别;
不同隔离级别下的问题;
InnoDB如何避免幻读?
快照读:不加锁 mvcc的机制
当前读:加锁 next-key的机制
MyISAM与InnoDB关于锁方面的区别是什么?
MyISAM和InnoDB适合的场景有哪些?
MyIsam和InnoDB引擎的区别:
(事务、锁、索引三方面来)
MyISAM索引和InnoDB索引实现:
(1) MyISAM索引实现:
非聚集性索引
a.主键索引
MyISAM引擎使用B+树作为索引结果,叶子结点的data域存放的是数据记录的地址。下图为MyISAM表的主索引,Col1为主键。
b.辅助索引
在MyISAM中,主索引和辅助索引在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。下图在Col2上建议一个辅助索引。
同样是一棵B+树,data域保存数据记录的地址。因此,MYISAM中索引检索的算法为首先按照B+ Tree搜索算法搜索索引,如果指定的key存在,则取出其data域的值,然后 以data域的值为地址,读取相应数据记录。
(2) InnoDB
聚集性索引
a.主键索引
同样是B+树,实现方式却完全不同。InnoDB表数据文件本身就是一个索引结构,树的叶节点data域保存了完整的数据记录,这种索引叫做聚集索引。
因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则mysql会自动选择一个可以唯一标识数据记录的列作为主键。如果不存在这种列,则mysql自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整型。
b.辅助索引
nnoDB的所有辅助索引都引用主键作为data域。下图为定义在Col3上的一个辅助索引
因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过,它的辅助索引也会包含主键列,所以如果主键定义的比较大,其他索引也将很大。InnoDB 不会压缩索引。
聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。
不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。
MyIsam和InooDB应用场景
MyISAM:以读写插为主的应用程序,比如博客系统、新闻门户网站;
Innodb:更新(删除)操作频率也高,或者要保证数据的完整性;并发量高、支持事务和外键。比如OA自动化办公系统。
如何选择MyISam和InnoDB引擎?
InnoDB为什么推荐使用自增ID作为主键?
自增ID可以保证每次插入时B+索引是从右边扩展的,可以避免B+树和频繁合并和分裂(对比使用UUID)。如果使用字符串主键和随机主键,会使得数据随机插入,效率比较差。
INNODB引擎的4大特性
插入缓冲 二次写 自适应哈希索引 预读
索引的灵感来自字典。主键、唯一键、普通键等只要有能够区分信息的都可以成为索引。
索引 | 区别 |
---|---|
Hash | hash索引,等值查询效率高,不能排序,不能进行范围查询 |
B+ | 数据有序,范围查询 |
B+树索引和哈希索引的明显区别是:
Hash索引对比B树的索引最大问题就是:只能等值查询效率高;而对于数据的范围查询不可以,而且没法利用索引完成排序;
聚集索引和非聚集索引的区别:
索引 | 区别 |
---|---|
聚集索引 | 数据按索引顺序存储,叶子结点中存储了真实的物理数据 |
非聚集索引 | 叶子结点中存储指向真实数据行的指针 |
密集索引和稀疏索引的区别:
InnoDB:
索引的好处:提高查询速度;
索引的坏处:更新数据时效率低,因为要同时更新索引;
对数据进行频繁查询时建立索引,如果要频繁更改数据不建议使用索引。
在Mysql中,无论是Innodb还是MyISAM引擎,都使用了B+树做索引结构(这里先不考虑Hash索引)。那么我们从最普通的二叉树开始,从而说明Mysql为什么选择B+树作为索引结构。
(1) 二叉查找树
二叉查找树(BST,binary search Tree)也叫二叉排序树,在二叉树的基础上满足:任意结点的左子树上的所有结点值不大于根节点的值,任意结点的右子树上所有结点值不小于根节点的值。
但如果采用二叉查找树作为索引,并且把id作为索引且id自增,那么二叉查找树就变成了一个单支树,相当于链表查询。即BST可能长歪而变得不平衡了
(2) 平衡二叉树
为了解决上述二叉搜索树的问题,引入了平衡得到二叉树。
AVL树是严格的平衡二叉树,所有节点的左右子树高度差不能超过1;AVL树查找、插入和删除在平均和最坏的情况下都是O(logn).
AVL实现平衡的关键在于旋转操作:插入和删除可能破坏二叉树的平衡,此时需要通过一次或多次树旋转来重新平衡这棵树。当插入数据时,最多只需要1次旋转(单旋转或双旋转);但是当删除数据时,会导致树失衡,AVL需要维护从被删除节点到根节点这条路径上所有节点的平衡,旋转的量级为O(lgn)。
由于旋转的耗时,AVL树在删除数据时效率很低。 在删除操作较多时,维护平衡所需的代码可能高于其带来的好处,因此AVL实际应用并不广泛。
(3)红黑树
与AVL树相比,红黑树并不追求严格的平衡,而是大致的平衡:只是确保从根到叶子的最长的可能路径不多于 最短的可能路径的两倍长。 实现上遵守以下规则:
与AVL树相比 ,红黑树丶查询效率会有所下降 ,这是因为树的平衡性变差,高度更高了。但是红黑树的删除效率大大提高了。此外因为红黑树同时引入了颜色,当插入或删除数据时,只需要进行O(1)次数的旋转以及变色就能保证基本的平衡,不需要像AVL树进行O(logn)次数的旋转。
当然对于数据在内存中的情况如(TreeMap和HashMap),红黑树的表现是非常优异的。但是对于数据在磁盘等辅助存储设备中的情况(如Mysql等数据库),红黑树还是并不擅长,因为红黑树还是有点高。因为当数据在磁盘中,磁盘IO会成为最大的性能瓶颈,设计的目标应该是尽量减少IO次数;而树的高度越高,增删改查所需要的IO次数也越多,会严重影响性能。
(4)B树
B树也被成为B-树,是为磁盘等辅存设备设计的多路平衡查找树,与二叉树相比,B树的每个非叶节点可以有多个子树。因此,当总节点数量相同时,B树的高度远远小于AVL树和红黑树,磁盘IO次数大大减少。
对于一棵m阶B树,需要满足以下条件:
B树的优势除了树高小,还有对访问局部性原理的利用。所谓局部性原理,是指当一个数据被使用时,其附近的数据有较大概率在短时间内被使用。B树将键相近的数据存储在同一个节点,当访问其中某个数据时,数据库会将该整个节点读到缓存中;当它临近的数据紧接着被访问时,可以直接在缓存中读取,无需进行磁盘IO;换句话说,B树的缓存命中率更高。
(5)B+树
B+树为B树的一种变形,m阶B+树有如下性质:
B+树也是多路平衡查找树,其与B树的区别在于:
优势:
总结:
- 更少的IO次数;
- 更适用于范围查询;
- 更稳定的查询效果;
面试题目:
问题一、Mysql中存储索引用到的数据结构是B+树,B+树的查询时间跟树的高度有关,是log(n),如果是用hash存储,那么查询时间是O(1).既然hash比B+树更快,为什么mysql用B+树来存储索引?
答案:
Hash索引的缺点:
是否还有别的结构呢?BitMap索引。
问题二、为什么不用红黑树或者二叉排序树?
答案:树的查询时间跟树的高度有关,B+树是一棵多路搜索树可以降低树的高度,提高查询后啊效率。
问题3:既然增加树的路数可以降低树的高度,那么无限增加树的路数是不是可以有最优的查找效率?
答:这样会形成一个有序数组,文件系统和数据库的索引都是存在硬盘上的,并且如果数据量大的话,不一定能一次性加载到内存中。有序数组没法一次性加载进内存,这时候B+树的多路存储威力就出来了,可以每次加载B+树的一个结点,然后一步步往下找,
问题4:在内存中,红黑树比B树更优,但是涉及到磁盘操作B树就更优了,那么你能讲讲B+树吗?
B+树是在B树的基础上进行改造,它的数据都在叶子结点,同时叶子结点之间还加了指针形成链表。
问题5:为什么B+树要这样设计?
答:这个跟它的使用场景有关,B+树在数据库的索引中用得比较多,数据库中select数据,不一定只选一条,很多时候会选中多条,比如按照id进行排序后选100条。如果是多条的话,B-树需要做局部的中序遍历,可能要跨层访问。而B+树由于所有数据都在叶子结点不用跨层,同时由于有链表结构,只需要找到首尾,通过链表就能把所有数据取出来了。
问题6:数据库索引为什么要用b+树而不用红黑树?
AVL 树和红黑树这些二叉树结构的数据结构可以达到最高的查询效率这是毋庸置疑的。
那么,数据库索引为什么不用AVL树或者红黑树呢?
AVL 数和红黑树基本都是存储在内存中才会使用的数据结构,那磁盘中会有什么不同呢?
这就要牵扯到磁盘的存储原理了
操作系统读写磁盘的基本单位是扇区,而文件系统的基本单位是簇(Cluster)。
也就是说,磁盘读写有一个最少内容的限制,即使我们只需要这个簇上的一个字节的内容,我们也要含着泪把一整个簇上的内容读完。
那么,现在问题就来了
一个父节点只有 2 个子节点,并不能填满一个簇上的所有内容啊?那多余的内容岂不是要浪费了?我们怎么才能把浪费的这部分内容利用起来呢?哈哈,答案就是 B+ 树。
由于 B+ 树分支比二叉树更多,所以相同数量的内容,B+ 树的深度更浅,深度代表什么?代表磁盘 io 次数啊!数据库设计的时候 B+ 树有多少个分支都是按照磁盘一个簇上最多能放多少节点设计的啊!
B树的特点:
B+树的特点:
索引查找过程中就要产生磁盘I/O消耗,主要看IO次数,和磁盘存取原理有关。
根据B-Tree的定义,可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理, 将一个节点的大小设为等于一个页,这样每个节点只需要一次I/O就可以完全载入 局部性原理与磁盘预读具体场景具体分析,只提出大致思路
根据慢日志定位慢查询sql;
show variables like '%quer%'
;打开show_query_log为ON;即set global slow_query_log=on
和set global long_query_time=1
使用explain等工具分析sql;
explain
对sql语句分析。其中有两列最重要,一个是type
; 一个是extra
. 修改sql或者尽量让sql走索引;
select account from person_info_large order by account desc
;alter table person_info_large add index idx_name(name);
最左优先,在检索数据时从联合索引的最左边开始匹配。以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
最左前缀匹配原则:
在Mysql建立联合索引时会遵守最左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
例子解释:
索引的底层是一颗B+树,那么联合索引当然还是一颗B+树,只不过联合索引的健值数量不是一个,而是多个。构建一颗B+树只能根据一个值来构建,因此数据库依据联合索引最左的字段来构建B+树。
例子:假如创建一个(a,b)的联合索引,那么它的索引树是这样的
可以看到a的值是有顺序的,1,1,2,2,3,3,而b的值是没有顺序的1,2,1,4,1,2。所以b = 2这种查询条件没有办法利用索引,因为联合索引首先是按a排序的,b是无序的。
同时我们还可以发现在a值相等的情况下,b值又是按顺序排列的,但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止,剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引,因为在a值确定的情况下b是相对有序的,而a>1and b=2,a字段可以匹配上索引,但b值不可以,因为a的值是一个范围,在这个范围中b是无序的。
索引类型 | 概念 |
---|---|
普通索引 | 最基本的索引,没有任何限制 |
唯一索引 | 与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。 |
主键索引 | 它是一种特殊的唯一索引,不允许有空值。 |
全文索引 | 针对较大的数据,生成全文索引很耗时好空间。 |
组合索引 | 为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则 |
复制方式 | 操作 |
---|---|
异步复制 | 默认异步复制,容易造成主库数据和从库不一致,一个数据库为Master, 一个数据库为slave,通过Binlog日志,slave两个线程,一个线程去读master binlog日志,写到自己的中继日志;一个线程解析日志,执行sql, master启动一个线程,给slave传递binlog日志 |
半同步复制 | 只有把master发送的binlog日志写到slave的中继日志,这时主库,才返回操作完成的反馈,性能有一定降低 |
并行操作 | slave 多个线程去请求binlog日志 |
设置参数,开启慢日志功能,得到耗时超过一定时间的sql语句。
类型 | 使用场景 |
---|---|
varchar | 字符长度经常变的 |
char | 用字符长度固定的 |
读写分离,读从库,写主库
spring配置两个数据库,通过AOP(面向切面编程),在写或读方法前面进行判断得到动态切换数据源。级别 | 概念 |
---|---|
1NF | 属性不可分 |
2NF | 非主键属性,完全依赖于主键属性 |
3NF | 非主键属性无传递依赖 |
关系型数据库
优点
缺点
非关系型数据库
优点
缺点
以A,B两张表为例
A left join B 选出A的所有记录,B表中没有的以null 代替 right join 同理inner join
A,B有交集的记录cross join (笛卡尔积)
A中的每一条记录和B中的每一条记录生成一条记录 例如A中有4条,B中有4条,cross join 就有16条记录当多个用户同时对数据库并发操作时,会带来数据不一致的问题。所以,锁主要用于多用户环境下保证数据库完整性和一致性。
数据库锁出现的目的:处理并发问题。
数据库锁的分类:
悲观锁(Pessimistic Lock):正如其名,具有强烈的独占和排他特性。它指的是对数据被外界(包括本系统当前的其他事务,以及来自外部系统的事务处理)修改持保守态度,因此,在整个数据处理过程中,将数据处于锁定状态。悲观锁的实现,往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据)。
悲观锁按照使用性质可划分为:共享锁、排他锁和更新锁。
S锁,也叫读锁,用于所有的只读数据操作。共享锁是非独占的,允许多个并发事务读取器锁定的资源。
性质
在SQL Server中,默认情况下,数据被读取后,立即释放共享锁。
例如,执行查询语句“SELECT * FROM my_table”时,首先锁定第一页,读取之后,释放对第一页的锁定,然后锁定第二页。这样,就允许在读操作过程中,修改未被锁定的第一页。 例如,语句“SELECT * FROM my_table HOLDLOCK”就要求在整个查询过程中,保持对表的锁定,直到查询完成才释放锁定。
X锁,也叫写锁,表示对数据进行写操作。如果一个事务对对象加了排他锁,其他事务就不能再给它加任何锁了。(某个顾客把试衣间从里面反锁了,其他顾客想要使用这个试衣间,就只有等待锁从里面打开了。)
性质
产生排他锁的SQL语句如下:select * from ad_plan for update;
U锁,在修改操作的初始化阶段用来锁定可能要被修改的资源,这样可以避免使用共享锁造成的死锁现象。
因为当使用共享锁时,修改数据的操作分为两步:
1.首先获得一个共享锁,读取数据, 2.然后将共享锁升级为排他锁,再执行修改操作。 这样如果有两个或多个事务同时对一个事务申请了共享锁,在修改数据时,这些事务都要将共享锁升级为排他锁。这时,这些事务都不会释放共享锁,而是一直等待对方释放,这样就造成了死锁。 如果一个数据在修改前直接申请更新锁,在数据修改时再升级为排他锁,就可以避免死锁。
性质
悲观锁按作用范围划分为:行锁和表锁。
锁的作用范围是行级别。
锁的作用范围是整张表。
数据库能够确定那些行需要锁的情况下使用行锁,如果不知道会影响哪些行的时候就会使用表锁。
举个例子,一个用户表user,有主键id和用户生日birthday。
当你使用update … where id=?这样的语句时,数据库明确知道会影响哪一行,它就会使用行锁; 当你使用update … where birthday=?这样的的语句时,因为事先不知道会影响哪些行就可能会使用表锁。
顾名思义,就是很乐观,每次去拿数据的时候都认为别人不会修改,所以,不会上锁。但是在更新的时候会判断一下在此期间别人有没有更新这个数据,可以使用版本号等机制。
乐观锁( Optimistic Locking ): 相对悲观锁而言,乐观锁机制采取了更加宽松的加锁机制。
悲观锁大多数情况下依靠数据库的锁机制实现,以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销,特别是对长事务而言,这样的开销往往无法承受。而乐观锁机制在一定程度上解决了这个问题。 乐观锁,大多是基于数据版本( Version )记录机制实现。 数据版本:为数据增加一个版本标识,在基于数据库表的版本解决方案中,一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时,将此版本号一同读出,之后更新时,对此版本号加一。此时,将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对,如果提交的数据版本号大于数据库表当前版本号,则予以更新,否则认为是过期数据。
乐观锁适用于多读的应用类型,这样可以提高吞吐量,像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。
版本号(记为version):就是给数据增加一个版本标识,在数据库上就是表中增加一个version字段,每次更新把这个字段加1,读取数据的时候把version读出来,更新的时候比较version,如果还是开始读取的version就可以更新了,如果现在的version比老的version大,说明有其他事务更新了该数据,并增加了版本号,这时候得到一个无法更新的通知,用户自行根据这个通知来决定怎么处理,比如重新开始一遍。这里的关键是判断version和更新两个动作需要作为一个原子单元执行,否则在你判断可以更新以后正式更新之前有别的事务修改了version,这个时候你再去更新就可能会覆盖前一个事务做的更新,造成第二类丢失更新,所以你可以使用update … where … and version=”old version”这样的语句,根据返回结果是0还是非0来得到通知,如果是0说明更新没有成功,因为version被改了,如果返回非0说明更新成功。
时间戳(使用数据库服务器的时间戳)
时间戳(timestamp):和版本号基本一样,只是通过时间戳来判断而已,注意时间戳要使用数据库服务器的时间戳不能是业务系统的时间。
待更新字段
待更新字段:和版本号方式相似,只是不增加额外字段,直接使用有效数据字段做版本控制信息,因为有时候我们可能无法改变旧系统的数据库表结构。假设有个待更新字段叫count,先去读取这个count,更新的时候去比较数据库中count的值是不是我期望的值(即开始读的值),如果是就把我修改的count的值更新到该字段,否则更新失败。java的基本类型的原子类型对象如AtomicInteger就是这种思想。
所有字段
所有字段:和待更新字段类似,只是使用所有字段做版本控制信息,只有所有字段都没变化才会执行更新。
死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方的资源,从而导致恶性循环的现象。
常见的解决死锁的方法
如果业务处理不好可以用分布式事务锁或者使用乐观锁
最左匹配原则是针对索引的
举例来说:两个字段(name,age)建立联合索引,如果where age=12这样的话,是没有利用到索引的, 这里我们可以简单的理解为先是对name字段的值排序,然后对age的数据排序,如果直接查age的话,这时就没有利用到索引了, 查询条件where name=‘xxx’ and age=xx 这时的话,就利用到索引了,再来思考下where age=xx and name=’xxx‘ 这个sql会利用索引吗, 按照正常的原则来讲是不会利用到的,但是优化器会进行优化,把位置交换下。这个sql也能利用到索引了# 查询所有同学的学号、选课数、总成绩select student_id, count(course_id), sum(score) from score group by student_id
# 查询所有同学的学号、姓名、选课数、总成绩select s.student_id, stud.name, count(s.course_id),sum(s.score) from score s, student stuwhere s.student_id = stu.student_idgroup by s.student_id;
# 查询平均成绩大于60分的同学的学号和平均成绩select student_id,avg(score)from scoregroup by student_idhaving avg(score) > 60
# 查询没有学全所有课的同学的学号、姓名select stu.student_id, stu.namefrom student stu,score swhere stu.student_id = s.student_idgroup by s.student_idhaving count(*) < (select count(*) from course)
转载地址:http://bcwdf.baihongyu.com/