Re: [sqlite] Multiple Writers and Database is Locked Problems
The database is locked error may occur on a select as well, in case a writer has spilt changes to disk. This is mentioned in http://sqlite.org/lockingv3.html. It would be SQLITE_BUSY and not SQLITE_LOCKED, as is the subject in the document you refer to. Is that possible? Then you'd need busy handling on the queries as well. (unless you choose to move everyting witin an exclusive transation). In my understanding you can get away without any programmatical busy handling, if you just set a timeout on the connection (sqlite3_busy_timeout). The only concern is deadlocks, which can occur if two processes are holding a shared lock (due to an unfinished read) adn both want to start a transaction. That is why you better finish all read-cursors (sqlite3_reset) before starting updates. Edzard Hi. Thanks for the suggestions. At the moment, im very new to using SQLite, so I haven't done anything advanced with its usage in my program. I pretty much read through the 5 minute intro using C/C++, looked at a few of the API docs, and then proceeded to use it from that information. So I've just modified the 5-minute intro to my needs, and used some of the other API's, but nothing too advanced. However, when searching for the problem I was receiving, I saw a trac/commit/wiki and page regarding the places that would cause locking issues. As far as I understand, I should be able to do a select on a table that is currently being updated without causing any locking issues right? As it currently stands, I did modify my program to deal with the SQLITE_BUSY returns, and I see now that im getting database is locked problems when doing a select now. Without debugging the issue too deeply, im guessing that the Database is locked problem is now being caused by a select while another program is updating the table. However, this shouldn't be an issue anymore according to the page : http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked If im wrong, or misunderstood or misread something, then please let me know. Also if you have further suggestions as to getting around this problem, I am happy to try anything. Thanks for any further help. Regards /Cole -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Edzard Pasma Sent: 19 July 2009 11:50 AM To: kennethinbox-sql...@yahoo.com; General Discussion of SQLite Database Cc: sqlite-users@sqlite.org Subject: Re: [sqlite] Multiple Writers and Database is Locked Problems I'd also make sure that all read-cursors are finished before starting a transaction. Or, take the exclusive lock already before the queries (possibly that is what was meant) --- kennethinbox-sql...@yahoo.com wrote: From: Ken kennethinbox-sql...@yahoo.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] Multiple Writers and Database is Locked Problems Date: Sat, 18 Jul 2009 12:29:33 -0700 (PDT) I like to start each of my transactions with a Begin Immediate that way the database file is locked at that point. And its relatively simple to test for the DB locked at that stage and handle waiting or returning an error. HTH --- On Fri, 7/17/09, Cole c...@opteqint.net wrote: From: Cole c...@opteqint.net Subject: [sqlite] Multiple Writers and Database is Locked Problems To: sqlite-users@sqlite.org Date: Friday, July 17, 2009, 6:38 AM Hi. Im hoping someone might be able to help me with the problems im having, or suggest a better method of doing what im trying to achieve. Currently im using sqlite3 3.6.10. I don't mind updating or downgrading it if needed. I have a program that I run multiple instances of. When they start, they parse the config file, and open a connection to each database that is listed. Each database only has a single table in it. They then receive requests, do a select on the database, parse the returned data, modify the data, then update the data to the database. However, I am running into the database is locked error when trying to update the database. I fully understand that 2 or more instances might be trying to update the same table, but is this the only reason this might happen now? Or are there also other scenarios where this might happen? Ive searched the mailing list and I see that you mention using random seed and time to handle the SQLITE_BUSY return value, and then trying to perform the update again. I was perhaps wondering if there are any other suggestions for dealing with this scenario where there might be multiple writers to the same database at the same time? Regards /Cole ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple Writers and Database is Locked Problems
I'd also make sure that all read-cursors are finished before starting a transaction. Or, take the exclusive lock already before the queries (possibly that is what was meant) --- kennethinbox-sql...@yahoo.com wrote: From: Ken kennethinbox-sql...@yahoo.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] Multiple Writers and Database is Locked Problems Date: Sat, 18 Jul 2009 12:29:33 -0700 (PDT) I like to start each of my transactions with a Begin Immediate that way the database file is locked at that point. And its relatively simple to test for the DB locked at that stage and handle waiting or returning an error. HTH --- On Fri, 7/17/09, Cole c...@opteqint.net wrote: From: Cole c...@opteqint.net Subject: [sqlite] Multiple Writers and Database is Locked Problems To: sqlite-users@sqlite.org Date: Friday, July 17, 2009, 6:38 AM Hi. Im hoping someone might be able to help me with the problems im having, or suggest a better method of doing what im trying to achieve. Currently im using sqlite3 3.6.10. I don't mind updating or downgrading it if needed. I have a program that I run multiple instances of. When they start, they parse the config file, and open a connection to each database that is listed. Each database only has a single table in it. They then receive requests, do a select on the database, parse the returned data, modify the data, then update the data to the database. However, I am running into the database is locked error when trying to update the database. I fully understand that 2 or more instances might be trying to update the same table, but is this the only reason this might happen now? Or are there also other scenarios where this might happen? Ive searched the mailing list and I see that you mention using random seed and time to handle the SQLITE_BUSY return value, and then trying to perform the update again. I was perhaps wondering if there are any other suggestions for dealing with this scenario where there might be multiple writers to the same database at the same time? Regards /Cole ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple Writers and Database is Locked Problems
I like to start each of my transactions with a Begin Immediate that way the database file is locked at that point. And its relatively simple to test for the DB locked at that stage and handle waiting or returning an error. HTH --- On Fri, 7/17/09, Cole c...@opteqint.net wrote: From: Cole c...@opteqint.net Subject: [sqlite] Multiple Writers and Database is Locked Problems To: sqlite-users@sqlite.org Date: Friday, July 17, 2009, 6:38 AM Hi. Im hoping someone might be able to help me with the problems im having, or suggest a better method of doing what im trying to achieve. Currently im using sqlite3 3.6.10. I don't mind updating or downgrading it if needed. I have a program that I run multiple instances of. When they start, they parse the config file, and open a connection to each database that is listed. Each database only has a single table in it. They then receive requests, do a select on the database, parse the returned data, modify the data, then update the data to the database. However, I am running into the database is locked error when trying to update the database. I fully understand that 2 or more instances might be trying to update the same table, but is this the only reason this might happen now? Or are there also other scenarios where this might happen? Ive searched the mailing list and I see that you mention using random seed and time to handle the SQLITE_BUSY return value, and then trying to perform the update again. I was perhaps wondering if there are any other suggestions for dealing with this scenario where there might be multiple writers to the same database at the same time? Regards /Cole ___ 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] Multiple Writers and Database is Locked Problems
Hi. Im hoping someone might be able to help me with the problems im having, or suggest a better method of doing what im trying to achieve. Currently im using sqlite3 3.6.10. I don't mind updating or downgrading it if needed. I have a program that I run multiple instances of. When they start, they parse the config file, and open a connection to each database that is listed. Each database only has a single table in it. They then receive requests, do a select on the database, parse the returned data, modify the data, then update the data to the database. However, I am running into the database is locked error when trying to update the database. I fully understand that 2 or more instances might be trying to update the same table, but is this the only reason this might happen now? Or are there also other scenarios where this might happen? Ive searched the mailing list and I see that you mention using random seed and time to handle the SQLITE_BUSY return value, and then trying to perform the update again. I was perhaps wondering if there are any other suggestions for dealing with this scenario where there might be multiple writers to the same database at the same time? Regards /Cole ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users