innodb存储引擎的预写日志方式来保证事务的完整性。这意味着磁盘上的存储的数据页和内存缓冲池中的页是不同步的，对于内粗缓冲中
在innodb存储引擎中，事务日志通过重做（redo）日志文件和innodb存储引擎的日志缓冲（innodb log buffer）来实现；当开始一个事务时，会记录该事务的一个lsn(log sequence number)，当事务执行是，会往innodb存储引擎的缓冲池里插入事务日志，当事务提交是，必须将innodb粗才能引擎的日志缓冲写入磁盘（默认的实现，即innodb_flush_log_at_trx_commit=1）,也就是写数据前，需要先写日志，这种方式为预写日志方式（write-ahead logging,wal）.
innodb存储引擎的预写日志方式来保证事务的完整性。这意味着磁盘上的存储的数据页和内存缓冲池中的页是不同步的，对于内粗缓冲中的页的修改，首先是写入重做日志文件，，然后再写入磁盘。因此是一种异步的方式。可以通过show engine innodb status来观察当前的磁盘和日志的差距；
mysql> create table z(a int,primary key(a)) engine=innodb;
query ok, 0 rows affected (0.01 sec)
mysql>
mysql> create procedure load_test(count int) begin declare i int unsigned default 0;
-> start transaction;
-> while i -> insert into z select i;
-> set i=i+1;
-> end while;
-> commit;
-> end;
-> $$
query ok, 0 rows affected (0.05 sec)
mysql> delimiter ;
mysql> show engine innodb status\g;
*************************** 1. row ***************************
type: innodb
name:
status:
=====================================
121130 15:41:01 innodb monitor output
=====================================
per second averages calculated from the last 12 seconds
-----------------
background thread
-----------------
srv_master_thread loops: 224 1_second, 224 sleeps, 18 10_second, 66 background, 66 flush
srv_master_thread log flush and writes: 228
----------
semaphores
----------
os wait array info: reservation count 114, signal count 113
mutex spin waits 6, rounds 180, os waits 2
rw-shared spins 99, rounds 2970, os waits 99
rw-excl spins 0, rounds 390, os waits 13
spin rounds per wait: 30.00 mutex, 30.00 rw-shared, 390.00 rw-excl
------------------------
latest detected deadlock
------------------------
121130 14:17:47
*** (1) transaction:
transaction 11f40, active 71 sec starting index read
mysql tables in use 1, locked 1
lock wait 3 lock struct(s), heap size 320, 2 row lock(s)
mysql thread id 80, os thread handle 0x33d62740, query id 4152 localhost root statistics
select * from t3 where a=2 for update
*** (1) waiting for this lock to be granted:
record locks space id 0 page no 3132 n bits 80 index `primary` of table `test`.`t3` trx id 11f40 lock_mode x locks rec but not gap waiting
record lock, heap no 3 physical record: n_fields 3; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000000011f35; asc 5;;
2: len 7; hex e00000013a011d; asc : ;;
*** (2) transaction:
transaction 11f41, active 42 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, 2 row lock(s)
mysql thread id 79, os thread handle 0x33d62600, query id 4153 localhost root statistics
select * from t3 where a=1 for update
*** (2) holds the lock(s):
record locks space id 0 page no 3132 n bits 80 index `primary` of table `test`.`t3` trx id 11f41 lock_mode x locks rec but not gap
record lock, heap no 3 physical record: n_fields 3; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000000011f35; asc 5;;
2: len 7; hex e00000013a011d; asc : ;;
*** (2) waiting for this lock to be granted:
record locks space id 0 page no 3132 n bits 80 index `primary` of table `test`.`t3` trx id 11f41 lock_mode x locks rec but not gap waiting
record lock, heap no 2 physical record: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000011f35; asc 5;;
2: len 7; hex e00000013a0110; asc : ;;
*** we roll back transaction (2)
------------
transactions
------------
trx id counter 11f45
purge done for trx's n:o history list length 1433
list of transactions for each session:
---transaction 11f41, not started
mysql thread id 79, os thread handle 0x33d62600, query id 4162 localhost root
show engine innodb status
---transaction 11f36, not started
mysql thread id 77, os thread handle 0x33d61e80, query id 4118 localhost root
---transaction 11f2b, not started
mysql thread id 75, os thread handle 0x33d61d40, query id 4077 192.168.0.69 root
---transaction 0, not started
mysql thread id 74, os thread handle 0x33d61fc0, query id 4070 192.168.0.69 root
---transaction 0, not started
mysql thread id 23, os thread handle 0x33d62380, query id 114 192.168.0.69 root
---transaction 11f40, active 5065 sec
3 lock struct(s), heap size 320, 2 row lock(s)
mysql thread id 80, os thread handle 0x33d62740, query id 4152 localhost root
--------
file i/o
--------
i/o thread 0 state: waiting for i/o request (insert buffer thread)
i/o thread 1 state: waiting for i/o request (log thread)
i/o thread 2 state: waiting for i/o request (read thread)
i/o thread 3 state: waiting for i/o request (read thread)
i/o thread 4 state: waiting for i/o request (read thread)
i/o thread 5 state: waiting for i/o request (read thread)
i/o thread 6 state: waiting for i/o request (write thread)
i/o thread 7 state: waiting for i/o request (write thread)
i/o thread 8 state: waiting for i/o request (write thread)
i/o thread 9 state: waiting for i/o request (write thread)
pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
pending flushes (fsync) log: 0; buffer pool: 0
691 os file reads, 1923 os file writes, 1010 os fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
insert buffer and adaptive hash index
-------------------------------------
ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
hash table size 553253, node heap has 6 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
log
---
log sequence number 594246629
log flushed up to 594246629
last checkpoint at 594246629
0 pending log writes, 0 pending chkp writes
649 log i/o's done, 0.00 log i/o's/second
----------------------
buffer pool and memory
----------------------
total memory allocated 135987200; in additional pool allocated 0
dictionary memory allocated 281610
buffer pool size 8192
free buffers 4305
database pages 3881
old database pages 1418
modified db pages 0
pending reads 0
pending writes: lru 0, flush list 0, single page 0
pages made young 5, not young 0
0.00 youngs/s, 0.00 non-youngs/s
pages read 633, created 3248, written 5875
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
no buffer pool page gets since the last printout
pages read ahead 0.00/s, evicted without access 0.00/s, random read ahead 0.00/s
lru len: 3881, unzip_lru len: 0
i/o sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
row operations
--------------
0 queries inside innodb, 0 queries in queue
1 read views open inside innodb
main thread id 869673728, state: waiting for server activity
number of rows inserted 105750, updated 0, deleted 0, read 59717
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
end of innodb monitor output
============================
1 row in set (0.00 sec)
error:
no query specified
mysql>
mysql> call load_test(10000);
query ok, 0 rows affected (0.50 sec)