事务隔离级别
my_id是主键
mysql> select * from mytable;
+-------------+------+------+-----------+-------+
| name | sex | age | birthaddr | my_id |
+-------------+------+------+-----------+-------+
| xiaoming | f | 20 | SZ | 1 |
| hello_world | m | 18 | BJ | 2 |
| world | f | 21 | SZ | 3 |
| happy | M | 99 | SH | 4 |
| happy1 | M | 99 | SH | 5 |
| happy2 | M | 99 | SH | 6 |
| happy3 | F | 18 | SH | 7 |
+-------------+------+------+-----------+-------+
7 rows in set (0.00 sec)
以下两个事务的执行结果，可以得出
1、Mysql的当前级别是可重复读Reapeatable Read（RR），即事务1 读取不到其他事务已提交的修改，通过MVCC来实现
2、Mysql的RR级别也实现了幻读，即事务1读取不到其他事务已提交的新增
3、事务之间的隔离是通过锁来实现的
3.1 事务中的查询和新增不需要锁
3.2 事务中的修改：
对于索引列的修改，只需要锁定修改的行（行锁），所以只要不修改同一行是可以成功的
对于非索引列的修改，需要锁整个表，所以会修改失败
事务1 事务2
1.1-begin;
2.1-select * = 7 rows 2.2begin;
3.1update mytable set name="hello_world11" where my_id=2; 3.2select * = 7 rows
4.2update mytable set name="xiao" where my_id=1;更新成功
5.2insert into mytable(name, sex, age, birthaddr) values ("happy66","F",18,"SH");插入成功
6.2commit;
7.1select * = 7 rows
未读取到my_id=1 name的更新,未读取到happy66的数据插入
说明：
1 Mysql是可重复读，RR repeatable Read
2 并且实际上Mysql是解决了幻读的
8.2begin;
9.2update mytable set name="hello_world22" where my_id=2;更新失败
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
10.2update mytable set name="nihao_hello" where name="nihao";更新失败
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
11.1commit;
12.1select * = 8 rows
并且读取到了my_id=1的更新，即name=xiao
13.2再次执行上面两个更新，都可以成功
13.3commit;
mysql> select * from mytable;
+---------------+------+------+-----------+-------+
| name | sex | age | birthaddr | my_id |
+---------------+------+------+-----------+-------+
| xiao | f | 20 | SZ | 1 |
| hello_world22 | m | 18 | BJ | 2 |
| world | f | 21 | SZ | 3 |
| happy | M | 99 | SH | 4 |
| happy1 | M | 99 | SH | 5 |
| happy2 | M | 99 | SH | 6 |
| happy3 | F | 18 | SH | 7 |
| happy55 | F | 18 | SH | 8 |
| happy66 | F | 18 | SH | 9 |
+---------------+------+------+-----------+-------+
9 rows in set (0.00 sec)
