[sqlite] read uncommitted data consistency
On 21 Feb 2015, at 9:01pm, Yuriy Stelmakh wrote: > When using read uncommitted pragma, is it possible to get a row of data > where some columns reflect state of that row at one point, while others at > another? For example when you are reading in one thread while writing in > another. No. They'll all be either one thing or the other. In other words, even with the PRAGMA set, SQLite is still transaction-safe. It uses a lock on the entire database to prevent the problem you described. Simon.
[sqlite] read uncommitted data consistency
When using read uncommitted pragma, is it possible to get a row of data where some columns reflect state of that row at one point, while others at another? For example when you are reading in one thread while writing in another. Thank you!
[sqlite] read uncommitted data consistency
On Feb 21, 2015, at 5:36 PM, Simon Slavin wrote: > > On 21 Feb 2015, at 9:01pm, Yuriy Stelmakh wrote: > >> When using read uncommitted pragma, is it possible to get a row of data >> where some columns reflect state of that row at one point, while others at >> another? For example when you are reading in one thread while writing in >> another. > > No. They'll all be either one thing or the other. In other words, even with > the PRAGMA set, SQLite is still transaction-safe. It uses a lock on the > entire database to prevent the problem you described. > No, not with columns, but it is possible with rows, depending on how the two treads are interacting. SQLite rows are essentially self-contained, so any time a row is updated it is updated as a complete unit. This is not true of rows within a table, however. Understand that READ UNCOMMITTED only applies to connections within the same process that are also using a shared cache (sqlite3_enable_shared_cache()). If, in the situation you describe, the threads are using different connections that are NOT using a shared cache, then the writer thread will ?see? any updates it has already made within the uncommitted transaction, while another reader thread (using a different connection) will not? it will see the data as it existed when the transaction was started. This has nothing to do with READ UNCOMMITTED, however, that?s how it always works when not using a shared cache (or when not using the same connection for reads and writes). Basically the connection context that created the transaction will see the actions it has performed, while all other connections will not see the changes until the transaction is committed (which, of course, is the whole point of transactions). -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson
Re: [sqlite] READ UNCOMMITTED isolation?
On Mon, 30 Jan 2006, Gerhard Häring wrote: >[EMAIL PROTECTED] wrote: >> "Dan Petitt" <[EMAIL PROTECTED]> wrote: >> >>>I think (looking at the source) that it's a pragma, but I don't know when >>>you set it, once when DB is opened, on each write or on each read. >>> >>>You are the third to ask (including me), maybe Richard or someone else can >>>through some light on it for us. >> >> READ UNCOMMITTED only works if you enable the shared cache >> feature and have two or more database connections sharing the >> same page and schema cache (meaning that they are both running >> in the same thread). Documentation is forthcoming. > >I don't understand the point of this feature. In fact I don't know why I >would want more than one database connection per thread at all. When >would I need that? I think the point is to use a single server thread to service multiple database connections from other threads, via a queue or some such. Have a look at server.c in the latest source for a sample implementation. > >-- Gerhard > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] READ UNCOMMITTED isolation?
[EMAIL PROTECTED] wrote: "Dan Petitt" <[EMAIL PROTECTED]> wrote: I think (looking at the source) that it's a pragma, but I don't know when you set it, once when DB is opened, on each write or on each read. You are the third to ask (including me), maybe Richard or someone else can through some light on it for us. READ UNCOMMITTED only works if you enable the shared cache feature and have two or more database connections sharing the same page and schema cache (meaning that they are both running in the same thread). Documentation is forthcoming. I don't understand the point of this feature. In fact I don't know why I would want more than one database connection per thread at all. When would I need that? -- Gerhard
Re: [sqlite] READ UNCOMMITTED isolation?
"Dan Petitt" <[EMAIL PROTECTED]> wrote: > I think (looking at the source) that it's a pragma, but I don't know when > you set it, once when DB is opened, on each write or on each read. > > You are the third to ask (including me), maybe Richard or someone else can > through some light on it for us. > READ UNCOMMITTED only works if you enable the shared cache feature and have two or more database connections sharing the same page and schema cache (meaning that they are both running in the same thread). Documentation is forthcoming. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] READ UNCOMMITTED isolation?
I think (looking at the source) that it's a pragma, but I don't know when you set it, once when DB is opened, on each write or on each read. You are the third to ask (including me), maybe Richard or someone else can through some light on it for us. Dan Petitt DigiGuide TV Guide First Floor Office Suite 17 The Strand Exmouth Devon. EX8 1AF Tel / Fax: 01395 272555 -Original Message- From: Jack Pan [mailto:[EMAIL PROTECTED] On Behalf Of Cecilia Chen Sent: 29 January 2006 15:33 To: sqlite-users@sqlite.org Subject: [sqlite] READ UNCOMMITTED isolation? Does anyone know how to use the new READ UNCOMMITTED isolation? It would be great to have this isolation level when one thread reads and another writes. My program doesn't worry too much about read consistency. Thanks, Jack Pan
[sqlite] READ UNCOMMITTED isolation?
Does anyone know how to use the new READ UNCOMMITTED isolation? It would be great to have this isolation level when one thread reads and another writes. My program doesn't worry too much about read consistency. Thanks, Jack Pan
[sqlite] Read Uncommitted
Hi I searched the mail archive and documentation but could not find any 'answer' to the new "READ UNCOMMITTED" ability. Is it a pragma? If it is; is it set when database is opened, or each time you start a write transaction, or maybe something else? Any assistance would be appreciated, thanks Dan
