Comp 305-001/488-305: Database Administration

Spring 2020: Mondays, 5:30-8:00

Week 13

Apr 20




Transactions

How Postgres implements repeatable-read; example at 'Postgres and MVCC'

Postgres and MVCC

select 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;


Normalization

  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.


Permissions and Security

General SQL rules



NoSQL and CAP

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.