mysql transaction_Mysql的transaction实现

transaction在数据库编程中是一个重要的概念，这样做可以控制对数据库操作的事务提交。

但是要想在程序中实现事务，要求数据库本身支持事务。

现在的关系型数据库，我们日常使用的mysql，oracle等等都支持事务，有的是安装后直接就支持，有的需要做一些设置。

这篇文章是针对mysql的，讲述从数据库安装，设置，一直到sql语句，甚至到java程序中，如何实现transaction。

1.安装

要想在mysql的表中支持transaction，必须要求是innodb表。普通表使用的autocommit模式，会自动提交每一条sql语句，不能算是transaction吧。

安装时要指定mysql支持innodb，./configure --with-innodb。

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

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

完整的配置参数如下表(下表引自http://man.chinaunix.net/database/mysql/inonodb_zh/2.htm#InnoDB_start)：

innodb_data_home_dir

这是InnoDB表的目录共用设置。如果没有在my.cnf进行设置，InnoDB将使用MySQL的datadir目录为缺省目录。如果设定一个空字串,可以在innodb_data_file_path中设定绝对路径。

innodb_data_file_path

单独指定数据文件的路径与大小。数据文件的完整路径由innodb_data_home_dir与这里所设定值的组合。 文件大小以MB单位指定。因此在文件大小指定后必有“M”。InnoDB也支持缩写“G”，1G = 1024M。从3.23.44开始，在那些支持大文件的操作系统上可以设置数据文件大小大于4 GB。而在另一些操作系统上数据文件必须小于2 GB。数据文件大小总和至少要达到10 MB。在MySQL-3.23中这个参数必须在my.cnf中明确指定。在MySQL-4.0.2以及更新版本中则不需如此，系统会默认在MySQL的datadir目录下创建一个16 MB自扩充(auto-extending)的数据文件ibdata1。你同样可以使用一个 原生磁盘分区(RAW raw disk partitions(raw devices))作为数据文件， 如何在my.cnf中详细指定它们请查看第12.1节。

innodb_mirrored_log_groups

为了保护数据而设置的日志文件组的拷贝数目，默认设置为1。在my.cnf中以数字格式设置。

innodb_log_group_home_dir

InnoDB日志文件的路径。必须与innodb_log_arch_dir设置相同值。 如果没有明确指定将默认在MySQL的datadir目录下建立两个5 MB大小的ib_logfile...文件。

innodb_log_files_in_group

日志组中的日志文件数目。InnoDB以环型方式(circular fashion)写入文件。数值3被推荐使用。在my.cnf中以数字格式设置。

innodb_log_file_size

日志组中的每个日志文件的大小(单位MB)。如果n是日志组中日志文件的数目，那么理想的数值为1M至下面设置的缓冲池(buffer pool)大小的1/n。较大的值，可以减少刷新缓冲池的次数，从而减少磁盘I/O。但是大的日志文件意味着在崩溃时需要更长的时间来恢复数据。 日志文件总和必须小于2 GB，3.23.55和4.0.9以上为小于4 GB。在my.cnf中以数字格式设置。

innodb_log_buffer_size

InnoDB将日志写入日志磁盘文件前的缓冲大小。理想值为1M至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。 因此，如果有大的事务处理，设置大的日志缓冲可以减少磁盘I/O。 在my.cnf中以数字格式设置。

innodb_flush_log_at_trx_commit

通常设置为1，意味着在事务提交前日志已被写入磁盘， 事务可以运行更长以及服务崩溃后的修复能力。如果你愿意减弱这个安全，或你运行的是比较小的事务处理，可以将它设置为0，以减少写日志文件的磁盘I/O。这个选项默认设置为0。

innodb_log_arch_dir

The directory where fully written log files would be archived if we used log archiving.这里设置的参数必须与innodb_log_group_home_dir相同。 从4.0.6开始，可以忽略这个参数。

innodb_log_archive

这个值通常设为0。 既然从备份中恢复(recovery)适合于MySQL使用它自己的log files，因而通常不再需要archive InnoDB log files。这个选项默认设置为0。

innodb_buffer_pool_size

InnoDB用来高速缓冲数据和索引内存缓冲大小。 更大的设置可以使访问数据时减少磁盘I/O。在一个专用的数据库服务器上可以将它设置为物理内存的80 %。 不要将它设置太大，因为物理内存的使用竞争可能会影响操作系统的页面调用。在my.cnf中以数字格式设置。

innodb_additional_mem_pool_size

InnoDB用来存储数据字典(data dictionary)信息和其它内部数据结构(internal data structures)的存储器组合(memory pool)大小。理想的值为2M，如果有更多的表你就需要在这里重新分配。如果InnoDB用尽这个池中的所有内存，它将从操作系统中分配内存，并将错误信息写入MySQL的错误日志中。在my.cnf中以数字格式设置。

innodb_file_io_threads

InnoDB中的文件I/O线程。 通常设置为4，但是在Windows下可以设定一个更大的值以提高磁盘I/O。在my.cnf中以数字格式设置。

innodb_lock_wait_timeout

在回滚(rooled back)之前，InnoDB事务将等待超时的时间(单位 秒)。InnoDB会自动检查自身在锁定表与事务回滚时的事务死锁。如果使用LOCK TABLES命令，或在同一个事务中使用其它事务安全型表处理器(transaction safe table handlers than InnoDB)，那么可能会发生一个 InnoDB无法注意到的死锁。在这种情况下超时将用来解决这个问题。这个参数的默认值为50秒。在my.cnf中以数字格式设置。

innodb_flush_method

这个参数仅仅与Unix相关。这个参数默认值为fdatasync。 另一个设置项为O_DSYNC。这仅仅影响日志文件的转储，在Unix下以fsync转储数据。InnoDB版本从3.23.40b开始，在Unix下指定fdatasync为使用fsync方式、指定O_DSYNC为使用O_SYNC方式。由于这在某些Unix环境下还有些问题所以在'data' versions并没有被使用。

innodb_force_recovery

警告：此参数只能在你希望从一个被损坏的数据库中转储(dump)数据的紧急情况下使用！ 可能设置的值范围为1 - 6。查看下面的章节'Forcing recovery'以了解这个参数的具体含义。参数设置大于0的值代表着InnoDB防止用户修改数据的安全度。从3.23.44开始，这个参数可用。在my.cnf中以数字格式设置。

innodb_fast_shutdown

InnoDB缺少在关闭之前清空插入缓冲。这个操作可能需要几分钟，在极端的情况下可以需要几个小时。如果这个参数据设置为1，InnoDB将跳过这个过程而直接关闭。从3.23.44和4.0.1开始，此参数可用。从3.23.50开始，此参数的默认值为1。

innodb_thread_concurrency

InnoDB会试图将InnoDB服务的使用的操作系统进程小于或等于这里所设定的数值。此参数默认值为8。如果计算机系统性能较低或innodb_monitor显示有很多线程等侍信号，应该将这个值设小一点。如果你的计算机系统有很我的处理器与磁盘系统，则可以将这个值设高一点以充分利用你的系统资源。建议设值为处理器数目+磁盘数目。 从3.23.44和4.0.1开始，此参数可用。在my.cnf中以数字格式设置。

innodb还需要使用二进制日志文件：

log-bin指定二进制文件名称，不指定默认生成。

log-bin-index 可以指定索引文件。

使用 binlog-do-db可以指定记录的数据库。

使用 binlog-ignore-db可以指定不记录的数据库。

注意的是： binlog-do-db 和binlog-ignore-db 一次只指定一个数据库，指定多个数据库需要多个语句。而且，MySQL会将所有的数据库名称改成小写， 在指定数据库时必须全部使用小写名字，否则不会起作用。

3.添加表

CREATE TABLE user (id INT NOT NULL AUTO_INCREMENT,PRIMARY KEY,fname VARCHAR(15),sname VARCHAR(20),sex VARCHAR(6),age VARCHAR(3)) TYPE=INNODB;

记得后面的TYPE=INNODB。

4.sql语句的transaction实现

两种方式：

如果SET AUTOCOMMIT=0;也就是关闭了自动提交，那么任何commit或rallback语句都可以触发事务提交。

比如：

mysql> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO user(fname,sname) VALUES ('Max','Ma');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO user(fname,sname) VALUES ('Sky','Sun');

Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

这样事务就算提交了。

如果SET AUTOCOMMIT=1;也就是开启了自动提交(默认值)，那么必须要以begin或者START TRANSACTION声明事务的开始，然后再以commit或rallback语句都可以触发事务提交。

比如：

mysql> SET AUTOCOMMIT=1;

Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO user(fname,sname) VALUES ('Max','Ma');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO user(fname,sname) VALUES ('Sky','Sun');

Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

像其他关系型数据库一样，也可以使用存储过程(procedure)来封装事务。

5.java程序开发中的实现。

涉及到程序开发实现方法就多了。

一.自己写方法把mysql的底层transaction命令封装。我感觉程序开发中应该尽量避免和底层数据库的过多交互，我没有实现它。

有人实现了，下面是他实现的一个例子网址：http://dlog.cn/html/diary/showlog.vm?sid=7&log_id=2516

二.java的jdbc开发包包含了操作transaction的方法，在java.sql.connection接口里。

使用他的好处是可以和多种类型数据库交互。

三.hibernate等ORM框架工具。

hibernate 中也封状了对transaction的操作，在org.hibernate.Session类中，使用beginTransaction()方法开启 transaction；使用getTransaction().commit()提交transaction；使用getTransaction(). rollback()方法回滚transaciton。

四.这是我常用的一种方法，把hibernate的session的方法封装或者实现jdbc的connection的接口。

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1481131