mysql版本
8.0.25
常用存储引擎
innodb (支持事务)
Myisam (不支持事务)
Meory (不支持事务)
查看表存储引擎
show create table school;
show table status from school_info;
show table status from school_info where name='school';
select * from information_schema.tables where table_schema='school_info' and table_name='school';
事务含义
一组sql语句组成一个执行单元,要么全部执行要么全部不执行
事务的特点ACID
原子性:一个事务不可再分割,要么都执行,要么都不执行。
一致性:一个事务执行会使数据从一个一致状态切换到另一个一致状态
隔离性:一个事务执行不受其他事务干扰
持久性:一个事务提交会永久改变数据库数据
事务实例
commit
set autocommit = 0;
start transaction;
update student set `age`= 29 where `id`= 10;
update student set `age`= 30 where `id`= 11;
commit;
rollback
set autocommit = 0;
start transaction;
update student set `age`= 29 where `id`= 10;
update student set `age`= 30 where `id`= 11;
rollback;
隔离级别
1 read uncommitted 读取尚未提交的数据(脏读)
2 read committed 读取已经提交的数据。可能多次读取的数据结果不一致(幻读)
3 repeatable read: 可以重复读取,但有幻读。
4 serializable: 可读,不可写。
设置隔离级别
set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;
查看隔离级别
select @@transaction_isolation;
show variables like '%isolation%';
select @@global.transaction_isolation;
查看当前隔离级别
select @@tx_isolation;