修改表结构DDL语句与锁

简介: --当pg开启一个事务,执行dml操作时,如果另一个事务要修改表结构,其不得不等待--session1 中执行dmlpostgres=# begin;BEGINpostgres=# ...
--当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日志复制到从库,但其却不得不等待从库查询语句执行完成,才能应用日志,进而造成主从之间的延迟


--故最好在修改表结构时观察下是否有大的查询在发生
 
 
目录
相关文章
|
4月前
|
SQL 数据库
DML的有关方法,练习
DML的有关方法,练习
|
16天前
|
数据库
SQL_语句常见的操作创建,使用,删除表和表级约束
SQL_语句常见的操作创建,使用,删除表和表级约束
26 0
|
4月前
|
SQL 数据库
零基础学习数据库SQL语句之操作表中数据的DML语句
零基础学习数据库SQL语句之操作表中数据的DML语句
52 0
零基础学习数据库SQL语句之操作表中数据的DML语句
|
4月前
|
SQL 关系型数据库 MySQL
MySQL数据库子查询练习——DDL与DML语句(包括引入视频)
MySQL数据库子查询练习——DDL与DML语句(包括引入视频)
49 1
|
3月前
|
SQL 存储 关系型数据库
MySQL数据库—初识数据库 | DDL语句 | DML语句
MySQL数据库—初识数据库 | DDL语句 | DML语句
|
4月前
|
SQL 安全 关系型数据库
MySQL DML语句insert全表数据添加语句以及注意事项
MySQL DML语句insert全表数据添加语句以及注意事项
48 0
|
5月前
|
SQL 数据库 索引
|
5月前
|
SQL 关系型数据库 MySQL
MySQL操作数据表(DDL语句)
MySQL操作数据表(DDL语句)
63 1
|
SQL 关系型数据库 MySQL
mysql数据库DDL语句和DML语句(基本增删改查)
mysql数据库DDL语句和DML语句(基本增删改查)
119 0
|
SQL 数据库
查看锁表语句以及创建触发器
查看锁表语句以及创建触发器