Week 12

April 10

Homework 4

Using information_schema and pg_catalog

Review of schemas (here and here)

Review of roles

Normalization

    Facebook timeline and denormalization:
    https://www.facebook.com/note.php?note_id=10150468255628920

Transactions

transaction notes

    conflict-serializability algorithm
    debit-credit

    isolation rules

• read committed
• repeatable read
• serializable

Postgres and MVCC

select txid_current();

    select * from pg_locks where transactionid = X;

pgbench: simple transaction simulator

When I run

    pgbench -c 25 -j 25 -t 400 pgbench    # 25 threads, 400 transactions per thread

I get

    tps = 441

That's one transaction in 2.26 ms.

My transaction demo

The version conndemo2.py is here.

Experiment 1: run with NUMTHREADS=1, PERTHREAD=1000. Try different isolation levels? Try mysql? Try sorting vs no sorting?

Explain SORTING, REQLOCKS, NOWAIT

Try with (25,40): lots of deadlock

Try with (2,500): still some deadlock (can we reproduce one of these deadlocks manually?)

Let's try asking for the locks upfront, with REQLOCKS, with/without NOWAIT.

NOWAIT false: works, but takes 20-40 sec!

NOWAIT true (and that one failure line commented out): handful of lock failures, but ~10 sec

Now let's enable SORTING. No problems! 250 tps.

Try:

• with/without NOWAIT
• with/without REQLOCK
• ISOLATION='repeatable read'
• ISOLATION='serializable'

250 tps is not bad. But now does Amazon do it? How could you speed this up?

• partitioning the parts database
• entering sales in a separate table, and then updating the parts table at intervals
• accepting some potential non-atomicity

    My transactions DB:

• customer(custid serial, custname varchar(20))
• part (partnum serial, price decimal, quan_in_stock integer)
• invoice (invnum serial, ordertime timestamp, custid integer, totalamt decimal)
• invitem(invoicenum integer, partnum integer, quantity integer)

    Typical transaction based on input custid, part1, quan1, part2, quan2:

insert into invoice(ordertime, custid) values (now(), custid);
insert into invitem values (invnum, part1, quan1);
update part set quan_in_stock = quan_in_stock - quan1 where partnum = part1;
insert into invitem values (invnum, part2, quan2);
update part set quan_in_stock = quan_in_stock - quan2 where partnum = part2;