… only to the following kinds of transactions: MySQL transactions started with START TRANSACTION READ ONLY MySQL transactions created by a non-locking SELECT… default sysbench POINT_SELECT mode benchmark does PK lookup SELECT queries wrapped into regular (i.e. not marked as read-only) transactions. In…
Post: trx descriptors: MySQL performance improvements in Percona Server 5.5.30-30.2
April 12, 2013 By 3 Comments
Post: Index lock and adaptive search - next two biggest InnoDB problems
February 25, 2010 By 12 Comments
… be done there internally: there is B-Tree lock free or only page-level lock algorithms, so operation does not need to block… appears when you have some scanning by secondary key select queries and write queries at the same time. InnoDB again uses single global… operations reading secondary keys. How it can be solved internally: I think some hashing algorithms may be applied to not lock select queries. We…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
…are. To read what Metadata Locking exactly is please read this section here… opened a table only held meta data locks till the …Query | 195 | Waiting for table metadata lock | select * from test_2 order by id | | 8 | msandbox | localhost | test | Query | 180 | Waiting for table metadata lock | select…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…read operations, the number of unique pages the query accessed, the length of time query waited for row locks…SELECT wp_options query, this is basically a unique way of identifying the query and simply implies that this is a SELECT query…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… DELETE or SELECT FOR UPDATE. InnoDB provides REPEATABLE READ for read-only SELECT, but it behaves as if you use READ COMMITTED for all write queries, in… (considering only the two most common isolation levels, REPEATABLE READ and READ COMMITTED). What is a gap lock? A gap lock is a lock on the…
Post: Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels
August 28, 2012 By 3 Comments
…lock struct(s), heap size 96696, 218786 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 0x7f8dfc35d700, query…locked. Non-repeatable reads (read committed) In READ COMMITTED, a read view is created at the start of each statement. This means that a SELECT…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… TABLES WITH READ LOCK to temporary make MySQL read only. In many cases the period for which server has to be made read only is very… any SELECT statement to complete in order to complete locking tables. This means if you have workload which includes some very long SELECT queries… above and either kill FLUSH TABLES WITH READ LOCK and fail backup or kill long running SELECT queries to let backup to proceed, but…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… Killed: 0 # Query_time: 9.031233 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 0 # Bytes… Killed: 0 # Query_time: 5.391612 Lock_time: 0.000121 Rows_sent: 2 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 2 # Bytes…_trx_id: 12F24 SET timestamp=1347399108; select count(*) from sbtest group by k; This only sends 2 rows while scanning 10 million…
Post: How fast is FLUSH TABLES WITH READ LOCK?
April 24, 2010 By 30 Comments
… this time, the server will get completely blocked (not just read-only!) To understand why, let’s look at what this command… the lock, anything else that currently holds the lock must finish what it’s doing. That means that every currently running query, including SELECT queries…
Post: SELECT LOCK IN SHARE MODE and FOR UPDATE
August 6, 2006 By 51 Comments
… run in READ-COMMITTED isolation mode even if current isolation mode is REPEATABLE-READ. This is done beause Innodb can only lock current version… you can simply add SELECT FOR UPDATE to your select and reduce deadlocks if you’re updating selected rows. As query results may chance…
