Transactional Locks in DBs

notes.md

TX1

BEGIN ;
UPDATE banks SET balance = 100 WHERE id = 825;

TX2

BEGIN ;
UPDATE banks SET balance = 201 WHERE id = 824;
UPDATE banks SET balance = 200 WHERE id = 825;

TX1

UPDATE banks SET balance = 101 WHERE id = 824;

Tables to look at

SELECT  * from pg_stat_activity;
SELECT  * from pg_locks;
select 1460461::regclass;

Look at current isolation

SHOW transaction_isolation; OR --select current_setting('transaction_isolation')
SHOW ALL; --Displays all the settings

Set tx

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Resource

• https://devcenter.heroku.com/articles/postgresql-concurrency/
• http://www.moioli.net/progetti/deadlocks-in-postgres-101/
• http://www.moioli.net/progetti/postgres-deadlocks-debugging-guidelines/
• http://rhaas.blogspot.com/2011/10/deadlocks.html
• http://www.postgresql.org/docs/9.1/static/transaction-iso.html
• http://eric.themoritzfamily.com/understanding-psqls-mvcc.html
• http://blog.acolyer.org/2016/02/24/a-critique-of-ansi-sql-isolation-levels/

MySql

• http://makandracards.com/makandra/17437-you-re-doing-it-wrong-consistent-reads-in-mysql-transactions
• https://bugs.mysql.com/bug.php?id=53341

Isolaction levels