{"id":2168,"date":"2010-10-25T21:48:45","date_gmt":"2010-10-25T11:48:45","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=2168"},"modified":"2010-10-25T21:49:12","modified_gmt":"2010-10-25T11:49:12","slug":"storage-engine-api-state-graph","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2010\/10\/25\/storage-engine-api-state-graph\/","title":{"rendered":"Storage Engine API state graph"},"content":{"rendered":"

Drizzle still has a number of quirks inherited from the MySQL Storage Engine API (e.g. BLOBs<\/a>, row buffer<\/a>, CREATE SELECT and lack of DDL transaction boundaries<\/a>, key tuple format<\/a>). One of the things we fixed a long time ago was to have proper methods for StorageEngines to be called for: startTransaction, startStatement, endStatement, commit and rollback.<\/p>\n

If you’ve had to implement a transactional storage engine in MySQL you will be well aware of the pattern of “in every Storage Engine\/handler call: if transaction doesn’t exist, begin.” We’ve tried to fix this in the Drizzle API for a number of reasons. I think having this obvious set of calls will make the API a lot easier to understand. I am also very interested in making things much easier to prove correct.<\/p>\n

A while ago I spotted Bug 587772<\/a>, which was the READ COMMITTED isolation level not working correctly with InnoDB. It turns out that the most basic example for READ COMMITTED failed. Hrrm… this is no good. It worked on MySQL, so this was certainly something that we broke. What was more worrying is that there wasn’t a test for this in the test suite (and at the time I couldn’t find one in the MySQL test suite either, so I think we inherited the missing test).<\/p>\n

I recently started delving in, actually going to solve this. I noticed something worrying, endStatement wasn’t being called, which is where the innobase plugin would release the read view that it used for the statement. You’d think that it would grab a new one on startStatement, but because of the previous design of the API (remember “if txn isn’t started, start it!”) this also\u00c2\u00a0happened\u00c2\u00a0for getting the read view for the statement… so we instead got a REPEATABLE READ isolation level.<\/p>\n

I wanted a test.<\/p>\n

Previously, I’ve created a dummy storage engine (tableprototester) and used it to test the server code for reading the table protobuf message<\/a>. I thought about doing a Storage Engine for this problem too, basically looking at the calls to the Storage Engine as transitions between states in a state machine.<\/p>\n

A basic view of a transaction could be:<\/p>\n

\"State<\/a>That is, a transaction starts and has zero or more statements before it commits or gets rolled back.<\/p>\n

By coding up a data structure of allowable state transitions, a small function to assert() on invalid transitions and enough of the boilerplate to make the engine “work”, I was able to hit an assert() exactly where I’d expected it: at an invalid transition from START STATEMENT to COMMIT.<\/p>\n

To fix the initial bug (READ COMMITTED not working), I filled in a few state transitions for the system as a whole that aren’t quite correct. From the diagram below, you can quite obviously see where the obvious bugs are (it helps that I’ve coloured them red):<\/p>\n

\"\"<\/a>There is absolutely no sense in going BEGIN -> END STATEMENT or immediately to COMMIT. These should be relatively easy to solve too, but are separate bugs.<\/p>\n

I wish to expand this in the future to cover Cursor as well. It will also be useful to ensure that DDL can be wrapped in transactions. Not to mention the last few HTON flags that exist (and should likely go away).<\/p>\n

To generate the diagrams, I just wrote a little utility to dump out the state transitions in dot, using it to generate the diagrams.<\/p>\n","protected":false},"excerpt":{"rendered":"

Drizzle still has a number of quirks inherited from the MySQL Storage Engine API (e.g. BLOBs, row buffer, CREATE SELECT and lack of DDL transaction boundaries, key tuple format). One of the things we fixed a long time ago was … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[76,75,14],"tags":[85,382,383,70,628,384,80,242],"class_list":["post-2168","post","type-post","status-publish","format-standard","hentry","category-code","category-drizzle-work-et-al","category-mysql","tag-bug","tag-cursor","tag-dot","tag-drizzle","tag-mysql","tag-read-committed","tag-storage-engine-api","tag-storageengine"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-yY","jetpack-related-posts":[{"id":1409,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/02\/20\/improving-the-storage-engine-api\/","url_meta":{"origin":2168,"position":0},"title":"Improving the Storage Engine “API”","author":"Stewart Smith","date":"2009-02-20","format":false,"excerpt":"I increasingly enclose the API part of \"Storage Engine API\" in quotes as it does score a rather large number on the API Design Rusty levels (Coined by Rusty Russell). I give it a 15 (out of 18. lower is better) in this case \"The obvious use is wrong\". The\u2026","rel":"","context":"In "code"","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3345,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/05\/23\/mysql-vs-drizzle-plugin-api\/","url_meta":{"origin":2168,"position":1},"title":"MySQL vs Drizzle plugin APIs","author":"Stewart Smith","date":"2013-05-23","format":false,"excerpt":"There's a big difference in how plugins are treated in MySQL and how they are treated in Drizzle. The MySQL way has been to create a C API in front of the C++-like (I call it C- as it manages to take the worst of both worlds) internal \"API\". The\u2026","rel":"","context":"In "code"","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1903,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/04\/21\/storage-engine-api-write_row-create-select-and-ddl\/","url_meta":{"origin":2168,"position":2},"title":"Storage Engine API: write_row, CREATE SELECT and DDL","author":"Stewart Smith","date":"2010-04-21","format":false,"excerpt":"(this probably applies exactly the same for MySQL and Drizzle... but I'm just speaking about current Drizzle here) In my current merge request for the embedded-innodb\u00ef\u00bb\u00bf-create-select-transaction-arrgh branch (also see this specific revision), you'll notice an odd hoop that we have to jump through to make CREATE SELECT statements work with\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2253,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/01\/05\/is-your-storage-engine-buggy-or-the-database-server\/","url_meta":{"origin":2168,"position":3},"title":"Is your Storage Engine buggy or the database server?","author":"Stewart Smith","date":"2011-01-05","format":false,"excerpt":"If your storage engine returns an error from rnd_init (or doStartTableScan as it's named in Drizzle) and does not save this error and return it in any subsequent calls to rnd_next, your engine is buggy. Namely it is buggy in that a) an error may not be reported back to\u2026","rel":"","context":"In "code"","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2210,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/11\/29\/a-more-complete-look-at-storage-engine-api\/","url_meta":{"origin":2168,"position":4},"title":"A more complete look at Storage Engine API","author":"Stewart Smith","date":"2010-11-29","format":false,"excerpt":"Okay... So I've blogged many times before about the Storage Engine API in Drizzle. This API is somewhat inherited from MySQL. We have very much attempted to make it a much cleaner interface. Our goals in making changes include: make it much easier to write and maintain a storage engine,\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.flamingspork.com\/blog\/wp-content\/uploads\/2010\/11\/engine-300x140.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1931,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/05\/26\/blobs-in-the-drizzlemysql-storage-engine-api\/","url_meta":{"origin":2168,"position":5},"title":"BLOBS in the Drizzle\/MySQL Storage Engine API","author":"Stewart Smith","date":"2010-05-26","format":false,"excerpt":"Another (AFAIK) undocumented part of the Storage Engine API: We all know what a normal row looks like in Drizzle\/MySQL row format (a NULL bitmap and then column data): Nothing that special. It's a fixed sized buffer, Field objects reference into it, you read out of it and write the\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.flamingspork.com\/blog\/wp-content\/uploads\/2010\/05\/rect28161-300x67.png?resize=350%2C200","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2168","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/comments?post=2168"}],"version-history":[{"count":2,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2168\/revisions"}],"predecessor-version":[{"id":2175,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2168\/revisions\/2175"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=2168"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=2168"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=2168"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}