http://sqlite.org/lockingv3.html http://sqlite.org/sharedcache.html http://sqlite.org/34to35.html (section 5.0) I don't see a need to document this as its already done by the above. I think you've missed the finer points. See my comments embedded below: HTH, Ken Shailesh Birari <[EMAIL PROTECTED]> wrote: any clarifications on the below statements?
-Shailesh > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Shailesh Birari > Sent: Wednesday, April 16, 2008 11:30 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Transaction across threads > > Thank you Ken, Hipp and Shawn. > Below I am trying to summarize sqlite in the three dimensions > of shared cache, transactions and threads. Please let me know > which of the following are correct. Ideally I would like to > put this list on the sqlite website for others. > > 1) With shared cache mode, multiple connections is same as a > single connection. So all facts to single connection in > following points apply to multiple connections with shared cache mode. Not really, Shared cache mode does not have anything to do with a single connection. Actually there would be no point in having a shared cache. > 2) With non shared cache mode, multiple connections are independent. > They are always multiple connections contending with each > other whether across threads or across processes. True. > 3) Sharing connection in non shared cache mode across threads > is same as each thread having independent connection in > shared cache mode. Not exactly correct. Sharing a connection is not the same as a shared connection. A shared connection across threads is only 1 connection, The application must mutex the connection to prevent concurrent access. So only one thread at a time may use the connection. The shared cache allows multiple threads to "share" memory resources. The do not share the connection. Sqlite handles internally mutexing access to the database and critical structures. Only one thread may have a transaction active. So only one may modify the cache. But many may read the cache but you may not read and write concurrently. > > Transaction > ----------- > Following points are when connection is shared across threads > or multiple connections are opened with shared cache mode enabled. > 1) If a connection in one thread does a BEGIN TRANSACTION and > another thread does a insert (using shared connection or > different connection with shared cache mode) then this insert > is strictly a part of the transaction. there is no way an > application can tell that this insert is not a part of the > transaction started by the first thread. So if the > application does not want this insert to be a part of the > transaction, it is upto the application to not do a insert if > a transaction is in progress. Shared cache mode is not the same as a shared connection. Basically the above is true, but only for a shared connection not shared cache! > 2) On the same lines, BEGIN TRANSACTION on the thread > followed by BEGIN TRANSACTION on another thread is as good as > nested transaction and will error. Similarly BEGIN > TRANSACTION on one thread can be committed by COMMIT > transaction on another thread. > Only one transaction may be active at a time. A second threads begin transactoin will fail with SQLITE_BUSY. If the connection is shared then the second thread may commit. > Following points apply when there are multiple independent > connections to the database which is essentially in > non-shared cache mode: > 1) one can begin multiple transaction across connections, but > they have to be "read" transactions. If it becomes a write > transaction, only one write transaction can be active. So > multiple "select" statements can be active but only one > "insert" statement will be active at any given point of time. > True. > > Processes Vs thread: > ==================== > 1) There is no way that one can share a connection across > processes using a non-shared cache mode. So each process will > have its own connection. True. > 2) With shared cache mode, multiple connections across > processes is as good a one connection and the all above rules > apply as they are. Not sure what the point is? Shared cache has nothing to do with sharing connections. It is a cache, not a connection. > Please let me know what all statements are correct. If they > are not correct try to rewrite them so that we can add them > to the wiki for version '3.5.?' > > Regards > Shailesh > > > > -----Original Message----- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On Behalf Of D. > Richard Hipp > > Sent: Tuesday, April 15, 2008 9:24 PM > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] Transaction across threads > > > > > > On Apr 15, 2008, at 11:31 AM, Shawn Wilsher wrote: > > >> 1) If shared, then the second threads insert is part of the > > >> transaction and should succeed. > > >> 2) No. > > >> 3) If the connection is shared between threads, there can > > only be 1 > > >> txn at a time. The second threads attempt to begin a txn > > will result > > >> in an error that indicates a txn is already active. > > > To be clear, when using a shared cache and more than one sqlite3 > > > connection object, only one transaction will exist at a > > time, correct? > > > > Correct. > > > > > > > > However, if it is not using the shared cache, you can have a > > > transaction opened up for each thread? > > > > > > > Well, sort of. Certainly true if each connection has a different > > database open. But there can only be one write transaction > at a time > > to a single database. If you have multiple connections to the same > > database file, one can have a write transaction open and > one or more > > others can have a read transaction open, but you cannot have two or > > more write transactions active at once and all of the read > > transactions will need to close prior to the write transaction > > committing (otherwise the writer gets an > > SQLITE_BUSY.) > > > > D. Richard Hipp > > [EMAIL PROTECTED] > > > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
