关键字
对象、锁
问题描述
如何在金仓数据库KingbaseES中查询对象封锁情况?
解决方案
KingbaseES使用锁控制用户对数据库对象,包括:表、页面、元组、事务、非表
对象(函数,触发器等)的并发访问。
编号 | 锁模式(LOCKMODE) | 对应操作 | 与之冲突的模式 |
1 | AccessShareLock | SELECT | 8 |
2 | RowShareLock | SELECT FOR UPDATE | 7,8 |
3 | RowExclusiveLock | INSERT, UPDATE, DELETE | 5,6,7,8 |
4 | ShareUpdateExclusiveLock | VACUUM | 4,5,6,7,8 |
5 | ShareLock | CREATE INDEX | 3,4,6,7,8 |
6 | ShareRowExclusiveLock | like ExclusiveLock, but allows ROW SHARE | 3,4,5,6,7,8 |
7 | ExclusiveLock | blocks ROW SHARE | 2,3,4,5,6,7,8 |
8 | AccessExclusiveLock | DROP TABLE, ALTER TABLE | 全部 |
如果一个对象被加上了一种模式的锁,那么其他用户如果想加上和此模式相冲突的锁,就会发生冲突。这个用户必须等待,直到加上的锁被释放。
示例:
test=# create table t1l(a int) ;
CREATE TABLE
test=# select oid from sys_ class where relname='tl' ;
oid
28461
(1行记录)
test=# select locktype,database,relation,pid,mode from sys_locks where relation= '28461’;
locktype | database | relation | pid | mode
--------+--------+-------+-----+-------
(0行记录)
test=# begin;
BEGIN
test=# select * from tl;
a
----
(0行记录)
test=# select locktype,database,relation,pid,mode from sys_locks where relation= '28461’;
locktype | database | relation | pid | mode
--------+--------+-------+-----+-------
relation | 16518 | 28461 | 13676 | AccessShareLock
(1行记录)
test=# insert into tl values (1);
INSERT 0 1
test=# select locktype,database,relation,pid,mode from sys_locks where relation= '28461’;
locktype | database | relation | pid | mode
--------+--------+-------+-----+-------
relation | 16518 | 28461 | 13676 | AccessShareLock
relation | 16518 | 28461 | 13676 | ROwE XC lusiveL ock
(2行记录)