Hello Matt, Matt Froncek wrote:
Are all transactions database locks (not table locks)?
Sqlite doesn't support table or row level locking. Locking is implemented via file locks. Hence if you separate your table space into different databases and use the ATTACH keyword, you can effectively get finer grain locking ( but I guess you know that ).
If I prepare a select statement and start stepping on Table A on process one and pause before finializing and do an update on Table B with process two or even the same process I will get a database is locked error? Do I understand that this is how SQLite works or am I doing something wrong?
You're correct I believe. Sqlite does not support concurrent writers on the same database. Also, any readers will get an SQLITE_SCHEMA error, that is, "the database schema has changed". Also we're ( us new to Sqlite ) use to a "DECLARE...INSENSITIVE...CURSOR" or resultant set type paradigm where the results of our queries are *separated* from the actual table data. Sqlite doesn't copy the resultant set if you use the step() API. Seems one has to get the data needed and close the transaction as soon as possible.
Does everyone make databases with one table each or what?
Yup, or do your own database marshalling logic. My wrapper is ugly, I "TRANSACTION EXCLUSIVE" almost everything. I tried many things, but that's what finally worked for me. When you have separate threads and processes doing mixed reads and writes in the same transaction even "TRANSACTION IMMEDIATE" can give leave you with unexpected results. Regards, Kervin
