PureBasic Forums - English

I have a question about using transactions in SQLite.

I wrap writing functions like Update, Insert and Delete between Begin Immediate and Commit statements. This locks the database so only one user can work with it while writing. I have never used Begin / Commit for ordinary queries. My question is: Should the Begin / Commit structure also be used for queries and read-only actions?

This would presumably restrict access to one user at a time. At present I only use Begin...Commit when updating the database, not for queries. If someone is writing others wanting to use the database file are put into a queue and wait until the writer has finished. Is there a timeout?

Yes, you need BEGIN and COMMIT for a transaction.
And SQLite locks always the file when I write happens.
Else an other 'user' can damage the database.

In general:
If you need a real multiuser database environment ... don't use SQLite.

It is not designed for real concurrent multiuser access.
If you need this, then you need soemthing like PostgreSQL.

For queries you need no transactions.
Transactions are used to speed up INSERTs and guarantee that dependent INSERTs/UPDATEs are not disturbed.

Reading (SELECT) from a database never needs a transaction.

Database Transactions also form the basis of their UNDO or ROLLBACK system.
There are 3 types of Transactions you can try; DEFERRED, IMMEDIATE, EXCLUSIVE.
Given SQLite applies Transactions to all queries(R/W), it is my opinion to wrap BEGIN .. COMMIT around everything.
Implicit behavior can be changed or forgotten, while your explicit code is under your control.