As an instructor with Percona I’m sometimes asked about the differences between the READ COMMITTED and REPEATABLE READ transaction isolation levels. There are a few differences between READ-COMMITTED and REPEATABLE-READ, and they are all related to locking.
Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels
August 28, 2012 By 3 Comments
Recovery after DROP & CREATE
August 22, 2012 By 1 Comment
In a very popular data loss scenario a table is dropped and empty one is created with the same name. This is because mysqldump in many cases generates the “DROP TABLE” instruction before the “CREATE TABLE”:
1 2 3 4 5 6 7 8 9 10 11 12 | DROP TABLE IF EXISTS `actor`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`) ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; |
If there were no subsequent CREATE TABLE the recovery would be trivial. Index_id of the PRIMARY index of [...]
Innodb Table Locks
July 31, 2012 By 4 Comments
Innodb uses row level locks right ? So if you see locked tables reported in SHOW ENGINE INNODB STATUS you might be confused and rightfully so as Innodb table locking is a bit more complicated than traditional MyISAM table locks. Let me start with some examples. First lets run SELECT Query:
1 2 3 4 5 | ---TRANSACTION 12303, ACTIVE 26 sec mysql tables in use 2, locked 0 MySQL thread id 53038, OS thread handle 0x7ff759b22700, query id 3918786 localhost root Sending data select count(*) from sbtest,sbtest x Trx read view will not see trx with id >= 12304, sees < 12301 |
As you can [...]
Paul McCullagh answers your questions about PBXT
November 20, 2009 By 2 Comments
Following on from our earlier announcement, Paul McCullagh has responded with the answers to your questions – as well as a few I gathered from other Percona folks, and attendees of OpenSQL Camp. Thank you Paul! What’s the “ideal” use case for the PBXT engine, and how does it compare in performance?  When would I [...]
Monty unviels Maria and starts Blogging
January 29, 2008 By 4 Comments
This weekend we’re hearing great news from Michael “Monty” Widenius – one of the Fathers of MySQL. Monty finally found a time to create his own blog with very descriptive name Monty Says. At the same time Monty finally announces Maria – the MyISAM successor storage engine he has been working for last few years. [...]
MVCC: Transaction IDs, Log Sequence numbers and Snapshots
December 19, 2007 By 3 Comments
MySQL Storage Engines implementing Multi Version Concurrency Control have several internal identifiers related to MVCC. I see a lot of people being confused what they are and why they are needed so I decided to take a time to explain it a bit. This is general explanation, it does not corresponds to Innodb in particular [...]
