autocommit对myisam和innodb引擎的影响,myisam不支持事务,默认引擎

 //转载  http://mxohy.blog.sohu.com/160758392.html

autocommit的测试

对于innodb表:

首先创建两个innodb表:
mysql> create table tab_kx(a int auto_increment,primary key(a));
Query OK, 0 rows affected (0.16 sec)

mysql> create table tab_kx2(a int auto_increment,primary key(a));
Query OK, 0 rows affected (0.17 sec)

在session1,设置autocommit为OFF:
mysql> set autocommit=0;                 
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

在session2设置autocommit为ON:
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)


在session1,对tab_kx插入三行数据但不提交:
mysql> insert into tab_kx values('');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into tab_kx values('');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into tab_kx values('');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from tab_kx;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

mysql> select * from tab_kx2;
Empty set (0.00 sec)

此时查看session2是否可以看到这三行数据:
mysql> select * from tab_kx;
Empty set (0.00 sec)

【说明】,session2看不到其他session没有提交的DML;
此时在session2插入四行数据到表tab_kx2(由于是auto commit的,所以不需要提交):
mysql> insert into tab_kx2 values();
Query OK, 1 row affected (0.07 sec)

mysql> insert into tab_kx2 values();
Query OK, 1 row affected (0.04 sec)

mysql> insert into tab_kx2 values();
Query OK, 1 row affected (0.04 sec)

mysql> insert into tab_kx2 values();
Query OK, 1 row affected (0.02 sec)

mysql> select * from tab_kx2;                                   
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.00 sec)

【注意】但此时session1仍然看不到tab_kx2的数据,
mysql> select * from tab_kx2;
Empty set (0.00 sec)
当手动执行commit时,才可以看到这些数据,同时session2也可以看到tab_kx表的数据。
mysql> commit;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from tab_kx2;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.00 sec)


【结论】对于autocommit为OFF的session,其事务开始时间点之后发生的DML操作对其都是不可见的,只有当事务结束时才可见。(其他session的DDL即时可见)
另外,如果其session执行了一个DDL,会隐式提交之前的事务,DML的修改其他session可见了。
但有意思的是,当我一个session中对tab_kx表drop的时候,会话被阻塞了(如果隐式提交了,应该不会发生这种情况),在另个session中执行读,也被阻塞了
此时我在原来的session(autocommit为OFF)执行显示提交commit:
其他session都被“激活”了,而且是按执行顺序激活的。


myisam表:

创建两个myisam表,
mysql> create table tab_kx(a int auto_increment,primary key (a)) engine=myisam;
Query OK, 0 rows affected (0.05 sec)

mysql> create table tab_kx2(a int auto_increment,primary key (a)) engine=myisam;
Query OK, 0 rows affected (0.05 sec)

在session1,(autocommit为OFF)插入三行数据,
mysql> insert into tab_kx values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into tab_kx values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into tab_kx values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from tab_kx;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)


在其他session中查询tab_kx,
mysql> select * from tab_kx;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

可以看到结果,这与innodb的表不同;不需要显示提交,DML修改就可以对外可见了。
【结论】myisam是非事务的存储引擎,commit是对事物有效的,所以没啥作用。
【注意】但当我对tab_kx做删除时,会话被阻塞,尽管没有事务性,但autocommit=OFF仍然在起作用,虽然不再持有表锁;
在session1执行显示提交commit,立刻解锁,阻塞的会话被激活。

注:mysql官方文档如是说:

ALTER TABLE运行时会对原表进行临时复制,在副本上进行更改,然后删除原表,再对新表进行重命名。在执行ALTER TABLE时,其它用户可以阅读原表,但是对表的更新和修改的操作将被延迟,直到新表生成为止。新表生成后,这些更新和修改信息会自动转移到新表上。


另外一个针对MyISAM的实验:

session1:

set autocommit=0;//设置为1不会出现session2 lock阻塞的情况

select * from user;//MyISAM表的select会隐式加读锁


session2:

lock table user write;//被阻塞


session1:

rollback;或者commit;


session2才lock成功


同样的,在session1执行insert 操作,session2无法lock table table_name read;

session1执行rollback之后,seesion2才能继续执行,但是session1的rollback并不会把已经插入的数据回滚;


但是for update不会阻塞lock,但是lock会阻塞for update(不理解)


所以是否可以这样总结autocommit对MyISAM的影响:

1、MyISAM不支持事务,begin或者start transaction肯定对其无效;

2、autocommit设置为0对单条MyISAM的操作语句有效,对多条语句无效(即不支持多条语句的事务);

展开阅读全文

没有更多推荐了,返回首页