
1.
Воспроизвести взаимоблокировку трех транзакций.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Создаем базу данных и таблицу с тремя строками.

        1| psql 

        1| => create database db4;
        1| CREATE DATABASE

        1| => \c db4
        1| You are now connected to database "db4" as user "postgres".

        1| => create table t(n numeric);
        1| CREATE TABLE

        1| => insert into t(n) values (1),(2),(3);
        1| INSERT 0 3

Воспроизведем взаимоблокировку трех транзакций.

        1| => begin;
        1| BEGIN

        1| => update t set n = 0 where n = 1;
        1| UPDATE 1

        2|         psql 

        2|         => \c db4
        2|         You are now connected to database "db4" as user "postgres".

        2|         => begin;
        2|         BEGIN

        2|         => update t set n = 0 where n = 2;
        2|         UPDATE 1

        3|                 psql 

        3|                 => \c db4
        3|                 You are now connected to database "db4" as user "postgres".

        3|                 => begin;
        3|                 BEGIN

        3|                 => update t set n = 0 where n = 3;
        3|                 UPDATE 1

        1| => update t set n = 0 where n = 2;
        2|         => update t set n = 0 where n = 3;
        3|                 => update t set n = 0 where n = 1;

sleep 3 

Вот какую информацию о взаимоблокировке можно получить из журнала:


tail -n 18 /home/postgres/logfile 
LOG:  shutting down
LOG:  database system is shut down
LOG:  database system was shut down at 2016-07-02 16:30:13 MSK
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
ERROR:  database "db2" does not exist
STATEMENT:  DROP DATABASE db2;
ERROR:  deadlock detected
DETAIL:  Process 18388 waits for ShareLock on transaction 4010; blocked by process 18425.
	Process 18425 waits for ShareLock on transaction 4011; blocked by process 18450.
	Process 18450 waits for ShareLock on transaction 4009; blocked by process 18388.
	Process 18388: update t set n = 0 where n = 2;
	Process 18425: update t set n = 0 where n = 3;
	Process 18450: update t set n = 0 where n = 1;
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "t"
STATEMENT:  update t set n = 0 where n = 2;

        1| => \q
        2|         => \q
        3|                 => \q

2.
Вывести в журнал информацию о блокировках.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Требуется изменить параметры log_lock_waits и deadlock_timeout.

sed -i "s/#\\'?\\'(log_lock_waits\\').*/\\'1 = on/" /usr/local/pgsql/data/postgresql.conf 
sed -i "s/#\\'?\\'(deadlock_timeout\\').*/\\'1 = 100ms/" /usr/local/pgsql/data/postgresql.conf 
egrep log_lock_waits|deadlock_timeout /usr/local/pgsql/data/postgresql.conf 
log_lock_waits = on
deadlock_timeout = 100ms



pg_ctl reload 
server signaled

Теперь воспроизведем блокировку.

        1| psql 

        1| => \c db4
        1| You are now connected to database "db4" as user "postgres".

        1| => begin;
        1| BEGIN

        1| => update t set n = 0 where n = 1;
        1| UPDATE 1

        2|         psql 

        2|         => \c db4
        2|         You are now connected to database "db4" as user "postgres".

        2|         => begin;
        2|         BEGIN

        2|         => update t set n = 0 where n = 1;

sleep 1 

        1| => rollback;
        1| ROLLBACK

        2|         => rollback;
        2|         UPDATE 1
        2|         ROLLBACK

Вот что попало в журнал:


tail -n 7 /home/postgres/logfile 
LOG:  process 18525 still waiting for ShareLock on transaction 4012 after 100.106 ms
DETAIL:  Process holding the lock: 18497. Wait queue: 18525.
CONTEXT:  while updating tuple (0,1) in relation "t"
STATEMENT:  update t set n = 0 where n = 1;
LOG:  process 18525 acquired ShareLock on transaction 4012 after 1004.387 ms
CONTEXT:  while updating tuple (0,1) in relation "t"
STATEMENT:  update t set n = 0 where n = 1;

        1| => \q
        2|         => \q

3.
Ситуация, при которой одна транзакция еще видит строку, а другая уже нет.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


        1| psql 

        1| => \c db4
        1| You are now connected to database "db4" as user "postgres".

        1| => begin;
        1| BEGIN

        1| => set transaction isolation level repeatable read;
        1| SET

        1| => select xmin, xmax, * from t where n = 1;
        1|  xmin | xmax | n 
        1| ------+------+---
        1|  4008 | 4013 | 1
        1| (1 row)
        1| 

        1| => select txid_current();
        1|  txid_current 
        1| --------------
        1|          4014
        1| (1 row)
        1| 

        1| => select txid_current_snapshot();
        1|  txid_current_snapshot 
        1| -----------------------
        1|  4014:4014:
        1| (1 row)
        1| 

        2|         psql 

        2|         => \c db4
        2|         You are now connected to database "db4" as user "postgres".

        2|         => begin;
        2|         BEGIN

        2|         => delete from t where n = 1;
        2|         DELETE 1

        2|         => select xmin, xmax, * from t where n = 1;
        2|          xmin | xmax | n 
        2|         ------+------+---
        2|         (0 rows)
        2|         

        2|         => select txid_current();
        2|          txid_current 
        2|         --------------
        2|                  4015
        2|         (1 row)
        2|         

        2|         => select txid_current_snapshot();
        2|          txid_current_snapshot 
        2|         -----------------------
        2|          4014:4014:
        2|         (1 row)
        2|         

Поля xmin и xmax можно посмотреть из первой транзакции:

        1| => select xmin, xmax, * from t where n = 1;
        1|  xmin | xmax | n 
        1| ------+------+---
        1|  4008 | 4015 | 1
        1| (1 row)
        1| 

Снимки одинаковые, но:
Первая транзакция видит строку, так как xmax позже момента создания снимка.
Вторая транзакция не видит строку, так как ее номер равен xmax.

        1| => \q
        2|         => \q

4.
Какой снимок будет использоваться в функции, вызываемой внутри запроса SQL?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


        1| psql 

        1| => \c db4
        1| You are now connected to database "db4" as user "postgres".

        1| => create function test() returns integer as $$
        1| => declare
        1| =>   cnt integer;
        1| => begin
        1| =>   select count(*) into cnt from t;
        1| =>   perform pg_sleep(1);
        1| =>   return cnt;
        1| => end;
        1| => $$ language plpgsql;
        1| CREATE FUNCTION

        1| => begin;
        1| BEGIN

        1| => select (select count(*) from t) as cnt, test() from generate_series(1,10);

Параллельно выполняем другую транзакцию (увеличиваем число строк в таблице):

        2|         psql 

        2|         => \c db4
        2|         You are now connected to database "db4" as user "postgres".

        2|         => select pg_sleep(3);
        2|          pg_sleep 
        2|         ----------
        2|          
        2|         (1 row)
        2|         

        2|         => insert into t values (42);
        2|         INSERT 0 1

        1| => end;
        1|  cnt | test 
        1| -----+------
        1|    3 |    3
        1|    3 |    3
        1|    3 |    3
        1|    3 |    3
        1|    3 |    4
        1|    3 |    4
        1|    3 |    4
        1|    3 |    4
        1|    3 |    4
        1|    3 |    4
        1| (10 rows)
        1| 
        1| COMMIT

Значение первого столбца не изменяется - запрос использует снимок,
созданный в начале выполнения.
Однако значение во втором столбце изменяется - запросы внутри функции
используют отдельные снимки.

        1| => \q
        2|         => \q
