On 2016/04/23 10:20 AM, Michele Pradella wrote: > I have an In-Memory DB that is written and read from connections of the > same process. All good with shared cache, but I found that TableLock > occur more often on In-Memory than on disk DB, probably because in > memory we can't use WAL. > > Anyway I found the PRAGMA read_uncommitted that from documentation seams > a way to read without the problem of table lock. The question is about > this sentence "This can lead to inconsistent query results if another > database connection modifies a table while it is being read". > "inconsistent" means just "out of date"? or there can be some other type > of inconsistent data?
It means that you can read a record set, using such a shared cache connection, while a sibling connection (with whom you are sharing) is altering the data, resulting in the possibility that the record set will be inconsistent with both the pre-change and the post-change DB states. To draw a picture, imagine the following scenario: Create connections C1 and C2 which shares the cache and at least C2 uses pragma read_uncomitted. The following table "t" exists so that: ID | Val ---|---- 1 | 10 2 | 10 3 | 10 Connection C1 starts updating the DB with: UPDATE t SET Val = 20; At close after that same moment, C2 starts reading (uncommitted, i.e. non-serialized) the DB with: SELECT * FROM t; But reading is faster than writing, so the result set might look like this perhaps: ID | Val ---|---- 1 | 20 2 | 20 3 | 10 which is not consistent with either the DB state before C1 writes, nor after C1 committed. So no, "inconsistent" doesn't "just" mean outdated, it truly means non-consistent. This may or may not be a problem to your scenario. Perhaps the timeout setting is of more value to you? I do not have experience of in-memory DBs that gets used to the point where table locks become intrusive - but perhaps someone else here have solved the problem and can shed some light. Cheers, Ryan
