• 技术文章 >数据库 >mysql教程

    深入了解MySQL中的事务和锁

    青灯夜游青灯夜游2021-09-16 10:58:59转载139

    MySQL数据库是一个多用户访问系统,那么就要面临当多个用户同时读取和更新数据时,数据不会被破坏,所以就诞生了锁,锁一种并发控制技术,当一个用户尝试修改数据库中的记录时,首先要获取锁,那么持有这个锁的用户还在修改时,其他用户就不能对这些记录进行修改了。【相关推荐:mysql视频教程

    MySQL中的锁

    但是相对其他数据库而言,MySQL的锁机制比较简单,MySQL不同的存储引擎有不同的锁机制,MylSAM和MEMORY存储引擎采用的是表级锁,BDB存储引擎采用的是页面锁,而常用的InnoDB存储引擎支持行级锁、表级锁,默认情况下是采用行级锁。

    这3种锁的特性如下:

    MyISAM

    MyISAM表锁

    MySQL为表提供了两种类型的锁,它们是:

    MyISAM对表的读操作,不会阻塞其他用户对同一表的读请求, 但是会阻塞对同一表的写请求,MyISAM对表的写操作,会阻塞其他用户对同一表的读和写操作, MyISAM表的读操作与写操作之间,以及写操作之间是串行的。

    MyISAM在执行查询语句(SELECT)前,会自动给使用到的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要我们手动干预,所以我们一般不需要用LOCK TABLE命令给MyISAM表显式加锁,但是显示加锁也没有什么问题。

    还有在用LOCK TABLES给表显式加表锁时,必须同时取得所有涉及表的锁,因为在执行LOCK TABLES后,只能访问显式加锁的这些表,不能访问未加锁的表,否则会出错,同时,如果加的是读锁,那么只能执行查询操作,不能执行更新操作,否则也会报错,在自动加锁的情况下也是如此,这也正是MyISAM表不会出现死锁的原因。

    下面看一个列子。

    1、创建一张表

    CREATE TABLE test_table (   
          Id INT NOT NULL AUTO_INCREMENT,   
          Name VARCHAR(50) NOT NULL,   
          Message VARCHAR(80) NOT NULL,  
          PRIMARY KEY (Id)  
    );

    2、会话1获取写锁

    mysql> lock table  test_table write;
    Query OK, 0 rows affected (0.01 sec)

    3、会话2读取。

    我们知道在某个会话持有WRITE锁时,所有其他会话都无法访问该表的数据,所以在第二个会话执行下面语句时,会一直处于等待状态。

    mysql> select * from test_table;

    4、会话1解锁

    unlock table;

    并发插入

    在MyISAM里读写操作是串行的,但是可以根据concurrent_insert的设置,让MyISAM支持并行查询和插入。

    concurrent_insert取值如下:

    空洞指的是表的中间没有被删除的行。

    InnoDB

    InnoDB不同于MyISAM,他有两个特点,一是支持事务,二是采用了行级锁,行级锁和表锁有很多不同的地方。

    事务特性

    并发事务处理带来的问题

    相对于串行处理来说,虽然提高了资源利用率,可以支持更多的用户,但并发事务处理也会带来些问题, 主要包括以下几种情况。

    更新丢失

    由于每个事务都不知道其他事务的存在,就会发生丢失更新问题,也就是最后的更新覆盖了由其他事务所做的更新。

    脏读

    脏读又称无效数据的读出,当事务1将某一值修改后,然后事务2读取该值,后面事务1又因为一些原因撤销对该值的修改,这就导致了事务2所读取到的数据是无效的。

    不可重复读

    指的是一个事务在读取某些数据后,再次读取之前读过的数据,却发现读出的数据已经发生了改变。

    幻读

    当事务1按相同的查询条件重新读取以前查询过的数据时,却发现其他事务插入了满足这个条件的新数据。

    事务隔离级别

    上面说的"更新丢失"是应该完全避免的,但防止更新丢失,并不能单靠数据库事务控制器来解决,需要应用程序对要更新的数据加必要的锁。

    而脏读、不可重复读、幻读,都是数据库读一致性问题,必须由数据库提供事务隔离机制来解决。数据库实现事务隔离的方式,可分为以下两种,一种是在读取数据前加锁,阻止其他事务对数据进行修改,另一种不需要锁,通过MVCC或MCC来实现,这种技术叫做数据多版本并发控制,通过一定机制生成一个数据请求时间点的一致性数据快照,并用这个快照来提供一定级别的一致性读取。

    数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上串行化进行。

    InnoDB有四个事务隔离级别: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ,和 SERIALIZABLE。默认隔离级别是REPEATABLE READ。

    隔离级别脏读不可重复性幻读
    读未提交
    读已提交×
    可重复读取××
    可序列化(serializable)×××

    查询/更改隔离级别

    显示隔离级别
    show global variables like '%isolation%';
    select @@transaction_isolation;
    
    设置隔离级别
    set global transaction_isolation ='read-committed';
    set session transaction isolation level read uncommitted;

    READ UNCOMMITTED(读未提交)

    在这个隔离级别,所有事务都可以看到其他未提交事务的执行结果。这种隔离级别在实际应用中很少使用,读取未提交的数据也称为脏读。

    例子

    启动两个会话,并设置隔离级别为READ UNCOMMITTED。

    mysql> select * from user;
    +-----------+---------+
    | user_name | balance |
    +-----------+---------+
    | 张三      |     100 |
    | 李四      |     100 |
    | 王五      |      80 |
    +-----------+---------+
    时间事务1事务2
    T1begin;begin;
    T2select * from user where user_name="张三";
    此时张三余额100

    T3
    select * from user where user_name="张三";
    此时张三余额100
    T4update user set balance =80 where user_name ="张三";
    T4
    select * from user where user_name="张三";
    此时张三余额80
    T5commitcommit

    可以看到,在T4时刻,事务1没有提交,但是事务2可以看到被事务1锁更改的数据。

    READ COMMITTED (读已提交)

    这是大多数数据库系统的默认隔离级别,但不是MySQL的默认级别,他避免了脏读现象,因为在任何未提交的事务前,对任何其他事务都是不可见的,也就是其他事务看不到未提交的数据,允许不可重复读。

    例子

    将两个会话中隔离级别设置为读已提交
    set session transaction isolation level read committed;
    时间事务1事务2
    T1begin;begin;
    T2select * from user where user_name="张三";
    此时张三余额100

    T3
    select * from user where user_name="张三";
    此时张三余额100
    T4update user set balance =80 where user_name ="张三";
    T4
    select * from user where user_name="张三";
    此时张三余额100
    T5commit
    T5
    select * from user where user_name="张三";
    此时张三余额80

    可以看到,在T4时刻,事务1没有提交,但是事务2读取到的数据还是100,当事务1提交后,事务2才可以看到。

    REPEATABLE READ (可重复读)

    这是 MySQL 的默认事务隔离级别,它确保同一事务读取数据时,将看到相同的数据行,但是会出现幻读,当事务1按条件进行查询后,另一个事务在该范围内插入一个新数据,那么事务1再次读取时,就会读到这个新数据。InnoDB 和 Falcon 存储引擎通过 mvcc(多版本并发控制)机制解决了这个问题。

    例子

    设置两个会话隔离级别为可重复读
    set session transaction isolation level repeatable read;
    时间事务1事务2
    T1begin;begin;
    T2update user set balance =80 where user_name ="张三";
    T3commit;
    T4
    select * from user where user_name="张三";
    张三余额为100

    可以看到,在T3时刻,事务1已经提交更改,但是在T4时刻的事务2中,还是读取到了原来的数据,但是如果事务2在原来的基础上再减10元,那么最终余额是90还是70呢?,答案是70。.

    mysql> update user set balance=balance-10 where user_name="张三";
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from user where user_name="张三";
    +-----------+---------+
    | user_name | balance |
    +-----------+---------+
    | 张三      |      70 |
    +-----------+---------+
    1 row in set (0.00 sec)

    SERIALIZABLE (序列化)

    他是最高的隔离级别,InnoDB将所有普通SELECT语句隐式转换为SELECT ... LOCK IN SHARE MODE,所有事务按照顺序依次执行,因此,脏读、不可重复读、幻读都不会出现。但是,由于事务是串行执行,所以效率会大大下降,

    例子
    设置隔离级别为序列化
    set session transaction isolation level serializable;
    时间事务1事务2
    T1begin;begin;
    T2select * from user where user_name="张三";
    T3
    update user set balance =80 where user_name ="张三";

    这一次,有趣的是,事务2在T3时刻更新被阻止了,原因是在serializable隔离级别下,MySQL隐式地将所有普通SELECT查询转换为SELECT FOR SHARE, 持有SELECT FOR SHARE锁的事务只允许其他事务对SELECT行进行处理,而不允许其他事务UPDATEDELETE它们。

    所以有了这个锁定机制,我们之前看到的不一致数据场景就不再可能了。

    但是,这个锁具有超时时间,在等待一会后,如果其他事务在这段时间内没有提交或回滚释放锁,将抛出锁等待超时错误,如下所示:

    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

    InnoDB行锁

    InnoDB 的行级锁也分为共享锁和排他锁两种。

    为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁,这两种意向锁都是表锁。

    InnoDB 行锁是通过锁定索引上的索引条目来实现的,因此,InnoDB 只有在通过索引条件检索到数据时才使用行级锁;否则,InnoDB 将使用表锁。

    我们可以显示的加锁,但对于update、delete、insert语句,InnoDB会自动给涉及的数据集加排他锁,对于普通的 select 语句,InnoDB 不会加任何锁,下面是显示的加锁方式:

    Next-Key锁

    当我们使用范围条件而不是相等条件检索数据,并请求其共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁,对于在条件范围内但并不存在的记录,叫做间隙(GAP), InnoDB也会对这个"间隙"加锁,这种锁机制就是所谓的Next-Key锁。

    举例来说,假如user表中只有101条记录,其user_id的值分别是1.2. ..100. 101,当查找大于100的user_id时,使用下面SQL。

    select.* from emp where user_id > 100 for update;

    这就是一个范围条件的查询, InnoDB不仅会对user_id为101的记录加锁,也会对user_id大于101的"间隙"加锁,虽然这些记录并不存在。

    InnoDB使用Next-Key锁的目的,一方面是为了防止幻读,另一方面, 是为了满足恢复和复制的需要。

    更多编程相关知识,请访问:编程视频!!

    以上就是深入了解MySQL中的事务和锁的详细内容,更多请关注php中文网其它相关文章!

    声明:本文转载于:掘金社区,如有侵犯,请联系admin@php.cn删除
    专题推荐:MySQL 事务和锁
    上一篇:MySQL中怎么启用并分析慢查询日志? 下一篇:你对MySQL BlackHole引擎有多少了解?
    线上培训班

    相关文章推荐

    • 详解MySQL中的count()、union()和group by语句• php7怎么安装mysqli扩展• 浅谈Mysql5.7中怎么搭建主从复制?• MySQL中怎么进行sql_mode查询与设置• MySQL中什么是游标?如何使用游标?

    全部评论我要评论

  • 取消发布评论发送
  • 1/1

    PHP中文网