mysql--隔离级别--提交读&&未提交读

提交读

准备工作：
create database test;
drop table test.read_committed;
create table test.read_committed(number int, text varchar(15));

1.更改隔离级别
窗口一：
set session transaction isolation read committed;

2.开启事务
窗口一：
start transaction;
窗口二：
start transaction;

3.查询
窗口一：
select * from test.read_committed;
查询结果：

mysql> select * from test.read_committed;
+--------+---------------+
| number | text          |
+--------+---------------+
|   NULL | Hello, World! |
|   NULL | Hello, World! |
+--------+---------------+
2 rows in set (0.00 sec)

4.插入数据
窗口二：
insert into test.read_committed (text) values ("Hello, World!");

5.查询
窗口一：
select * from test.read_committed;
查询结果：

mysql> select * from test.read_committed;
+--------+---------------+
| number | text          |
+--------+---------------+
|   NULL | Hello, World! |
|   NULL | Hello, World! |
+--------+---------------+
2 rows in set (0.00 sec)

6.提交事务
窗口二：
commit;

7.查询
窗口一：
select * from test_read_committed;
查询结果：

mysql> select * from test.read_committed;
+--------+---------------+
| number | text          |
+--------+---------------+
|   NULL | Hello, World! |
|   NULL | Hello, World! |
|   NULL | Hello, World! |
+--------+---------------+
3 rows in set (0.00 sec)

未提交读

准备工作：
create database if not exists test;
drop table if exists test.read_uncommitted ;
create table test.read_uncommitted(number int, text varchar(15)) ;

1.更改隔离级别
窗口一：
set session transaction isolation level read uncommitted;

2.开启事务
窗口一：
start transaction;
窗口二：
start transaction;

3.查询
窗口一：
select * from test.read_uncommitted;
查询结果：

mysql> select * from test.read_uncommitted;
Empty set (0.00 sec)

4.插入数据
窗口二：
insert into test.read_uncommitted(text) values ("Hello, World!");

5.查询
窗口一：
select * from test.read_uncommitted;
查询结果：

mysql> select * from test.read_uncommitted;
+--------+---------------+
| number | text          |
+--------+---------------+
|   NULL | Hello, World! |
+--------+---------------+
1 row in set (0.00 sec)

ps:



归档：

1.

1.重建日志
set global general_log = off;
drop TABLE mysql.general_log;
CREATE TABLE mysql.general_log (
event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
user_host MEDIUMTEXT NOT NULL,
thread_id BIGINT UNSIGNED NOT NULL,
server_id INT(10) UNSIGNED NOT NULL,
command_type VARCHAR(64) NOT NULL,
argument MEDIUMBLOB NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';



1.开启日志
show variables like 'general_log%';
show variables like 'log_output';
set global general_log = on;
show variables like 'general_log%';
set global log_output = 'table';
show variables like 'log_output';

2.开启事件调度器
show variables like 'event_scheduler';
set global event_scheduler = 'off';
show variables like 'event_scheduler';
set global event_scheduler = 'on';
show variables like 'event_scheduler';
3.更改隔离级别
select @@transaction_isolation;
set session transaction isolation level read committed;
select @@transaction_isolation;
4.创建库创建表
create database test;

drop table test.read_committed;
create table test.read_committed(number int, text varchar(15));

5.创建定时任务

use test;
drop event read_committed_read_one;
CREATE EVENT read_committed_read_one
ON SCHEDULE AT '2025-02-10 03:31:00'
DO select number from test.read_committed;

use test;
drop event read_committed_read_two;
CREATE EVENT read_committed_read_two
ON SCHEDULE AT '2025-02-10 03:31:00'
DO select text from test.read_committed;

use test;
drop event read_committed_insert;
CREATE EVENT read_committed_insert
ON SCHEDULE AT '2025-02-10 03:31:00'
DO INSERT INTO  test.read_committed(text) VALUES ('Hello, World!');
6.查看定时任务
show events;

7.查看定时任务创建时间
select * from information_schema.events;

8.关闭日志
set global general_log = 'off';
show variables like 'general_log%';

9.查看日志
SELECT event_time, user_host, thread_id, server_id, command_type, CONVERT(argument USING utf8) AS argument FROM mysql.general_log;

2

#提交读
事务提交前后查询结果不同
实现流程：
1.查询
select * from test.read_committed;
查询结果：

mysql> select * from test.read_committed;
+--------+---------------+
| number | text |
+--------+---------------+
| NULL | Hello, World! |
+--------+---------------+
1 row in set (0.00 sec)

2.开启事务
start transaction;
insert into test.read_committed (text) values ("Hello, World!");
3.查询
select * from test_committed;
查询结果：

mysql> select * from test.read_committed;
+--------+---------------+
| number | text |
+--------+---------------+
| NULL | Hello, World! |
+--------+---------------+
1 row in set (0.00 sec)

4.提交
commit;
5.查询
select * from test.read_committed;
查询结果：

mysql> select * from test.read_committed;
+--------+---------------+
| number | text |
+--------+---------------+
| NULL | Hello, World! |
| NULL | Hello, World! |
+--------+---------------+
2 rows in set (0.00 sec)