Re: [sqlite] Transaction across threads
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 > > > > -Origina
Re: [sqlite] Transaction across threads
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 >
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
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
Re: [sqlite] Transaction across threads
> 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? However, if it is not using the shared cache, you can have a transaction opened up for each thread? Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction across threads
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. Shailesh Birari <[EMAIL PROTECTED]> wrote: Does anyone have any answers for the queries below? -Shailesh > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Shailesh Birari > Sent: Monday, April 14, 2008 9:55 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Transaction across threads > > Hello, > I had a doubt of sqlite transactions across threads. > I have a multithreaded application which shares the same > sqlite connection across threads. I had a few doubts > 1) If I do a begin transaction and insert on one thread. Then > do a insert on the second thread and finally a commit on the > first thread will the insert from the second thread succeed > or will it fail saying SQLITE_BUSY? if it succeeds, will the > insert on the second thread be a part of the transaction on > the first thread? > 2) When I do a Begin transaction will I get a transactionId? > 3) If one thread is in the middle of a transaction and > another thread does a begin transaction, will it be able to > start its own transaction or will it fail with SQLITE_BUSY.? > > Kindly let me know, > > Regards, > Shailesh. > ___ > 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
Re: [sqlite] Transaction across threads
Does anyone have any answers for the queries below? -Shailesh > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Shailesh Birari > Sent: Monday, April 14, 2008 9:55 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Transaction across threads > > Hello, > I had a doubt of sqlite transactions across threads. > I have a multithreaded application which shares the same > sqlite connection across threads. I had a few doubts > 1) If I do a begin transaction and insert on one thread. Then > do a insert on the second thread and finally a commit on the > first thread will the insert from the second thread succeed > or will it fail saying SQLITE_BUSY? if it succeeds, will the > insert on the second thread be a part of the transaction on > the first thread? > 2) When I do a Begin transaction will I get a transactionId? > 3) If one thread is in the middle of a transaction and > another thread does a begin transaction, will it be able to > start its own transaction or will it fail with SQLITE_BUSY.? > > Kindly let me know, > > Regards, > Shailesh. > ___ > 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] Transaction across threads
Hello, I had a doubt of sqlite transactions across threads. I have a multithreaded application which shares the same sqlite connection across threads. I had a few doubts 1) If I do a begin transaction and insert on one thread. Then do a insert on the second thread and finally a commit on the first thread will the insert from the second thread succeed or will it fail saying SQLITE_BUSY? if it succeeds, will the insert on the second thread be a part of the transaction on the first thread? 2) When I do a Begin transaction will I get a transactionId? 3) If one thread is in the middle of a transaction and another thread does a begin transaction, will it be able to start its own transaction or will it fail with SQLITE_BUSY.? Kindly let me know, Regards, Shailesh. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
