mysql 会话变量并发_MySQL并发访问控制机制

最新推荐文章于 2022-07-28 21:30:00 发布

rain john

最新推荐文章于 2022-07-28 21:30:00 发布

阅读量378

点赞数

文章标签： mysql 会话变量并发

本文链接：https://blog.csdn.net/weixin_35215045/article/details/113279280

版权

并发控制：并发控制对于任何一个允许多个用户连入进来并请求资源的服务来讲都是必须要完成的功能

MySQL作为服务器来讲，其内部有很多数据有很多张表，表中有数据，无论是myisam还是InnoDB存储引擎对于单张表来讲如果没做分区，那么其数据都放在同一个文本文件，当两个客户端同时发起MySQL会话，连入MySQL时，MySQL服务会启动两个会话线程来给两个用户建立连接，假如第一个用户发起select语句查询表中的所有行，如表tb1，而第二个用户希望在表中同时要插入数据，那么这两条语句能否同时执行？如果插入语句刚好插入一半查询语句刚查到了刚插入的语句，那么查询的结果就不完整了，因此当并发有很多用户同时访问时，必须使用一种机制来实现其内部数据的并发访问控制，以保证每个用户所看到的数据尽可能是完整的。因此无论任何时候，只要有多个查询需要在同一时刻修改数据，都会产生并发控制问题。

MySQL是插件式存储引擎，及其并发访问控制可以在两个层次上完成，1.MySQL的核心程序【MySQL内部完成】2.MySQL存储引擎层完成

在两个层次上实现：

MySQL并发访问控制大多数情况下都应该由存储引擎自动完成，存储引擎自身会实现对文件进行加锁而且是隐式锁，由存储引擎自动完成。如：将来要备份数据，如果要备份很大的数据集，备份和修改的时间点不一致，数据拿来使用也是没法用的，必要时要备份整个数据集就必须对整个数据进行加锁，禁止任何人修改，把数据复制一份来做备份，必须要施加一个全局锁，就可以在服务器层来完成。

服务器层

存储引擎层

MySQL在大多数情况下可以实现并发控制，尤其在支持事务引擎的存储引擎，其底层实现了并发控制的细节和复杂度非常高。

锁：lock

对于数据库的数据访问来讲，锁一般分两类

两类：

读锁：共享锁

写锁：独占锁拒绝其他人同时使用

锁粒度：在表修改时是1到3行，而查找是在10行以后，没有关联性，能不能同时执行就取决于锁粒度。如果是修改时就必须锁表，那么后面就不能查询了

表级锁锁一张表 myisam是表级锁如果是数据仓库，一个数据形成以后，很少改了，一次写多次读，就用表级锁

行级锁锁定时，只需要锁相关的行 InnoDB是行级锁，如果是读写访问操作一样多，选择行级锁，在线事务处理的场景一般都是用InnoDB存储引擎，锁控制非常复杂，锁状态维护和检查非常麻烦，容易产生死锁

在行级锁内部必须要随时监控死锁发生的位置，如果发生了，则需要让一个后退，释放一个资源，让另外一个先使用

死锁：

如表中有两行1，2

A: 1, 其中A回话需要对1,2行加锁来做操作，此时已请求了1行锁，再去请求第2行时，发现B回话锁定了第2行，B也需要使用第1行，此时B需要请求第1行，在互相请求对方锁定的资源时，结果就出现死锁

存储引擎层施加的锁由存储引擎自己实现隐式维护的

锁分类：

隐式锁：由存储引擎自动完成

显式锁：用户可手动施加锁，表级锁

手动加锁：服务器级别实现的

LOCK TABLES tb1 {READ|WRITE},... 对表请求施加锁，可以指定多个表用','分开，如果请求时，别人锁定了就要延迟一段时间才能请求到，必须要等到别人释放锁，才能获取到锁

UNLOCK TABLES 释放所有锁

FLUSH TABLES WITH READ LOCK; 关闭表，请求施加所有表的读锁

UNLOCK TABLES;

MariaDB [hellodb]> lock tables students read;

Query OK, 0 rows affected (0.00 sec)

只要不释放，锁将一直存在

用别的回话请求可以读students表

往表中插入数据

新开的会话

MariaDB [(none)]> insert into hellodb.students (Name,Age) values ('liufang',23);插入数据时会一直处于等待状态，因为添加了读锁，如果不释放锁，此处将无法执行，读锁可以共享给读但是不能共享给写，写锁是排他

释放锁后马上就可以写入成功了

MariaDB [hellodb]> unlock tables;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> insert into hellodb.students (Name,Age) values ('liufang',23);

Query OK, 1 row affected (31.95 sec) 用来31.95s

施加写锁，自己会话读写都没有问题，其他会话读写就会处于等待状态

MariaDB [(none)]> lock tables hellodb.students write;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select * from hellodb.students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 22 | M | 1 | 7 |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec)

MariaDB [(none)]> insert into hellodb.students (Name,Age) values ('yuanyou',25);

Query OK, 1 row affected (0.01 sec)

另外的会话

MariaDB [hellodb]> select * from hellodb.students where StuID<3;处于等待当中

取消锁

ariaDB [(none)]> unlock tables;

Query OK, 0 rows affected (0.00 sec)

此时执行成功

MariaDB [hellodb]> select * from hellodb.students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 22 | M | 1 | 7 |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (1 min 18.43 sec)

InnoDB存储引擎也支持另外一种显式锁(只锁定挑选出的行)：

SELECT ... LOCK IN SHARE MODE; 施加读锁

SELECT ... FOR UPDATE;

如果说一个数据库存储引擎是支持事务的，则事务是另外一种并发访问控制机制

事务：ACID【事务的目的也是实现并发访问控制，而且事务本身是更高级别的访问控制功能】

一个执行单元：多个SQL语句，要么都执行，要么都不执行；

以银行转账列子

事务有两类状态：提交【执行成功，就全部成功】，回滚【事务失败，全部失败】

A：atomicity，原子性执行单元那么成功要么失败

C：consistency，一致性一个一致性状态转到另一个一致性状态如账户总额，加和减要一致

I：isolation，隔离性一个事务在修改在提交之前其他事务是不可见的，事务彼此之间是隔离的

隔离级别：关系型事务有4个级别

D：durability，持久性一旦事务提交了，事务执行成功了，必须保证所有数据都能够永久存储到持久存储中去，必须保证数据是提交后的状态。即便数据库服务器发生崩溃，其数据依然可以得到。不一定能达到100%，因为数据在内存中还没写到磁盘时就断电了，那么数据一定会丢失的。

机械硬盘写的能力在80-100次之间每秒钟，固态硬盘在300-500次之间每秒，PCI-E并行的固态硬盘可以达到数万到数十万个io

事务的隔离级别：事务如果完全执行隔离，由于数据集很少，而一个事务中可能包含多个语句，一个事务一旦发起，那么其他事务就没法运行，所以并发性很低，隔离性是比想象复杂多，在sql标准上实现了四种隔离级别，每一种都对应了事务所做的修改。

MySQL隔离级别是通过一个参数来设定的

MariaDB [(none)]> show global variables like '%iso%';

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| tx_isolation | REPEATABLE-READ | 是会话级别的动态变量，可在会话级别修改立即生效

+---------------+-----------------+

1 row in set (0.00 sec)

READ-UNCOMMITTED(读未提交)：最低隔离级别，会产生“脏读”(还未提交就能读到)；你所看到的数据别人只要已改，你马上看到，别人没提交的数据也可以看到，别人滚过去了也可以看到，没有隔离的意义，

READ-COMMTTED(读提交)：“不可重复读” 产生的问题：比如在没提交之前读了一个数据是3，提交之后数字改为5了，再一次读时是5了。这同一个事务在两次执行时不一样，在一个事务启动起来以后，事务是隔离的，事务在启动和不启动之间所看到的数据都是一样的才对。前后读取数据不一致。

REPEATABLE-READ(可重读)：“幻读” 在一个事务内部，只要自己不提交，看到的都是一模一样的，只有自己提交后，才能看到别人提交后的数据，为避免幻读使用下一种方法

SERIALIZABILE(可串行化)：使用加锁读前后都是一致的，当别人提交一个数据以后，数据提交了，看到的就是最终的数据，如果别人一个事务启动起来还没提交，要读这个数据需要等到提交后才能读。能够提供最安全的数据保证，但是并发能力也是最低的。

级别越高并发性能越差，但数据安全程度越高

SELECT @@global.tx_isolation; 查看默认隔离级别

+-----------------------+

| @@global.tx_isolation |

+-----------------------+

| REPEATABLE-READ |

+-----------------------+

启动事务：

START TRANSACTION

提交事务：COMMIT

回滚事务：ROLLBACK 执行后，前面那个执行的事务就回到初始值了(前提事务要未提交才能回滚到未更新状态，提交后事务就完成了)

READ-UNCOMMITTED(读未提交)：最低隔离级别，会产生“脏读”(还未提交就能读到)；你所看到的数据别人只要已改，你马上看到，别人没提交的数据也可以看到，别人滚过去了也可以看到

第1个会话

ariaDB [(none)]> set session tx_isolation='read-uncommitted';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select @@global.tx_isolation;

+-----------------------+

| @@global.tx_isolation |

+-----------------------+

| REPEATABLE-READ |

+-----------------------+

1 row in set (0.00 sec)

MariaDB [(none)]> select @@session.tx_isolation; 该会话下事务级别修改成功

+------------------------+

| @@session.tx_isolation |

+------------------------+

| READ-UNCOMMITTED |

+------------------------+

1 row in set (0.00 sec)

第2个会话

MariaDB [hellodb]> set session tx_isolation='read-uncommitted';

Query OK, 0 rows affected (0.00 sec)

手动的显示启动事务

start transaction

两边都启动事务

MariaDB [(none)]> start transaction;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> start transaction;

Query OK, 0 rows affected (0.00 sec)

把表students，Shi Potian Age改为200

MariaDB [hellodb]> select * from hellodb.students;

+-------+---------------+-----+--------+---------+-----------+

+-------+---------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 22 | M | 1 | 7 |

| 3 | Xie Yanke | 53 | M | 2 | 16 |

MariaDB [hellodb]> update students set Age=200 where StuID=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [hellodb]> select Name,Age from students where StuID=2;

+------------+-----+

| Name | Age |

+------------+-----+

| Shi Potian | 200 |

+------------+-----+

1 row in set (0.00 sec)

MariaDB [(none)]> select * from hellodb.students;

+-------+---------------+-----+--------+---------+-----------+

+-------+---------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 200 | M | 1 | 7 | Age值已改

| 3 | Xie Yanke | 53 | M | 2 | 16 |

回滚事务，以上事务就结束了

MariaDB [hellodb]> rollback;

Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [hellodb]> select Name,Age from students where StuID=2;

+------------+-----+

| Name | Age |

+------------+-----+

| Shi Potian | 200 | 发现没有回滚回去

+------------+-----+

1 row in set (0.00 sec)

查看一下存储引擎

MariaDB [hellodb]> show table status like 'students'\G

*************************** 1. row ***************************

Name: students

Engine: MyISAM 是myisam存储引擎

Version: 10

Row_format: Dynamic

Rows: 27

Avg_row_length: 24

Data_length: 664

Max_data_length: 281474976710655

Index_length: 2048

Data_free: 0

Auto_increment: 28

Create_time: 2015-04-01 18:53:08

Update_time: 2015-04-02 13:42:01

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

myisam存储引擎不支持事务，所以不能回滚

修改存储引擎

MariaDB [hellodb]> alter table students engine=innodb; 此种修改方式很危险

Query OK, 27 rows affected (0.08 sec)

Records: 27 Duplicates: 0 Warnings: 0

MariaDB [hellodb]> show table status like 'students'\G

*************************** 1. row ***************************

Name: students

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 27

Avg_row_length: 606

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: 28

Create_time: 2015-04-02 14:31:10

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

会话1 提交事务

MariaDB [hellodb]> commit;

Query OK, 0 rows affected (0.00 sec)

会话2

MariaDB [hellodb]> start transaction;

Query OK, 0 rows affected (0.00 sec)

会话1

MariaDB [hellodb]> start transaction;

Query OK, 0 rows affected (0.00 sec)

会话2

MariaDB [hellodb]> select * from students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 200 | M | 1 | 7 |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec)

MariaDB [hellodb]> update students set Age=99 where StuID=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [hellodb]> select * from students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 99 | M | 1 | 7 |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec)

会话1

MariaDB [hellodb]> select * from students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 99 | M | 1 | 7 |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec)

会话2

MariaDB [hellodb]> rollback; 事务回滚

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 200 | M | 1 | 7 | 已经返回到未修改状态了

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec)

会话1

MariaDB [hellodb]> select * from students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 200 | M | 1 | 7 |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec)

MariaDB [hellodb]> rollback;

Query OK, 0 rows affected (0.00 sec)

READ-COMMTTED(读提交)：“不可重复读” 产生的问题：比如在没提交之前读了一个数据是3，提交之后数字改为5了，再一次读时是5了。这同一个事务在两次执行时不一样，在一个事务启动起来以后，事务是隔离的，事务在启动和不启动之间所看到的数据都是一样的才对。前后读取数据不一致。提交后就可以查看到。提交前和提交后读是不同的，所以不可重复读

会话1

MariaDB [hellodb]> set session tx_isolation='read-committed';

Query OK, 0 rows affected (0.00 sec)

会话2

MariaDB [hellodb]> set session tx_isolation='read-committed';

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> start transaction;

Query OK, 0 rows affected (0.00 sec)

会话1

MariaDB [hellodb]> start transaction;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 200 | M | 1 | 7 |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec)

MariaDB [hellodb]> update students set Age=60 where StuID=1;

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [hellodb]> select * from students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 60 | M | 2 | 3 |

| 2 | Shi Potian | 200 | M | 1 | 7 |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec)

会话2

MariaDB [hellodb]> select * from students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 200 | M | 1 | 7 |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec) 未提交

会话1

MariaDB [hellodb]> commit; 提交

Query OK, 0 rows affected (0.01 sec)

会话2

MariaDB [hellodb]> select * from students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 60 | M | 2 | 3 | 可以查看到了

| 2 | Shi Potian | 200 | M | 1 | 7 |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec)

MariaDB [hellodb]> rollback;

Query OK, 0 rows affected (0.00 sec)

会话1

MariaDB [hellodb]> set session tx_isolation='repeatable-read';

Query OK, 0 rows affected (0.00 sec)

会话2

MariaDB [hellodb]> set session tx_isolation='repeatable-read';

Query OK, 0 rows affected (0.00 sec)

会话1

MariaDB [hellodb]> start transaction;

Query OK, 0 rows affected (0.00 sec)

会话2

MariaDB [hellodb]> start transaction;

Query OK, 0 rows affected (0.00 sec)

会话1

MariaDB [hellodb]> select * from students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 60 | M | 2 | 3 |

| 2 | Shi Potian | 200 | M | 1 | 7 |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec)

MariaDB [hellodb]> update students set Age=68 where StuID=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [hellodb]> select * from students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 60 | M | 2 | 3 |

| 2 | Shi Potian | 68 | M | 1 | 7 |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec)

会话2

MariaDB [hellodb]> select * from students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 60 | M | 2 | 3 |

| 2 | Shi Potian | 200 | M | 1 | 7 |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec)

会话1

MariaDB [hellodb]> commit;

Query OK, 0 rows affected (0.00 sec)

会话2

MariaDB [hellodb]> select * from students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 60 | M | 2 | 3 |

| 2 | Shi Potian | 200 | M | 1 | 7 |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec)

MariaDB [hellodb]> update students set Age=Age+1 where StuID=2;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [hellodb]> select * from students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 60 | M | 2 | 3 |

| 2 | Shi Potian | 69 | M | 1 | 7 | 此时叫幻读

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec)

MariaDB [hellodb]> commit;

Query OK, 0 rows affected (0.01 sec)

会话1

MariaDB [hellodb]> set session tx_isolation='serializable';

Query OK, 0 rows affected (0.00 sec)

会话2

MariaDB [hellodb]> set session tx_isolation='serializable';

Query OK, 0 rows affected (0.00 sec)

会话1

MariaDB [hellodb]> start transaction;

Query OK, 0 rows affected (0.00 sec)

会话2

MariaDB [hellodb]> start transaction;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 60 | M | 2 | 3 |

| 2 | Shi Potian | 69 | M | 1 | 7 |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec)

MariaDB [hellodb]> update students set Age=89 where StuID=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [hellodb]> select * from students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 60 | M | 2 | 3 |

| 2 | Shi Potian | 89 | M | 1 | 7 |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (0.00 sec)

会话1

MariaDB [hellodb]> select * from students where StuID<3;

处于等待状态，因为事务没有提交，只有等待对方提交了才能看到，保证看到的是最新的数据

只要对方释放锁了就可以看到

会话2

MariaDB [hellodb]> rollback;

Query OK, 0 rows affected (0.00 sec)

会话1

MariaDB [hellodb]> select * from students where StuID<3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 60 | M | 2 | 3 |

| 2 | Shi Potian | 69 | M | 1 | 7 |

+-------+-------------+-----+--------+---------+-----------+

2 rows in set (22.99 sec)

请求不到锁就不会读数据

为了保证服务器有更好的并行性，也不至于数据彼此间影响过大，所以MySQL使用了可重读，即便使用了可重读，中间还是会出现幻读问题，由此建议用小事务替代大事务是比较理想的做法。默认情况下InnoDB存储引擎，默认会启动自动提交功能

MariaDB [hellodb]> select @@global.autocommit;

+---------------------+

| @@global.autocommit |

+---------------------+

| 1 |

+---------------------+

1 row in set (0.00 sec)

手动指定sql语句没指定事务，它会自动执行完每一条语句都提交一下

自动提交能保证sql语句执行完成，就能够得到数据持久保证，但是会极大的降低系统性能

建议使用InnoDB存储引擎时，把自动提交关闭，自己手动启动事务手动提交事务

无法autocommit是否为1对myisam没有影响，myisam不支持事务，myisam也是无法回滚的

SAVEPOINT 保存点，回滚时，只回滚到保存的时间点

SAVEPOINT identifier

ROLLBACK [WORK] TO [SAVEPOINT] identifier 回滚某个保存下来的位置

RELEASE SAVEPOINT identifier 释放保存点

MariaDB [hellodb]> start transaction; 启动事务

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from students where StuID > 3 and StuID<6;

+-------+-----------+-----+--------+---------+-----------+

+-------+-----------+-----+--------+---------+-----------+

| 4 | Ding Dian | 32 | M | 4 | 4 |

| 5 | Yu Yutong | 26 | M | 3 | 1 |

+-------+-----------+-----+--------+---------+-----------+

2 rows in set (0.01 sec)

这里修改数据

MariaDB [hellodb]> update students set Age=80 where StuID=5;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [hellodb]> savepoint oop;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delete from students where StuID=4 and StuID=5;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> savepoint ooo;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delete from students where StuID=4 and StuID=5;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> savepoint ooo;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delete from students where StuID=10;

Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from students;

+-------+---------------+-----+--------+---------+-----------+

+-------+---------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 60 | M | 2 | 3 |

| 2 | Shi Potian | 69 | M | 1 | 7 |

| 3 | Xie Yanke | 53 | M | 2 | 16 |

| 4 | Ding Dian | 32 | M | 4 | 4 |

| 5 | Yu Yutong | 80 | M | 3 | 1 |

| 6 | Shi Qing | 46 | M | 5 | NULL |

| 7 | Xi Ren | 19 | F | 3 | NULL |

| 8 | Lin Daiyu | 17 | F | 7 | NULL |

| 9 | Ren Yingying | 20 | F | 6 | NULL |

| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |

| 12 | Wen Qingqing | 19 | F | 1 | NULL |

| 13 | Tian Boguang | 33 | M | 2 | NULL |

| 14 | Lu Wushuang | 17 | F | 3 | NULL |

| 15 | Duan Yu | 19 | M | 4 | NULL |

| 16 | Xu Zhu | 21 | M | 1 | NULL |

| 17 | Lin Chong | 25 | M | 4 | NULL |

| 18 | Hua Rong | 23 | M | 7 | NULL |

| 19 | Xue Baochai | 18 | F | 6 | NULL |

| 20 | Diao Chan | 19 | F | 7 | NULL |

| 21 | Huang Yueying | 22 | F | 6 | NULL |

| 22 | Xiao Qiao | 20 | F | 1 | NULL |

| 23 | Ma Chao | 23 | M | 4 | NULL |

| 24 | Xu Xian | 27 | M | NULL | NULL |

| 25 | Sun Dasheng | 100 | M | NULL | NULL |

| 26 | liufang | 23 | F | NULL | NULL |

| 27 | yuanyou | 25 | F | NULL | NULL |

+-------+---------------+-----+--------+---------+-----------+

26 rows in set (0.00 sec)

MariaDB [hellodb]> savepoint look;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> rollback to ooo;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from students where StuID=10;

+-------+--------------+-----+--------+---------+-----------+

+-------+--------------+-----+--------+---------+-----------+

| 10 | Yue Lingshan | 19 | F | 3 | NULL |

+-------+--------------+-----+--------+---------+-----------+

1 row in set (0.00 sec)

MariaDB [hellodb]> release savepoint oop;

Query OK, 0 rows affected (0.00 sec)

MySQL的自动提交功能：

SELECT @@GLOBAL.autocommit;

+---------------------+

| @@GLOBAL.autocommit |

+---------------------+

| 1 |

+---------------------+

MySQL下，事务通过事务日志来保证其性能和ACID的测试的，依赖于事务日志的功能

MVCC【Multiversion Concurrency Control多版本并发控制，对InnoDB来讲启动一个事务就相当于对MySQL中的所有数据做了一个快照，而后在快照的基础上实现查找工作】: 通过保存数据在某个时间的快照实现。无论事务执行多长时间，其看到的数据都是一致的。

InnoDB这种存储引擎其mvcc是在每行记录后面添加了两个隐藏字段，是看不到的，其中一个保存了行的创建时间，一个保存了行的过期时间或删除时间【只是标记它看不到】。

MVCC仅在第二、第三隔离级别下有效；

事务日志：目的将随机I/O转换为顺序I/O，以提升事务操作效率；事务日志也称为Write-Ahead Logging 任何事务相关数据的修改都是先在内存中修改，修改后是不会组合，先写到磁盘上一份而后写到日志文件中。事务日志是一种追加式的log 由于要写两次磁盘，性能比较差，如何降低其对磁盘io的影响？将数据文件和事务日志放在不同的磁盘上，这样可以避免将事务回写到数据文件中，但如果事务所在的磁盘坏了怎么办呢？因此事务日志所在的硬盘应该具有容灾性，比如说用raid1，或raid10， MySQL支持使用两种事务日志，一种写一种备份事务日志文件应该有多大？数据是否要在事务文件中一直存放着？放在事务文件中的存在的问题：有了事务日志就能保存持久性，当服务器软件程序崩溃，有些数据在事务文件没在数据文件中，下次启动时，必须想办法把数据从事务日志统统都写到数据文件中去。并保证数据文件处于clean状态，服务器才能够继续向外提供服务

，此过程就叫崩溃后恢复过程。如果事务文件很大，把事务文件写到数据文件中都要很长时间，因此日志文件一定不能太大，根据业务数据的容量来定大小。事务日志文件是成组出现的，每一组当中不止一个事务日志两个文件轮流使用，第一个文件填满了，填第二个日志文件，而此时把第一个日志文件同步到数据文件中去。InnoDB存储引擎自动管理的

数据从事务日志写到数据文件中，其频繁度如何衡量？

越快，事务丢失的可能性越小，但磁盘io越大，是MySQL自我管理的

但是从内存同步到事务中必须要及时，只要一提交事务，就往事务日志文件中写

rain john

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql 会话变量并发_MySQL并发访问控制机制

并发控制：并发控制对于任何一个允许多个用户连入进来并请求资源的服务来讲都是必须要完成的功能MySQL作为服务器来讲，其内部有很多数据有很多张表，表中有数据，无论是myisam还是InnoDB存储引擎对于单张表来讲如果没做分区，那么其数据都放在同一个文本文件，当两个客户端同时发起MySQL会话，连入MySQL时，MySQL服务会启动两个会话线程来给两个用户建立连接，假如第一个用户发起select语句...
复制链接

扫一扫

mysql 会话变量 并发_MySQL并发访问控制机制

“相关推荐”对你有帮助么？

mysql 会话变量并发_MySQL并发访问控制机制