Sqlserver_Oracle_Mysql_Postgresql不同关系型数据库的select和ddl(alter,drop)是否互相堵塞的验证

结论
Sqlserver和Postgresql一样，select堵塞ddl，ddl也堵塞select
Oracle的话，select不堵塞DDL(0级锁不堵塞6级锁),DDL会堵塞select但不是表或行级别的锁(堵塞类型是内存层面的library cache lock，所以传统的说法6级锁不堵塞0级锁即写不堵塞读是没问题的)
Mysql的话，select堵塞DDL，DDL不直接堵塞select

Oracle 19C的实验

CREATE TABLE t1 (h1 int,h2 char(200),h3 char(200),h4 char(200),h5 char(200))
declare hid number:=1;
begin
loop
insert into t1 (h1,h2,h3,h4,h5) values(hid,'hhhhhh2','hhhhhh3','hhhhhh4','hhhhhh5');
commit;
hid:=hid+1;
exit when hid>300000;
end loop;
commit;
end;

案例1
会话1

select count(*) from t1,t1

备注：会话1执行完毕需要耗时10分钟以上

会话2

drop table t1

会话1执行过程中，会话2正常执行不会被堵塞,当会话2执行完后不久会话1报错了ORA-08103: object no longer exists

案例2
会话1

alter table t1 add t1_col varchar2(100) default '1222' not null

备注1：会话1执行之前，需要执行alter system set “_add_col_optim_enabled”=false scope=spfile;，因为11G开始新增字段为非空并有默认值时并不会修改所有行，而是直接修改的数据字典，这样的话执行alter table tablename add columnname default ‘value’ not null时会很快，为了模拟类似10G的新增字段为非空并有默认值时会修改所有行的操作，这就需要修改这个隐藏参数值，这样就能使alter table tablename add columnname default ‘value’ not null这个ddl操作很慢
备注2：t1是一张大表，会话1执行需要耗时10分钟以上

会话2

select * from t1

会话2被堵塞，会话1完成后会话2才能结束，但是堵塞事件是library cache lock而非表\行的lock

Oracle结论：
select不堵塞DDL(0级锁不堵塞6级锁),DDL会堵塞select(6级锁堵塞0级锁，但是和传统理解中的写不堵塞读不是一个概念)
查询堵塞的语句：select sid,status,LOGON_TIME,sql_id,blocking_session "死锁直接源",FINAL_BLOCKING_SESSION "死锁最终源",event,seconds_in_wait "会话锁住时间_S",LAST_CALL_ET "会话STATUS持续时间_S" from v$session where state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID'

Sqlserver 2019的实验

CREATE TABLE t1 (h1 int,h2 char(200),h3 char(200),h4 char(200),h5 char(200))
begin transaction insert1
declare @i int 
set @i=1 
while @i<1000000
begin
   insert into t1 (h1,h2,h3,h4,h5) values(@i,'hhhhhh2','hhhhhh3','hhhhhh4','hhhhhh5');
   set @i=@i+1 
   end
commit transaction insert1

案例1
会话1

select * from t1 order by table_name

备注：t1是一张大表，会话1执行需要耗时10分钟以上

会话2

drop table t1

会话2被堵塞，堵塞事件是LCK_M_SCH_M

案例2
会话1

alter table t1 add t1_col bigint IDENTITY (1,1) NOT NULL

备注：t1是一张大表，会话1执行需要耗时10分钟以上

会话2

select * from t1  或  select * from t1 with (nolock)    

会话2不管加不加with (nolock)都被堵塞，堵塞事件是LCK_M_SCH_M

Sqlserver结论：
select堵塞DDL，DDL堵塞select
查询堵塞的语句:select * from sys.sysprocess where blocked<>0

Mysql 8.0的实验

CREATE TABLE testtable1 (h1 int(11),h2 char(200),h3 char(200),h4 char(200),h5 char(200))

DELIMITER $$
CREATE  PROCEDURE autoInsert3()
BEGIN
    DECLARE i int default 1;
    START TRANSACTION;
    select sysdate();
    WHILE(i < 100000) DO
        insert into testtable1 (h1,h2,h3,h4,h5) value (i,'hhhhhhhhhhh2','hhhhhhhhhhh3','hhhhhhhhhhh4','hhhhhhhhhhh5');
        SET i = i+1;
    END WHILE;
    COMMIT;
    select sysdate();
END$$
DELIMITER ;

案例1
会话1

select count(*) from testtable1 a,testtable1 b,testtable1 c;

会话1执行需要5分钟

会话2

drop table testtable1;

会话2被堵塞，会话1完成后会话2才能结束，堵塞事件是Waiting for table metadata lock

案例2
会话1

alter table testtable1 add col_id1 int not null auto_increment,add key(col_id1);

会话1执行需要5分钟

会话2

select count(*) from testtable1 a,testtable1 b

会话2不堵塞

案例3
会话1

select count(*) from testtable1 a,testtable1 b,testtable1 c;

会话1执行需要5分钟

会话2

ALTER TABLE testtable1 ADD col_id4 int NOT NULL DEFAULT '110'
或
ALTER TABLE testtable1 ADD col_id4 int NOT NULL DEFAULT '110',ALGORITHM=Inplace, LOCK=NONE;

会话2被会话1堵塞，不管会话2加不加"ALGORITHM=Inplace, LOCK=NONE;"都会被堵塞，堵塞事件是Waiting for table metadata lock

会话3

select * from testtable1 limit 1;

会话3显示被会话1堵塞，会话3也显示被会话2堵塞，堵塞事件是Waiting for table metadata lock

案例4
会话1

alter table testtable1  drop col_id1;

会话1执行需要5分钟

会话2

select count(col_id1) from testtable1;
或
select col_id1 from testtable1;

会话2不堵塞

Mysql结论：
select堵塞DDL，DDL不直接堵塞select，因为DDL其实类似重建表，Mysql重建表原理：先创建一张临时表，MySQL会自动把原表数据拷贝到临时表、再交换表名、再删除旧表的操作。所以这个过程会堵塞DML但是不堵塞select，如果DDL也不想堵塞DML，则就是需要使用online DDL，online DDL原理：先创建一张临时表，MySQL会自动把原表数据拷贝到临时表、再拷贝原表数据到临时表的过程中将所有对原表的DML操作记录在一个日志文件，再把日志文件中的数据写入到临时表，再交换表名、再删除旧表。
查询堵塞的语句:show full processlist;结合select * from sys.schema_table_lock_waits\G;

Postgresql 11的实验

CREATE TABLE public.testtable1(h1 int,h2 char(200),h3 char(200),h4 char(200),h5 char(200));

CREATE PROCEDURE public.autoInsert() LANGUAGE plpgsql
AS $$
declare i int;
begin
  i = 1;
  while i< 5000001 loop
  insert into public.testtable1 values (i,'hhhh2','hhhh3','hhhh4','hhhh5');
  i = i+1;
  end loop; 
END$$;

call public.autoInsert();

案例1
会话1

select count(*) from public.testtable1;

会话1执行需要5分钟

会话2

drop table public.testtable1;

会话2被堵塞，会话2锁类型AccessExclusiveLock被会话1锁类型AccessShareLock堵塞

案例2
会话1

ALTER TABLE public.testtable1 ADD COLUMN col_1 serial;

会话1执行需要5分钟,添加一个自增长的列col_1

会话2

select count(*) from public.testtable1;

会话2被堵塞，会话2锁类型AccessShareLock被会话1锁类型AccessExclusiveLock堵塞

Postgresql结论：
select堵塞DDL，。
查询堵塞的语句:

select a.locktype,b.datname,a.pid,a.mode,a.granted,regclass(a.relation),regclass(a.classid),CASE WHEN granted='f' THEN 'wait_lock' WHEN granted='t' THEN 'hold_lock' END lock_satus from pg_locks a join pg_database b on a.database=b.oid;
select * from pg_stat_activity where wait_event_type in ('Lock','LWLock');