金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 事务与锁表,开发进阶篇系列

事务与锁表,开发进阶篇系列

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-09-13 06:26
  1. innodb 行锁是基于索引实现的,如果不通过索引访问数据,innodb会使用表锁。

手工锁表、释放锁

  • lock table table_name read/write
  • unlock table

在InnoDB加锁前,为什么要先start transaction

  innodb下锁的释放在事务提交/回滚之后,事务一旦提交/回滚之后,就会自动释放事务中的锁,innodb默认情况下autocommit=1即开启自动提交

检索条件使用索引和不使用索引的锁区别:

  检索条件有索引的情况下会锁定特定的一些行。

检索条件没有使用使用的情况下会进行全表扫描,从而锁定全部的行(包括不存在的记录)

在InnoDB加锁前,为什么要先start transaction

  innodb下锁的释放在事务提交/回滚之后,事务一旦提交/回滚之后,就会自动释放事务中的锁,innodb默认情况下autocommit=1即开启自动提交

检索条件使用索引和不使用索引的锁区别:

  检索条件有索引的情况下会锁定特定的一些行。

检索条件没有使用使用的情况下会进行全表扫描,从而锁定全部的行(包括不存在的记录)

     http://www.cnblogs.com/MrHSR/p/9376086.html

锁的类型

读锁:

  读锁是共享的,或者说是相互不阻塞的。多个用户在同一时刻可以同时读取同一个资源,而互不干扰。

金沙棋牌app手机下载,读锁:

  读锁是共享的,或者说是相互不阻塞的。多个用户在同一时刻可以同时读取同一个资源,而互不干扰。

 2. Innodb 间隙锁(Next-key)机制,以及innodb使用间隙锁的原因

表锁

  • show status like 'table%'查看表锁的竞争情况
    • Table_locks_waited 表示表级锁的争用情况

写锁:

  写锁是排他的,也就是说一个写锁会阻塞其他的写锁和读锁。另外写锁比读锁有更高的优先级,因此一个写锁请求可能会被插入到读锁 队列的前面,但是读锁则不可能插入到写锁的前面

写锁:

  写锁是排他的,也就是说一个写锁会阻塞其他的写锁和读锁。另外写锁比读锁有更高的优先级,因此一个写锁请求可能会被插入到读锁 队列的前面,但是读锁则不可能插入到写锁的前面

  http://www.cnblogs.com/MrHSR/p/9390350.html

行锁

表锁:

  InnoDB还有两个表锁:意向共享锁(IS),意向排它锁(IX)

表锁:

  InnoDB还有两个表锁:意向共享锁(IS),意向排它锁(IX)

 3.不同隔离级别下,innodb的锁机制和一致性读策略不同。

页面锁

行锁:

  InnoDB实现了两种类型行级锁,共享锁和排它锁

金沙棋牌app手机下载 1

行锁:

  InnoDB实现了两种类型行级锁,共享锁和排它锁

金沙棋牌app手机下载 2

  

myisam 锁机制

myisam 更新的sql语句执行优先级优于查询语句,一旦大量的更新操作就会阻塞表,导致死锁。锁myisam引擎不适合大量更新的表。

乐观锁:

  乐观锁,也叫乐观并发控制,它假设多用户并发的事务在处理时不会彼此互相影响,各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前,每个事务会先检查在该事务读取数据后,有没有其他事务又修改了该数据。如果其他事务有更新的话,那么当前正在提交的事务会进行回滚。

乐观锁:

  乐观锁,也叫乐观并发控制,它假设多用户并发的事务在处理时不会彼此互相影响,各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前,每个事务会先检查在该事务读取数据后,有没有其他事务又修改了该数据。如果其他事务有更新的话,那么当前正在提交的事务会进行回滚。

 4.mysql 的恢复和复制对innodb锁机制和一致性读策略也有较大影响。

调节myisam调度机制

  • 通过启动参数设定 low-priority-updates
  • 命令行: set LOW_PRIORITY_UPDATES = 1
  • sql语句中指定 insert update delete low_priority 属性

悲观锁:

  悲观锁,也叫悲观并发控制,当事务A对某行数据应用了锁,并且当这个事务把锁释放后,其他事务才能够执行与该锁冲突的操作,这里事务A所施加的锁就叫悲观锁。共享锁和排他锁(行锁,间隙锁,next-key lock)都属于悲观锁

悲观锁:

  悲观锁,也叫悲观并发控制,当事务A对某行数据应用了锁,并且当这个事务把锁释放后,其他事务才能够执行与该锁冲突的操作,这里事务A所施加的锁就叫悲观锁。共享锁和排他锁(行锁,间隙锁,next-key lock)都属于悲观锁

  

辅助机制

通过设置max_write_lock_count设置合适的值避免一直查询不到数据

悲观锁与乐观锁的实现方式:

  悲观锁的实现依靠的是数据库提供的锁机制来实现,例如select * from news where id=12 for update,而乐观锁依靠的是记录数据版本来实现,即通过在表中添加版本号字段来作为是否可以成功提交的关键因素。

金沙棋牌app手机下载 3

悲观锁与乐观锁的实现方式:

  悲观锁的实现依靠的是数据库提供的锁机制来实现,例如select * from news where id=12 for update,而乐观锁依靠的是记录数据版本来实现,即通过在表中添加版本号字段来作为是否可以成功提交的关键因素。

金沙棋牌app手机下载 4

 5.调整锁冲突和死锁策略

innodb 锁机制

innodb行锁是通过给索引上的索引项加锁来实现,只有通过索引条件检索数据,innodb才使用行级锁,否则使用表锁

共享锁(S):

  共享锁也叫读锁,一个事务获取了一个数据行的共享锁,其他事务能获得该行对应的共享锁,但不能获得排他锁,即一个事务在读取一个数据行的时候,其他事务也可以读,但不能对该数据行进行增删改

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

共享锁(S):

  共享锁也叫读锁,一个事务获取了一个数据行的共享锁,其他事务能获得该行对应的共享锁,但不能获得排他锁,即一个事务在读取一个数据行的时候,其他事务也可以读,但不能对该数据行进行增删改

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

         5.1 尽量使用较低的隔离级别

查看innodb行锁竞争情况

  • show status like 'innodb_row_lock%' InnoDB_row_lock_waits和我InnoDB_row_lock_avg的值比较高,锁竞争严重

排它锁(X):

  排它锁也叫写锁,一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁(排他锁或者共享锁),即一个事务在读取一个数据行的时候,其他事务不能对该数据行进行增删改查

  设置排它锁:SELECT .... FOR UPDATE

  注意点:

  • 对于select 语句,innodb不会加任何锁,也就是可以多个并发去进行select的操作,不会有任何的锁冲突,因为根本没有锁。
  • 对于insert,update,delete操作,innodb会自动给涉及到的数据加排他锁,只有查询select需要我们手动设置排他锁。

排它锁(X):

  排它锁也叫写锁,一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁(排他锁或者共享锁),即一个事务在读取一个数据行的时候,其他事务不能对该数据行进行增删改查

  设置排它锁:SELECT .... FOR UPDATE

  注意点:

  • 对于select 语句,innodb不会加任何锁,也就是可以多个并发去进行select的操作,不会有任何的锁冲突,因为根本没有锁。
  • 对于insert,update,delete操作,innodb会自动给涉及到的数据加排他锁,只有查询select需要我们手动设置排他锁。

         5.2 精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会。

手动在sql语句中指定锁

  • 共享锁 select * from tbl_name where ... lock in share mode
  • 排他锁 select * from tbl_name where ... for update

意向共享锁(IS):

  通知数据库接下来需要施加什么锁并对表加锁。如果需要对记录A加共享锁,那么此时innodb会先找到这张表,对该表加意向共享锁之后,再对记录A添加共享锁。也就是说一个数据行加共享锁前必须先取得该表的IS锁

意向共享锁(IS):

  通知数据库接下来需要施加什么锁并对表加锁。如果需要对记录A加共享锁,那么此时innodb会先找到这张表,对该表加意向共享锁之后,再对记录A添加共享锁。也就是说一个数据行加共享锁前必须先取得该表的IS锁

         5.3 选择合理的事务大小,小事务发生锁冲突的几率也更小。

innodb行锁使用注意事项

  • 再不通过索引条件查询时,innodb使用的是表锁并非行锁
  • 多列索引时,如果使用相同的索引键(即同时使用索引1的同一行记录),会出现索引冲突
  • 索引是否会被使用,取决于mysql的执行计划,如果小表可能全表扫描比索引更快
  • 尽量减少使用范围的条件

意向排它锁(IX):

  通知数据库接下来需要施加什么锁并对表加锁。如果需要对记录A加排他锁,那么此时innodb会先找到这张表,对该表加意向排他锁之后,再对记录A添加共享锁。也就是说一个数据行加排它锁前必须先取得该表的IX锁

意向排它锁(IX):

  通知数据库接下来需要施加什么锁并对表加锁。如果需要对记录A加排他锁,那么此时innodb会先找到这张表,对该表加意向排他锁之后,再对记录A添加共享锁。也就是说一个数据行加排它锁前必须先取得该表的IX锁

         5.4 给记录集显示加锁时,最好一次性请求足哆级别的锁。比如要修改数据的话,最好直接申请排它锁,而不是先申请共享锁,修改时再请求排它锁,这样容易死锁。

non-deterministic 不确定的sql

两种方式都会对oldtab 增加间隙阻止更oldtab数据

  • insert into newtab select * form oldtab
  • create newtab select * from oldtab
    使用这两种方式创建表时要注意,oldtab是否有在使用, 是否能让其他请求等待时间

  共享锁和意向共享锁,排他锁与意向排他锁的区别:

  • 共享锁和排他锁,系统在特定的条件下会自动添加共享锁或者排他锁,也可以手动添加共享锁或者排他锁。
  • 意向共享锁和意向排他锁都是系统自动添加和自动释放的,整个过程无需人工干预。
  • 共享锁和排他锁都是锁的行记录,意向共享锁和意向排他锁锁定的是表。

  共享锁和意向共享锁,排他锁与意向排他锁的区别:

  • 共享锁和排他锁,系统在特定的条件下会自动添加共享锁或者排他锁,也可以手动添加共享锁或者排他锁。
  • 意向共享锁和意向排他锁都是系统自动添加和自动释放的,整个过程无需人工干预。
  • 共享锁和排他锁都是锁的行记录,意向共享锁和意向排他锁锁定的是表。

         5.5 不同程序访问一组表时,尽量约定以相同的顺序访问各表。

相关变量

 锁的实现方式:

  在MySQL中,行级锁并不是直接锁记录,而是锁索引。索引分为主键索引和非主键索引两种,如果一条sql语句操作了主键索引,MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引,MySQL会先锁定该非主键索引,再锁定相关的主键索引。

  InnoDB行锁是通过给索引项加锁实现的,如果没有索引,InnoDB会通过隐藏的聚簇索引来对记录加锁。也就是说:如果不通过索引条件检索数据,那么InnoDB将对表中所有数据加锁,实际效果跟表锁一样

 锁的实现方式:

  在MySQL中,行级锁并不是直接锁记录,而是锁索引。索引分为主键索引和非主键索引两种,如果一条sql语句操作了主键索引,MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引,MySQL会先锁定该非主键索引,再锁定相关的主键索引。

  InnoDB行锁是通过给索引项加锁实现的,如果没有索引,InnoDB会通过隐藏的聚簇索引来对记录加锁。也就是说:如果不通过索引条件检索数据,那么InnoDB将对表中所有数据加锁,实际效果跟表锁一样

         5.6 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响。

- innodb_lock_wait_timeout innodb锁等待超时时间

行锁分为三种情况:

  Record Lock:对索引项加锁,即锁定一条记录。

  Gap Lock:对索引项之间的 ‘间隙’ 、对第一条记录前的间隙或最后一条记录后的间隙加锁,即锁定一个范围的记录,不包含记录本身

  Next-key Lock:锁定一个范围的记录并包含记录本身(上面两者的结合)

  注意:InnoDB默认级别是repeatable-read(重复读)级别。ANSI/IOS SQL标准定义了4种事务隔离级别:未提交读(read uncommitted),提交读(read committed),重复读(repeatable read),串行读(serializable)

行锁分为三种情况:

  Record Lock:对索引项加锁,即锁定一条记录。

  Gap Lock:对索引项之间的 ‘间隙’ 、对第一条记录前的间隙或最后一条记录后的间隙加锁,即锁定一个范围的记录,不包含记录本身

  Next-key Lock:锁定一个范围的记录并包含记录本身(上面两者的结合)

  注意:InnoDB默认级别是repeatable-read(重复读)级别。ANSI/IOS SQL标准定义了4种事务隔离级别:未提交读(read uncommitted),提交读(read committed),重复读(repeatable read),串行读(serializable)

事务

  1. 开启事务:start transaction | begin
  2. 释放事务:
  • commit and release / chain; release 提交事务,并释放事务; chain 提交并开启同一性质的事务
  • rollback and release / chain;
  1. savapoint test;
  2. rollback to test;

Gap Lock和Next-key Lock的区别:

  Next-Key Lock是行锁与间隙锁的组合,这样,当InnoDB扫描索引记录的时候,会首先对选中的索引记录加上行锁(Record Lock),再对索引记录两边的间隙加上间隙锁(Gap Lock)。如果一个间隙被事务T1加了锁,其它事务是不能在这个间隙插入记录的。

  行锁防止别的事务修改或删除,Gap锁防止别的事务新增,行锁和GAP锁结合形成的Next-Key锁共同解决了RR界别在写数据时的幻读问题。

Gap Lock和Next-key Lock的区别:

  Next-Key Lock是行锁与间隙锁的组合,这样,当InnoDB扫描索引记录的时候,会首先对选中的索引记录加上行锁(Record Lock),再对索引记录两边的间隙加上间隙锁(Gap Lock)。如果一个间隙被事务T1加了锁,其它事务是不能在这个间隙插入记录的。

  行锁防止别的事务修改或删除,Gap锁防止别的事务新增,行锁和GAP锁结合形成的Next-Key锁共同解决了RR界别在写数据时的幻读问题。

小结

对于MyISAM的表锁,主要讨论了以下几点:

  • 共享读锁(S)之间是兼容的,但共享读锁(S)与排他写锁(X)之间,以及排他写锁(X)之间是互斥的,也就是说读和写是串行的。

  • 在一定条件下,MyISAM允许查询和插入并发执行,我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。

  • MyISAM默认的锁调度机制是写优先,这并不一定适合所有应用,用户可以通过设置LOW_PRIORITY_UPDATES参数,或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。

  • 由于表锁的锁定粒度大,读写之间又是串行的,因此,如果更新操作较多,MyISAM表可能会出现严重的锁等待,可以考虑采用InnoDB表来减少锁冲突。

对于InnoDB表,本章主要讨论了以下几项内容。

  • InnoDB的行锁是基于锁引实现的,如果不通过索引访问数据,InnoDB会使用表锁。
  • 介绍了InnoDB间隙锁(Next-key)机制,以及InnoDB使用间隙锁的原因。
  • 在不同的隔离级别下,InnoDB的锁机制和一致性读策略不同。
  • MySQL的恢复和复制对InnoDB锁机制和一致性读策略也有较大影响。
  • 锁冲突甚至死锁很难完全避免。

在了解InnoDB锁特性后,用户可以通过设计和SQL调整等措施减少锁冲突和死锁,包括:

  • 尽量使用较低的隔离级别;
  • 精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会;
  • 选择合理的事务大小,小事务发生锁冲突的几率也更小;
  • 给记录集显示加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁;
  • 不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会;
  • 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响;
  • 不要申请超过实际需要的锁级别;除非必须,查询时不要显示加锁
  • 对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能。

何时在InnoDB中使用表锁:

  InnoDB在绝大部分情况会使用行级锁,因为事务和行锁往往是我们选择InnoDB的原因,但是有些情况下我们也考虑使用表级锁

  • 当事务需要更新大部分数据时,表又比较大,如果使用默认的行锁,不仅效率低,而且还容易造成其他事务长时间等待和锁冲突。
  • 事务比较复杂,很可能引起死锁导致回滚。

何时在InnoDB中使用表锁:

  InnoDB在绝大部分情况会使用行级锁,因为事务和行锁往往是我们选择InnoDB的原因,但是有些情况下我们也考虑使用表级锁

  • 当事务需要更新大部分数据时,表又比较大,如果使用默认的行锁,不仅效率低,而且还容易造成其他事务长时间等待和锁冲突。
  • 事务比较复杂,很可能引起死锁导致回滚。

在InnoDB下 ,使用表锁要注意以下两点。

    (1)使用LOCK TALBES虽然可以给InnoDB加表级锁,但必须说明的是,表锁不是由InnoDB存储引擎层管理的,而是由其上一层MySQL Server负责的,仅当autocommit=0、innodb_table_lock=1(默认设置)时,InnoDB层才能知道MySQL加的表锁,MySQL Server才能感知InnoDB加的行锁,这种情况下,InnoDB才能自动识别涉及表级锁的死锁;否则,InnoDB将无法自动检测并处理这种死锁。

    (2)在用LOCAK TABLES对InnoDB锁时要注意,要将AUTOCOMMIT设为0,否则MySQL不会给表加锁;事务结束前,不要用UNLOCAK TABLES释放表锁,因为UNLOCK TABLES会隐含地提交事务;COMMIT或ROLLBACK不能释放用LOCAK TABLES加的表级锁,必须用UNLOCK TABLES释放表锁,正确的方式见如下:

  例如:如果需要写表t1并从表t读

  

SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and here];
COMMIT;
UNLOCK TABLES;

在InnoDB下 ,使用表锁要注意以下两点。

    (1)使用LOCK TALBES虽然可以给InnoDB加表级锁,但必须说明的是,表锁不是由InnoDB存储引擎层管理的,而是由其上一层MySQL Server负责的,仅当autocommit=0、innodb_table_lock=1(默认设置)时,InnoDB层才能知道MySQL加的表锁,MySQL Server才能感知InnoDB加的行锁,这种情况下,InnoDB才能自动识别涉及表级锁的死锁;否则,InnoDB将无法自动检测并处理这种死锁。

    (2)在用LOCAK TABLES对InnoDB锁时要注意,要将AUTOCOMMIT设为0,否则MySQL不会给表加锁;事务结束前,不要用UNLOCAK TABLES释放表锁,因为UNLOCK TABLES会隐含地提交事务;COMMIT或ROLLBACK不能释放用LOCAK TABLES加的表级锁,必须用UNLOCK TABLES释放表锁,正确的方式见如下:

  例如:如果需要写表t1并从表t读

  

SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and here];
COMMIT;
UNLOCK TABLES;

 死锁:

  我们说过MyISAM中是不会产生死锁的,因为MyISAM总是一次性获得所需的全部锁,要么全部满足,要么全部等待。而在InnoDB中,锁是逐步获得的,就造成了死锁的可能。

     发生死锁后,InnoDB一般都可以检测到,并使一个事务释放锁回退,另一个获取锁完成事务。但在涉及外部锁,或涉及锁的情况下,InnoDB并不能完全自动检测到死锁,这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要说明的是,这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获取所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖垮数据库。我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。

 死锁:

  我们说过MyISAM中是不会产生死锁的,因为MyISAM总是一次性获得所需的全部锁,要么全部满足,要么全部等待。而在InnoDB中,锁是逐步获得的,就造成了死锁的可能。

     发生死锁后,InnoDB一般都可以检测到,并使一个事务释放锁回退,另一个获取锁完成事务。但在涉及外部锁,或涉及锁的情况下,InnoDB并不能完全自动检测到死锁,这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要说明的是,这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获取所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖垮数据库。我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。

  有多种方法可以避免死锁,这里介绍常见的三种:

  1. 如果不同程序会并发存取多个表,尽量约定以相同的顺序访问表,可以大大降低死锁机会。如果两个session访问两个表的顺序不同,发生死锁的机会就非常高!但如果以相同的顺序来访问,死锁就可能避免。
  2. 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率。
  3. 对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概。
  4. 在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低死锁的可能
  5. 在REPEATEABLE-READ隔离级别下,如果两个线程同时对相同条件记录用SELECT...ROR UPDATE加排他锁,在没有符合该记录情况下,两个线程都会加锁成功。程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成READ COMMITTED,就可以避免问题。
  6. 当隔离级别为READ COMMITED时,如果两个线程都先执行SELECT...FOR UPDATE,判断是否存在符合条件的记录,如果没有,就插入记录。此时,只有一个线程能插入成功,另一个线程会出现锁等待,当第1个线程提交后,第2个线程会因主键重出错,但虽然这个线程出错了,却会获得一个排他锁!这时如果有第3个线程又来申请排他锁,也会出现死锁。对于这种情况,可以直接做插入操作,然后再捕获主键重异常,或者在遇到主键重错误时,总是执行ROLLBACK释放获得的排他锁

   ps:如果出现死锁,可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因和改进措施。

  有多种方法可以避免死锁,这里介绍常见的三种:

  1. 如果不同程序会并发存取多个表,尽量约定以相同的顺序访问表,可以大大降低死锁机会。如果两个session访问两个表的顺序不同,发生死锁的机会就非常高!但如果以相同的顺序来访问,死锁就可能避免。
  2. 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率。
  3. 对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概。
  4. 在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低死锁的可能
  5. 在REPEATEABLE-READ隔离级别下,如果两个线程同时对相同条件记录用SELECT...ROR UPDATE加排他锁,在没有符合该记录情况下,两个线程都会加锁成功。程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成READ COMMITTED,就可以避免问题。
  6. 当隔离级别为READ COMMITED时,如果两个线程都先执行SELECT...FOR UPDATE,判断是否存在符合条件的记录,如果没有,就插入记录。此时,只有一个线程能插入成功,另一个线程会出现锁等待,当第1个线程提交后,第2个线程会因主键重出错,但虽然这个线程出错了,却会获得一个排他锁!这时如果有第3个线程又来申请排他锁,也会出现死锁。对于这种情况,可以直接做插入操作,然后再捕获主键重异常,或者在遇到主键重错误时,总是执行ROLLBACK释放获得的排他锁

   ps:如果出现死锁,可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因和改进措施。

 总结:

  对于InnoDB表,主要有以下几点

    (1)InnoDB的行销是基于索引实现的,如果不通过索引访问数据,InnoDB会使用表锁。

    (2)InnoDB间隙锁机制,以及InnoDB使用间隙锁的原因。

    (3)在不同的隔离级别下,InnoDB的锁机制和一致性读策略不同。

    (4)MySQL的恢复和复制对InnoDB锁机制和一致性读策略也有较大影响。

    (5)锁冲突甚至死锁很难完全避免。

 

      在了解InnoDB的锁特性后,用户可以通过设计和SQL调整等措施减少锁冲突和死锁,包括:

  • 尽量使用较低的隔离级别
  • 精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会。
  • 选择合理的事务大小,小事务发生锁冲突的几率也更小。
  • 给记录集显示加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁。
  • 不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大减少死锁的机会。
  • 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响。
  • 不要申请超过实际需要的锁级别;除非必须,查询时不要显示加锁。
  • 对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能。

 总结:

  对于InnoDB表,主要有以下几点

    (1)InnoDB的行销是基于索引实现的,如果不通过索引访问数据,InnoDB会使用表锁。

    (2)InnoDB间隙锁机制,以及InnoDB使用间隙锁的原因。

    (3)在不同的隔离级别下,InnoDB的锁机制和一致性读策略不同。

    (4)MySQL的恢复和复制对InnoDB锁机制和一致性读策略也有较大影响。

    (5)锁冲突甚至死锁很难完全避免。

 

      在了解InnoDB的锁特性后,用户可以通过设计和SQL调整等措施减少锁冲突和死锁,包括:

  • 尽量使用较低的隔离级别
  • 精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会。
  • 选择合理的事务大小,小事务发生锁冲突的几率也更小。
  • 给记录集显示加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁。
  • 不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大减少死锁的机会。
  • 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响。
  • 不要申请超过实际需要的锁级别;除非必须,查询时不要显示加锁。
  • 对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能。

参考文献:

 [1] Baron Schwartz等 著,宁海元等 译 ;《高性能MySQL》(第3版); 电子工业出版社 ,2013

 [2] 简书博客,

 [3]CSDN博客,

 [4] CSDN博客,

 [5] CSDN博客,

 [6] CSDN博客,

 [7] CSDN博客,

 [8] 官网文档,

参考文献:

 [1] Baron Schwartz等 著,宁海元等 译 ;《高性能MySQL》(第3版); 电子工业出版社 ,2013

 [2] 简书博客,

 [3]CSDN博客,

 [4] CSDN博客,

 [5] CSDN博客,

 [6] CSDN博客,

 [7] CSDN博客,

 [8] 官网文档,

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:事务与锁表,开发进阶篇系列

关键词:

上一篇:没有了

下一篇:没有了