postgresql 使用ROW SHARE锁时是否执行FOR UPDATE和JOIN?

8xiog9wr  于 2023-06-05  发布在  PostgreSQL
关注(0)|答案(1)|浏览(130)

根据PostgreSQL文档,ROW SHARE锁是这样工作的:
SELECT命令在指定了FOR UPDATEFOR NO KEY UPDATEFOR SHAREFOR 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只对SELECTvehicle_brand执行SELECT操作,而对其他表testing_role执行JOIN操作时,testing_role可以是INSERTUPDATEDELETE,那么是否可以只对这些表执行FOR UPDATE操作?

o4hqfura

o4hqfura1#

FOR UPDATE采用一个可选的OF子句,该子句列出了哪些表的行被锁定。如果使用OF子句,则只有列出的表需要具有比SELECT更高的特权。对于未列出的,SELECT priv就足够了。

相关问题