binlog记录事务顺序问题测试

问题：

1.开启了一个事务，往里面插一条记录，然后过段时间再commit，里面记录的数据的时间是什么时候？

2.先后开启了两个事务，a先开，b后开，然后各自插入一条记录，b先commit,a后commit.binlog里面谁在前面

测试一：

开启一个事务

root@xbb 10:54:49>set autocommit =0;

Query OK, 0 rows affected (0.00 sec)

插入一条记录(5，now())

root@xbb 10:55:17>insert into xbb1(id,created_at) values(5,now());

Query OK, 1 row affected (0.00 sec)

查看记录

root@xbb 10:56:12>select * from xbb1;

+----+---------------------+

| id | created_at          |

+----+---------------------+

|  5 | 2013-10-15 02:55:52 |

+----+---------------------+

4 rows in set (0.00 sec)

此时记录的是插入记录的时间

root@xbb 10:56:16>select now();

+---------------------+

| now()               |

+---------------------+

| 2013-10-15 02:56:25 |

+---------------------+

1 row in set (0.00 sec)

此时commit，查看数据库中记录是否有变化

root@xbb 10:56:25>commit;

Query OK, 0 rows affected (0.05 sec)


查看结果，没有变化。
root@xbb 10:56:43>select * from xbb1;

+----+---------------------+

| id | created_at          |

+----+---------------------+

|  5 | 2013-10-15 02:55:52 |

+----+---------------------+

4 rows in set (0.00 sec)

由此可见，时间以插入记录的时间为准，与commit时间无关。

测试二：

先后开启两个事务a,b,分别在a中插入(4，now()),在b中插入(5,now()).先commit b事务，隔段时间commit a事务。结果记录binlog的顺序是先b后a。

BEGIN

/*!*/;

# at 173

# at 216

#131015 10:55:52 server id 1  end_log_pos 216   Table_map: `xbb`.`xbb1` 
mapped to number 29

#131015 10:55:52 server id 1  end_log_pos 254   Write_rows: table id 29 
flags: STMT_END_F



BINLOG '

uK5cUhMBAAAAKwAAANgAAAAAAB0AAAAAAAEAA3hiYgAEeGJiMQACAwcAAA==

uK5cUhcBAAAAJgAAAP4AAAAAAB0AAAAAAAEAAv/8BQAAALiuXFI=

'/*!*/;

'/*!*/;

### INSERT INTO `xbb`.`xbb1`

### SET

###   @1=5 /* INT meta=0 nullable=0 is_null=0 */

###   @2=1381805752 /* TIMESTAMP meta=0 nullable=0 is_null=0 */

# at 254

#131015 10:56:42 server id 1  end_log_pos 281   Xid = 55

COMMIT/*!*/;

# at 281

#131015 10:57:14 server id 1  end_log_pos 348   Query thread_id=1     
exec_time=0     error_code=0

SET TIMESTAMP=1381805834/*!*/;

/*!\C utf8 *//*!*/;

SET 
@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

BEGIN

/*!*/;

# at 348

# at 391

#131015 10:56:29 server id 1  end_log_pos 391   Table_map: `xbb`.`xbb1` 
mapped to number 29

#131015 10:56:29 server id 1  end_log_pos 429   Write_rows: table id 29 
flags: STMT_END_F



BINLOG '

3a5cUhMBAAAAKwAAAIcBAAAAAB0AAAAAAAEAA3hiYgAEeGJiMQACAwcAAA==

3a5cUhcBAAAAJgAAAK0BAAAAAB0AAAAAAAEAAv/8BAAAAN2uXFI=

'/*!*/;

### INSERT INTO `xbb`.`xbb1`

### SET

###   @1=4 /* INT meta=0 nullable=0 is_null=0 */

###   @2=1381805789 /* TIMESTAMP meta=0 nullable=0 is_null=0 */

# at 429

#131015 10:57:14 server id 1  end_log_pos 456   Xid = 60

COMMIT/*!*/;