Mysql Transaction控制机制探讨从（旧文初发）

最近比较忙，一是事情比较多，二是忙着看Tom的书，最近看了不少，第二册已经快看完了，可以说受益匪浅；TOM的书看够了，一直对MYSQL这个数据库比较感兴趣，以前看过一本，最近又找了本MYSQL优化的书，今天迫不及待的看了几十页，受启发很多,尤其是MYSQL的灵活性，从前段时间看那本初级书就感觉到了，从外到内的灵活、简介，今天通过深入了解，确实很好，同时通过研究ORACLE外的数据库，也加深了对数据库再某些方面的理解，现总结如下：
1、MYISAM->BDB->INNODB，锁粒度越来越细（TABLE LOCK->PAGE LOCK->ROW LOCK），资源耗费越来越多(对锁的管理)，对并发行和事务的支持程度越来越好。
(1)MYISAM tables perform very well as long as the ratio of reads to writes is very high or very low;
(2)Using the page lock,the main factor infulencing concurrency is the page size;MYSQL BDB tables use page_level locking on 8k pages;The only hot spot in page locking is the last page in the table.If records are inserted there at regular intervals,the last page will be locked frequently;
(3)InnoDB doesn't use a simple row locking mechachism,however.Instead it uses row-level locking in conjunction with a multiversioning scheme,so let's have a look at that.
2、MVCC（Multi-Version Concurrency Control) is used by oracle,by postgresql,and by Mysql's InnoDB storage engine.In an versioning system,each row has two additoinal,hedden values associated with it.These values represent when the row was created and when it was expired (or deleted).The database version(or system version) is a number that increments each time a query begins.the version number is incremented for each transaction rather than each query.Under MVCC,a final duty of the database server is to keep track of all the running queries(with their associated version numbers).Let's see how this applies to particular operations:
(1).SELECT:
    when records are selected from a table,the server must examine each row to ensure that it meets several criteria:
    a.Its creation id must be less than or equal to the system version number.This ensures that the row was created before the current query began.
    b.Its delete id,id not null,must be greater than the current system version.This ensures that the row wasn't deleted before the current query began.
    c.Its creation id can't be in the list of running queries.This ensures that the row wasn't added or changed by a query that is still running.
    d.Rows that pass all of these tests may be returned as the result of the query.
(2).Insert:
   when a row is added to a table,the database server records the current version number along with the new row,using it as the row's creation id.
(3).Delete:
   To delete a row,the database server records the current version number as the row's deletion id
(4).Update:
   When a row is modified,the database server writes a new copy of the row,using the verion number as the new row's creation id.It also writes the version number as the old row's delete id.
由此，我们可以认为：ORACLE和MYSQL都存在多版本并发控制问题，也就是ORACLE的SCN,虽然他们的VERSION NUMBER的获取方法和具体应用方法不同，但原理是一样的，也就是针对每次改变，都会有不同的版本号VERSION NUMBER,在获取版本号时，一定也存在序列化的问题，否则，会存在多个事务或更改有一个版本号的问题，这本身也没什么，但在数据库的恢复和事务控制上就会存在问题，因此，我认为会有序列化的问题。还有，由此可见，版本号是一个累加的结果，而不是象以前在群里有些人说的那样，随时间而改变，如果那样，即使时间精度再细，也会有多个事务共用一个版本号的问题，对事务的控制未免太粗糙了，所以，他们的说法是不对的，版本号应该是个数字累加的结果，看来还是要看哪些官方文档，不能随便听别人说和乱看书，虽然以前所谓的SCN每三秒就增长一次的试验，但那仅仅是试验而已，存在极大的片面性，可能的原因有二：
1.是因为系统内作业定期执行的结果，每三秒执行一次，执行的结果导致对数据库的更改，因此，触动了SCN号的增加。
2.是系统内采取每三秒增长一次和累加相结合的策略。
3.还有种可能，就是大家讨论的每三秒增长一次的数字，是用作其他用途，和事务控制的版本号没任何关系。
以上纯粹是个人猜测而已，希望将来能得到正确而明确的答案。大家研究oracle都着魔了。。。

3、隔离级别（Isolation Levels）:
  (1).Read uncommitted;(2).read committed;(3).repeated read;(4).Serializable;
从(1)->(4),You're sacrificing concurrency and performance for incremented safety;
4、You can't reliably mix tables stored in transactional engines(such as InnoDB and BDB) in a single transacion. At least ,Now No.