… the REPEATABLE_READ isolation level, the snapshot is created on the first SELECT for each transaction. For lower isolation levels it is created for each SELECT, even within the same transaction. Why is that… transactions, while a great optimization in itself, applies only to the following kinds of transactions: MySQL transactions started with START TRANSACTION READ ONLY MySQL transactions…
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, in your case what you are doing is that by executing an update query on the table, the transaction… to 1 and then issuing the ALTER: — session 1 start transaction; select * from t1 where id=3 for update; — session 2…
Post: Innodb undo segment size and transaction isolation
January 12, 2011 By 3 Comments
…. Now lets move on to the main topic – how does transaction isolation affects purging ? Innodb is smart and trying to purge data… cause problems in this case anyway, even if you set transaction isolation to lowest READ-UNCOMMITTED level Innodb will not purge the… setting isolation mode to READ-COMMITTED can be good idea if your application supports it. Note you can set isolation mode on transaction…
Post: Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels
August 28, 2012 By 3 Comments
… about the differences between the READ COMMITTED and REPEATABLE READ transaction isolation levels. There are a few differences between READ-COMMITTED and…, but we are now holding only one lock. In all transaction isolation levels InnoDB creates locks over every index entry scanned. The… internals day covers MVCC and locking in detail The SET TRANSACTION ISOLATION LEVEL MySQL manual page
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…
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… the ISOLATION level to READ COMMITTED. In this isolation level, it is normal and expected that query results can change during a transaction…
Post: MVCC: Transaction IDs, Log Sequence numbers and Snapshots
December 19, 2007 By 3 Comments
… consider transaction 101 which is running in REPEATABLE-READ isolation mode and which was started before transaction 100 have committed. In such case transaction 101 should not see changes done by transaction…
Comment: InnoDB's gap locks
April 13, 2012 By 15 Comments
… queries, in spite of your chosen transaction isolation level” I have two sessions with REPEATABLE READ transaction isolation level. mysql> show create table t\G… | repls | +——+——–+ Then I start two transactions, session1 and session2: session1> start transaction; select * from t; session2> start transaction; select * from t; I delete… you were using READ COMMITTED in spite of your chosen transaction isolation level. So lets try to remove the row 1 again…
Comment: SELECT LOCK IN SHARE MODE and FOR UPDATE
March 18, 2008 By 51 Comments
… the transaction isolation level properly set? For ODBC use the ODBC API and something like: SQLSetConnectAttr(dbc_h, SQL_ATTR_TXN_ISOLATION, (SQLPOINTER) isolation… to specify BEGIN statements. A new transaction start automatically after the previous transaction commits. To commit a transaction in ODBC, use the API…
Comment: Innodb Performance Optimization Basics
January 2, 2012 By 75 Comments
….5/en/binary-log-mixed.html Page, if you set transaction-isolation=READ-COMMITTED, you cannot use Statement-based Binary Logging, but… the transaction-isolation. Since (I assume) Row-based logging is a performance hit, isn’t it better to stick to the default transaction-isolation…
