Innodb事务隔离级别

Innodb事务隔离级别

REPEATABLE READ

This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.

这是 InnoDB 默认的事务隔离级别,同一事物通过第一次创建的快照来构造一致性读.这意味着如果你在同一会话执行多次查询(非锁定读),那么每次获取的结果都是彼此相等的

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition.

对于锁定读,(SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, 和 DELETE语句,是否锁定取决于此次查询(UPDATE DELETE 也是一种查询)是否通过唯一条件或范围条件查询使用唯一索引

For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.

  • For a unique index with a unique search condition,InnoDB只锁索引记录,不锁 gap
    • For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range

READ COMMITTED

Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. For information about consistent reads

read view在 innodb 如何避免幻读中已经介绍过

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. Gap locking is only used for foreign-key constraint checking and duplicate-key checking.

locking reads(SELECT with FOR UPDATE or LOCK IN SHARE MODE),UPDATE 语句,DELETE语句,InnoDB 都只会锁index records(也就是 record lock),不会产生 gap lock,因此允许在 gap 中插入新的 records.Gap locking只在外键约束检查和重复值检查时产生

Because gap locking is disabled, phantom problems may occur, as other sessions can insert new rows into the gaps

因为gap locking被 disabled,幻读问题可能会产生,其他会话可以在 gap 中插入新的记录

阅读全文

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 - 2019 Fan() All Rights Reserved.

访客数 : | 访问量 :

#