Discussion about this post

User's avatar
Ryan Johnson's avatar

Why is it that using enums requires validation?

Expand full comment
Olek Gornostal's avatar

| But if the table you’re locking isn’t the same as the one you’re changing, flagging a specific row won’t work. You’d have to lock the entire table.

What do you think about the example below where it locks the row FOR UPDATE from one table and updates other tables? It should give you that transactional guarantees, lock just a single row, and allow concurrent reads.

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- to allow concurrent reads

SELECT * FROM accounts WHERE account_id = X FOR UPDATE;

INSERT INTO transaction_ledger (account_id, transaction_date, debit, credit, description)

VALUES (X, NOW(), 100, 0, 'Purchase of item Y');

UPDATE inventory SET count = count - 1 WHERE item_id = Y; -- or do any other business-related updates

UPDATE accounts SET total_balance = total_balance - 100 WHERE account_id = X;

COMMIT;

Expand full comment
4 more comments...

No posts