排他锁和共享锁,数据库锁和事务

1、表锁和行锁

大部分电商业务使用的是事务性数据库,我们本文以mysql作为分析对象,数据库引擎为innodb,并结合常用spring框架结合起来分析数据库锁和事务在具体场景下怎样发挥作用。本文从以下几个方面讲述数据库锁和事务。

一、事务的隔离级别/锁问题

锁的基本原理

为了保证数据的完事性和一致性,数据库系统采用锁来实现事务的隔离性。各种大型数据库采用的锁基本理论是一致的,但在具体实现上各有差别。

从并发事务锁定的关系上看,可以分为共享锁定和独占锁定。从锁定的对象不同,一般可以分为表锁定和行锁定。

表锁和行锁锁的粒度不一样,表锁锁住的是一整张表,行锁锁住的是表中的一行数据。

1、数据库锁定义和类型

基本的介绍: 当我们的mysql表,被多个线程或者客户端同时操作时,mysql提供一种机制,可以让不同的事务在操作数据时,具有隔离性。

共享锁用于读取数据操作,它是非独占的,允许其他事务同时读取其锁定的资源,但不允许其他事务更新它。

独占锁也叫排他锁,适用于修改数据的场合。它所锁定的资源,其他事务不能读取也不能修改。

当一个事务访问某种数据库资源时,如果执行select语句,必须先获得共享锁,如果执行insert、update或delete语句,必须获得独占锁,这些锁用于锁定被操作的资源。

当第二个事务也要访问相同的资源时,如果执行select语句,也必须先获得共享锁,如果执行insert、update或delete语句,也必须获得独占锁。此时根据已经旋转在资源上的锁的类型,来决定第二个事务应该等待第一个事务解除对应资源的锁定,还是可以立刻获得锁。

资源上已经放置的锁

第二个事务进行读操作

第二个事务进行更新操作

立即获得共享锁

立即获得独占锁

共享锁

立即获得共享锁

等待第一个事务解除共享锁

独占锁

等待第一个事务解除独占锁

等待第一个事务解除独占锁

 

InnoDB使用的是行级锁,MyISAM使用的是表级锁。

首先简要介绍一下什么是锁,目前才多进程多线程执行都会存在并发问题,简单的说就是多个操作按照随意顺序进行进行相关处理,如果不加锁就会出现数据覆盖数据计算错误等问题,因此为了让操作有序进行需要加锁,例如java中lock,sychronized。数据库实现中为了防止并发问题使用了锁,但是对于数据库使用者来说我们关注的是什么情况下数据库会加锁,加了什么锁。

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁冲突也是影响数据库并发访问性能的一个重要因素。MySQL不同的存储引擎支持不同的锁机制,如 MyISAMMEMORY 存储引擎采用表级锁BDB 采用页面锁,也支持表级锁;InnoDB**既支持行级锁也支持表级锁,默认采用行级锁**;

1 共享锁

1、加锁的条件:当一个事务执行select语句时,数据库系统会为这个事务分配一把共享锁,来锁定被查询的数据。

2、解锁的条件:在默认情况下,数据被读取后,数据库系统立即解除共享锁。例如,当一个事务执行查询“SELECT * FROM accounts”语句时,数据库系统首先锁定第一行,读取之后,解除对第一行的锁定,然后锁定第二行。这样,在一个事务读操作过程中,允许其他事务同时更新accounts表中未锁定的行。

3、与其他锁的兼容性:如果数据资源上放置了共享锁,还能再放置共享锁和更新锁。

4、并发性能:具有良好的并发性能,当数据被放置共享锁后,还可以再放置共享锁或更新锁。所以并发性能很好。 

注意:在InnoDB中,例如模糊查询select * from tb where name like 'lin%'的时候也会锁住一整张表。

相对其他数据库而言,MySQL的锁机制比较简单,其最 显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。表级锁开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。行级锁开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。 

表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低;

2 独占锁

1、加锁的条件:当一个事务执行insert、update或delete语句时,数据库系统会自动对SQL语句操纵的数据资源使用独占锁。如果该数据资源已经有其他锁(任何锁)存在时,就无法对其再放置独占锁了。

2、解锁的条件:独占锁需要等到事务结束才能被解除。

3、兼容性:独占锁不能和其他锁兼容,如果数据资源上已经加了独占锁,就不能再放置其他的锁了。同样,如果数据资源上已经放置了其他锁,那么也就不能再放置独占锁了。

4、并发性能:不用说了,最差。只允许一个事务访问锁定的数据,如果其他事务也需要访问该数据,就必须等待,起到前一个事务结束,解除了独占锁,其他事务才有机会访问该数据。

2、共享锁和排他锁

(1) MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作 (UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预

行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高;

3 更新锁

更新锁在的初始化阶段用来锁定可能要被修改的资源,这可以避免使用共享锁造成的死锁现象。例如,对于以下的update语句:

UPDATE accounts SET balance=900 WHERE id=1

更新操作需要分两步:

l 读取accounts表中id为1的记录。

l 执行更新操作。

如果在第一步使用共享锁,再第二步把锁升级为独占锁,就可能出现死锁现象。例如:两个事务都获取了同一数据资源的共享锁,然后都要把锁升级为独占锁,但需要等待另一个事务解除共享锁才能升级为独占锁,这就造成了死锁。

更新锁有如下特征:

l 加锁的条件:当一个事务执行update语句时,数据库系统会先为事务分配一把更新锁。

l 解锁的条件:当读取数据完毕,执行更新操作时,会把更新锁升级为独占锁。

l 与其他锁的兼容性:更新锁与共享锁是兼容的,也就是说,一个资源可以同时放置更新锁和共享锁,但是最多放置一把更新锁。这样,当多个事务更新相同的数据时,只有一个事务能获得更新锁,然后再把更新锁升级为独占锁,其他事务必须等到前一个事务结束后,才能获取得更新锁,这就避免了死锁。

l 并发性能:允许多个事务同时读锁定的资源,但不允许其他事务修改它。

 

共享锁又称读锁(S锁),一个事务获取了共享锁,其他事务可以获取共享锁,不能获取排他锁,其他事务可以进行读操作,不能进行写操作。

(2) InnoDB实现了以下两种类型的行锁。

页面锁:开销和加锁时间介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并发度一般;

MySQL行级锁、表级锁、页级锁介绍

页级:引擎 BDB。
表级:引擎 MyISAM , 理解为锁住整个表,可以同时读,写不行
行级:引擎 INNODB , 单独的一行记录加锁

表级,直接锁定整张表,在你锁定期间,其它进程无法对该表进行写操作。如果你是写锁,则其它进程则读也不允许
行级,,仅对指定的记录进行加锁,这样其它进程还是可以对同一个表中的其它记录进行操作。
页级,表级锁速度快,但冲突多,行级冲突少,但速度慢。所以取了折衷的页级,一次锁定相邻的一组记录。

MySQL 5.1支持对MyISAM和MEMORY表进行表级锁定,对BDB表进行页级锁定,对InnoDB表进行行级锁定。
对WRITE,MySQL使用的表锁定方法原理如下:
如果在表上没有锁,在它上面放一个写锁。
否则,把锁定请求放在写锁定队列中。

对READ,MySQL使用的锁定方法原理如下:
如果在表上没有写锁定,把一个读锁定放在它上面
否则,把锁请求放在读锁定队列中。

InnoDB使用行锁定,BDB使用页锁定。对于这两种存储引擎,都可能存在死锁。这是因为,在SQL语句处理期间,InnoDB自动获得行锁定和BDB获得页锁定,而不是在事务启动时获得。

行级锁定的优点:
· 当在许多线程中访问不同的行时只存在少量锁定冲突。
· 回滚时只有少量的更改。
· 可以长时间锁定单一的行。

行级锁定的缺点:
· 比页级或表级锁定占用更多的内存。
· 当在表的大部分中使用时,比页级或表级锁定速度慢,因为你必须获取更多的锁。
· 如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表,比其它锁定明显慢很多。
· 用高级别锁定,通过支持不同的类型锁定,你也可以很容易地调节应用程序,因为其锁成本小于行级锁定。

在以下情况下,表锁定优先于页级或行级锁定:
· 表的大部分语句用于读取。
· 对严格的关键字进行读取和更新,你可以更新或删除可以用单一的读取的关键字来提取的一行:
· UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
· DELETE FROM tbl_name WHERE unique_key_col=key_value;
· SELECT 结合并行的INSERT语句,并且只有很少的UPDATE或DELETE语句。
· 在整个表上有许多扫描或GROUP BY操作,没有任何写操作。

/* ========================= mysql 锁表类型和解锁语句 ========================= */

如果想要在一个表上做大量的 INSERT 和 SELECT 操作,但是并行的插入却不可能时,可以将记录插入到临时表中,然后定期将临时表中的数据更新到实际的表里。可以用以下命令实现:

复制代码 代码如下:

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;

 

行级锁的优点有:
在很多线程请求不同记录时减少冲突锁。
事务回滚时减少改变数据。
使长时间对单独的一行记录加锁成为可能。

行级锁的缺点有:
比页级锁和表级锁消耗更多的内存。
锁是计算机协调多个进程或线程并发访问某一资源的机制,不同的数据库的锁机制大同小异。由于数据库资源是一种供许多用户共享的资源,所以如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。了解锁机制不仅可以使我们更有效的开发利用数据库资源,也使我们能够更好地维护数据库,从而提高数据库的性能。

MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。

例如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level-locking);BDB存储引擎采用的是页面锁(page-level-locking),同时也支持表级锁;InnoDB存储引擎既支持行级锁,也支持表级锁,默认情况下是采用行级锁。

上述三种锁的特性可大致归纳如下:
1) 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
2) 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
3) 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

三种锁各有各的特点,若仅从锁的角度来说,表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如WEB应用;行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。

MySQL表级锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。什么意思呢,就是说对MyISAM表进行读操作时,它不会阻塞其他用户对同一表的读请求,但会阻塞 对同一表的写操作;而对MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作。

MyISAM表的读和写是串行的,即在进行读操作时不能进行写操作,反之也是一样。但在一定条件下MyISAM表也支持查询和插入的操作的并发进行,其机制是通过控制一个系统变量(concurrent_insert)来进行的,当其值设置为0时,不允许并发插入;当其值设置为1 时,如果MyISAM表中没有空洞(即表中没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录;当其值设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。

MyISAM锁调度是如何实现的呢,这也是一个很关键的问题。例如,当一个进程请求某个MyISAM表的读锁,同时另一个进程也请求同一表的写锁,此时MySQL将会如优先处理进程呢?通过研究表明,写进程将先获得锁(即使读请求先到锁等待队列)。但这也造成一个很大的缺陷,即大量的写操作会造成查询操作很难获得读锁,从而可能造成永远阻塞。所幸我们可以通过一些设置来调节MyISAM的调度行为。我们可通过指定参数low-priority-updates,使MyISAM默认引擎给予读请求以优先的权利,设置其值为1(set low_priority_updates=1),使优先级降低。

InnoDB锁与MyISAM锁的最大不同在于:一是支持事务(TRANCSACTION),二是采用了行级锁。我们知道事务是由一组SQL语句组成的逻辑处理单元,其有四个属性(简称ACID属性),分别为:

原子性(Atomicity):事务是一个原子操作单元,其对数据的修改,要么全部执行,要么全都不执行;
一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态;
隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行;
持久性(Durable):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

InnoDB有两种模式的行锁:

1)共享锁:允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
( Select * from table_name where ......lock in share mode)

2)排他锁:允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和 排他写锁。(select * from table_name where.....for update)
为了允许行锁和表锁共存,实现多粒度锁机制;同时还有两种内部使用的意向锁(都是表锁),分别为意向共享锁和意向排他锁。
InnoDB行锁是通过给索引项加锁来实现的,即只有通过索引条件检索数据,InnoDB才使用行级锁,否则将使用表锁!

另外:插入,更新性能优化的几个重要参数

复制代码 代码如下:

bulk_insert_buffer_size
批量插入缓存大小, 这个参数是针对MyISAM存储引擎来说的.适用于在一次性插入100-1000 条记录时, 提高效率.默认值是8M.可以针对数据量的大小,翻倍增加.

 

concurrent_insert
并发插入, 当表没有空洞(删除过记录), 在某进程获取读锁的情况下,其他进程可以在表尾部进行插入.

值可以设0不允许并发插入, 1当表没有空洞时, 执行并发插入, 2不管是否有空洞都执行并发插入.
默认是1 针对表的删除频率来设置.

delay_key_write
针对MyISAM存储引擎,延迟更新索引.意思是说,update记录时,先将数据up到磁盘,但不up索引,将索引存在内存里,当表关闭时,将内存索引,写到磁盘. 值为 0不开启, 1开启. 默认开启.

delayed_insert_limit, delayed_insert_timeout, delayed_queue_size
延迟插入, 将数据先交给内存队列, 然后慢慢地插入.但是这些配置,不是所有的存储引擎都支持, 目前来看, 常用的InnoDB不支持, MyISAM支持. 根据实际情况调大, 一般默认够用了


/* ==================== MySQL InnoDB 锁表与锁行 ======================== */

 

由于InnoDB预设是Row-Level Lock,所以只有「明确」的指定主键,MySQL才会执行Row lock (只锁住被选取的资料例) ,否则MySQL将会执行Table Lock (将整个资料表单给锁住)。

举个例子: 假设有个表单products ,里面有id跟name二个栏位,id是主键。

例1: (明确指定主键,并且有此笔资料,row lock)

复制代码 代码如下:

SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

 

例2: (明确指定主键,若查无此笔资料,无lock)

复制代码 代码如下:

SELECT * FROM products WHERE id='-1' FOR UPDATE;

 

例3: (无主键,table lock)

复制代码 代码如下:

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

 

例4: (主键不明确,table lock)

复制代码 代码如下:

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

 

例5: (主键不明确,table lock)

复制代码 代码如下:

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

 

注1: FOR UPDATE仅适用于InnoDB,且必须在交易区块(BEGIN/COMMIT)中才能生效。
注2: 要测试锁定的状况,可以利用MySQL的Command Mode ,开二个视窗来做测试。

在MySql 5.0中测试确实是这样的

另外:MyAsim 只支持表级锁,InnerDB支持行级锁
添加了(行级锁/表级锁)锁的数据不能被其它事务再锁定,也不被其它事务修改(修改、删除)
是表级锁时,不管是否查询到记录,都会锁定表
此外,如果A与B都对表id进行查询但查询不到记录,则A与B在查询上不会进行row锁,但A与B都会获取排它锁,此时A再插入一条记录的话则会因为B已经有锁而处于等待中,此时B再插入一条同样的数据则会抛出Deadlock found when trying to get lock; try restarting transaction然后释放锁,此时A就获得了锁而插入成功

排他锁又称写锁(X锁),如果事务T对数据A加上排他锁后,则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据,又能修改数据。

共享锁(s):又称读锁。允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。

锁冲突:例如说事务A将某几行上锁后,事务B又对其上锁,锁不能共存否则会出现锁冲突。(但是共享锁可以共存,共享锁和排它锁不能共存,排它锁和排他锁也不可以)

对于insert、update、delete,InnoDB会自动给涉及的数据加排他锁(X);

排他锁(X):又称写锁。允许获取排他锁的事务更新数据,阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁。

死锁:例如说两个事务,事务A锁住了1~5行,同时事务B锁住了6~10行,此时事务A请求锁住6~10行,就会阻塞直到事务B施放6~10行的锁,而随后事务B又请求锁住1~5行,事务B也阻塞直到事务A释放1~5行的锁。死锁发生时,会产生Deadlock错误。两个事务都需要获得对方持有的排他锁才能继续完成事务,形成循环锁成死锁。如果锁是对表操作的,所以自然锁住全表的表锁就不会出现死锁。

对于一般的Select语句,InnoDB不会加任何锁,事务可以通过以下语句给显示加共享锁或排他锁。

对于共享锁大家可能很好理解,就是多个事务只能读数据不能改数据。 

Show Innodb status 命令查看最后一个死锁的产生原因。

共享锁:SELECT ... LOCK IN SHARE MODE;

对于排他锁大家的理解可能就有些差别,我当初就犯了一个错误,以为排他锁锁住一行数据后,其他事务就不能读取和修改该行数据,其实不是这样的。排他锁指的是一个事务在一行数据加上排他锁后,其他事务不能再在其上加其他的锁。mysql InnoDB引擎默认的修改数据语句:update,delete,insert都会自动给涉及到的数据加上排他锁,select语句默认不会加任何锁类型,如果加排他锁可以使用select …for update语句,加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的,也不能通过for update和lock in share mode锁的方式查询数据,但可以直接通过select …from…查询数据,因为普通查询没有任何锁机制。

从锁的角度看,表级锁更适合用于以查询为主,只有少量按索引条件更新数据的应用;而行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用。

排他锁:SELECT ... FOR UPDATE;

另外innodb引擎还有意向锁参见mysql数据库意向锁意义 - 简书

二、MyISAM表锁

共享锁例子:

2、数据库锁和隔离级别

查询表级锁征用情况:

事务一:使用lock in share lock获取共享锁,开始事务,不提交也不回滚

数据库中在并发情况下数据发生异常情况主要分为以下几类:

Show status like ‘table%’;

图片 1

a、脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据

如果 table_locks_waited 值较高,则说明存在较严重的表级锁征用情况;

事务二:对同一条记录进行修改,会产生阻塞,查询不会阻塞

b、不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果 不一致。

对于MyISAM表

图片 2

c、幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。

读锁表共享读锁,不会阻塞其他用户对同一表的请求,但会阻塞请求(即我读的时候我不能改,你能读也不能写;

事务三:对同一记录进行查询(加共享锁),不阻塞,共享锁允许其他事务也获取共享锁

       说完了数据库异常我们就来说下为了解决这些数据库异常提出四种数据库隔离级别,分别是读未提交(read-uncommitted),不可重复读(read-committed),可重复读(repeatable-read),串行化(serializable)

图片 3

图片 4

       每种隔离级别都是通过加锁的方式来保证数据库数据一致性。另外innodb为了提交数据库并发性结合mvcc(多版本快照,加版本号区别)和锁结合方式,读采用多版本不加锁,其他都是加锁防止发生数据异常

图片 5

排他锁例子:

3、数据库事务和锁关系

直到解锁表才执行:

事务一:select语句后跟for update获取排他锁,其他事务不能进行查询和修改的操作

什么是数据库事务简单的说来,事务指逻辑上的一组操作,组成这组操作的各个单元,要不全部成功,要不全部不成功。 

图片 6

 图片 7

例如:A向B转账100元,对应于如下两条sql语句:

图片 8

事务二:对同一记录进行加锁查询会被阻塞

updatefromaccountsetmoney=money 100wherename='b';

写锁表独占写锁,则即阻塞也阻塞(我能读写,你不能读且不能写;

图片 9

updatefromaccountsetmoney=money-100wherename='a';

图片 10

事务三:对该记录的加锁写操作会阻塞

数据库默认事务是自动提交的,也就是发一条sql它就执行一条,如果想多条sql放在一个事务中执行,则需要使用如下语句:

读写互斥(别人在读的时候你不能写,在写的时候不能读),但在一定条件下,也支持查询和插入操作的并发进行,设置系统变量concurrent_insert 值为0,1,2

图片 11

数据库开启事务命令:

MyISAM在执行SELECT前,自动给涉及的所有表加读锁,执行UPDATE、DELETE|、INSERT等前,自动写锁;一般不需要手动显示加锁。

参考网址:

start transaction :开启事务

MyISAM 总是一次获得SQL语句所需的全部锁,所以不会出现死锁;

rollback:回滚事务

在执行 LOCK TABLES 后,只能访问显示加锁的这些表,不能访问未加锁的表,自动加锁的情况也是如此;

commit:提交事务

如果SQL中出现锁定表取表名的,也需要显示申明锁定别名表

         下面我们来说一下数据库锁和事务关系,数据库在事务开始时申请数据库锁,例如update操作,但是事务在未提交时候获取的锁不释放,如果该update是锁定表或者制定行则其他操作该表或者改行数据操作就需要等待锁释放,如果其他事务不提交数据则其他等待锁的操作等到直到超时,因此数据库事务操作范围不可过大,导致阻塞其他事务处理数据。

图片 12

4、电商平台中数据库事务怎么处理并发

Lock tables a read, b read ; # 同时锁定 a, b表

电商平台肯定会设计到事务数据库的操作,并且数据库的操作可能还会引起重复操作和并发问题,在使用过程中涉及到多个表操作一定要加事务,另外事务加范围不可太大尽量最小范围,另外利用事务的传播机制来限制事务的是否回滚以及范围,另外需要并发操作的可以考虑cas乐观锁或者条件限制进行并发操作具体一些可以参考:电商平台高并发思考-幂等性(1)

MyISAM的锁调度:

MyISAM的读锁和写锁互斥,读写操作是串行的。两个不同进程同时分别请求读锁、写锁时,写进程优先获得锁,所以有大量的更新操作会造成查询操作很难获得读锁,从而可能永久阻塞。尽量避免长时间运行的查询操作,如复杂查询不可避免,尽量安排在数据库空闲时段执行,如夜间

解决查询相对重要的应用中读锁等待严重的问题

1.指定启动参数low_priority_updates,使MyISAM引擎给予读请求以优先的权利

2.命令set low_priority_updates = 1,使该连接发出的更新请求优先级降低

3.指定insert、update、delete语句的low_priority属性,降低该语句的优先级

4.折中,系统参数max_write_lock_count设置一个合适的值,当一个表的读锁达到这个值后,MySQL就暂时将写请求的优先级降低,给读进程一定获得锁的机会;

图片 13

并发插入(系统变量concurrent_insert)

concurrent_insert = 0 ,不允许并发插入

concurrent_insert = 1,如果MyISAM表中没有空洞,MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。Mysql的默认设置

concurrent_insert =2,无论有没有空洞,都允许在表尾并发插入记录

  利用MyISAM的并发插入来解决应用中对同一表查询和插入的锁争用。

三、查看Innodb行锁争用情况

Show status like ‘innodb_row_lock%’;

图片 14

Innodb_row_waits 和 Innodb_row_lock_time_avg 的值比较高,可

查看 information_schema 数据库中相关的表,如 innodb_locks 和 innodb_lock_waits;

或通过设置 Innodb monitors 来观察锁冲突情况:

Create table innodb_monitor engine=innodb;

然后 Show engine innodb statusG 查看

Drop table innodb_monitor; //停止监视器,默认情况下每15秒写入监控日志

四、Innodb实现两种类型的行锁(**针对开启事务,否则无效**):

  innodb 表锁操作跟 myisam 一样。

  共享锁(S):读锁。当一个事务对某几行上读锁时,我可以读写允许其他事务对这几行进行读操作,但不允许其进行写操作,也不允许其他事务给这几行上排它锁,但允许上读锁

上共享锁的写法:select语句 … lock in share mode

  图片 15

  图片 16

排他锁(X):写锁。当一个事务对某几个上写锁时,不允许其他事务写,但允许读。更不允许其他事务给这几行上任何锁。包括写锁。

上排它锁的写法:select语句 … for update

  图片 17

  图片 18

  如果一个事务请求的锁模式与当前线程的锁兼容,Innodb就将请求的锁授予该事务;反之,如果两者不兼容,该事务就要等待锁释放。

  即能同时获得共享锁,但不能同时获得排他锁,会锁等待

  图片 19

  对于UPDATE、DELETE、INSERT语句,InnoDB会自动给涉及数据集加排他锁X;对于普通的SELECT语句,InnoDB不会加任何锁。意向锁是系统自动加的。

意向共享锁:事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁

意向排他锁:事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁

注意几点:

  1. Innodb行锁是通过给索引上的索引项加锁来实现。如果没有索引将通过隐藏的聚簇索引来对记录加锁,即如果不通过索引条件检索数据,将对表中所有记录加锁,实际效果跟表锁一样
  2. 两个事务不能锁同一个索引
  3. insert ,delete , update在事务中都会自动默认加上排它锁
  4. 在没有索引的情况下,Innodb会对所有记录都加锁,当给其增加一个索引后,Innodb只锁定了符合条件的行。
  5. 访问不同行的记录,但如果使用相同索引键,会出现锁冲突

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:排他锁和共享锁,数据库锁和事务

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。