分区表锁以及如何增加分区

最新推荐文章于 2024-06-18 14:25:50 发布

DBGuardian

最新推荐文章于 2024-06-18 14:25:50 发布

阅读量411

点赞数

文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/shipeng1022/article/details/134317922

版权

创建分区

#创建分区分区表
postgres=# create table t_partition (p timestamp,v1 varchar(2),v2 text) partition by range ( p);
CREATE TABLE
# 创建三个分区
postgres=# create table p0 partition of t_partition for values  from ('2022-3-13') to ('2022-3-14');
CREATE TABLE

postgres=# create table p1 partition of t_partition for values  from ('2022-3-14') to ('2022-3-15');
CREATE TABLE

postgres=# create table p2 partition of t_partition for values  from ('2022-3-15') to ('2022-3-16');
CREATE TABLE

验证1：查询某几个分区锁会加多少锁

session1 pid=12962 开启事务，查询分区p0

postgres=# begin;
BEGIN
postgres=*# select *from t_partition where p>='2022-3-13' and p<'2022-3-14';
 p | v1 | v2 
---+----+----
(0 rows)

查看锁情况

发现t_partition和分区p0加上了锁accesssharelock
postgres=# select relation::regclass,locktype,transactionid,pid,mode,granted  from pg_locks ;
  relation   |  locktype  | transactionid |  pid  |      mode       | granted 
-------------+------------+---------------+-------+-----------------+---------
 p0          | relation   |               | 12962 | AccessShareLock | t
 t_partition | relation   |               | 12962 | AccessShareLock | t
             | virtualxid |               | 12962 | ExclusiveLock   | t
 pg_locks    | relation   |               | 23195 | AccessShareLock | t
             | virtualxid |               | 23195 | ExclusiveLock   | t
(5 rows)

sesssion2 pid=34336 truncate p0

postgres=# truncate table p0;

此处卡主

查看锁情况

postgres=# select relation::regclass,locktype,transactionid,pid,mode,granted  from pg_locks ;
  relation   |   locktype    | transactionid |  pid  |        mode         | granted 
-------------+---------------+---------------+-------+---------------------+---------
             | virtualxid    |               | 34336 | ExclusiveLock       | t
 t_partition | relation      |               | 12962 | AccessShareLock     | t
             | virtualxid    |               | 12962 | ExclusiveLock       | t
 pg_locks    | relation      |               | 23195 | AccessShareLock     | t
             | virtualxid    |               | 23195 | ExclusiveLock       | t
 p0          | relation      |               | 12962 | AccessShareLock     | t
 p0          | relation      |               | 34336 | AccessExclusiveLock | f
             | transactionid |         20397 | 34336 | ExclusiveLock       | t

会话1：加上了两把锁，主表AccessShareLock和一个子分区AccessShareLock

结论：在查询分区表时，主表以及查询范围内的子分区都会被加上AccessSharelock锁。阻止其他级别的互斥锁，比如truncate锁

验证2：对分区表查询过程是否可以正常增加分区

session1保持不动
session2 增肌分区操作

postgres=# create table p3 partition of t_partition for values from ( '2022-3-16') to ('2022-3-17');

此处卡主

查看锁情况

postgres=# select relation::regclass,locktype,transactionid,pid,mode,granted  from pg_locks ;
  relation   |   locktype    | transactionid |  pid  |        mode         | granted 
-------------+---------------+---------------+-------+---------------------+---------
             | virtualxid    |               | 34336 | ExclusiveLock       | t
             | virtualxid    |               | 12962 | ExclusiveLock       | t
 pg_locks    | relation      |               | 23195 | AccessShareLock     | t
             | virtualxid    |               | 23195 | ExclusiveLock       | t
 p0          | relation      |               | 12962 | AccessShareLock     | t
 t_partition | relation      |               | 12962 | AccessShareLock     | t
             | transactionid |         20398 | 34336 | ExclusiveLock       | t
 t_partition | relation      |               | 34336 | AccessExclusiveLock | f
             | object        |               | 34336 | AccessShareLock     | t
(9 rows)

会话2可以发现 ：
（1）增加分区时，会在主表t_partition上加AccessExclusiveLock，新对象(p3)的锁
AccessShareLock,以及事务锁ExclusiveLock。
（2） pid=34336多了3把锁，并且Mode=AccessExclusiveLock的锁模式的
granted=f,即该锁和会话1的锁AccessShareLock冲突，导致等待。

结论：
（1）在查询分区表时候，主表以及查询范围内的子分区都会增加AccessSharelock锁，无法新增分区。
（2）在增加分区过程中，会对主表加AccessExclusiveLock，导致其他会话无法通过主表访问子分区表(直接访问分区表没有问题)。
比如：

会话1创建分区期间：
create table p4 partition of t_partition for values from ( '2022-3-17') to ('2022-3-18');

会话2通过主表查询子分区：该会话必须等待
select *from t_partition where p>='2022-3-13' and p<'2022-3-14';


会话3直接查询子分区：没有等待
select * from p0;

如何在主表查询过程中增加分区

session1保持
sesssion2增加分区

ppostgres=# create table p4(like t_partition including defaults including constraints);
CREATE TABLE
postgres=# begin;
BEGIN
postgres=*# alter table t_partition attach partition p4 for values from ('2022-3-17') to ('2022-3-18');
ALTER TABLE


增加分区完成

查看锁情况

postgres=# select relation::regclass,locktype,transactionid,pid,mode,granted  from pg_locks ;
  relation   |   locktype    | transactionid |  pid  |           mode           | granted 
-------------+---------------+---------------+-------+--------------------------+---------
             | virtualxid    |               | 34336 | ExclusiveLock            | t
 p0          | relation      |               | 12962 | AccessShareLock          | t
 t_partition | relation      |               | 12962 | AccessShareLock          | t
             | virtualxid    |               | 12962 | ExclusiveLock            | t
 pg_locks    | relation      |               | 23195 | AccessShareLock          | t
             | virtualxid    |               | 23195 | ExclusiveLock            | t
             | transactionid |         20405 | 34336 | ExclusiveLock            | t
 t_partition | relation      |               | 34336 | ShareUpdateExclusiveLock | t
 p4          | relation      |               | 34336 | AccessExclusiveLock      | t
(9 rows)

会话2多了t_partition的锁ShareUpdateExclusiveLock，和p4的AccessExclusiveLock。
由于ShareUpdateExclusiveLock和会话1的AccessShareLock不互斥可以增加成功。

之所以分区可以增加成功，是以为在attach时候对主表增加了ShareUpdateExclusiveLock。

分区表truncate的影响

session1(查询分区p0):
postgres=# begin;
BEGIN
postgres=*# select * from t_partition where p>='2022-3-13' and p<'2022-3-14';
          p          | v1 |  v2  
---------------------+----+------
 2022-03-13 00:00:00 | 1  | aaaa
(1 row)

postgres=*# select relation::regclass,mode,pid from pg_locks ;
  relation   |      mode       |  pid  
-------------+-----------------+-------
 pg_locks    | AccessShareLock | 64609
 p0          | AccessShareLock | 64609
 t_partition | AccessShareLock | 64609
             | ExclusiveLock   | 64609
(4 rows)


session2(truncate 分区p1):
postgres=*# truncate table p1;
TRUNCATE TABLE
没有卡主

session3(truncate 分区p0):
postgres=*# truncate table p0;
卡主

结论：在查询范围的分区的同时，如果truncate不重叠的分区，则不会有锁。
反过来，假如先truncate指定分区，查询只要不查询该分区就不会阻塞。

分区表修改表空间对表访问的影响

session1:
postgres=# begin;
BEGIN
postgres=*# alter table t_partition set tablespace pg_default ;
ALTER TABLE
--锁情况
postgres=# select relation::regclass,mode,pid from pg_locks ;
  relation   |        mode         |  pid  
-------------+---------------------+-------
             | ExclusiveLock       | 65011
 pg_locks    | AccessShareLock     | 64609
             | ExclusiveLock       | 64609
 t_partition | AccessExclusiveLock | 65011
             | ExclusiveLock       | 65011
(5 rows)


session2(通过主表访问分区):
postgres=# select * from t_partition where p>='2022-3-13' and p<'2022-3-14';
卡主

session3(直接访问分区):
postgres=# select * from p0;
          p          | v1 |  v2  
---------------------+----+------
 2022-03-13 00:00:00 | 1  | aaaa
(1 row)
未被卡主

结论：在分区表修改表空间的情况下，直接访问分区不会被阻塞。而通过表访问分区会被阻塞。

分区表修改表空间

默认情况下都是在pg_default表空间下
--p0分区插入数据
postgres=# insert into t_partition select '2022-3-13','aa' from generate_series(1,100000) as id;
INSERT 0 100000
--p1分区插入数据
postgres=# insert into t_partition select '2022-3-14','aa' from generate_series(1,100000) as id;
INSERT 0 100000

1 修改主表的表空间，发现没有占用空间，也就是说子表的表空间仍是pg_default
postgres=# alter table t_partition  set tablespace tbs;
ALTER TABLE

[pg13@sdw2 13580]$ pwd
/home/pg13/tbs/pg13/PG_13_202007201/13580
[pg13@sdw2 13580]$ du -sh
0       .

2 修改p0表空间，发现p0的数据已经到了指定的表空间
postgres=# alter table t_partition  set tablespace tbs;
ALTER TABLE
[pg13@sdw2 13580]$ du -sh
8.5M    .

3 修改p1的表表空间，发现p1的数据已经到了指定的表空间
postgres=# alter table p1  set tablespace tbs;
ALTER TABLE
[pg13@sdw2 13580]$ du -sh
13M     .---从8.5M变成13M

结论：
如果修改分区表的表空间，需要对各个分区子表都修改，不然只会修改主表的表空间而子表还是在默认表空间

查询日分区使用current_date的雷

select * from t_partition,pg_sleep(100) where p=current_date
这种情况下有多少个分区，就会有都多少个accesssharelock锁，这样会造成很多个共享锁

select * from t_partition,pg_sleep(100) where p=‘2022-5-11’::date
这样只对总表和指定分区表加accessharelock锁

表(分区表)和MAX_LOCKS_PER_TRANSACTION

我们修改max_connection和MAX_LOCKS_PER_TRANSACTION

postgres=# show max_connections ;
 max_connections 
-----------------
 10
(1 row)

postgres=# show max_locks_per_transaction ;
 max_locks_per_transaction 
---------------------------
 11
(1 row)

#创建1000个分区表
BEGIN;
CREATE TABLE t_part (id int) PARTITION BY LIST (id);
 
SELECT 'CREATE TABLE t_part_' || id
    || ' PARTITION OF t_part FOR VALUES IN ('
    || id || ');'
FROM    generate_series(1, 1000) AS id;
 
\gexec
...........
CREATE TABLE
CREATE TABLE
CREATE TABLE
#创建途中报错
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
...............

我们可以在共享内存中保留的锁的数量是 max_connections x max_locks_per_ transaction=110。请记住，行级别的锁与此无关。如果超过一定的数量会报错。

参考：https://www.cybertec-postgresql.com/en/postgresql-you-might-need-to-increase-max_locks_per_transaction/

DBGuardian

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
分区表锁以及如何增加分区

我们可以在共享内存中保留的锁的数量是 max_connections x max_locks_per_ transaction=110。结论：在查询分区表时，主表以及查询范围内的子分区都会被加上AccessSharelock锁。（1）在查询分区表时候，主表以及查询范围内的子分区都会增加AccessSharelock锁，无法新增分区。如果修改分区表的表空间，需要对各个分区子表都修改，不然只会修改主表的表空间而子表还是在默认表空间。结论：在查询范围的分区的同时，如果truncate不重叠的分区，则不会有锁。
复制链接

扫一扫