MySQL存储引擎、事务日志并发访问以及隔离级别 · php开发笔记

#### MySQL存储引擎 * MySQL是插件式存储存储引擎，支持多种存储引擎 * 常见的存储引擎有：MyISAM, Aria, InnoDB, MRG\_MYISAM, CSV, BLACKHOLE, MEMORY, PERFORMANCE\_SCHEMA, ARCHIVE, FEDERATED ~~~objectivec mysql> SHOW ENGINES; #查看支持多少种存储引擎 *************************** 1. row *************************** Engine: FEDERATED #引擎名称 Support: NO #是否支持 Comment: Federated MySQL storage engine #简要注释 Transactions: NULL #是否支持事务功能 XA: NULL #是否支持分布式事务功能 Savepoints: NULL # 是否支持保存点功能 *************************** 2. row *************************** ~~~ ... * 存储引擎是表级别的概念，因此又称为“表类型”；不建议在同一个库中的表上使用不同的ENGINE； ~~~css CREATE TABLE ... ENGINE[=]STORAGE_ENGINE_NAME ... SHOW TABLE STATUS ~~~ #### 1\. InnoDB存储引擎 InnoDB存储引擎，把数据存储在一个文件中，是个黑盒，根varnish是一样，varnish中的数据缓存都存储在一个文件中，但其内部完全有自组织有序的结果从外部看来就是一个文件，内部可以有自己的元数据，数据是构建在文件系统之上的文件系统；而InnoDB也是这样，默认情况下可以将n张表放在一个表空间里，在外部看来就是一个表，其内部存放了n张表甚至存放了n张表的索引；不过把n张表的索引放在同一表中很难实现高级功能；比如像单表导入导出等；因此，这就是为什么在安装MariaDB时，都要求使用innodb\_file\_per\_table这表示每张表使用一个表空间； InnoDB的表空间为了能跨文件系统（分区），InnoDB的表空间背后所对应的文件可以不止一个，而且还可以自动增长；表空间刚创建时，就占用固定大小的空间，如果再许愿空间时，就又占用固定大小的空间，用不用都占用；可理解为是基于步进的方式进行划分；InnoDB将所有的表和索引放在一个表空间中；如果所有表和数据都存储在一个表空间中时，表空间文件是存储在datadir定义的目录中的；如果每张表单独存储在一个表空间中，则表空间文件是放置在数据库目录下，而不是数据目录下；例如有一个数据库叫mydb就相当于在数据库目录下创建一个mydb的目录而已；当然数据库目录有可能在数据目录下的子目录； ~~~objectivec MariaDB [(none)]> SHOW ENGINES\G; ... *************************** 6. row *************************** Engine: InnoDB Support: DEFAULT Comment: Percona-XtraDB, Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES ~~~ * 数据存储于“表空间（table space）”中，是个黑盒：有两种管理方式：（1）所有数据库中的所有类型为InnoDB的表的数据和索引存储于同一个表空间中； * 表空间文件：存储在datadir定义的目录下； * 表现出的文件为：ibddata1，ibddata2，... （2）每个表单独使用一个表空间存储表的数据和索引；使用`innodb_file_per_table=ON`开启； * 数据文件（存储数据和索引）名：tb1\_name.frm * 每张表由2个文件组成：tb1\_name.ldb和tb1\_name.frm * 文件存储于数据库目录中注：基于第一种方式管理时，所有数据库的InnoDB表将使用同一个表空间，例如有10个库，每个库中有100张表，都放在同一个表空间中，于后期管理无益。 * InnoDB存储引擎是事务性存储引擎，适合处理大量的短期任务； * 基于MVCC（Mutli Version Concurrenvy Contro，多版本并发控制）支持高并发，支持所有的四个隔离级别；默认支持间隙锁防止幻读； * 使用聚簇索引（一般是主键索引）； * 一张表的聚簇索引只能有一个；索引在哪数据就在哪里 * 支持自适应hash索引； * 锁粒度：行级锁，间隙锁；注意：级别越高事务发生冲突性越小，事务安全性越高但并发性能就越低； > 注：MVCC，multi version concurrency control：多版本并发访问控制，它就是类似于lvm的快照功能；也就意味着，当启动n个事务时，每一个事务，都针对于mysql当中的InnoDB存储引擎现有数据做一个快照；每一个快照，通过快照过去访问时间点是一致的，所以现在要启动一个事务，就对InnoDB数据集做个快照，就在快照基础上做操作；因此有50个事务为了彼此间不受影响，每个事务启动一次做一个快照。 > 但是，这病不意味着就没有冲突，想象以下，如果第一个快照和第二个也快照了，两个操作同一个数据集，一个删了一行，而第二个事务把这个行改了，此时就会出现问题，所以，必须有隔离机制来实现所谓的事务隔离； > 而事务隔离中最核心组件就是锁；这么一来事务也没法并发了，既然锁了，看上去事务同时启动，但是还得等待一个个完成；所以，隔离级别至关重要了； * * * > 另：percona公司对mysql做了二次开发，服务器版本为percona-server，把InnoDB改进为XtraDB存储引擎，mariadb使用了XtraDB引擎，但是名称依然叫InnoDB ##### 总结InnoDB引擎的特性： * 存储数据：表空间 * 并发：MVCC，间隙锁 * 索引：聚集索引、辅助索引 * 性能：预读操作、自适应hash索引、插入操作缓存区 * 备份：支持热备 `SHOW ENGINE INNODB STATUS;` #### 2\. MyISAM存储引擎 MyISAM内部的复杂机制很少，特别适应于读多写少的应用；但是，mysql自己的表，内部的元数据的库还是使用的MyISAM存储引擎； MyISAM存储引擎最大特性是支持全文索引，全文索引指的是全文中的每一个关键字都可以被搜索； MyISAM在崩溃后无法安全恢复，有可能会导致某些数据丢失，比如要插入一行数据，刚插入一般数据库崩溃，只能把刚插入的半行删了，保证数据是一致性的；解决办法是使用MyISAM表修复工具，会对全表扫描，一行行扫描，看哪一行中的数据不是完整的给它删了，所以这就是崩溃后的恢复，如果表要大的话，恢复时间会非常长；索引说可以接受较长时间的修复操作才能使用MyISAM;这个较长时间还是指的较小的表，如果表答可能修复一天都有可能。 MariaDB虽然支持MyISAM存储引擎但是，默认对于MyISAM而言使用的是Aria存储引擎，其实就是MyISAM的增强版，支持崩溃后的安全恢复； MyISAM存储引擎的文件，表和索引是独立存放的；索引可看出来使用的是非聚集索引；因此，每一个索引的叶子节点，都是个指针，指向了真正数据所在的位置；这就类似于书中的目录和后面正文的关系；对于InnoDB，每张表使用两个文件一个是用来存储数据和索引的.ibd文件，一个是用来存储表格式的.frm格式的文件；而对于MyISAM，每张表有三个文件且都放在数据库目录下而不是datadir=指定的目录下，也就是说的datadir指定的目录下的子目录里； * tbl\_name.frm：表格式定义； * tbl\_name.MYD：数据文件； * tbl\_name.MYI：索引文件； ~~~objectivec MariaDB [(none)]> SHOW ENGINES\G; *************************** 5. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO ~~~ ##### 总结MyISAM存储引擎特性： * 加锁好并发：支持表级锁 * 修复：只能手动或自动修复（使用工具）、但可能会丢失数据；并不是安全恢复 * 索引：使用的是非聚集索引 * 支持延迟更新索引：只是为了提高性能 * 支持表压缩：压缩后表数据不能修改 ##### 行格式： `{DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}` * `dynamic:` 动态行 * `fixed`:固定行 * `compressed：`压缩行 * `compact：`紧致行 * `redundent：`冗余行态还是固定行取决于表中字段的类型，例如字段的类型是varchar类型长度是可变的，如果某一字段使用varchar类型，每行可以不同的长度；行长度不一样管理很麻烦，性能比较低；行的长度一样只需根据距离来判断下一行在什么位置就可以了； **以上InnoDB和MyISAM是mysql中最为经典的两种存储引擎；** ~~~ruby MariaDB [hellodb]> SHOW ENGINES; #查看所有支持的存储引擎 +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | FEDERATED | YES | FederatedX pluggable storage engine | MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE '%STORAGE%'; #查看默认存储引擎 +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | InnoDB | | storage_engine | InnoDB | +------------------------+--------+ ~~~ #### 3\. 其他存储引擎 * CSV：将普通的CSV文件（字段基于逗号分隔）作为MySQL表使用； * 所谓CSV文件：一个文件中也是有很多行组成，每一行有很多字段组成，而字段用逗号分隔，把整个文件当表使用； * CVS文件好处在于可以使用grep、awk、sed工具直接处理，但是由于基于文本存储的，浮点型数据转换为文本字符串存储后会丢失精度 * CVS特别适合用于在多个不同的数据库之间共享数据时使用 * MRG\_MYISAM：由多个MyISAM表合并成的虚拟表 * 表可以分割成小表，就像zabbix中数据一样，把表分成多个小标按天存放，一天一张表，要想删除时，直接删除某天的表即可；每一天的表其实就是一个表分区table partion * 要查询多张表是合并即可 * BLACKHOLE：类似于/dev/null，不真正存储任何数据，只会在特定场景中使用 * 对mysql而言blachole stroage engine相当于linux上的/dev/null，索引某张表的存储引擎是该类型，这个表存储的所有数据都不会产生任何IO，因为数据被丢弃了 * 只在特定的场景中使用：比如多级架构执行复制操作时； * MEMORY：基于内存存储，支持hash索引，表级锁，常用语临时表； * PERFORMANCE\_SCHEMA：类似于Linux/proc目录，用于存储mysql的性能数据； * 是关于mysql性能各类统计的收集存储的表，只用来收集mysql运行中的统计数据，做性能评估，或者是有助于优化器优化； * 表中的统计数据只对当前有效，一旦mysql停机，这个表就作废变为空的，启动后就会不断的填入很多收集到的与性能相关的数据 * 此存储引擎用处在于mysql为了达到一切皆sql表的目的，索引把它内部的统计数据基于sql接口进行输出了 * ARCHIVE：支持SELECT 和INSERT 操作，支持行级锁和专用缓存区； * 用于数据永久存储并且当做数据仓库使用；不支持修改操作 * FEDERATED：类似于MRG\_MYISAM，不用支出在于MRG\_MYISAM所合并的多张表一定是同一个mysql服务器上的，甚至是同一个数据库中的多张表；但是FEDERATED能把两个数据库服务器上的表联合在一起使用 * 用于访问其它的远程的MySQL Server的代理接口，它通过创建一个到远程MySQL Server的客户端连接，并将查询语句传输至远程服务器执行； * 自己本地没有表，但可以把这个当做接口，所有发给FEDERATED存储引擎表的所有语句，都会基于mysql连接发给另一个数据库服务器上另外一张表，而后完成数据存取；注：FEDERATED也仅支持mariadb；是夸数据库服务器的角色 #### 4\. MariaDB支持的其他存储引擎 * OQGraph：支持实体存储 * Sphinx SE：搜索引擎 * 而mariadb支持sphinxSE存储引擎，意味着，这个表存储的所有数据都直接基于sphinxSE接口导入到sphinx存储里了； * TokuDB：支持海量数据的存储引擎，支持原生分布式存储 * Cassandra：由facebook研发，分布式无中心的存储； * mariadb支持把表存储Cassandra以后，没有在mysql本地存储，而是导入到Cassandra表中； * CONNECT存储引擎 * SQUENCE存储引擎 #### MySQL事务 ##### 并发控制：锁是mysql实现并发访问控制的重要组件； * 为什么要有锁机制：任何文件或数据集只要支持并发访问模型，就必须基于锁机制对其机制进行控制；对mysql服务器，允许多个线程同时连进来支持做读写操作请求，这样多个查询语句就有可能只对同一个数据集进行，例如第一个线程对数据集中的某行做修改，第二个线程对同一个数据集中的同一行做查询，这样二者同时操作就会带来混乱；所以，mysql为了能够实现并发访问中避免数据读写冲突，就必须施加所机制。 * 锁类型： * 读锁：共享锁，可以共享给其他的读操作 * 写锁：独占所，其他操作不可读写 * 锁粒度： * 表级锁：在表级别施加锁，并发性较低； * 行级锁：在行级别施加锁，并发性较高；维持锁状态的成本较大； * 页级锁（早些时候支持） * 所策略：在锁粒度及数据安全性之间寻求一种平衡机制； * 存储引擎：每种存储引擎都可以自行实现其所策略和锁粒度 * MySQL Server：表级别，可自行决定，也允许显示请求； * 根据所本身根据用户手动施加还是由服务器自动实现，锁又可以分两种类型： * 显式锁：用火狐手动请求施加的锁 * 隐式锁：有存储引擎（或mysql服务器）自行根据需要施加的锁；显示锁使用： (1) LOCK TABLES 命令：锁定整张表或多张表用法：LOCK TABLES tbl\_name \[\[AS\] alias\] lock\_type \[,tbl\_name \[\[AS\] alias \] lock\_type\] ... lock\_type : 所类型 READ\[ LOCAL\] | \[LOW\_PRIORITY\] WRITE tbl\_name: 锁名称 ~~~css UNLOCK TABLES; 解锁； (2) FLUSH TABLES 命令：作用在于如果一张表被打开了，需要把内存中的所有数据都清到磁盘上，在把表关闭；用法：FLUSH TABLES tbl_name[,...] [WITH READ LOCK] [FOR UPDATE] WITH READ LOCK: 施加读锁 FOR UPDATE: 请求修改，即施加写锁 (3) SELECT 语句：锁定表中的某些行用法：SELECT clause [FOR UPDATE] [WITH READ LOCK] ~~~ * * * ~~~csharp 示例： MariaDB [(none)]> CREATE DATABASE testdb; #创建数据库 Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> USE testdb Database changed MariaDB [testdb]> CREATE TABLE tbl; #创建表 ERROR 1113 (42000): A table must have at least 1 column MariaDB [testdb]> CREATE TABLE tbl (id INT NOT NULL ,name VARCHAR(50)); #插入数据 Query OK, 0 rows affected (0.05 sec) MariaDB [testdb]> INSERT INTO tbl (id,name) VALUE (1,'TOM'); Query OK, 1 row affected (0.01 sec) MariaDB [testdb]> SELECT * FROM tbl; +----+------+ | id | name | +----+------+ | 1 | TOM | +----+------+ 1 row in set (0.01 sec) MariaDB [testdb]> LOCK TABLE tbl READ; #施加读锁 Query OK, 0 rows affected (0.00 sec) 另外启动一个线程： MariaDB [(none)]> use testdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [testdb]> SELECT * FROM tbl; +----+------+ | id | name | +----+------+ | 1 | TOM | +----+------+ 1 row in set (0.00 sec) MariaDB [testdb]> INSERT INTO tbl (id,name) VALUE (2,‘JERRY’); #插入数据被阻塞线程1释放锁： MariaDB [testdb]> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) 线程2插入数据成功： MariaDB [testdb]> INSERT INTO tbl (id,name) VALUE (2,'JERRY'); Query OK, 1 row affected (0.01 sec) ~~~ ##### 事务日志：事务日志就是mysql数据文件之外的另外一个存储区域，这个存储区域带来的作用是：当一个事务型存储引擎在运行过程中，需要启动一个事务并完成数据修改时，所有的修改操作（由多个sql语句组成），每一次的操作所涉及到的数据修改，这个修改的操作要转换成底层存储引擎所支持的相关操作的操作过程，它会把每一次的sql语句所涉及的操作步骤，具体的过程记录在事务日志文件中。注意，事务日志记录的是每一步的具体操作；例如，插入一行的数据是什么、插入什么位置、什么时候插入得等都记录下来；再例如，如果是修改几行，先修改哪行后修改哪行、几点开始修改、几点结束等等都记录下来；而且这个日志要能够重现操作就叫做事务日志。一个事务型存储引擎它的操作借助于事务日志来保证其对应的事务特性；所以，任何事务型存储引擎的相关操作，默认不会直接写在数据库文件上，而是先写在事务日志中，并且事务日志为保证足够可靠，基本上很少在内存中缓冲，写完建基本比较差，都会先写在内存上，然后等过一会再同步到磁盘上；事务日志也有这段缓冲区，但这个缓冲区不能太大时间也不能太久，因为如果允许缓存5秒钟，系统崩溃最大会丢失5秒的数据，所以建议这个时间要足够短，一般为1秒钟同步到磁盘上一次；但是，同步越频繁性能就越差，数据可靠性就越高。事务日志的每一步在记录时，还会把修改之前的原始数据内容也记录下来，这是为了支持undo和redo机制而记录的；即当记录的是修改数据时会把又该的数据之前的数据记录下来。假如，操作进行到一半服务器崩溃了，把修改的操作日志撤销了即可，因为还没有同步到磁盘上；如为例如保证数据的稳定性，一个大事务中间有可能事务还没完成，已经在事务日志记录了30个，再记录事务日志会先把一部分信息往磁盘上同步，开始真正修改原始数据了，这时如果崩溃，则只能把已经同步到表中的数据撤销，这就是undo机制；还有一种情况是，比如一个事务有60个sql语句都写完了且都记录在事务日志中了，但只有气筒30个同步到了磁盘上，如果此时崩溃，则此时数据是不一致的；因为有些事务已经提交，但是没有存储到磁盘上，这样就必须把事务日志中为完成的语句同步到磁盘上，这就是redo机制；同步的要做提交，没能完成的要做回滚，这就是崩溃后恢复。如果日志文件非常大，恰好写满了崩溃，下次启动mysql时，必须把里面的所有语句统统同步到磁盘才能正常启动，；如果事务日志文件大道2G，为了能够把2G的语句同步到磁盘上，有可能mysql服务器启动半小时，所以为了避免崩溃后启动时间太长，把日志文件设置小一点；但是，有事会出现日志填满了，但是日志内容还没来的急同步到磁盘上，还有新的日志需要写进来，那么久在启动一个文件；也就是说日志文件一般启动2个或3个是轮转使用的，所谓轮转指的是第一个日志文件填满了就用第二个，同时把第一个日志文件同步到磁盘上，等第二个日志写满了就可以再使用第一个了，这样轮转使用，由多个事务日志文件组成的叫做事务日志组，日志组内至少应该有2个文件，但是多了同样不好。事务日志文件和数据文件不应该放在同一磁盘上，因为会对磁盘写IO操作带来很大压力，影响其性能；分开存放比较理想，但是有些场景又要必须放在一起，例如基于逻辑卷操作时；如果事务日志所在磁盘崩溃，则数据库数据无法保持一致；所以，要把事务日志磁盘做镜像；建议使用raid1；数据文件也很重要建议使用raid10； * 事务：一组原子性的SQL查询，或者多个SQL语句组成了一个独立的工作单元 * 事务日志：将随机写转换为顺序写 `innodb_log_files_in_group`：一个日志组内有多少个日志文件，默认2个； `innodb_log_group_home_dir`：日志组目录路径，当前目录就是datadir指定的路径 `innodb_log_file_size`：每一个InnoDB事务日志的大小，默认5M `innodb_mirrored_log_groups` 修改配置文件，重启生效；这些参数，建议在mysql初始化时就要设置好再启动，而不能在启动后再修改参数是不理想的做法； * ACID测试：从四个角度判断存储引擎是否支持事务 * A：automicity，原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚； * C：consistency，一致性；数据库总是从一个一致性状态转换为另一个一致性状态； * I：isolation，隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见的，隔离有多种级别，主要是为了并发，隔离级别一个有4个； * D：durability，持久性；事务一旦提交，其所做的修改就会永久保存于数据库中；注意：MyISAM存储引擎是不支持事务的，InnoDB支持事务，所以要想使用事务得确保使用的是InnoDB等支持事务的存储引擎； * mysql默认把每个语句当做一个事务提交，可以手动关闭自动提交功能，手动来启动事务； ~~~css 启动事务：START TRANSACTION 结束事务： (1)完成，提交：COMMIT (2)未完成，回滚：ROLLBACK 回滚时如果做过时间点保存可以一点点回滚；事务支持savepoint SAVEPINIT identifier；设置保存点 ROLLBACK TO [SAVEPOINT] identifier；回滚到指定保存点，默认回滚到最开始处 RELEASE SAVEPOINT identifier；释放保存点 ~~~ * * * ~~~ruby 示例：查看是否开启自动提交： MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%commit%'; +-------------------------------------------+-------+ | Variable_name | Value | +-------------------------------------------+-------+ | aria_group_commit | none | | aria_group_commit_interval | 0 | | autocommit | ON | | innodb_commit_concurrency | 0 | | innodb_flush_log_at_trx_commit | 1 | | innodb_use_global_flush_log_at_trx_commit | ON | +-------------------------------------------+-------+ autocommit=ON:自动提交，把每一个执行的语句当做一个事务，执行语句就提交，因为每一次事务提交都会导致数据要从事务日志缓冲区写到事务日志，随后还要从事务日志写到数据文件；这很可能会影响性能，而且很多时候多个语句才是一个事务；因此可以使用set修改变量，关闭自动提交功能； MariaDB [(none)]> SET @@SESSION.autocommit=OFF; #修改session级别的参数，关闭自动提交 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 示例：手动启动事务： MariaDB [(none)]> START TRANSACTION; MariaDB [(none)]> use testdb; Database changed MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | +----+-------+ 2 rows in set (0.00 sec) MariaDB [testdb]> INSERT INTO tbl (id,name) VALUE (3,'LUCY'); Query OK, 1 row affected (0.01 sec) MariaDB [testdb]> UPDATE tbl SET name='guojing' WHERE id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [testdb]> SELECT * FROM tbl; +----+---------+ | id | name | +----+---------+ | 1 | guojing | | 2 | JERRY | | 3 | LUCY | +----+---------+ 3 rows in set (0.00 sec) MariaDB [testdb]> ROLLBACK; #手动回滚，结束事务 Query OK, 0 rows affected (0.01 sec) MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | +----+-------+ 2 rows in set (0.00 sec) 示例2: MariaDB [testdb]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) MariaDB [testdb]> INSERT INTO tbl VALUE (3,'LUCK'),(4,'MURNN'); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | +----+-------+ 4 rows in set (0.00 sec) MariaDB [testdb]> SAVEPOINT first; #做时间点，名称为first Query OK, 0 rows affected (0.00 sec) MariaDB [testdb]> UPDATE tbl SET name='guojing' WHERE id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [testdb]> SAVEPOINT second; #做第二个时间保存点，名称为second Query OK, 0 rows affected (0.00 sec) MariaDB [testdb]> INSERT INTO tbl VALUE (5,'HAHA'); Query OK, 1 row affected (0.00 sec) MariaDB [testdb]> SELECT * FROM tbl; +----+---------+ | id | name | +----+---------+ | 1 | guojing | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | | 5 | HAHA | +----+---------+ 5 rows in set (0.00 sec) MariaDB [testdb]> ROLLBACK TO second; #回滚到第二个保存点 Query OK, 0 rows affected (0.00 sec) MariaDB [testdb]> SELECT * FROM tbl; +----+---------+ | id | name | +----+---------+ | 1 | guojing | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | +----+---------+ 4 rows in set (0.00 sec) MariaDB [testdb]> ROLLBACK TO first; #回滚到第一个保存点 Query OK, 0 rows affected (0.00 sec) MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | +----+-------+ 4 rows in set (0.00 sec) MariaDB [testdb]> COMMIT; 手动提交，结束事务 Query OK, 0 rows affected (0.01 sec) ~~~ * * * **建议：手动显示请求和提交事务，不要使用自动提交功能** #### 事务的隔离级别：由低到高，分为4个级别： * 第一级别：read-uncommitted；读未提交--> 会存在脏读；看到数据可能是未提交的，别人回滚后，看到的数据不准确； * 第二级别：read-committed；读提交--> 会产生不可重复读；只有别人提交后才能看到数据，解决了脏读；不可重复读，指的是同一事务中两次读取同一数据得到内容不一样；就是别人没提交时读到的内容，别人修改数据提交后又读到的，两次读取的结果不同；这就叫不可重复读； * 第三级别：repeatable-read；可重读--> 会产生幻读；默认级别; 两次读取数据，只要自己没提交，不管别人提没提交，自己做一快照，如果启动事务时有三行数据，读的过程中只要自己不提交只有三行数据，但是，外衣别的事务提交了数据，例如新增了几个行，那几行明明在，但自己假装看不见，产生幻读； * 第四级别：serializable；可串行化--> 性能较差；如果别人没提交，读的数据是别人没提交的，要等待别人的事务完成才能读取到，有先有后，避免了幻读； ~~~ruby 查看默认事务隔离级别： MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%tx%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 设置隔离级别： MariaDB [(none)]> SET @@global.tx_isolation='READ-COMMITTED'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%tx%'; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+ 1 row in set (0.00 sec) 查看InnoDB存储引擎的状态信息： MariaDB [testdb]> SHOW ENGINE innodb STATUS\G; ~~~ MySQL是通过MVCC机制来实现事务隔离级别控制的；为什么启动2个事务，基于PREPEATABLE-READ 隔离级别修改数据但还能看到之前的数据，就是因为自己启动了一个快照，而快照功能的创建，删除等等都是由事务功能mysql服务器自动在服务器级别进行管理的 ~~~ruby 示例：两个线程，分别验证各个事务隔离级别的效果 (1)演示读未提交,两个线程控制台均设置事务隔离级别为读未提交，并关闭事务自动提交功能 MariaDB [testdb]> SET @@SESSION.tx_isolation='READ-UNCOMMITTED'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SET @@SESSION.autocommit=OFF; Query OK, 0 rows affected (0.00 sec) 两边同时启动事务： MariaDB [(none)]> START TRANSACTION; 线程1查看： MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | +----+-------+ 4 rows in set (0.00 sec) 线程2插入数据： MariaDB [testdb]> INSERT INTO tbl VALUE (5,'haha'); Query OK, 1 row affected (0.00 sec) 线程1再查看： MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | | 5 | haha | +----+-------+ 5 rows in set (0.00 sec) 线程2：回滚，结束事务： MariaDB [testdb]> ROLLBACK; Query OK, 0 rows affected (0.01 sec) 线程1再查看： MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | +----+-------+ 4 rows in set (0.00 sec) MariaDB [testdb]> COMMIT; #提交结束事务 Query OK, 0 rows affected (0.00 sec) ~~~ * * * ~~~ruby 示例：演示读提交两个线程都改为读提交，并启动事务： MariaDB [testdb]> SET @@SESSION.tx_isolation='READ-COMMITTED'; MariaDB [testdb]> START TRANSACTION; 线程1启动查询： MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | +----+-------+ 4 rows in set (0.01 sec) 线程2插入数据未提交： MariaDB [testdb]> INSERT INTO tbl VALUE (5,'haha'); Query OK, 1 row affected (0.02 sec) 线程1再查看数据未变化： MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | +----+-------+ 4 rows in set (0.00 sec) 线程2提交事务： MariaDB [testdb]> COMMIT; Query OK, 0 rows affected (0.02 sec) 线程1再查看，数据发生变化 MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | | 5 | haha | +----+-------+ 5 rows in set (0.00 sec) MariaDB [testdb]> COMMIT; #提交事务 ~~~ * * * ~~~ruby 示例：演示幻读，可重复读两个线程都修改事务级别为可重复读，并启动事务： mysql> SHOW GLOBAL VARIABLES LIKE '%iso%'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.00 sec) mysql> SET @@SESSION.autocommit=OFF; Query OK, 0 rows affected (0.02 sec) 线程1查看： mysql> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | HAHA | +----+-------+ 4 rows in set (0.00 sec) 线程2删除1行（未提交）： mysql> DELETE FROM tbl WHERE id=2; Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM tbl; +----+------+ | id | name | +----+------+ | 1 | TOM | | 3 | LUCK | | 4 | HAHA | +----+------+ 3 rows in set (0.00 sec) 线程1查看，未发生变化： mysql> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | HAHA | +----+-------+ 4 rows in set (0.00 sec) 线程2提交： mysql> COMMIT; Query OK, 0 rows affected (0.07 sec) 线程1再查看，扔然未变化： mysql> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | HAHA | +----+-------+ 4 rows in set (0.00 sec) 线程1提交事务后再查看数据发生变化： mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM tbl; +----+------+ | id | name | +----+------+ | 1 | TOM | | 3 | LUCK | | 4 | HAHA | +----+------+ 3 rows in set (0.00 sec) ~~~ * * * ~~~ruby 示例：演示可串行化 2个线程都设置事务隔离级别可串行化： mysql> SET @@SESSION.transaction_isolation='SERIALIZABLE'; 线程1查看： mysql> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 3 | LUCK | | 4 | HAHA | | 2 | JERRY | +----+-------+ 4 rows in set (0.00 sec) 线程2启动事务，并插入数据： mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO tbl VALUE (5,'HUANG RONG'); Query OK, 1 row affected (18.31 sec) 线程1启动事务并查看，被阻塞： mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM tbl; 线程2提交事务： mysql> COMMIT; Query OK, 0 rows affected (0.03 sec) 线程1查看： mysql> SELECT * FROM tbl; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> SELECT * FROM tbl; +----+------------+ | id | name | +----+------------+ | 1 | TOM | | 3 | LUCK | | 4 | HAHA | | 2 | JERRY | | 5 | HUANG RONG | +----+------------+ 5 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) ~~~