This talk gives an overview of:
How Postgres implements the various isolation levels described by the SQL standard, and the visibility rules and exact set of guarantees made by each level.
The various locks that Postgres acquires on tables, indexes, rows and transactions, and even query predicates, and how all of this fits together with the isolation modes.
Other ways in which the isolation modes handle conflict resolution.
How Postgres locks can be represented as a tree, with cascading lock dependencies, and how this information can be interpreted to find backends that block other backends without being blocked themselves (the "real offenders") using a recursive SQL query.
Common scenarios in which race condition bugs can be inadvertently added to applications, and how you can avoid them.
How to write queries to avoid locking issues such as deadlocks.
New improvements to foreign key locking added to Postgres 9.3, and what they mean for your application.
How to implement UPSERT (i.e. atomic insert-or-update) correctly, in the absence of core functionality to take care of this for you.