Argodb 常见sql对应的锁类型

2024-09-25 其他常见问题

内容纲要

概要描述

本文主要介绍常见sql及对应的加锁类型。

一. 前提准备

1. DBA开启INFO日志

file

2. 在quark-server的log搜索锁关键字

tail -f quark-server.log |grep preparing
tail -f quark-server.log |grep lockType
tail -f quark-server.log |grep 'lockType\|preparing'   – 更全一点，包括quark拿锁以及shiva返回结果

3. 在quark-metastore的log搜索锁关键字

开启metastore日志，去修改/etc/conf/quark中的日志文件，可以将对应的WARN替换为INFO，然后重启quark/或者直接修改metainfo文件。
涉及DDL变更，如下：

drop table 
alter table add/drop partition

二.sql对应的各种锁类型

1. 非分区表

1.1 select * from table_a

RO
file

1.2 insert

1.2.1 insert into table_a

APPEND

insert into holoA values(1,'a');
insert into holoA select 1,'a' from system.dual;
batchinsert into holoA batchvalues(values(2,'b'),values(3,'c'));

file

1.2.2 insert into a select * from a

MUTATE–READ

insert into holoA select * from holoA;

file

1.2.3 insert into a select * from b

APPEND–READ

insert into holoB select * from holoA;

file

1.2.4 insert overwrite a select from a/insert overwrite b select from a

TRUNCATE–APPEND–READ

insert overwrite holoA select * from holoA;
insert overwrite holoB select * from holoA;

file

1.3 update/delete

MUTATE–READ

update table holoA set id=2;
delete from table holoA where id=1;

file

1.4 truncate

truncate

truncate table holoA;

file

1.5 drop

promotionType:KDropTable

drop table holoA;

file

1.6 alter

1.6.1 alter table holoA rename to holoA_testA/alter table holoA change c3_char c3_char char(12);

无锁

1.6.2 alter table holoA add columns(score string);/alter table holoA delete columns(score);

增减列

promotionType: kAddColumn
promotionType: kDropColumn
preparing altering table success

file

1.6.3 compact

RO full/major/minor
compact/concurrentfullcompact
fullcompact

compact full

file

compact major

file

compact minor

file

2. 分区表

2.1 select

select * from holoA_p order by id,name;

file

select * from holoA_p where p_id=1;

file

2.2 insert

2.2.1 动态分区插入

APPEND–VALIDATE

insert into holoB_p select 1, "name1",1 from system.dual; -- 1是分区

file

2.2.2 insert into A_p select * from B_p

APPEND–READ–VALIDATE(VALIDDATE是否存在取决于是否动态新增分区)

insert into holoA_p select * from holoB_p;

file

2.2.3 insert into A_p select * from A_p

APPEND–READ

①首先判断是不是单值分区动态插入，是了就拿append锁
②再考虑是不是insert a select a，如果是就拿mutate，不是就拿append

insert into holoA_p select * from holoA_p;

file

2.2.4 insert overwrite A_p select * from A_p

APPEND–APPEND–READ

insert overwrite holoA_p select * from holoA_p;

为啥带分区的没有truncate锁，APPEND相当于把表清空，写对应行空记录，在动态分区插入时候不知道后面数据对应哪个分区，所以需要append，不指定分区整张表清空掉，指定分区对应分区清空掉

file

2.2.5 insert overwrite A_p select * from B_p

APPEND–APPEND–READ–VALIDATE(VALIDDATE是否存在取决于是否动态新增分区)

insert overwrite holoA_p select * from holoB_p;

file

2.2.6 指定分区insert into/overwrite

指定分区Insert into
APPEND

insert into holoA_p partition(p_id=1) select 1, "name1" from system.dual;

file

指定分区insert overwrite
TRUNCATE–APPEND

file

2.2.7 指定分区insert into from

MUTATE–READ（非RO）
insert into A partition select from A （相同分区/不同分区）

insert into holoA_p partition(p_id=1) select 1,"name1" from holoA_p;
insert into holoA_p partition(p_id=1) select 2,"name2" from holoA_p;

file

APPEND–READ（非RO）

insert into A partition select from B （相同分区/不同分区）

insert into holoA_p partition(p_id=1) select 1,"name1" from holoB_p;
insert into holoA_p partition(p_id=1) select 2,"name2" from holoB_p;

file

2.2.8 指定分区insert overwrite

TRUNCATE–APPEND–READ

insert overwrite A partition select from A/insert overwrite A partition select from B （相同分区/不同分区）

insert overwrite holoA_p partition(p_id=1) select 1,"name1" from holoA_p;
insert overwrite holoA_p partition(p_id=1) select 2,"name2" from holoA_p;
insert overwrite holoA_p partition(p_id=1) select 1,"name1" from holoB_p;
insert overwrite holoA_p partition(p_id=1) select 2,"name2" from holoB_p;

file

2.3 alter

2.3.1 alter table holoA rename to holoA_testA;/alter table holoA change c3_char c3_char char(12);

无锁

2.3.2 add partition

alter table holoA_p add partition(p_id=2);

promotionType: KAddPartition
preparing alter table success
file

2.3.3 drop partition

alter table holoA_p drop partition(p_id=1);

promotionType: KDropPartition
preparing alter table success

file

2.3.4 alter table holoA add columns(score string);/alter table holoA delete columns(score);

promotionType: KAddColumn
promotionType: KDropColumn
preparing alter table success

file

2.3.5 compact

compact full/major/minor 多个分区读多次？– 是的，可参考非分区的，一个是一整个分区，一个是多个分区

file

2.4 update/delete

2.4.1 update

非指定分区：set hive.crud.dynamic.partition=true;
MUTATE–READ(非RO isPartitionLockLevel:true)

update table holoA_p set name='name2';

file

指定分区：
MUTATE–READ(非RO isPartitionLockLevel:true)

update table holoA_p partition(p_id=1) set name='name2';

file

2.4.2 delete

非指定分区：
MUTATE–READ(非RO isPartitionLockLevel:true)

delete from table holoA_p where name='name2';

file

指定分区：
MUTATE–READ(非RO isPartitionLockLevel:true)

delete from table holoA_p partition(p_id=1);

file

2.4.3 truncate

非指定分区：
TRUNCATE（非RO isPartitionLockLevel:true）有几个分区，truncate几次？ – 确定

truncate table holoA_p;

file

指定分区：（isPartitionLockLevel:true）
TRUNCATE（非RO isPartitionLockLevel:true）

truncate table holoA_p partition(p_id=1);

file

注意：

①perf-rowkey表是mutate，因为rowkey唯一，线程并发时候可能会写同一个key，结果就不对；正常应该是append
②对于非rowkey表来说，目前版本的insert就只有insert a select a这一个case会拿mutate，其他都是append
③VALIDATE锁类型是判断并发时候其他线程有没有也去建这个分区的，如果不建分区的话，就没有VALIDATE
④非perf rowkey表，只要动态分区插入就是append
⑤truncate的语句，虽然是在quark-server上做的，但是是提交到shiva内部的，很快，而且这种truncate和drop table冲突，和其他的sql语句与drop table 冲突是差不多的，所以可以跳过

近期文章

分类

归档