I know that table locking sucks and it doesn’t scale, but I’m not sure the best way to handle this particular problem, so maybe one of you SQL dudes might have a good idea. FWIW, I’m using SQLAlchemy instead of SQL statements directly, but I can drop down to SQL statements if necessary.
So I have a table full of prioritized stuff, like a Netflix Queue. Multiple people can be editing this queue simultaneously. Each item in the queue as an order # from 1 to N, where N is the # of items in the queue.
For simplicity, I’m ensuring that the ordering is contiguous, so every time an operation is made on the table elements I go ahead and update the ordering. This is slow, yeah, but it works for now (if anyone has a better idea, I’m all ears). In other words, if you have 100 items, and you delete the 50th item, I just go regrab the table, sort, and then renumber.
The potential problem I’m running into is that multiple people might be monkeying with the table order, which means all kinds of fun and fucked up race conditions. While I’m reorganizing the table, someone else can log in and delete one of the things I’m working on.
What is the standard way to handle this type of thing in DB land? I could lock a range of rows or an entire table. Locking a range of rows has a problem because it can still introduce a hiccup if someone is merely adding an item to the top of the queue, so when they try to reorder things automatically it’ll stall waiting for the lock to release.
I’m guessing there might be a nifty way to express this in SQL and throw it in as a transaction?
I’m sure this is a common problem, but just googling “SQL table locking” didn’t show up much useful information.
I’m using SQLite as the backend for now but will probably migrate to something else* eventually.
- Which is not up for religious debate yet