Linux：mysql 下transaction的实现

2.配置
安装后，可以对innodb做一些配置，在my.cnf或my.ini中的[mysqld]段。
#存储目录，如果不指定默认为安装的data目录，为空时以innodb_data_file_path指定路径为准
innodb_data_home_dir =
#数据文件名及大小，默认为ibdata1，10m大小。autoextend可以自增，max:2000M文件最大2g，因为有的硬盘有2g文件大小限制。
innodb_data_file_path = ibdata1:2000M;ibdata2:2000M:autoextend:max:2000M
# 设置缓冲池大小
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
#设置日志文件路径，默认在date目录下，名称为ib_logfile...
innodb_log_group_home_dir =
#设置日志文件数目，默认为3
set-variable = innodb_log_files_in_group=3
# 设置日志文件大小
set-variable = innodb_log_file_size=10M
# 设置日志缓冲大小
set-variable = innodb_log_buffer_size=8M
# 任何事务提交前写入日志，方便故障诊断，请设为1。如果丢失最近的几个事务影响不大的话，设置为0（默认值）。
innodb_flush_log_at_trx_commit=1
#设置超时时间
set-variable = innodb_lock_wait_timeout=50

注意：1.innodb不会自动生成目录，上面所有指定目录要手工生成，默认不用。

     2.在/etc下面默认没有my.cnf文件，需要copy，如下：

[root@test-server root]# find / -name 'my*.cnf'
/etc/my.cnf
/usr/share/doc/packages/MySQL-server/my-huge.cnf
/usr/share/doc/packages/MySQL-server/my-innodb-heavy-4G.cnf
/usr/share/doc/packages/MySQL-server/my-large.cnf
/usr/share/doc/packages/MySQL-server/my-medium.cnf
/usr/share/doc/packages/MySQL-server/my-small.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf

在/usr/share/mysql目录下有5个my*.cnf文件，打开查看根据自己配置情况选择一个，执行#cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

这时候，就可以进行上面的配置啦！

3.添加表

mysql> create table testerinfo(ID int primary key,  name char(30), address char(30), sex char(10)) type=innodb;  

Query OK, 0 rows affected, 1 warning (0.02 sec)     //设置表的engine

这里出现一个warning，我们查看warning内容：

mysql> show warnings;
+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                                  |
+---------+------+-----------------------------------------------------------+
| Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead |
+---------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

意思是应设置engin=innodb，而不是type。没有关系，下面我们修改：

mysql> alter table testerinfo engine='InnoDB';
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0 

没有出现警告提示，搞定！

mysql> show table status;
+------------+--------+---------+------------+------+----------+-------------+-----
| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment              |
+------------+--------+---------+------------+------+----------------+-------------+
| dept       | MyISAM |      10 | Fixed      |    3 |             59 |         177 | 16607023625928703 |         2048 |         0 |           NULL | 2009-06-06 10:23:30 | 2009-06-06 12:18:02 | NULL       | latin1_swedish_ci |     NULL |                |                      |
| tem_tb     | MyISAM |      10 | Fixed      |    3 |             73 |         219 | 20547673299877887 |         2048 |         0 |           NULL | 2009-06-05 16:18:49 | 2009-06-05 16:47:01 | NULL       | latin1_swedish_ci |     NULL |                |                      |
| temp       | MyISAM |      10 | Fixed      |    4 |             68 |         272 | 19140298416324607 |         2048 |         0 |           NULL | 2009-06-05 16:18:49 | 2009-06-05 16:18:49 | NULL       | latin1_swedish_ci |     NULL |                |                      |
| tester     | MyISAM |      10 | Fixed      |    6 |             73 |         438 | 20547673299877887 |         5120 |         0 |           NULL | 2009-06-06 14:50:47 | 2009-06-06 14:52:12 | NULL       | latin1_swedish_ci |     NULL |                |                      |
| testerinfo | InnoDB |      10 | Compact    |    0 |              0 |       16384 |                 0 |            0 |         0 |           NULL | 2009-06-06 15:23:23 | NULL                | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 4096 kB |
+------------+--------+---------+------------+------+----------------+-----+
5 rows in set (0.06 sec)

4.试验

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

mysql> desc testerinfo;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| ID      | int(11)  | NO   | PRI |         |       |
| name    | char(30) | YES  |     | NULL    |       |
| address | char(30) | YES  |     | NULL    |       |
| sex     | char(10) | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> insert into testerinfo values(1,'jason','shenzhen','male');
Query OK, 1 row affected (0.01 sec)

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

mysql> insert into testerinfo values(2,'tina','zhuhai','female');
Query OK, 1 row affected (0.00 sec)

mysql> select * from testerinfo;
+----+-------+----------+--------+
| ID | name  | address  | sex    |
+----+-------+----------+--------+
|  1 | jason | shenzhen | male   |
|  2 | tina  | zhuhai   | female |
+----+-------+----------+--------+
2 rows in set (0.00 sec)

mysql> insert into testerinfo values(5,'john','zhuhai','male');
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from testerinfo;
+----+-------+----------+------+
| ID | name  | address  | sex  |
+----+-------+----------+------+
|  1 | jason | shenzhen | male |
+----+-------+----------+------+
1 row in set (0.00 sec)

很高兴，设置成功！接下来大家就可以进行事务处理啦！