[sqlite] In-Memory database PRAGMA read_uncommitted
Hello ! I posted this some time ago, it's a program to test and tune sqlite with concurrency: https://gist.github.com/mingodad/79225c88f8dce0f174f5 Maybe it can be util to you and if you ! Cheers !
[sqlite] In-Memory database PRAGMA read_uncommitted
Il 2016-04-23 11:05 R Smith ha scritto: > 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 > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Ok understood thank you. I'll have a look to the timeout settings just to check it, but in my environment this kind of "inconsistency" it's not a problem. Anyway I think that shared cache in Memory DB give you the ability to make sqlite realy very fast in SELECT statement, very good feature.
[sqlite] In-Memory database PRAGMA read_uncommitted
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
[sqlite] In-Memory database PRAGMA read_uncommitted
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?
[sqlite] In-Memory database PRAGMA read_uncommitted
On 2016-04-23 1: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? Usually "inconsistent" means you see a result that is partially an old value and partially a new one, such as if you open a photograph and find that the top half was overwritten with a different photo; you get the incomplete results of a change in progress, for example, say if one is recording a bank funds transfer with one record showing an account debit and another showing an account credit, readers may only see one of those and then they see a database where the numbers don't add up properly, as if it were corrupt; that is what "inconsistent" means. -- Darren Duncan
