{"id":2750,"date":"2011-03-24T05:02:48","date_gmt":"2011-03-24T13:02:48","guid":{"rendered":"http:\/\/www.chesnok.com\/daily\/?p=2750"},"modified":"2012-03-26T02:51:30","modified_gmt":"2012-03-26T10:51:30","slug":"raw-notes-from-kevin-grittners-talk-on-ssi","status":"publish","type":"post","link":"https:\/\/www.chesnok.com\/daily\/2011\/03\/24\/raw-notes-from-kevin-grittners-talk-on-ssi\/","title":{"rendered":"Raw notes from Kevin Grittner&#8217;s talk on SSI"},"content":{"rendered":"<p>This is just a quick dump of my notes from yesterday. Unfortunately, the talk wasn&#8217;t recorded, but Kevin (and Dan Ports) is <a href=\"http:\/\/www.pgcon.org\/2011\/schedule\/events\/333.en.html\">giving a similar talk on this topic<\/a>, more focused on core developers at PgCon. <\/p>\n<p><!--more--><\/p>\n<pre>\r\n== True Serializable Transactions Are Here ==\r\nKevin Grittner's talk\r\nSerializable Snapshot Isolation\r\n\r\nsnapshots\r\n* Serializable or repeatable read uses one snapshot for entire transaction\r\n* read committed - new snapshot for each statement, and may see data outside snapshot if it is blocked by a write conflict\r\n\r\ndeclarative constraints \r\n- can do in triggers with the new serializable code, BUT DON'T DO THAT\r\n- esp exclusion constraints\r\n- always use the most specific constraint you can get away with\r\n\r\n== blocking on locks ==\r\n\r\nrow granularity: update\/delete, select for update, select for share\r\n- cause write to disk\r\n- last until commit or rollback\r\n\r\ntable granularity\r\n- automatic locks as part of most sql statements\r\n- explicit locks can be made in application programming -- get it before you do your other stuff, because if you later get your lock, someone else might have dove in.\r\n- limited by shared memory allocation\r\n- last until commit or rollback\r\n- for repeatable read, or serializable, must be acquired before data access\r\n\r\nadvisory\r\n- based on numbers with no standard semantics (??)\r\n- may be acquired and released at any time\r\n\r\n== Serialization failures have a specific SQLSTATE ==\r\n- sql standard: write conflict - cancels a transaction using sqlstate 40001\r\n- deadlock: cancels a transaction using sqlstate 40P01\r\n- dangerous structure: non-blocking read\/write conflicts and a commit which form a possible cycle in the apparent order of execution among two or three concurrent transactions) cancells a transaction using SQLSTATE 40001\r\n\r\nTransaction retry -- all should be safe for retry\r\n* immediate retry of the canceled transaction is very unlikely to fail again on conflict with the same set of transaction\r\n* don't want to use SSI if you don't have a framework to automatically retry\r\n* automated means of transaction retry based on SQLSTATE is highly desirable\r\n\r\nKevin's code --> \r\n* everything in Java Class checks for the serializable state error and retries \r\n\r\n== Simple cases ==\r\n*SQL standard does not specify that the order has to be consistent with the commit order*\r\n\r\nActual transaction serialization\r\n* transaction isolation doesn't matter, apparent order of execution can always be considered to match actual order of exec \r\n\r\nAll reads\r\n* apparent order can be anything because nothing changed\r\n\r\nOne writer and many readers\r\n* if readers are repeatable read or serializable there is no blocking and xactions are completely isolated\r\n* when read only trans overlaps the read\/write the read-only trans can always be considered to be executed first since it can't see the work of the writer\r\n* apparent order of readwrite can always be considered to match order of execution\r\n\r\n== Read committed ==\r\n\r\n\r\n== serializable ==\r\n* writes don't block reads, they just rollback to avoid conflicts\r\n\r\nANOMOLIES!\r\nsimple write skew\r\n* two concurrent transactions can generate a result which could not have occurred if either committed before the start of the other this is known as write skew. \r\n* if either had run first, there could not be both kiwi fruit\r\n\r\nmore than two transactions\r\n* T0 tries to read data matching what t1 writes\r\n* t0 and tn both overlap with t1\r\n* t1 tries to read data matching what Tn writes but can't see Tn's writes because they are concurrent\r\n* Tn commits first\r\n* SSI makes an assumption that Tn depends on T0 in some way which makes it look like Tn executed before T0\r\n** Issue: this could be through multiple transactions and involve different types of dependencies\r\n\r\nattempts to do full cycle tracking: too much cost in tracking all those complex possibilities\r\n\r\n== multi-row integrity constraints ==\r\n** read up on this.\r\n\r\n== TO USE SSI ==\r\n* Identifying conflicting transactions\r\n** intuition: small data, well-versed developers\r\n** reaction: when data is found which violates the rules!\r\n** static analysis: manually or using software, search application code for transactions and build a graph of all dangerous interactions among them\r\n\r\nprogramming to protect against anomalies\r\n* external scheduling\r\n* materialize the conflict \r\n* promote the conflict\r\n* lock tables\r\n\r\n== SSI ==\r\n* published in 2008 -- best paper at ACM SIGMOD, Michael Cahill (2009)\r\n* First production level implementation of it !!\r\n\r\n== Tradeoffs ==\r\n* Advantages\r\n** simplified programming\r\n** avoids table level locks\r\n** no extra disk writes (like select for update would generate)\r\n** no blocking beyond current snapshot isolation\r\n\r\n* Disadvantages\r\n** database client must be prepared to handle serialization failure from any serializable query at any time\r\n** cause of ser failure may not be obvious\r\n** will sometimes happen on read-only transactions\r\n** rate of serialization failure is higher than other techniques\r\n\r\nDB2 stress test.. Daniel (who?) worked on this with Kevin\r\n\r\nWhere is serializable a good fit? \r\n* many devs write queries for a single database\r\n* ad hoc queries are run against the database\r\n* some or all queries are generated by a framework or ORM\r\n* multi-row integrity rules are enforced by triggers or app code\r\n* data violating business rules have been found .. and corruption is occuring\r\n\r\n== how to maximize performance ==\r\n* declare transactions as READ ONLY when possible\r\n* control the number of active connections, using a connection pool. especially important with SSI\r\n* don't put more into a single transaction than is needed for integrity purposes\r\n* don't leave connections dangling \"idle in transaction\" longer than necessary\r\n* eliminated explicit locks, select for update and select for share where no longer needed due to protections automatically provided by serializable transactions\r\n\r\n\r\nMichael James cahill - http:\/\/hdl.handle.net\/2123\/5353\r\nSudhir Jorwekar, fekete, ramamritham, sudarshan, http:\/\/www.cse.iitb.ac.in\/~udarsha\/Pubs-dir\/VLDB07-snapshot.pdf\r\nTom lane: http:\/\/www.postgresql.org\/files\/developer\/concurrency.pdf\r\n\r\n== ssi with hot standby ==\r\n* we don't support it with hot standby -- throw an error if people request it on the hot standby \r\n** maytbe 9.2 we can cover transient anomolies\r\n\r\n\r\n\r\n** Dan at MIT \r\n** wants to upgrade the predicate locking on btere to usee next key, finer grained than page locking\r\n\r\n-- indexes other than btree are locking at index relation level, get finer grained on that\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>This is just a quick dump of my notes from yesterday. Unfortunately, the talk wasn&#8217;t recorded, but Kevin (and Dan Ports) is giving a similar talk on this topic, more focused on core developers at PgCon.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[97,9],"tags":[647,439],"class_list":["post-2750","post","type-post","status-publish","format-standard","hentry","category-postgres","category-postgresql","tag-postgres","tag-ssi"],"_links":{"self":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/2750","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/comments?post=2750"}],"version-history":[{"count":7,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/2750\/revisions"}],"predecessor-version":[{"id":3938,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/2750\/revisions\/3938"}],"wp:attachment":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/media?parent=2750"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/categories?post=2750"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/tags?post=2750"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}