innodb和myisam 对事务的处理

innodb和myisam 对事务的处理:


mysql> show create table test200\G;
*************************** 1. row ***************************
Table: test200
Create Table: CREATE TABLE `test200` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

ERROR: 

对innodb 关闭自动提交:

SESSION A:

mysql>  set autocommit=off;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> select * from test200;
+------+
| id   |
+------+
|  200 |
+------+
1 row in set (0.00 sec)



SESSION B:

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

mysql> 

innodb 在关闭自动提交的情况下,事务隔离。



----------------------------------------------------------------------------
mysql> create table test200(id int) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table test200\G;
*************************** 1. row ***************************
Table: test200
Create Table: CREATE TABLE `test200` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

SESSION A:
mysql>  set autocommit=off;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from test200;
+------+
| id   |
+------+
|  200 |
+------+
1 row in set (0.00 sec)


SESSION B：
mysql> select * from test200;
+------+
| id   |
+------+
|  200 |
+------+
1 row in set (0.00 sec)

mysql> 

myisam 不支持事务,没有事务隔离概念。


---------------------------------------------------------------------------

测试 myisam 更新锁全表:

mysql> select min(sn),max(sn) from Client;
+---------+---------+
| min(sn) | max(sn) |
+---------+---------+
|       1 |    1772 |
+---------+---------+
1 row in set (0.38 sec)