Notes On InnoDB MySQL Transactions ~


References ~

  Baron:
  http://www.xaprb.com/blog/2011/01/29/how-innodb-performs-a-checkpoint/
  
  Mark Callagan's Note on InnoDB Fuzzy checkpoint :
  http://www.facebook.com/note.php?note_id=408059000932

  Ewen Fortune:
   http://www.mysqlperformanceblog.com/2011/02/03/how-innodb-handles-redo-logging/
  
  Peter:
  http://www.mysqlperformanceblog.com/2006/05/10/innodb-fuzzy-checkpointing-woes/
  


Contents ~

InnoDB Transactions ~

 Undo log is also called Rollback segment and is used in MVCC 
implementation.

========================================================================
  
InnoDB Transactions ~

   Sharp checkpoint and Fuzzy checkpoint



========================================================================


- To understand transactions, you should understand isolation level.

. Before you experiment with txns in mysql set autocommit=0.
  Otherwise, the txns are committed even within begin...end block!

. How to disable autocommit in my.cnf ?
     "SET GLOBAL init_connect='SET AUTOCOMMIT=0';" in "my.cnf 
     init_connect='SET AUTOCOMMIT=0'
     select @@autocommit;

Note:  set global autocommit=1; complains that it is session variable.

mysql> help ISOLATION;
Name: 'ISOLATION'
Description:
Syntax:
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
  {
       READ UNCOMMITTED
     | READ COMMITTED
     | REPEATABLE READ
     | SERIALIZABLE
   }

Typically set both GLOBAL & SESSION transaction levels to immediately affect:

SET GLOBAL  TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET GLOBAL  TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET GLOBAL  TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET GLOBAL  TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

CREATE TABLE innodbt1 (v CHAR(100);
CREATE TABLE innodbt2 (v CHAR(100);

INSERT INTO innodb1t1 values('innodb t1 val1');
INSERT INTO innodb1t1 values('innodb t1 val2');
INSERT INTO innodb1t1 values('innodb t1 val3');
INSERT INTO innodb1t1 values('innodb t1 val4');

INSERT INTO innodb1t2 values('innodb t2 val1');
INSERT INTO innodb1t2 values('innodb t2 val2');
INSERT INTO innodb1t2 values('innodb t2 val3');
INSERT INTO innodb1t2 values('innodb t2 val4');

SELECT * FROM innodbt2;
SELECT * FROM innodbt2;

o With the GLOBAL keyword, the statement sets the default transaction
  level globally for all subsequent sessions. Existing sessions are
  unaffected.

o With the SESSION keyword, the statement sets the default transaction
  level for all subsequent transactions performed within the current
  session.

o Without any SESSION or GLOBAL keyword, the statement sets the
  isolation level for the next (not started) transaction performed
  within the current session.

Note: myisam statements are not atmoic:

     The statement could be partially done even if it had failed!
     e.g. create table (i int primary key); insert into i values(1, 2,3,4,2);
     Though stmt fails, it has already inserted '1, 2, 3' !

Q: How does the binlog behave ?


Investigate  Bug #53147
  innobackup --copy-back overwrites the existing data contents without warning

Observation:
  - binlog does not grow for innodb table update transactions until 
    it is committed.
  - ibbackup applylog reports binlog position.
  - binlog just grows as soon as myisam table update is done, though it is
    inside tx which is not yet committed.


Step1:

Backup taken.
Apply log says:
Last MySQL binlog file position 0 2621, file name ./master-bin.000001

 mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |     5583 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

shutdown.

Restore to mysql installation:
InnoDB: Last MySQL binlog file position 0 2621, file name ./master-bin.000001

========================================================================