sorted by: recent | see : popular

Content Tagged with InnoDB + Database

This is a bit surprise when we encountered a case where InnoDB is not releasing its row lock when there is an error condition within the transaction. And I verified with Falcon, Oracle, SQL Server and Sybase; all seemed to work as expected.

For example; just open a transaction in a session and execute a error statement (lets say duplicate key) and on the other new session try to get a row lock on the same record (use where clause with FOR UPDATE) and you will notice that InnoDB blocks on this statement until you issue a explicit rollback or commit. But remember there is nothing happened on the first session other than duplicate error on that row. So, InnoDB should implicitly unlock the row when there is an error; and looks like it is not doing that.

Here is the scenario:

First create a single column table and populate some rows (lets say 20 rows in this case) on any version of MySQL/InnoDB.

mysql> create table t1(c1 int not null auto_increment primary key)Engine=InnoDB;
Query OK, 0 rows affected (0.14 sec)

mysql> insert into t1 values(),(),(),(),(),(),(),(),(),();
Query OK, 10 rows affected (0.12 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> insert into t1 values(),(),(),(),(),(),(),(),(),();
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

and then execute the following statements; first in session-I and then in session-II and notice that session-II select statement hangs till you explicitly release the transaction in session-I.

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(10);
ERROR 1062 (23000): Duplicate entry ‘10′ for key ‘PRIMARY’

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where c1=10 for update;

Even though am not really sure whether this is a bug or feature in InnoDB (I suspect this as a bug); but Oracle, SQL Server or Sybase will not block the select and releases the lock on the duplicate error. Even Falcon engine in MySQL 6.0 does seem to release the lock appropriately.

Here is the quick notes from the session Falcon from the beginning by Jim Starkey and Ann Harrison

• Why Falcon
  • Hardware is evolving rapidly, world is changing, so taking advantage
  • Customers need ACID transactions
• Where hardware is going
  • CPUS breed like rabbits (more sockets, cores, threads/core)
  • Memory is bigger, faster and cheaper
  • Disks are bigger and cheaper but not much faster
  • In general boxes are getting cheaper
• Where applications are going
  • batch - dead
  • timesharing - dead
  • departmental computing - dead
  • client server - fading fast
  • application servers for most of us
  • web services for the really big buys
• Database Challenges
  • Traditional challenge
  • exhaust CPU, memory and disk simultaneously
• Tradeoffs
  • use memory to page cache to avoid disk reads
  • record cache to avoid page cache manipulation
  • use CPU to find the fastest path to record
  • use CPU to minimize record size
  • Synchronize most data structures with user mode read/write locks
  • Synchronize high contention data structures with interlocked instructions
• Architecture
  • Incomplete in-memory db with disk backfill
  • Multi-version concurrency control in memory
  • Updates in memory until commit
  • Group commits to a single serial log write
  • post-commit multi-threaded pipe line to move updates to disk
• Incomplete in-memory database
  • records cached in memory
  • separate cache for disk pages
  • record cache hits 15% the cost of a page cache hit
  • record cache is more memory efficient than page cache
• Record Encoding - cache efficiency
  • records encoded by value, not declaration
  • string “abc” occupies the same space in varchar(3) or varchar(4096)
  • the number 7 is the same where small, medium, int, bigint, decimal or numeric
• MVCC
  • update ops create new record versions
  • new one is tagged with id, points to old version
  • keep tracks which
• Updates are in memory
  • held in memory pending commit
  • index changes held in memory
  • verb rollback is dirt cheap
  • trxs rollback is dirt cheap
• At commit
  • pending record updates flushed to serial log
  • pending index updates flushed to serial log
  • commit record written to serial log
  • serial log flushed to the oxide
  • and trx is also committed
• Memory is infinite, so
  • large txns chills uncommitted data (flushes it to the log early)
  • chilled records can be thawed
  • scavenger garbage collects unloved records periodically
  • when things get really had, entire record chains flushed to backlog
• Weakness
  • transactions are ACID but not serializable
  • latency advantage disappears at saturation
  • very large transactions degrade performance
  • optimized for web, not batch
• Strengths
  • runs like a memory db when data fits
  • scales like disk-based db when db doesn’t fit in cache
  • lowest possible latency for web apps
  • absorbs huge spiky loads
• Performance
  • benchmark against InnoDB vs Falcon only
  • DBT2 benchmark (what about sysbench?)
  • High contention
  • Writes intensive - 40% records touched are updated
  • measures only performance at saturation
• DBT2 is InnoDB’s best spot and Falcon’s worst, so do not take benchmark results, decide on what you want
• When should you use what ?
  • don’t need ACID ? then MyISAM is good
  • single processor, small memory - InnoDB is good
  • large transactions, batch inserts/updates, InnoDB is good
  • multi cores, more memory, more threads , use Falcon
  • For web, Falcon is hard to beat