mysql 主键和事务

 

新建一个没有指定主键的表

CREATE TABLE `tb` (
`name` varchar(10) DEFAULT '',
`age` int(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

insert into tb(name,age)values('a',1),('b',2),('c',3),('d',4)

新增自增id : alter table add column id int auto_increment ; 报错

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'add column id int auto_increment' at line 1

尝试解决

alter table tb drop primary key 

Error Code: 1091. Can't DROP 'PRIMARY'; check that column/key exists

 

事务 acid(原子，一致，隔离，持久)

show variables like '%tx_isolation%'
select @@tx_isolation;

CREATE TABLE `app_ip` (
`id` int(11) auto_increment ,
`name` varchar(10) NOT NULL DEFAULT '',
`age` int(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into app_ip(name,age)values('a',1),('b',2),('c',3),('d',4)

设置事务隔离级别

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

 

测试前先修改

set autocommit=0,
当前session禁用自动提交事物，自此句执行以后，每个SQL语句或者语句块所在的事务都需要显示"commit"才能提交事务。

查看正再执行的事务

SELECT * FROM information_schema.INNODB_TRX

 1， 脏读 READ UNCOMMITTED

顺序	事务A	事务B
1	begin;
2		begin;
3	select name from app_ip where id = 1; --‘a_bench’
4		update app_ip set name = 'a_bench_updated' where id = 1;
5	select name from app_ip where id = 1; --‘a_bench_update’
6	commit;
7		commit;

 

 

 

2，不可重复读 READ COMMITTED

顺序	事务A	事务B
1	begin;
2		begin;
3	select name from app_ip where id = 1; -- 'a_A'
4		update app_ip set name = 'a_B' where id = 1;  --'a_B'
5		commit;
6	select name from app_ip where id = 1; -- 'a_B'	--'a_B'
7	commit;

3，幻读　REPEATABLE-READ

顺序	事务A	事务B
1	begin;
2		begin;
3	select count(1) from app_ip; --4	delete from app_ip where id =2;
4		select count(1) from app_ip; --3
5		commit;
6	select count(1) from app_ip; --4
7	commit;

4，序列化 SERIALIZABLE