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
