Enums allow you to introduce bugs if you're not careful, because they don't impose restrictions on the rest of the object's properties. It demands a higher level of attention to detail, that's all.
Which is what I'm exploring: can I remove one major obstacle to enforce these kind of restrictions at the code level, not at the "me being cautious" level?
| 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;
Thanks so much for this comment, it made me think more than I care to admit.
You're onto something Olek. Allowing concurrent reads, but preventing concurrent writes is the underlying advantage of optimistic locking, which is what I'm aiming for.
Now, here's the (minor) issue: this doesn't work for non-transactional databases. Or, at least, for those that don't support repeatable read isolation levels.
Which is fine for many! So my point of view is that you're right, but the caveat is that this approach is not generalizable.
> this doesn't work for non-transactional databases.
I definitely agree with that.
> Allowing concurrent reads, but preventing concurrent writes
What I like to do there is to use CQRS. Queries are connected to read replicas (infinitely scalable) and commands (writes) are using the master node for repeatable reads and writes.
Why is it that using enums requires validation?
Enums allow you to introduce bugs if you're not careful, because they don't impose restrictions on the rest of the object's properties. It demands a higher level of attention to detail, that's all.
Which is what I'm exploring: can I remove one major obstacle to enforce these kind of restrictions at the code level, not at the "me being cautious" level?
| 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;
Thanks so much for this comment, it made me think more than I care to admit.
You're onto something Olek. Allowing concurrent reads, but preventing concurrent writes is the underlying advantage of optimistic locking, which is what I'm aiming for.
Now, here's the (minor) issue: this doesn't work for non-transactional databases. Or, at least, for those that don't support repeatable read isolation levels.
Which is fine for many! So my point of view is that you're right, but the caveat is that this approach is not generalizable.
> this doesn't work for non-transactional databases.
I definitely agree with that.
> Allowing concurrent reads, but preventing concurrent writes
What I like to do there is to use CQRS. Queries are connected to read replicas (infinitely scalable) and commands (writes) are using the master node for repeatable reads and writes.
I'll explore CQRS in a future article. Stay tuned ;)