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. > 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. > 3) Sharing connection in non shared cache mode across threads > is same as each thread having independent connection in > shared cache mode. > > 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. > 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. > > 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. > > > 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. > 2) With shared cache mode, multiple connections across > processes is as good a one connection and the all above rules > apply as they are. > > 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
