修改表结构DDL语句与锁

--当pg开启一个事务，执行dml操作时，如果另一个事务要修改表结构，其不得不等待
--session1 中执行dml
postgres=# begin;
BEGIN
postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
           4144
(1 row)


postgres=# insert into t values(2,'rudy2');
INSERT 0 1
postgres=# 




--在session2 中执行ddl语句，发现其要等待
postgres=# begin;
BEGIN
postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
           3128
(1 row)


postgres=# alter table t add password1 varchar(50);                            




--在session3中可以发现session2发等待session 1
postgres=# SELECT locktype,       
postgres-#         pg_locks.pid,
postgres-#          virtualtransaction,
postgres-#          transactionid,
postgres-#          nspname,
postgres-#          relname,
postgres-#          mode,
postgres-#          granted,
postgres-#     CASE
postgres-#     WHEN granted='f' THEN
postgres-#     'get_lock'
postgres-#     WHEN granted='t' THEN
postgres-#     'wait_lock'
postgres-#     END lock_satus,
postgres-#     CASE
postgres-#     WHEN waiting='f' THEN
postgres-#     'waiting'
postgres-#     WHEN waiting='t' THEN
postgres-#     'executing'
postgres-#     END lock_satus, cast(date_trunc('second',query_start) AS timestamp) AS query_start, substr(query,1,25) AS query
postgres-# FROM pg_locks LEFT OUTER
postgres-# JOIN pg_class
postgres-#     ON (pg_locks.relation = pg_class.oid) LEFT OUTER
postgres-# JOIN pg_namespace
postgres-#     ON (pg_namespace.oid = pg_class.relnamespace), pg_stat_activity
postgres-# WHERE NOT pg_locks.pid=pg_backend_pid()
postgres-#         AND pg_locks.pid=pg_stat_activity.pid
postgres-#         AND transactionid is NOT null
postgres-# ORDER BY  query_start;
   locktype    | pid  | virtualtransaction | transactionid | nspname | relname |     mode      | granted | lock_satus | lock_satus |     query_start     |           query           
---------------+------+--------------------+---------------+---------+---------+---------------+---------+------------+------------+---------------------+---------------------------
 transactionid | 4144 | 8/3137             |       4638456 |         |         | ExclusiveLock | t       | wait_lock  | waiting    | 2015-10-21 20:29:58 | insert into t values(2,'r'
 transactionid | 3128 | 2/14315            |       4638461 |         |         | ExclusiveLock | t       | wait_lock  | executing  | 2015-10-21 20:30:32 | alter table t add passwor
 
 
 
 
 
 
 
 
 
 
 
 
 --在修改表结构时，如果把ddl放在一个事务中，如果事务没有提交，则其它的事务必须等待，包括查询
 
 
--在session2事务中执行一个ddl
postgres=# begin;
BEGIN
postgres=# select pg_backend_pid();                
 pg_backend_pid 
----------------
           3128
(1 row)


postgres=# alter table t add password2 varchar(50);
ALTER TABLE
 
 
 --在session1事务中执行dml发生等待
 postgres=# begin;
BEGIN
postgres=# insert into t values(2,'rudy2');




--在session3中可以发现session1在等待session2
   locktype    | pid  | virtualtransaction | transactionid | nspname | relname |     mode      | granted | lock_satus | lock_satus |     query_start     |           query           
---------------+------+--------------------+---------------+---------+---------+---------------+---------+------------+------------+---------------------+---------------------------
 transactionid | 3128 | 2/14316            |       4638466 |         |         | ExclusiveLock | t       | wait_lock  | waiting    | 2015-10-21 20:37:38 | alter table t add passwor




 --在session4中的查询也发生了等待
postgres=# select * from t limit 1;


--故最好不要把ddl语句放在一个事务中，如果该事务没有提交，否则其会造成后续的查询与修改都等待


















--如果一个session中有一个大的查询在执行，此时最好不要在另一个session中修改表结构，因为查询会阻塞ddl语句
--在session1中执行一个大的查询
postgres=# select count(*) from t,t t1;


--在session2中的ddl语句不得不等待
postgres=# alter table t add password3 varchar(50);


--在session3中可以查看到等待的锁
   locktype    | pid  | virtualtransaction | transactionid | nspname | relname |     mode      | granted | lock_satus | lock_satus |     query_start     |           query           
---------------+------+--------------------+---------------+---------+---------+---------------+---------+------------+------------+---------------------+---------------------------
 transactionid | 3128 | 2/14317            |       4638470 |         |         | ExclusiveLock | t       | wait_lock  | executing  | 2015-10-21 20:46:09 | alter table t add passwor


--此时如果有其它session的dml操作，又不得不等待session2的ddl语句执行结束，也不得不等待，进而造成排对等待
--由此展开，在主从复制过程中，如果从库再进行类似报表的查询，主库有一个修改表结构的动作，wal日志复制到从库，但其却不得不等待从库查询语句执行完成，才能应用日志，进而造成主从之间的延迟


--故最好在修改表结构时观察下是否有大的查询在发生