mysql transaction isolation
# 数据库准备
mysql> use test_transaction
Database changed
mysql>
mysql> show tables;
Empty set (0.00 sec)
mysql>
# 创建测试表
mysql> create table account(
-> id int auto_increment not null primary key,
-> name char(10) not null,
-> balance int
-> );
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> show tables;
+----------------------------+
| Tables_in_test_transaction |
+----------------------------+
| account |
+----------------------------+
1 row in set (0.00 sec)
mysql>
mysql> desc account;
+---------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | NO | | NULL | |
| balance | int(11) | YES | | NULL | |
+---------+----------+------+-----+---------+----------------+
3 rows in set (0.04 sec)
mysql>
# 添加测试数据
mysql> insert into account values('', 'lilei', 450);
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> insert into account values('', 'hanmei', 16000);
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> insert into account values('', 'lucy', 2400);
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql>
mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | lilei | 450 |
| 2 | hanmei | 16000 |
| 3 | lucy | 2400 |
+----+--------+---------+
3 rows in set (0.00 sec)
mysql>
# 查看数据库的事务隔离级别
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql>
# 修改数据库隔离级别的命令如下：
读未提交(read-uncommitted)
set session transaction isolation level read uncommitted;
不可重复读(read-committed)
set session transaction isolation level read committed;
可重复读(repeatable-read)
set session transaction isolation level repeatable read;
串行化(serializable)
set session transaction isolation level serializable;
# 事务操作方法如下：
start transaction;
commit;
rollback;
# 修改数据行命令如下：
update account set balance = balance - 50 where id =1;
