Hi, MARK! On Dec 25, MARK CALLAGHAN wrote:
InnoDB fixed group commit in the InnoDB plugin. This performs as expected when the binlog is disabled. This does not perform as I expect when the binlog is enabled.
The problems for InnoDB are: 1) commit is serialized on the binlog write/fsync 2) row locks are not released until the commit step of XA prepare/commit 3) per-table auto inc locks not released until the commit step of XA
I think that 2) and 3) can be fixed without significant changes.
It's not that easy, I think. What InnoDB needs locks for ? Not for protecting uncommitted changes - it uses versioning for it. For serializability (when innodb_locks_unsafe_for_binlog=true or on SERIALIZABLE level) and for explicit SELECT ... IN SHARE MORE or FOR UPDATE. Explicit locks are typically used when one reads the data and later modifies them in the same transaction based on the read values, right ? After xa_prepare no data can be modified anymore, it's safe to release these explicit locks. If InnoDB locks would be protecting uncommitted data from beeing seen by another transaction, they would have to stay until commit - but InnoDB doesn't use locks for this. Safe too. But locks that help to maintain serializability still have to be released on commit, I'm afraid. Otherwise you'll have trn1> start transaction; insert t1 select * from t2; trn1> commit; trn1>> ... xa_prepare() ... trn2> start transaction; insert t2 values (1); commit; trn2>> xa_prepare(); binlog.write(); xa_commit(); trn1> ... binlog.write(); xa_commit(); and you have incorrect transaction order in binlog. To summarize - you can release InnoDB locks on prepare only if innodb_locks_unsafe_for_binlog=false or RBR, and not SERIALIZABLE. Which could be the only case you care about anyway :) Regards / Mit vielen Grüßen, Sergei -- __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <serg@sun.com> / /|_/ / // /\ \/ /_/ / /__ Principal Software Engineer/Server Architect /_/ /_/\_, /___/\___\_\___/ Sun Microsystems GmbH, HRB München 161028 <___/ Sonnenallee 1, 85551 Kirchheim-Heimstetten Geschäftsführer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Häring