… a snapshot of the transaction states to determine which transactions are seen in the view later. To this end, InnoDB scans trx_list (i.e. the list of currently open transactions) and copies IDs of transactions that have not… the REPEATABLE_READ isolation level, the snapshot is created on the first SELECT for each transaction. For lower isolation levels it is created…
Post: trx descriptors: MySQL performance improvements in Percona Server 5.5.30-30.2
April 12, 2013 By 3 Comments
Comment: Implications of Metadata Locking Changes in MySQL 5.5
March 27, 2013 By 9 Comments
… of a transaction not holding metadata locks on tables that have been queried in the transaction, which causes problems by breaking isolation. However… waiting on InnoDB row locks by setting innodb_lock_wait_timeout to 1 and then issuing the ALTER: — session 1 start transaction; select…
Post: Innodb undo segment size and transaction isolation
January 12, 2011 By 3 Comments
… does transaction isolation affects purging ? Innodb is smart and trying to purge data as soon as possible. In default (REPEATABLE-READ) mode Innodb has… this case anyway, even if you set transaction isolation to lowest READ-UNCOMMITTED level Innodb will not purge the rows from undo space…
Post: Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels
August 28, 2012 By 3 Comments
… we are now holding only one lock. In all transaction isolation levels InnoDB creates locks over every index entry scanned. The difference between… … FOR UPDATE. Useful links Percona Training – InnoDB internals day covers MVCC and locking in detail The SET TRANSACTION ISOLATION LEVEL MySQL manual page
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… write queries, in spite of your chosen transaction isolation level (considering only the two most common isolation levels, REPEATABLE READ and READ COMMITTED…? Is possible to detect those gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct(s), heap…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
… especially on a lot of short write transactions. innodb_thread_concurrency=8 Even with current Innodb Scalability Fixes having limited concurrency helps. The… can run in READ-COMMITED isolation mode – if it does – set it to be default as transaction-isolation=READ-COMMITTED. This option has…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
…: How do you assess current state of Innodb scalability with multiple threads and multiple concurrent transactions ? HT: The scalability in my opinion… implemented in 5.1: if you set the transaction isolation level READ COMMITTED, then InnoDB normally does not lock the ‘gaps’, and it… not remove that serialization. MySQL’s binlog and InnoDB‘s log must have the transactions in the same order, for a recovery…
Post: XtraDB/InnoDB CPU bound benchmarks on 24cores server
December 29, 2008 By 6 Comments
…_group=2 innodb_log_file_size=512M innodb_status_file=0 innodb_thread_concurrency=0 innodb_io_capacity=1000 innodb_write_io_threads = 16 innodb_read… thread_concurrency=16 thread_stack=196K tmp_table_size=64M transaction_isolation=REPEATABLE-READ local-infile=1 At first I tried sysbench…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… to the table while a transaction in REPEATABLE-READ isolation mode is ACTIVE. session1 > select @@tx_isolation; +—————–+ | @@tx_isolation | +—————–+ | REPEATABLE-READ | +—————–+ 1 row in…) And you can see how isolation is broken because the SELECT was not repeatable although transaction isolation level of REPEATABLE-READ was used…(i int(11) not null auto_increment primary key) ENGINE=InnoDB; Query OK, 0 rows affected (0.09 sec) session1 > insert…
Post: MVCC: Transaction IDs, Log Sequence numbers and Snapshots
December 19, 2007 By 3 Comments
… bit. This is general explanation, it does not corresponds to Innodb in particular and some implementation can be different but I… consider transaction 101 which is running in REPEATABLE-READ isolation mode and which was started before transaction 100 have committed. In such case transaction…
