現(xiàn)象
記錄一個(gè)生產(chǎn)問題莫杈，DBA在執(zhí)行下面這個(gè)GRANT操作的時(shí)候亏娜，偶發(fā)的會(huì)出現(xiàn)這個(gè)報(bào)錯(cuò)
[guqi@intel175 ~]$ psql -p 51300
psql (14devel)
Type "help" for help.
guqi=# grant select on all tables in schema public to guqi ;
ERROR: tuple concurrently updated
guqi=# \q
分析
GRANT操作不會(huì)更新表內(nèi)的數(shù)據(jù)津肛，應(yīng)該只是會(huì)更新系統(tǒng)表內(nèi)的行。換言之梅肤，當(dāng)執(zhí)行GRANT的時(shí)候，有些數(shù)據(jù)表在系統(tǒng)表內(nèi)的行被別人更新了邑茄。設(shè)置下errverbose姨蝴，發(fā)現(xiàn)是在更新pg_class的relacl屬性
[guqi@intel175 ~]$ psql -p 51300
psql (14devel)
Type "help" for help.
guqi=#\errverbose
guqi=# grant select on all tables in schema public to guqi ;
^CCancel request sent
ERROR: canceling statement due to user request
CONTEXT: while updating tuple (6,6) in relation "pg_class"
guqi=# select * from pg_class where ctid = '(6,6)';
oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples
| relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasrules |
relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relr
ewrite | relfrozenxid | relminmxid | relacl | reloptions | relpartbound
-------+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------
+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+-------------+-
---------------+----------------+----------------+---------------------+----------------+--------------+----------------+-----
-------+--------------+------------+--------+------------+--------------
16458 | tb1 | 2200 | 16460 | 0 | 10 | 2 | 16462 | 0 | 0 | -1
| 0 | 0 | f | f | p | r | 2 | 0 | f |
f | f | f | f | t | d | f |
0 | 580 | 1 | | |
(1 row)
同時(shí)查看pg_stat_activity，發(fā)現(xiàn)有TRUNCATE在運(yùn)行肺缕，與內(nèi)核開發(fā)的小伙伴溝通了下左医，truncate在內(nèi)部會(huì)重建表，重建會(huì)導(dǎo)致表的物理存儲(chǔ)發(fā)生變化同木「∩遥基于這一點(diǎn)，我測試了下
# 窗口1
guqi=# begin;
BEGIN
guqi=*# truncate tb1 ;
TRUNCATE TABLE
-- 暫不提交
# 窗口2
guqi=# grant select on all tables in schema public to guqi ;
-- 操作會(huì)處于鎖等狀態(tài)
# 窗口1
guqi=*# commit;
COMMIT
# 窗口2
guqi=# grant select on all tables in schema public to guqi ;
ERROR: tuple concurrently updated
按照上述順序執(zhí)行彤路，100%觸發(fā)秕硝，而且通過跟蹤tb1在pg_class內(nèi)的ctid值，發(fā)現(xiàn)TRUNCATE前后確實(shí)變了洲尊，那么基本原因就是這個(gè)了缝裤。
解決的話，把GRANT和TRUNCATE分開執(zhí)行颊郎，不要并發(fā)憋飞。
