结论Sqlserver和Postgresql一样select堵塞ddlddl也堵塞selectOracle的话select不堵塞DDL(0级锁不堵塞6级锁),DDL会堵塞select但不是表或行级别的锁(堵塞类型是内存层面的library cache lock所以传统的说法6级锁不堵塞0级锁即写不堵塞读是没问题的)Mysql的话select堵塞DDLDDL不直接堵塞selectOracle 19C的实验CREATETABLEt1(h1int,h2char(200),h3char(200),h4char(200),h5char(200))declarehid number:1;beginloopinsertintot1(h1,h2,h3,h4,h5)values(hid,hhhhhh2,hhhhhh3,hhhhhh4,hhhhhh5);commit;hid:hid1;exitwhenhid300000;endloop;commit;end;案例1会话1selectcount(*)fromt1,t1备注会话1执行完毕需要耗时10分钟以上会话2droptablet1会话1执行过程中会话2正常执行不会被堵塞,当会话2执行完后不久会话1报错了ORA-08103: object no longer exists案例2会话1altertablet1addt1_col varchar2(100)default1222notnull备注1会话1执行之前需要执行alter system set “_add_col_optim_enabled”false scopespfile;因为11G开始新增字段为非空并有默认值时并不会修改所有行而是直接修改的数据字典这样的话执行alter table tablename add columnname default ‘value’ not null时会很快为了模拟类似10G的新增字段为非空并有默认值时会修改所有行的操作这就需要修改这个隐藏参数值这样就能使alter table tablename add columnname default ‘value’ not null这个ddl操作很慢备注2t1是一张大表会话1执行需要耗时10分钟以上会话2select*fromt1会话2被堵塞会话1完成后会话2才能结束但是堵塞事件是library cache lock而非表\行的lockOracle结论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 stateWAITING and BLOCKING_SESSION_STATUSVALID and FINAL_BLOCKING_SESSION_STATUSVALIDSqlserver 2019的实验CREATETABLEt1(h1int,h2char(200),h3char(200),h4char(200),h5char(200))begintransactioninsert1declareiintseti1whilei1000000begininsertintot1(h1,h2,h3,h4,h5)values(i,hhhhhh2,hhhhhh3,hhhhhh4,hhhhhh5);setii1endcommittransactioninsert1案例1会话1select*fromt1orderbytable_name备注t1是一张大表会话1执行需要耗时10分钟以上会话2droptablet1会话2被堵塞堵塞事件是LCK_M_SCH_M案例2会话1altertablet1addt1_colbigintIDENTITY(1,1)NOTNULL备注t1是一张大表会话1执行需要耗时10分钟以上会话2select*fromt1 或select*fromt1with(nolock)会话2不管加不加with (nolock)都被堵塞堵塞事件是LCK_M_SCH_MSqlserver结论select堵塞DDLDDL堵塞select查询堵塞的语句:select * from sys.sysprocess where blocked0当一个长事务中的 SELECT 语句持有表上的共享锁S 锁或意向共享锁IS 锁时若后续有 INSERT / UPDATE / DELETE 语句尝试在该表上获取排他锁X 锁或触发锁升级该 INSERT / UPDATE / DELETE 语句会被SELECT语句阻塞。若此时在 SELECT 语句中使用 WITH (NOLOCK)可以使 SELECT 不申请 S 锁从而避免阻塞后续的 DML 操作但同样存在脏读风险。Mysql 8.0的实验CREATETABLEtesttable1(h1int(11),h2char(200),h3char(200),h4char(200),h5char(200))DELIMITER$$CREATEPROCEDUREautoInsert3()BEGINDECLAREiintdefault1;STARTTRANSACTION;selectsysdate();WHILE(i100000)DOinsertintotesttable1(h1,h2,h3,h4,h5)value(i,hhhhhhhhhhh2,hhhhhhhhhhh3,hhhhhhhhhhh4,hhhhhhhhhhh5);SETii1;ENDWHILE;COMMIT;selectsysdate();END$$DELIMITER;案例1会话1selectcount(*)fromtesttable1 a,testtable1 b,testtable1 c;会话1执行需要5分钟会话2droptabletesttable1;会话2被堵塞会话1完成后会话2才能结束堵塞事件是Waiting for table metadata lock案例2会话1altertabletesttable1addcol_id1intnotnullauto_increment,addkey(col_id1);会话1执行需要5分钟会话2selectcount(*)fromtesttable1 a,testtable1 b会话2不堵塞案例3会话1selectcount(*)fromtesttable1 a,testtable1 b,testtable1 c;会话1执行需要5分钟会话2ALTERTABLEtesttable1ADDcol_id4intNOTNULLDEFAULT110或ALTERTABLEtesttable1ADDcol_id4intNOTNULLDEFAULT110,ALGORITHMInplace,LOCKNONE;会话2被会话1堵塞不管会话2加不加ALGORITHMInplace, LOCKNONE;都会被堵塞堵塞事件是Waiting for table metadata lock会话3select*fromtesttable1limit1;会话3显示被会话1堵塞会话3也显示被会话2堵塞堵塞事件是Waiting for table metadata lock案例4会话1altertabletesttable1dropcol_id1;会话1执行需要5分钟会话2selectcount(col_id1)fromtesttable1;或selectcol_id1fromtesttable1;会话2不堵塞Mysql结论select堵塞DDLDDL不直接堵塞select因为DDL其实类似重建表Mysql重建表原理先创建一张临时表MySQL会自动把原表数据拷贝到临时表、再交换表名、再删除旧表的操作。所以这个过程会堵塞DML但是不堵塞select如果DDL也不想堵塞DML则就是需要使用online DDLonline DDL原理先创建一张临时表MySQL会自动把原表数据拷贝到临时表、再拷贝原表数据到临时表的过程中将所有对原表的DML操作记录在一个日志文件再把日志文件中的数据写入到临时表再交换表名、再删除旧表。查询堵塞的语句:show full processlist;结合select * from sys.schema_table_lock_waits\G;Postgresql 11的实验CREATETABLEpublic.testtable1(h1int,h2char(200),h3char(200),h4char(200),h5char(200));CREATEPROCEDUREpublic.autoInsert()LANGUAGEplpgsqlAS$$declareiint;begini1;whilei5000001loopinsertintopublic.testtable1values(i,hhhh2,hhhh3,hhhh4,hhhh5);ii1;endloop;END$$;callpublic.autoInsert();案例1会话1selectcount(*)frompublic.testtable1;会话1执行需要5分钟会话2droptablepublic.testtable1;会话2被堵塞会话2锁类型AccessExclusiveLock被会话1锁类型AccessShareLock堵塞案例2会话1ALTERTABLEpublic.testtable1ADDCOLUMNcol_1serial;会话1执行需要5分钟,添加一个自增长的列col_1会话2selectcount(*)frompublic.testtable1;会话2被堵塞会话2锁类型AccessShareLock被会话1锁类型AccessExclusiveLock堵塞Postgresql结论select堵塞DDL。查询堵塞的语句:selecta.locktype,b.datname,a.pid,a.mode,a.granted,regclass(a.relation),regclass(a.classid),CASEWHENgrantedfTHENwait_lockWHENgrantedtTHENhold_lockENDlock_satusfrompg_locks ajoinpg_database bona.databaseb.oid;select*frompg_stat_activitywherewait_event_typein(Lock,LWLock);