根据PostgreSQL文档,ROW SHARE
锁是这样工作的:SELECT
命令在指定了FOR UPDATE
、FOR NO KEY UPDATE
、FOR SHARE
或FOR KEY SHARE
选项之一的所有表上获取此模式的锁。
所以如果我有一个存储车辆品牌的表:
CREATE TABLE my_schema.vehicle_brand (
k_vehicle_brand varchar(40) NOT NULL,
CONSTRAINT vehicle_brand_pk PRIMARY KEY (k_vehicle_brand)
);
CREATE TABLE my_schema.vehicle (
k_vehicle integer NOT NULL,
k_vehicle_brand varchar(40) NOT NULL,
license_plate varchar(7) NOT NULL,
vehicle_color varchar(20) NOT NULL,
CONSTRAINT vehicle_pk PRIMARY KEY (k_vehicle)
);
ALTER TABLE my_schema.vehicle ADD CONSTRAINT vehicle_brand_fk
FOREIGN KEY (k_vehicle_brand)
REFERENCES my_schema.vehicle_brand (k_vehicle_brand) MATCH FULL
ON DELETE RESTRICT ON UPDATE CASCADE;
然后创建一个角色:
CREATE ROLE testing_role WITH
LOGIN
PASSWORD 'MYPASS';
GRANT USAGE
ON SCHEMA my_schema
TO testing_role;
GRANT SELECT
ON TABLE my_schema.vehicle_brand
TO testing_role;
GRANT SELECT, INSERT, UPDATE, DELETE
ON TABLE my_schema.vehicle
TO testing_role;
然后我想跑:
DO $$
DECLARE
license_plate VARCHAR;
BEGIN
LOCK TABLE my_schema.vehicle IN ROW SHARE MODE;
SELECT V.license_plate INTO license_plate
FROM my_schema.vehicle_brand B
INNER JOIN my_schema.vehicle V ON B.k_vehicle_brand = V.k_vehicle_brand
WHERE B.k_vehicle_brand = 'Audi'
FOR UPDATE;
RAISE NOTICE 'License Plate: %', license_plate;
END;
$$;
如果我希望testing_role
只对SELECT
表vehicle_brand
执行SELECT
操作,而对其他表testing_role
执行JOIN
操作时,testing_role
可以是INSERT
、UPDATE
或DELETE
,那么是否可以只对这些表执行FOR UPDATE
操作?
1条答案
按热度按时间o4hqfura1#
FOR UPDATE采用一个可选的OF子句,该子句列出了哪些表的行被锁定。如果使用OF子句,则只有列出的表需要具有比SELECT更高的特权。对于未列出的,SELECT priv就足够了。