This is just a quick dump of my notes from yesterday. Unfortunately, the talk wasn’t recorded, but Kevin (and Dan Ports) is giving a similar talk on this topic, more focused on core developers at PgCon.

== True Serializable Transactions Are Here ==
Kevin Grittner's talk
Serializable Snapshot Isolation

snapshots
* Serializable or repeatable read uses one snapshot for entire transaction
* read committed - new snapshot for each statement, and may see data outside snapshot if it is blocked by a write conflict

declarative constraints 
- can do in triggers with the new serializable code, BUT DON'T DO THAT
- esp exclusion constraints
- always use the most specific constraint you can get away with

== blocking on locks ==

row granularity: update/delete, select for update, select for share
- cause write to disk
- last until commit or rollback

table granularity
- automatic locks as part of most sql statements
- 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.
- limited by shared memory allocation
- last until commit or rollback
- for repeatable read, or serializable, must be acquired before data access

advisory
- based on numbers with no standard semantics (??)
- may be acquired and released at any time

== Serialization failures have a specific SQLSTATE ==
- sql standard: write conflict - cancels a transaction using sqlstate 40001
- deadlock: cancels a transaction using sqlstate 40P01
- 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

Transaction retry -- all should be safe for retry
* immediate retry of the canceled transaction is very unlikely to fail again on conflict with the same set of transaction
* don't want to use SSI if you don't have a framework to automatically retry
* automated means of transaction retry based on SQLSTATE is highly desirable

Kevin's code --> 
* everything in Java Class checks for the serializable state error and retries 

== Simple cases ==
*SQL standard does not specify that the order has to be consistent with the commit order*

Actual transaction serialization
* transaction isolation doesn't matter, apparent order of execution can always be considered to match actual order of exec 

All reads
* apparent order can be anything because nothing changed

One writer and many readers
* if readers are repeatable read or serializable there is no blocking and xactions are completely isolated
* 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
* apparent order of readwrite can always be considered to match order of execution

== Read committed ==


== serializable ==
* writes don't block reads, they just rollback to avoid conflicts

ANOMOLIES!
simple write skew
* 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. 
* if either had run first, there could not be both kiwi fruit

more than two transactions
* T0 tries to read data matching what t1 writes
* t0 and tn both overlap with t1
* t1 tries to read data matching what Tn writes but can't see Tn's writes because they are concurrent
* Tn commits first
* SSI makes an assumption that Tn depends on T0 in some way which makes it look like Tn executed before T0
** Issue: this could be through multiple transactions and involve different types of dependencies

attempts to do full cycle tracking: too much cost in tracking all those complex possibilities

== multi-row integrity constraints ==
** read up on this.

== TO USE SSI ==
* Identifying conflicting transactions
** intuition: small data, well-versed developers
** reaction: when data is found which violates the rules!
** static analysis: manually or using software, search application code for transactions and build a graph of all dangerous interactions among them

programming to protect against anomalies
* external scheduling
* materialize the conflict 
* promote the conflict
* lock tables

== SSI ==
* published in 2008 -- best paper at ACM SIGMOD, Michael Cahill (2009)
* First production level implementation of it !!

== Tradeoffs ==
* Advantages
** simplified programming
** avoids table level locks
** no extra disk writes (like select for update would generate)
** no blocking beyond current snapshot isolation

* Disadvantages
** database client must be prepared to handle serialization failure from any serializable query at any time
** cause of ser failure may not be obvious
** will sometimes happen on read-only transactions
** rate of serialization failure is higher than other techniques

DB2 stress test.. Daniel (who?) worked on this with Kevin

Where is serializable a good fit? 
* many devs write queries for a single database
* ad hoc queries are run against the database
* some or all queries are generated by a framework or ORM
* multi-row integrity rules are enforced by triggers or app code
* data violating business rules have been found .. and corruption is occuring

== how to maximize performance ==
* declare transactions as READ ONLY when possible
* control the number of active connections, using a connection pool. especially important with SSI
* don't put more into a single transaction than is needed for integrity purposes
* don't leave connections dangling "idle in transaction" longer than necessary
* eliminated explicit locks, select for update and select for share where no longer needed due to protections automatically provided by serializable transactions


Michael James cahill - http://hdl.handle.net/2123/5353
Sudhir Jorwekar, fekete, ramamritham, sudarshan, http://www.cse.iitb.ac.in/~udarsha/Pubs-dir/VLDB07-snapshot.pdf
Tom lane: http://www.postgresql.org/files/developer/concurrency.pdf

== ssi with hot standby ==
* we don't support it with hot standby -- throw an error if people request it on the hot standby 
** maytbe 9.2 we can cover transient anomolies



** Dan at MIT 
** wants to upgrade the predicate locking on btere to usee next key, finer grained than page locking

-- indexes other than btree are locking at index relation level, get finer grained on that