MySQL(InnoDB)如何避免幻读

幻读Phantom Rows

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

幻读问题是指一个事务的两次不同时间的相同查询返回了不同的的结果集。例如:一个 select 语句执行了两次,但是在第二次返回了第一次没有返回的行,那么这些行就是“phantom” row.

read view(或者说 MVCC)实现了一致性不锁定读(Consistent Nonlocking Reads),从而避免了幻读

实验1:

开两个窗口设置

1
2
3
4
5
set session tx_isolation='REPEATABLE-READ';
select @@session.autocommit;select @@global.tx_isolation,@@session.tx_isolation;
create table read_view(text varchar(50));
insert into read_view values('init');

两个会话开始事务

1
2
3
4
5
SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_A执行一个查询,这个查询可以访问任何表,这个查询的目的是创建一个当前时间点的快照
START TRANSACTION WITH CONSISTENT SNAPSHOT;也可以达到同样的效果

1
2
3
4
5
6
7
8
9
10
SESSION_A>select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)

SESSION_B 插入一条记录并提交

1
2
3
4
5
SESSION_B>insert into read_view values('after session A select');
Query OK, 1 row affected (0.01 sec)
SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SESSION_A>select * from read_view;
+------+
| text |
+------+
| init |
+------+
1 row in set (0.00 sec)
SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+------------------------+
| text |
+------------------------+
| init |
| after session A select |
+------------------------+
2 rows in set (0.00 sec)

由于 SESSION_A 第一次的查询开始于 SESSION_B 插入数据前,通过创建了一个以SELECT操作的时间为基准点的 read view,避免了幻读的产生
所以在 SESSION_A 的事务结束前,无法看到 SESSION_B 对表 read_view 做出的任何更改 (insert,delete,update)

阅读全文

Auto_increment详解

Auto_increment

Mysql AUTO_INCREMENT

1.Innodb表的自动增长列可以手工插入,但是插入的值如果是空或者0,则实际插入的将是自动增长后的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> create table t1(id int not null auto_increment primary key,name varchar(10));
Query OK, 0 rows affected (0.06 sec)
mysql> desc t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> insert into t1 values(0,'fanboshi');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(null,'duyalan');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 1 | fanboshi |
| 2 | duyalan |
+----+----------+
2 rows in set (0.00 sec)

2.可以通过alter table t1 auto_incremenrt=n 语句强制设置自动增长列的初始值,默认从1开始,但是该强制的默认值是保留在内存中的,如果该值在使用之前数据库重新启动,那么这个强制的默认值就会丢失,就需要数据库启动后重新设置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> alter table t1 auto_increment=5;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into t1 values(null,'handudu');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 1 | fanboshi |
| 2 | duyalan |
| 5 | handudu |
+----+----------+
3 rows in set (0.00 sec)

3.可以是用last_insert_id()查询当前线程最后插入记录使用的值。如果一次插入多条记录,那么返回的是第一条记录使用的自动增长值。

阅读全文


Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2018 Fan() All Rights Reserved.

访客数 : | 访问量 :

#