ok, thank you Clemens and Kevin. another question please. In terms of pure performance, is that there is a difference between:
- SELECT - SELECT (so two read transactions) AND - BEGIN - SELECT - SELECT - END olivier > Simon Slavin <mailto:slavins at bigfraud.org> > 10 juin 2015 14:39 > > In addition to Clemens' excellent answer, I tell you that most of > these questions will be answered if you just trust that SQLite will do > The Right Thing. > > So a SELECT locks the database so that nothing can change it, but an > UPDATE locks the database so that nothing can change it and nothing > can get an inconsistent view of it. Another thread doing a SELECT > while you are doing an UPDATE will retrieve all its data from before > anything changes, or retrieve all its data from after the UPDATE is > finished. Never with some updated and some not. > > If you are making a change which depends on the answer to a SELECT > operation and it's important that nothing changes the database between > the two of them, you just put both operations in the same transaction: > > BEGIN; > SELECT ...; > UPDATE ...; > END; > > BEGIN DEFERRED (which is BEGIN's default) does not lock the database > until the first command which needs it locked. This makes sure that > the databases stays unlocked for the longest time possible, which will > suit other threads trying to access it. BEGIN IMMEDIATE puts a > write-lock on the database immediately. BEGIN EXCLUSIVE puts a > read-lock and write-lock on the database immediately. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > Olivier Vidal <mailto:paxdo at mac.com> > 10 juin 2015 10:48 > Hello, > > some questions please about transactions/locks in a multithreaded > environment, multiple applications, > WAL mode, NO shared cache, NO read_uncommitted pragma: > > ------------------------------------ > > Some examples: > > 1- > UPDATE > Internally, is the same thing that: BEGIN DEFERRED - UPDATE -COMMIT? > > 2- > SELECT > UPDATE > In this example, the set of these commands is serialized? The two > commands act on the same snapshot? So, two commands have the same data > (same snapshot), but those data may be modified by another > thread/process between SELECT and UPDATE, and UPDATE will be unaware > of this previous change? > > 3- > BEGIN (or BEGIN DIFFERED) > SELECT > UPDATE > COMMIT > Same as 2? > > 5- > BEGIN IMMEDIATE > SELECT > UPDATE > COMMIT > I'm sure that the data can not be modified (between BEGIN IMMEDIATE > and COMMIT) by another thread/proces? > > Thank you! > > olivier > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
