一、概述
1.作用
存储引擎用于规划和存取数据，类似于Linux文件系统
2.类型
- MEMORY
- CSV
- MRG_MYISAM
- BLACKHOLE
- InnoDB(当前版本默认使用)
- PERFORMANCE_SCHEMA
- ARCHIVE
- MyISAM
- FEDERATED
|
可以使用show engines; #进入数据库查看MySQL现有的存储引擎 可以使用select @@defaullt_storage_engine; #查看MySQL当前使用的默认存储引擎 |
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
1 row in set (0.00 sec)二、InnoDB
1.InnoDB核心特性
- 事务
- A(原子性)
- C(一致性)
- I(隔离性)
- D(持久性)
- 多版本并发控制(MVCC)
- 行级锁
- 支持热备份
- 外键
- 自动故障恢复(ACSR)
2.InnoDB引擎相关操作
- 建表时设定存储引擎
| create table 表名 (列1,列2,列3,...)engine=存储引擎 |
mysql> create table t1(id int primary key auto_increment,name varchar(5) not null default 'NA')engine='myisam';
Query OK, 0 rows affected (0.00 sec)
mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(5) NOT NULL DEFAULT 'NA',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)- 替换存储引擎
| alter table 表名 engine=存储引擎 |
|
alter table 表名 engine=存储引擎 #语句不仅可以替换存储引擎，而且可以整理表碎片； optimize table 表名; #语句也可以整理表碎片； |
mysql> alter table t1 engine=InnoDB charset=utf8mb4;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(5) CHARACTER SET latin1 NOT NULL DEFAULT 'NA',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)3.表空间迁移
表空间文件 *.ibd
|
每个表分为.frm和.ibd文件存储在系统内；
.frm文件负责保存表的结构信息(如列的定义和数据类型)
.ibd文件负责保存数据行和索引信息，.ibd文件称为独立表空间；
ibdata1是系统表空间，主要用于存储系统数据字典、undo信息和临时表信息(早期) |
step01创建与原表结构相同的空表
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 6 |
+-------------+
1 row in set (0.00 sec)
mysql> show create table city;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| city | CREATE TABLE `city` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
KEY `idx_population` (`Population`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
mysql> CREATE TABLE city(
-> ID int(11) NOT NULL AUTO_INCREMENT,
-> Name char(35) NOT NULL DEFAULT '',
-> CountryCode char(3) NOT NULL DEFAULT '',
-> District char(20) NOT NULL DEFAULT '',
-> Population int(11) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`ID`),
-> KEY CountryCode (CountryCode)
-> )ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)step02删除空表的的表空间文件
| alter table 表名 discard tablespace; #放弃表空间 |
mysql> alter table city discard tablespace;
Query OK, 0 rows affected (0.45 sec)
mysql> system ls -l /data/mysql/3307/data/test;
total 16
-rw-r----- 1 mysql mysql 8710 Aug 30 14:52 city.frm
-rw-r----- 1 mysql mysql 65 Aug 30 14:50 db.optstep03将原表的表空间文件导入
| alter table 表名 import tablespace; #导入表空间 |
[root@db01 ~]# cd /data/mysql/3306/data/world/
[root@db01 /data/mysql/3306/data/world]# ls city.*
city.frm city.ibd
[root@db01 /data/mysql/3306/data/world]# cp city.ibd /data/mysql/3307/data/test/mysql> system chown -R mysql. /data/mysql/3307/data/test/city.ibd;
mysql> system ls -l /data/mysql/3307/data/test;
total 7184
-rw-r----- 1 mysql mysql 8710 Aug 30 14:52 city.frm
-rw-r----- 1 mysql mysql 7340032 Aug 30 14:55 city.ibd
-rw-r----- 1 mysql mysql 65 Aug 30 14:50 db.opt
mysql> alter table city import tablespace;
Query OK, 0 rows affected, 1 warning (0.17 sec)step04验证
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 6 |
+-------------+
1 row in set (0.00 sec)
mysql> select * from world.city where id<5;
+----+----------------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
1 row in set (0.00 sec)
mysql> select * from test.city where id<5;
+----+----------------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)4. InnoDB引擎核心参数
| innodb_flush_log_at_trx_commit=1 #事务提交时立即刷写redobuffer到磁盘("双一标准"之一) |
|
0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入文件系统缓存并且秒fsync磁盘一次； 1，每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；flush 到操作系统的文件系统缓存 fsync到物理磁盘； 2，每次事务提交引起写入文件系统缓存,但每秒钟完成一次fsync磁盘操作； |
mysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)| innodb_buffer_pool_size=容量 #设定Page buffer cache内存池空间大小(建议设置为物理内存的70%-80%，最大不要超过物理内存的95%) |
mysql> select @@innodb_buffer_pool_size/1024/1024;
+-------------------------------------+
| @@innodb_buffer_pool_size/1024/1024 |
+-------------------------------------+
| 128.00000000 |
+-------------------------------------+
1 row in set (0.00 sec)| innodb_flush_method=O_DIRECT #指定数据刷写不经由OS buffer，从Page buffer cache直接写入磁盘(适用于高速磁盘环境) |
|
O_DIRECT :数据缓冲区写磁盘,不走OS buffer fsync :日志和数据缓冲区写磁盘,都走OS buffer O_DSYNC :日志缓冲区写磁盘,不走OS buffer |
mysql> select @@innodb_flush_method;
+-----------------------+
| @@innodb_flush_method |
+-----------------------+
| NULL |
+-----------------------+
1 row in set (0.00 sec)[root@db01 /data/mysql/3306]# vim my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/3306/log/error.log
log_bin=/data/mysql/3306/log/bin-log
binlog_format=row
slow_query_log=ON
slow_query_log_file=/data/mysql/3306/log/slow.log
long_query_time=0.1
log_queries_not_using_indexes=ON
innodb_flush_log_at_trx_commit=1
innodb_buffer_pool_size=1G
innodb_flush_method=O_DIRECT
[mysql]
socket=/tmp/mysql.sock
[root@db01 /data/mysql/3306]# systemctl restart mysqld
[root@db01 /data/mysql/3306]# mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
| 1.000000000000 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> select @@innodb_flush_method;
+-----------------------+
| @@innodb_flush_method |
+-----------------------+
| O_DIRECT |
+-----------------------+
1 row in set (0.00 sec)5.InnoDB引擎核心参数使用建议
|
最高安全模式 innodb_flush_log_at_trx_commit=1 Innodb_flush_method=O_DIRECT 最高性能 innodb_flush_log_at_trx_commit=0 Innodb_flush_method=fsync |
三、事务
1.事务的控制语句
标准的事务生命周期
| begin; #开启一个事务 |
| 标准的事务语句：insert，update，delete;出现在begin之后的语句一定是标准的事务语句。 |
| rollback; #回滚并结束事务 |
#原始数据
mysql> select * from city where id=1;
+----+-------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)
mysql> delete from city where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from city where id=1;
+----+-------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)| commit; #提交并结束事务 |
#原始数据
mysql> select * from city where id=1;
+----+-------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from city where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from city where id=1;
Empty set (0.00 sec)
mysql> select * from city limit 2;
+----+----------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------+-------------+----------+------------+
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
+----+----------+-------------+----------+------------+
2 rows in set (0.00 sec)自动提交机制
| 自动提交机制默认开启 |
| select @@autocommit; #查看数据库自动提交机制状态； |
- 关闭自动提交机制
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql> set autocommit=0; #当前窗口有效
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
mysql> ^DBye
[root@db01 /data/mysql/3306]# mysql -uroot -p123 -e "select @@autocommit"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+隐式提交机制
- 同一个会话连续出现两个begin;
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from city where id=2;
Query OK, 1 row affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from city limit 2;
+----+----------------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
+----+----------------+-------------+----------+------------+
2 rows in set (0.00 sec)- SET语句
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from city where id=3;
Query OK, 1 row affected (0.00 sec)
mysql> set innodb_flush_method=null;
ERROR 1238 (HY000): Variable 'innodb_flush_method' is a read only variable
mysql> select * from city limit 2;
+----+----------------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+---------------+------------+
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
+----+----------------+-------------+---------------+------------+
2 rows in set (0.00 sec)- 触发隐式提交的非事务语句
|
DDL语句(ALTER、CREATE、DROP、TRUNCATE) DCL语句(GRANT、REVOKE和SET PASSWORD) 锁定语句(LOCK TABLES和UNLOCK TABLES) |
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from city where id=4;
Query OK, 1 row affected (0.00 sec)
mysql> alter table city charset=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from city limit 2;
+----+-----------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+-----------+-------------+---------------+------------+
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
+----+-----------+-------------+---------------+------------+
2 rows in set (0.00 sec)- LOAD DATA INFILE
- SELECT FOR UPDATE
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from city where id=5;
Query OK, 1 row affected (0.00 sec)
mysql> select * from city limit 2 for update;
+----+-----------+-------------+--------------+------------+
| ID | Name | CountryCode | District | Population |
+----+-----------+-------------+--------------+------------+
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| 7 | Haag | NLD | Zuid-Holland | 440900 |
+----+-----------+-------------+--------------+------------+
2 rows in set (0.00 sec)2.事务特性
| 每个事务由Begin开始，由Commit结束； |
- 事务的特性
- A 原子性：在整个事务的流程中，数据库操作最小单元；要么全执行成功，要么全部执行失败
- C 一致性：保证数据库事务关系数据的完整性不被破坏以及业务逻辑上的一致性
- I 隔离性：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离
- D 持久性：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
- redo事务日志(重做日志)
|
redo 作用：主要用于保证事务的D特性，对于A和C特性也有一定保证；在自动故障恢复(ACSR)期间，实现了前滚的功能； innodb_flush_log_at_trx_commit=1 #事务提交立即刷写redo日志到磁盘(刷写失败则事务提交失败); |
[root@db01 ~]# ll /data/mysql/3306/data/ib_logfile*
-rw-r----- 1 mysql mysql 50331648 Aug 30 15:50 /data/mysql/3306/data/ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Aug 30 15:50 /data/mysql/3306/data/ib_logfile1
[root@db01 ~]# file /data/mysql/3306/data/ib_logfile0 /data/mysql/3306/data/ib_logfile1
/data/mysql/3306/data/ib_logfile0: data
/data/mysql/3306/data/ib_logfile1: data- undo事务日志(回滚日志)
|
undo事务日志文件ibdata1； 作用:主要用户保证事务的A和C特性，对于I特性也有一定保证；在自动故障恢复(ACSR)期间，实现的是未提交事务回滚的功能； |
[root@db01 ~]# ll /data/mysql/3306/data/ibdata*
-rw-r----- 1 mysql mysql 12582912 Aug 30 15:50 /data/mysql/3306/data/ibdata1
[root@db01 ~]# file /data/mysql/3306/data/ibdata1
/data/mysql/3306/data/ibdata1: data- I的特性保证机制
- 锁(Lock)：行级锁定，事务在对某行数据修改时会持有数据行的锁，其他事务不能同时更新此行；
- 隔离级别
|
RU(读未提交) RC(读已提交) RR(可重复读) SR(可串行化) |
- undo快照(一致性快照)
3.隔离级别(transcation_isolation)
隔离级别可以解决MVCC和读一致性问题。
|
隔离级别会影响数据的读取，默认级别是RR模式 select @@tx_isolation; #查看数据库当前隔离界别 |
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)- RU(READ-UNCOMMITTED)：可脏读(读内存数据)，一般不允许出现
- RC(READ-COMMITTED)：可能出现幻读的，但能防止脏读
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t100w set num=123456 where id=1;
Query OK, 1 row affected (0.54 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t100w where id=1;
+------+--------+------+------+---------------------+
| id | num | k1 | k2 | dt |
+------+--------+------+------+---------------------+
| 1 | 123456 | 0M | IJ56 | 2019-08-30 16:50:41 |
+------+--------+------+------+---------------------+
1 row in set (0.29 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)mysql> select * from t100w where id=1;
+------+--------+------+------+---------------------+
| id | num | k1 | k2 | dt |
+------+--------+------+------+---------------------+
| 1 | 123456 | 0M | IJ56 | 2019-08-30 16:50:41 |
+------+--------+------+------+---------------------+
1 row in set (0.29 sec)- RR(REPEATABLE-READ)：可防止"幻读"现象；(利用的是undo的快照技术+GAP[间隙锁]+NextLock[下键锁])
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
mysql> begin;
mysql> update t100w set num=20190830 where id=1;
mysql> select * from t100w where id=1;
+------+----------+------+------+---------------------+
| id | num | k1 | k2 | dt |
+------+----------+------+------+---------------------+
| 1 | 20190830 | 0M | IJ56 | 2019-08-30 16:21:49 |
+------+----------+------+------+---------------------+
1 row in set (0.30 sec)
mysql> commit;mysql> select * from t100w where id=1;
+------+-------+------+------+---------------------+
| id | num | k1 | k2 | dt |
+------+-------+------+------+---------------------+
| 1 | 25503 | 0M | IJ56 | 2019-08-12 11:41:16 |
+------+-------+------+------+---------------------+
1 row in set (0.29 sec)mysql> select * from t100w where id=1;
+------+----------+------+------+---------------------+
| id | num | k1 | k2 | dt |
+------+----------+------+------+---------------------+
| 1 | 20190830 | 0M | IJ56 | 2019-08-30 16:21:49 |
+------+----------+------+------+---------------------+
1 row in set (0.30 sec)mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t100w where id=1;
+------+----------+------+------+---------------------+
| id | num | k1 | k2 | dt |
+------+----------+------+------+---------------------+
| 1 | 20190830 | 0M | IJ56 | 2019-08-30 16:21:49 |
+------+----------+------+------+---------------------+
1 row in set (0.29 sec)- SR(SERIALIZABLE)：可防止死锁，但是并发事务性较差
|
在RC级别下，可以减轻GAP+NextLock锁的问题，但是会出现幻读现象，一般为了保证读一致性会在正常的SELECT语句后添加for update语句，但是语句执行完一定要记得commit，否则容易出现等待比较严重的情况 SELECT 列1,列2,列3,... FROM 表名 WHERE 筛选条件 FOR UPDATE COMMIT |
附：思维导图
