结论
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
testtable1ADD 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
iint; 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');

技术
下载桌面版
GitHub
百度网盘(提取码:draw)
Gitee
云服务器优惠
阿里云优惠券
腾讯云优惠券
华为云优惠券
站点信息
问题反馈
邮箱:[email protected]
QQ群:766591547
关注微信