©2020-2024 ioDraw All rights reserved,
Privacy Policy
结论
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');