
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:  process 21007 detected deadlock while waiting for ShareLock on transaction 558032 after 100.233 ms
DETAIL:  Process holding the lock: 21044. Wait queue: .
CONTEXT:  while updating tuple (0,2) in relation "t"
STATEMENT:  update t set n = 0 where n = 2;
ERROR:  deadlock detected
DETAIL:  Process 21007 waits for ShareLock on transaction 558032; blocked by process 21044.
	Process 21044 waits for ShareLock on transaction 558033; blocked by process 21069.
	Process 21069 waits for ShareLock on transaction 558031; blocked by process 21007.
	Process 21007: update t set n = 0 where n = 2;
	Process 21044: update t set n = 0 where n = 3;
	Process 21069: 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;
LOG:  process 21044 still waiting for ShareLock on transaction 558033 after 100.081 ms
DETAIL:  Process holding the lock: 21069. Wait queue: 21044.
CONTEXT:  while updating tuple (0,3) in relation "t"
STATEMENT:  update t set n = 0 where n = 3;

        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 21141 still waiting for ShareLock on transaction 558034 after 100.198 ms
DETAIL:  Process holding the lock: 21116. Wait queue: 21141.
CONTEXT:  while updating tuple (0,1) in relation "t"
STATEMENT:  update t set n = 0 where n = 1;
LOG:  process 21141 acquired ShareLock on transaction 558034 after 1003.946 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|  558030 | 558035 | 1
        1| (1 row)
        1| 

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

        1| => select txid_current_snapshot();
        1|  txid_current_snapshot 
        1| -----------------------
        1|  558036:558036:
        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|                558037
        2|         (1 row)
        2|         

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

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

        1| => select xmin, xmax, * from t where n = 1;
        1|   xmin  |  xmax  | n 
        1| --------+--------+---
        1|  558030 | 558037 | 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
