Apr 20
How Postgres implements repeatable-read; example at 'Postgres and MVCC'
Postgres and MVCCselect txid_current();
select * from pg_locks where transactionid = X;
Postgres serializable
Locking
and deadlocks
From the Postgres manual:
PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete. (Exactly which transaction will be aborted is difficult to predict and should not be relied upon.)
The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.
Deadlock detection via wait-for graph loops
How Postgres detects deadlock
Wait-for graphs versus transaction-conflict graphs (Ti→Tj if Ti accesses X and later Tj does and at least one of the accesses is a write)
Three-way deadlock:
T1:
begin;
update part set quan_in_stock = quan_in_stock -1 where partnum = 101;
update part set quan_in_stock = quan_in_stock -1 where partnum = 102;
T2:
update part set quan_in_stock = quan_in_stock -1 where partnum = 102;
update part set quan_in_stock = quan_in_stock -1 where partnum = 103;
T3:
update part set quan_in_stock = quan_in_stock -1 where partnum = 103;
update part set quan_in_stock = quan_in_stock -1 where partnum = 101;
Facebook timeline and denormalization:
https://www.facebook.com/note.php?note_id=10150468255628920
Tuning postgres to handle SSDs vs Spinning Iron: see postgresql.conf, Planner Cost Constants
Documentation at postgresql.org/docs/9.5/static/runtime-config-query.html.
General SQL rules
How would you store a database of user posts? The post itself would be of type text (or a long varchar).
How would you search texts for a given keyword?
Suppose a map consists of segments represented as
arrays of coordinates. That would be another example.