7 ms·
While this is cool to harden your transactions, as more locks you add as more deadlocks and blocks appear, and it becomes harder and harder to keep transactions
by varelaz 7y ago
While this is cool to harden your transactions, as more locks you add as more deadlocks and blocks appear, and it becomes harder and harder to keep transactions fast and isolated from each other. For example if you do select for update and record is not found, DB should lock key space to prevent adding such records, did you suppose to do this and block other inserts/updates? It's often easy to find problem with transactions in code, but very hard to fix all consequences.
By the way because of this instead of select for update for single record I prefer update with no change. If there is no record, nothing will be locked.
- hobs 7y agoBlocking phantom reads/writes is usually only that of a serializeable transaction isolation level - and a range lock shouldnt block other writes, unless they are of the exact range specified, and then you are asking for a consistent view of reality. Generally I dont find adding more locks hurts things that are well designed - often you will find deadlocks turn into just waiting for your turn in line - and then you can tune your db code or your hardware or your whatever to maximize that throughput. Also fwiw if you are doing any sort of upsert and you are not locking for the update/insert combo, then you are definitely opening the door to a duplicate key insert - as long as you handle that though, nbd.