MySql学习之事务和并发控制

数据库模型:

ACID:

原子性(automicity)：一个事务必须被看作一个不可分割的最小单元。对于事务里的操作要么全部成功，要么全部失败，不可能执行其中一部分。这就是事务的原子性。
一致性(consistency)：数据库总是从一个一致性的状态转换到另外一个一致性的状态。在事务没有提交之前，事务中做出的修改不会被保存到数据库中。
隔离性(isolation)：通常来说，一个事务做的修改，对于其他事务是不可见的。当然这取决于隔离的级别。
持久性(durability)：一旦事务提交，数据将会被永久地保存在数据库中，永远保存在数据库。

数据库隔离的级别：

1、未提交可读：A事务修改某条记录，未提交；B事务可以读取A事务修改后的该条记录数据值。
2、提交可读(不可重复读)：A事务第一次读取某条记录后，B事务修改该条记录且提交，A事务再次读取该条记录，A事务2次读取的值不同。
3、可重复读：A事务第一次读取某条记录后，B事务修改该条记录且提交，A事务再次读取该条记录，A事务2次读取的值相同。
该级别无法防住“幻读”，即A事务第一次读取某个范围记录中，B事务在该范围新增一条记录且提交，A事务再次读取该范围记录时，A事务第2次读取的范围值会多一行。
4、可串行化：强制事务串行执行，避免了幻读的问题。该方式是在每一行都加锁，所以可能会存在大量的超时和锁竞争，一般很少用这个级别，
只用必须保证数据的一致性且可以接受不需要并发的场景才会使用。

死锁：

是指两个或多个事务在同一个资源上的相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就会产生死锁，
多个事务锁定同一个资源也会产生死锁。
比如下面两个事务：
事务1：
START TRANSACTION;
UPDATE T_ACCOUNT SET ACCOUNT_NO = 55555 WHERE ID = 3;
UPDATE T_ACCOUNT SET ACCOUNT_NO = 33333 WHERE ID = 4;
COMMIT;

事务2：
START TRANSACTION;
UPDATE T_ACCOUNT SET ACCOUNT_NO = 33333 WHERE ID = 4;
UPDATE T_ACCOUNT SET ACCOUNT_NO = 55555 WHERE ID = 3;
COMMIT;

如果凑巧，两个事务都执行了第一条UPDATE语句，更新了一行数据且同时锁定了该行数据，接着同时去UPDATE第二条语句会发现对方被锁定，等待获得锁，进而进入死循环。
为了解决这个问题，数据库系统实现了各种死锁检测和死锁超时检测机制。越复杂的系统，比如InnoDB存储引擎，越能检测出死锁的循环依赖，并返回一个错误。这种解决
方式很有效，否则会出现非常慢的查询。还有一种解决方式，就是在查询的时候达到锁的超时时间的设定后放弃锁请求，这种方式通常来说不太好。InnoDB目前处理死锁的
方式是：将持有最少行级排它锁的事务进行回滚。

MYSQL采用自动提交模式。如果不是显示开始一个事务，则每个查询都被当做一个事务执行提交操作。在当前连接中，可以通过设置AUTOCOMMIT变量来启用或禁用自动提交模式。
查看自动提交是否开启：SHOW VARIABLES LIKE ‘AUTOCOMMIT’;
1或者ON表示开启，0或者OFF表示关闭：SET AUTOCOMMIT = ‘1’;

设置全局的事务隔离级别：SET TRANSACTION ISOLATION LEVEL READ COMMIT;
设置当前会话的事务隔离级别：SET SESSION TRANSACTION ISOLATION LEVEL READ COMMIT;

InnoDB的多版本并发控制(MVCC):

MVCC是行级锁的一个变种，但它在很多情况下避免了加锁的操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。
MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个
事务对同一张表，同一时刻看到的数据可能是不一样的。
InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间(或删除时间)。当然存储的并不是实际
的时间值，而是系统版本号。没开始一个事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号作为比
较。下面看一下可重复读隔离级别下，MVCC如何操作的：
1、SELECT
InnoDB会根据一下两个条件检查记录
a.InnoDB只会查找版本早于当前事务版本的行(也就是行的系统版本号小于或等于当前事务的系统版本号)，这样可以确保事务读取的行，要么在事务开始前已经存在，要
么是事务自身插入或者修改的。
b.行的删除版本号要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始前未被删除。
2、INSERT
InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
3、DELETE
InnoDB为删除的每一行保存当前系统版本号作为删除标志。
4、InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标志。

使用这2个额外系统版本号，使大多数读操作都可以不用加锁。这使大多数读操作都可以不用加读锁。这样设计使得读数据操作更简单，性能更好，并且能保证读取的准确性。
不足之处是需要额外的存储空间，需要做更多的行检查工作。MVCC只在不可重复读和可重复读两个隔离级别下工作。

在文件系统中，MYSQL将每个数据库(也可称schema)保存为数据目录下的一个子目录。创建表时，MYSQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。例如创建
一个名为MyTable的表，MYSQL会将MyTable.frm文件中保存该表的定义。因为MYSQL使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感和具体的平台有关。
查询表的基本信息：

select * from information_schema.TABLES where information_schema.TABLES.TABLE_SCHEMA = '库名' and information_schema.TABLES.TABLE_NAME = '表名';

Name：表名
Engine:表的存储引擎类型。
Row_format:行的格式。对于MyISAM表，可选的值为Dynamic、Fixed、Compressed。Dynamic的长度是可变的，一般包含可变长度的字段，比如VARCHAR或BLOB。Fixed的行长度则是
固定的，只包含固定长度的列，如CHAR、INTEGER。Compressed的则只在压缩表中存在。
Rows:表中的行数。对于MyISAM和其他一些引擎，该值是精确的，但对于InnoDB，该值是估计值。
Avg_row_length:平均每行包含的字节数。
Data_length:表数据的大小(以字节为单位)。
Max_data_length:表的最大容量，该值和存储引擎有关。
Index_length:索引的大小(以字节为单位)。
Data_free:对于MyISAM表，表示已分配但目前没有使用的空间。这部分空间包括了之前删除的行，以及后续可以被INSERT利用到的空间。
Auto_increment:下一个AUTO_INCREMENT的值。
Create_time:表的创建时间。
Update_time:表的最后修改时间。
Check_time:使用CHECK TABLE命令或者工具最后一次检查表的时间。
Collation:表的默认字符集和字符排列规则。
Checksum:如果开启，保存的是整个表的实时校验和。
Create_options:创建表指定的其他选项。
Comment:该列包含了一些其他的额外信息。