Menu
Yesterday I was working with some sqlalchemy stuff that needed a "select ... for update" concept to avoid a race condition. Adding .with_lockmode('update') to the query works a treat on InnoDB and Postgres, but for sqlite I end up having to sneak in a
if session.bind.name == 'sqlite':
session.execute('begin immediate transaction')
before doing the select.
This seems to work for now, but it feels like cheating. Is there a better way to do this?
446
Code language:SQL (Structured Query Language)(sql) SQLite transaction example We will create two new tables: accountsand account_changesfor the demonstration. The accountstable stores data about the account numbers and their balances. The account_changestable stores the changes of the accounts.
Use SQLAlchemy Core with SQLite in Python Step 1: Install SQLAlchemy. To use SQLAlchemy in Python, first, we need to install it. Please type the following command... Step 2: Import SQLAlchemy. Now import in your project using the Python import statement. As you can see that we imported... Step 3: ...
Be sure to read the de facto SO question on improving sqlite performance. What the author of the blog might have been trying to say, is that if you plan to do an INSERT, then a SELECT, then another INSERT, then it would increase performance to manually wrap these statements in a single transaction.
The errors that can cause an automatic rollback include: For all of these errors, SQLite attempts to undo just the one statement it was working on and leave changes from prior statements within the same transaction intact and continue with the transaction.
SELECT ... FOR UPDATE OF ... is not supported. This is understandable considering the mechanics of SQLite in that row locking is redundant as the entire database is locked when updating any bit of it. However, it would be good if a future version of SQLite supports it for SQL interchageability reasons if nothing else. The only functionality required is to ensure a "RESERVED" lock is placed on the database if not already there.
excerpt from https://www2.sqlite.org/cvstrac/wiki?p=UnsupportedSql
[EDIT] also see https://sqlite.org/isolation.html thanks @michauwilliam.
i think you have to synchronize the access to the whole database. normal synchronization mechanism should also apply here file lock, process synchronization etc
50
I think a SELECT FOR UPDATE is relevant for SQLite. There is no way to lock the database BEFORE I start to write. By then it's too late. Here is the scenario:
I have two servers and one database queue table. Each server is looking for work and when it picks up a job, it updates the queue table with an "I got it” so the other server doesn’t also pick it up the same work. I need to leave the record in the queue in case of recovery.
Server 1 reads the first unclaimed item and has it in memory. Server 2 reads the same record and now has it in memory too. Server 1 then updates the record, locking the database, updates, then unlocks. Server 2 then locks the database, updates, and unlocks. The result is both servers now work on the same job. The table shows Server 2 has it and the Server 1 update is lost.
I solved this by creating a lock database table. Server 1 begins a transaction, writes to the lock table which locks the database for writing. Server 2 now tries to begin a transaction and write to the lock table, but is prevented. Server 1 now reads the first queue record and then updates it with the “I got it” code. Then deletes the record it just wrote to the lock table, commits and releases the lock. Now server 2 is able to begin its transaction, write to the lock table, read the 2nd queue record, update it with its “I got it” code, delete it’s lock record, commits and the database is available for the next server looking for work.
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
