1

I'm trying to acquire a serializable lock on a JSONB column, but it looks like by default JSONB supports only row level lock on transactions.

Basically I want to store JSON like:

{a:"v1", b:"v2"}

Here I want to acquire lock on my json, so that when two users edit a/b concurrently for me database shouldn't block transactions. However, if both user edit the same key concurrently, I want to stop them.

My application needs properties that need to be stored and updated that's the reason I went ahead for JSONB. Not sure are there any way to achieve serializable lock on JSONB.

The keys will be fixed. They can edit more than one key. No one can delete the JSON keys according to my design, only update them.

0

2 Answers 2

2

You can't do this with JSONB. To get this level of separation, you would have to have a parent and child table, where the child table would be something like:

(parent_id whatever, key text, value text, primary key (parent_id, key))

If your JSON can be nested and not just a flat key-value store, then it is ambiguous at what level you want the locking to occur. If it always occurs at the first level, then this would still work but the value column would be of type JSONB.

If the locking occurs deeper, then that would really be a mess. What if someone wants to change a value 4 levels down, and someone else wants to remove the entire sub-tree from the 2nd level?

EAV is the design which fits your desired concurrency/locking model. Obviously there are other considerations than that, but I wouldn't know what those are. You could always use a single row, block until the other guy commits, then "manually" re-check that the value you want to update wasn't changed by the other guy.

0
1

I would never do something like this but you can certainly stop a single user from editing two keys.

CREATE TABLE foo(frekinJSON)
  AS VALUES ('{"a":1, "b":2}'::jsonb);

CREATE FUNCTION halt()
RETURNS trigger
AS $$
  BEGIN
    RAISE EXCEPTION 'NOAP!';
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER halt_in_the_name_of_evan
AFTER UPDATE OF frekinJSON ON foo
    FOR EACH ROW
    WHEN (
      (OLD.frekinJSON->'a' IS DISTINCT FROM NEW.frekinJSON->'a')
      AND (OLD.frekinJSON->'b' IS DISTINCT FROM NEW.frekinJSON->'b')
    )
    EXECUTE PROCEDURE halt();

UPDATE foo SET frekinjson = '{"a":10}';           -- fails (changing a, unsetting b)
UPDATE foo SET frekinjson = '{"a":10, "b":2}';    -- works (changing a)
UPDATE foo SET frekinjson = '{"b":10}';           -- fails (changing b, unsetting a)
UPDATE foo SET frekinjson = '{"a":10,"b":42}';    -- works (changing a)
UPDATE foo SET frekinjson = '{"a":1, "b":2}';     -- fails (both)
UPDATE foo SET frekinjson = '{"a":1, "b":42}';    -- works (changing a)
UPDATE foo SET frekinjson = '{"a":1, "b":2}';     -- works (changing b)

Having two users edit the keys concurrently would be a function of the app. And it wouldn't change in a different schema.

BEGIN;
  User one's statement;
  User two's statement;
  (logic to commit or rollback)
END;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.