[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
Hello Kathleen, I'd set it to 5 minutes and see what happens. The timeout is worst case. It's not going to slow normal processing. I use infinite here. Is there ever a situation where you don't want to wait for it to finish? If you have hard real time requirements. I'd probably re-design it to be client server and have only one process actually talk to the DB and act like a server to the other processes. If you're going down that route though, a big database might be a better solution. C Thursday, July 2, 2015, 6:22:52 PM, you wrote: KA> Thanks so much for the help. I set the timeout to 5 seconds (at each KA> instance of a db open connection) and was still seeing the database locked KA> errors with some frequency (haven't quantified it yet), so I will try KA> extending it to 30 seconds, as you suggest, and hopefully that makes a KA> difference. KA> Failing that, PostgreSQL looks promising (thanks so much for the KA> suggestion!), so I will start trying to implement it. KA> Thanks Again! KA> On Thu, Jul 2, 2015 at 6:06 PM, Simon Slavin wrote: >> >> On 2 Jul 2015, at 3:59pm, Kathleen Alexander wrote: >> >> > I don't explicitly set sqlite3_busy_timeout(sqlite3*, int ms) so I will >> try >> > adding that after the database connection is opened to see if it limits >> > those errors. >> >> This will have an extreme effect. The default timeout for SQLite is not >> to use a timeout at all: any clash in access will result in immediate >> failure. Set your timeout to 30 seconds and see what happens. >> >> Worth noting that the timeout needs to be set by each application for each >> connection. If you set it for the first application which opens the >> database it won't automatically be applied by other connections. >> >> Simon. >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> KA> ___ KA> sqlite-users mailing list KA> sqlite-users at mailinglists.sqlite.org KA> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Tegmailto:Teg at djii.com
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
Hi, I'm not an expert on bash or scripts etc. However, if I were doing something similar in C++ then I'd consider having a dedicated thread to manage a queue of dB operations and committing them all from this single thread. You can then take control of a whole host of things - maintaining the write order if important, doing the writes either synchronously or asynchronously (and notifying the caller when complete etc.) You might also have to consider amalgamating many requests into a single transaction (although that would complicate the handling of errors) Graham On 02/07/2015 15:09, Kathleen Alexander wrote: > Hi, > > I apologize if this is an incorrect forum for this question, but I am > pretty new to SQLite and have been unable to resolve this issue through > other searches. Feel free to direct me to a more appropriate forum. > > Essentially, I have written an application in C++ that interfaces (reads > and writes) with a SQLite database, and I am getting lots of 'database is > locked' errors. Right now, I am trying to establish whether those errors > are due to my improper use of SQLite itself, or if the real problem is that > SQLite is not a good fit for my application. > > My application runs on Linux (ubuntu 13.10), and is driven by a bash script > that spawns many (~60 on a 64 core workstation) instances of a serial, C++ > program, each of which opens its own connection to the database and > performs reads and writes. > > *An example SELECT query from my program looks like:* > //open db connection > sqlite3 *db; > char *zErrMsg = 0; > SQLITE3 sql(dbase.c_str()); > > statement = "SELECT * from configs_table WHERE id='31'"; > sql.exe(statement.c_str()); > if( sql.vcol_head.size() > 0 ){ > //do things with sql.vdata[] > }//end query returned results > > *An example of a write statement looks like:* > statement = "UPDATE configs_table SET searched='2' WHERE id='31'"; > sql.exe(statement.c_str()); > > About 97% of the time, the select statement works fine, but in the other 3% > of cases, I see a 'database is locked' error in the log file of my program. > About 50% of the time, the write statement returns 'database is locked'. > > Additionally, if this application is running and I try to query the > database from the terminal, I almost always get a 'database is locked' > error. > > Thus, I am wondering if I am doing something wrong in my implementation of > the C++ --> SQLite interaction, or if the real problem is that this > application is not well suited to use with SQLite (I went through the > checklist before implementing it and thought my application passed the > suitability requirements). > > Lastly: > A. if it seems like this is an implementation issue, rather than a > capability issue, if I were to scale up my method to spawn say 500-1000 > processes at a time (on a supercomputing cluster), would there be any > concern about SQLite scaling to that level? > B. If SQLite is not a good fit for my program, do you have any suggestions > of an alternative database engine that is free or might be free or > inexpensive for academic use? > > Thanks in advance, > Kathleen > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > - > No virus found in this message. > Checked by AVG - www.avg.com > Version: 2015.0.6037 / Virus Database: 4365/10144 - Release Date: 07/02/15 > >
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
On 2 Jul 2015, at 3:59pm, Kathleen Alexander wrote: > I don't explicitly set sqlite3_busy_timeout(sqlite3*, int ms) so I will try > adding that after the database connection is opened to see if it limits > those errors. This will have an extreme effect. The default timeout for SQLite is not to use a timeout at all: any clash in access will result in immediate failure. Set your timeout to 30 seconds and see what happens. Worth noting that the timeout needs to be set by each application for each connection. If you set it for the first application which opens the database it won't automatically be applied by other connections. Simon.
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
Thanks so much for the help. I set the timeout to 5 seconds (at each instance of a db open connection) and was still seeing the database locked errors with some frequency (haven't quantified it yet), so I will try extending it to 30 seconds, as you suggest, and hopefully that makes a difference. Failing that, PostgreSQL looks promising (thanks so much for the suggestion!), so I will start trying to implement it. Thanks Again! On Thu, Jul 2, 2015 at 6:06 PM, Simon Slavin wrote: > > On 2 Jul 2015, at 3:59pm, Kathleen Alexander wrote: > > > I don't explicitly set sqlite3_busy_timeout(sqlite3*, int ms) so I will > try > > adding that after the database connection is opened to see if it limits > > those errors. > > This will have an extreme effect. The default timeout for SQLite is not > to use a timeout at all: any clash in access will result in immediate > failure. Set your timeout to 30 seconds and see what happens. > > Worth noting that the timeout needs to be set by each application for each > connection. If you set it for the first application which opens the > database it won't automatically be applied by other connections. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
Hi, Kathleen, On Thu, Jul 2, 2015 at 5:34 PM, James K. Lowden wrote: > On Thu, 2 Jul 2015 10:09:12 -0400 > Kathleen Alexander wrote: > >> Essentially, I have written an application in C++ that interfaces >> (reads and writes) with a SQLite database, and I am getting lots of >> 'database is locked' errors. [...] >> >> My application runs on Linux (ubuntu 13.10), and is driven by a bash >> script that spawns many (~60 on a 64 core workstation) instances of a >> serial, C++ program, each of which opens its own connection to the >> database and performs reads and writes. > > It may be that SQLite is not a good fit for your application. > Concurrent update is SQLite's Achilles heel. > > Each insert/update/delete in SQLite requires exclusive access. In WAL > mode, it requires exclusive access to the table; else it requires > exclusive access to the whole database. That means, by default, only > one process can update the database at a time. If you have 60 > processes, 59 will wait. > > Depending on your requirements, that may still be OK. If the updates > run quickly enough for your purposes, then increasing the timeout may > do the trick. Estimate the processing time and number of updates to > compute an overall throughput. If that's acceptable, problem solved. > > Most DBMSs, the heavy kind, devote extensive resources to support > concurrent update. Usually contention is managed at the page or row > level, and a whole section of the manual covers how the system > implements SQL's "isolation levels". Just exactly how many processes > can update the database at once is a function of almost everything: > DBMS configuration, table design, index definition, and isolation > level, not to mention hardware capacity and the OS I/O subsystem. Your > mileage will most certainly vary. > >> B. If SQLite is not a good fit for my program, do you have any >> suggestions of an alternative database engine that is free or might >> be free or inexpensive for academic use? > > *If* that's the case, your best option IMO is Postgres. If you want to > handle ~1000 concurrent updates, though, you will have to pay attention > to the details, and may have to arrange to minimize resource contention > in the DBMS. It all depends on the throughput and response-time > requirements. +1 for PostgreSQL. We use it in our application where there are many threads runs concurrently. and the DB needs updating almost continuosly. Also, it is free and available to every Linux distribution. Thank you. > > HTH. > > --jkl > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
On Thu, 2 Jul 2015 10:09:12 -0400 Kathleen Alexander wrote: > Essentially, I have written an application in C++ that interfaces > (reads and writes) with a SQLite database, and I am getting lots of > 'database is locked' errors. [...] > > My application runs on Linux (ubuntu 13.10), and is driven by a bash > script that spawns many (~60 on a 64 core workstation) instances of a > serial, C++ program, each of which opens its own connection to the > database and performs reads and writes. It may be that SQLite is not a good fit for your application. Concurrent update is SQLite's Achilles heel. Each insert/update/delete in SQLite requires exclusive access. In WAL mode, it requires exclusive access to the table; else it requires exclusive access to the whole database. That means, by default, only one process can update the database at a time. If you have 60 processes, 59 will wait. Depending on your requirements, that may still be OK. If the updates run quickly enough for your purposes, then increasing the timeout may do the trick. Estimate the processing time and number of updates to compute an overall throughput. If that's acceptable, problem solved. Most DBMSs, the heavy kind, devote extensive resources to support concurrent update. Usually contention is managed at the page or row level, and a whole section of the manual covers how the system implements SQL's "isolation levels". Just exactly how many processes can update the database at once is a function of almost everything: DBMS configuration, table design, index definition, and isolation level, not to mention hardware capacity and the OS I/O subsystem. Your mileage will most certainly vary. > B. If SQLite is not a good fit for my program, do you have any > suggestions of an alternative database engine that is free or might > be free or inexpensive for academic use? *If* that's the case, your best option IMO is Postgres. If you want to handle ~1000 concurrent updates, though, you will have to pay attention to the details, and may have to arrange to minimize resource contention in the DBMS. It all depends on the throughput and response-time requirements. HTH. --jkl
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
On 2 July 2015 at 15:09, Kathleen Alexander wrote: > Hi, > > I apologize if this is an incorrect forum for this question, but I am > pretty new to SQLite and have been unable to resolve this issue through > other searches. Feel free to direct me to a more appropriate forum. > > Essentially, I have written an application in C++ that interfaces (reads > and writes) with a SQLite database, and I am getting lots of 'database is > locked' errors. Right now, I am trying to establish whether those errors > are due to my improper use of SQLite itself, or if the real problem is that > SQLite is not a good fit for my application. > > My application runs on Linux (ubuntu 13.10), and is driven by a bash script > that spawns many (~60 on a 64 core workstation) instances of a serial, C++ > program, each of which opens its own connection to the database and > performs reads and writes. http://www.sqlite.org/whentouse.html#dbcklst item 2 may be relevant Regards, Simon
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
Hi Adam, Thank you very much for your response. I had not considered using copies of the database. In my method there are cases where writes to the database by one process may be relevant to another process, so I'm not sure that that would be a good option. I don't explicitly set sqlite3_busy_timeout(sqlite3*, int ms) so I will try adding that after the database connection is opened to see if it limits those errors. Thanks Again. On Thu, Jul 2, 2015 at 10:28 AM, Adam Devita wrote: > Good day, > > I'm sure others on the list will add better insight, but is your task > parallel enough that your nodes can work with a copy of the database > and submit changes the one the others copy from when 'done' their > calculation? > > Are you using https://www.sqlite.org/c3ref/busy_timeout.html ? > > regards, > Adam > > This may be beside the point in terms of optimization, but your query > looks rather character based on int like information. > > On Thu, Jul 2, 2015 at 10:09 AM, Kathleen Alexander > wrote: > > Hi, > > > > I apologize if this is an incorrect forum for this question, but I am > > pretty new to SQLite and have been unable to resolve this issue through > > other searches. Feel free to direct me to a more appropriate forum. > > > > Essentially, I have written an application in C++ that interfaces (reads > > and writes) with a SQLite database, and I am getting lots of 'database is > > locked' errors. Right now, I am trying to establish whether those errors > > are due to my improper use of SQLite itself, or if the real problem is > that > > SQLite is not a good fit for my application. > > > > My application runs on Linux (ubuntu 13.10), and is driven by a bash > script > > that spawns many (~60 on a 64 core workstation) instances of a serial, > C++ > > program, each of which opens its own connection to the database and > > performs reads and writes. > > > > *An example SELECT query from my program looks like:* > > //open db connection > > sqlite3 *db; > > char *zErrMsg = 0; > > SQLITE3 sql(dbase.c_str()); > > > > statement = "SELECT * from configs_table WHERE id='31'"; > > sql.exe(statement.c_str()); > > if( sql.vcol_head.size() > 0 ){ > >//do things with sql.vdata[] > > }//end query returned results > > > > *An example of a write statement looks like:* > > statement = "UPDATE configs_table SET searched='2' WHERE id='31'"; > > sql.exe(statement.c_str()); > > > > About 97% of the time, the select statement works fine, but in the other > 3% > > of cases, I see a 'database is locked' error in the log file of my > program. > > About 50% of the time, the write statement returns 'database is locked'. > > > > Additionally, if this application is running and I try to query the > > database from the terminal, I almost always get a 'database is locked' > > error. > > > > Thus, I am wondering if I am doing something wrong in my implementation > of > > the C++ --> SQLite interaction, or if the real problem is that this > > application is not well suited to use with SQLite (I went through the > > checklist before implementing it and thought my application passed the > > suitability requirements). > > > > Lastly: > > A. if it seems like this is an implementation issue, rather than a > > capability issue, if I were to scale up my method to spawn say 500-1000 > > processes at a time (on a supercomputing cluster), would there be any > > concern about SQLite scaling to that level? > > B. If SQLite is not a good fit for my program, do you have any > suggestions > > of an alternative database engine that is free or might be free or > > inexpensive for academic use? > > > > Thanks in advance, > > Kathleen > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > -- > VerifEye Technologies Inc. > 151 Whitehall Dr. Unit 2 > Markham, ON > L3R 9T1 > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
Good day, I'm sure others on the list will add better insight, but is your task parallel enough that your nodes can work with a copy of the database and submit changes the one the others copy from when 'done' their calculation? Are you using https://www.sqlite.org/c3ref/busy_timeout.html ? regards, Adam This may be beside the point in terms of optimization, but your query looks rather character based on int like information. On Thu, Jul 2, 2015 at 10:09 AM, Kathleen Alexander wrote: > Hi, > > I apologize if this is an incorrect forum for this question, but I am > pretty new to SQLite and have been unable to resolve this issue through > other searches. Feel free to direct me to a more appropriate forum. > > Essentially, I have written an application in C++ that interfaces (reads > and writes) with a SQLite database, and I am getting lots of 'database is > locked' errors. Right now, I am trying to establish whether those errors > are due to my improper use of SQLite itself, or if the real problem is that > SQLite is not a good fit for my application. > > My application runs on Linux (ubuntu 13.10), and is driven by a bash script > that spawns many (~60 on a 64 core workstation) instances of a serial, C++ > program, each of which opens its own connection to the database and > performs reads and writes. > > *An example SELECT query from my program looks like:* > //open db connection > sqlite3 *db; > char *zErrMsg = 0; > SQLITE3 sql(dbase.c_str()); > > statement = "SELECT * from configs_table WHERE id='31'"; > sql.exe(statement.c_str()); > if( sql.vcol_head.size() > 0 ){ >//do things with sql.vdata[] > }//end query returned results > > *An example of a write statement looks like:* > statement = "UPDATE configs_table SET searched='2' WHERE id='31'"; > sql.exe(statement.c_str()); > > About 97% of the time, the select statement works fine, but in the other 3% > of cases, I see a 'database is locked' error in the log file of my program. > About 50% of the time, the write statement returns 'database is locked'. > > Additionally, if this application is running and I try to query the > database from the terminal, I almost always get a 'database is locked' > error. > > Thus, I am wondering if I am doing something wrong in my implementation of > the C++ --> SQLite interaction, or if the real problem is that this > application is not well suited to use with SQLite (I went through the > checklist before implementing it and thought my application passed the > suitability requirements). > > Lastly: > A. if it seems like this is an implementation issue, rather than a > capability issue, if I were to scale up my method to spawn say 500-1000 > processes at a time (on a supercomputing cluster), would there be any > concern about SQLite scaling to that level? > B. If SQLite is not a good fit for my program, do you have any suggestions > of an alternative database engine that is free or might be free or > inexpensive for academic use? > > Thanks in advance, > Kathleen > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- -- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
Hi, I apologize if this is an incorrect forum for this question, but I am pretty new to SQLite and have been unable to resolve this issue through other searches. Feel free to direct me to a more appropriate forum. Essentially, I have written an application in C++ that interfaces (reads and writes) with a SQLite database, and I am getting lots of 'database is locked' errors. Right now, I am trying to establish whether those errors are due to my improper use of SQLite itself, or if the real problem is that SQLite is not a good fit for my application. My application runs on Linux (ubuntu 13.10), and is driven by a bash script that spawns many (~60 on a 64 core workstation) instances of a serial, C++ program, each of which opens its own connection to the database and performs reads and writes. *An example SELECT query from my program looks like:* //open db connection sqlite3 *db; char *zErrMsg = 0; SQLITE3 sql(dbase.c_str()); statement = "SELECT * from configs_table WHERE id='31'"; sql.exe(statement.c_str()); if( sql.vcol_head.size() > 0 ){ //do things with sql.vdata[] }//end query returned results *An example of a write statement looks like:* statement = "UPDATE configs_table SET searched='2' WHERE id='31'"; sql.exe(statement.c_str()); About 97% of the time, the select statement works fine, but in the other 3% of cases, I see a 'database is locked' error in the log file of my program. About 50% of the time, the write statement returns 'database is locked'. Additionally, if this application is running and I try to query the database from the terminal, I almost always get a 'database is locked' error. Thus, I am wondering if I am doing something wrong in my implementation of the C++ --> SQLite interaction, or if the real problem is that this application is not well suited to use with SQLite (I went through the checklist before implementing it and thought my application passed the suitability requirements). Lastly: A. if it seems like this is an implementation issue, rather than a capability issue, if I were to scale up my method to spawn say 500-1000 processes at a time (on a supercomputing cluster), would there be any concern about SQLite scaling to that level? B. If SQLite is not a good fit for my program, do you have any suggestions of an alternative database engine that is free or might be free or inexpensive for academic use? Thanks in advance, Kathleen
[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?
If you are not using WAL mode that might help. On 7/2/2015 7:59 AM, Kathleen Alexander wrote: > Hi Adam, > > Thank you very much for your response. I had not considered using copies of > the database. In my method there are cases where writes to the database by > one process may be relevant to another process, so I'm not sure that that > would be a good option. > > I don't explicitly set sqlite3_busy_timeout(sqlite3*, int ms) so I will try > adding that after the database connection is opened to see if it limits > those errors. > > Thanks Again. > > On Thu, Jul 2, 2015 at 10:28 AM, Adam Devita wrote: > >> Good day, >> >> I'm sure others on the list will add better insight, but is your task >> parallel enough that your nodes can work with a copy of the database >> and submit changes the one the others copy from when 'done' their >> calculation? >> >> Are you using https://www.sqlite.org/c3ref/busy_timeout.html ? >> >> regards, >> Adam >> >> This may be beside the point in terms of optimization, but your query >> looks rather character based on int like information. >> >> On Thu, Jul 2, 2015 at 10:09 AM, Kathleen Alexander >> wrote: >>> Hi, >>> >>> I apologize if this is an incorrect forum for this question, but I am >>> pretty new to SQLite and have been unable to resolve this issue through >>> other searches. Feel free to direct me to a more appropriate forum. >>> >>> Essentially, I have written an application in C++ that interfaces (reads >>> and writes) with a SQLite database, and I am getting lots of 'database is >>> locked' errors. Right now, I am trying to establish whether those errors >>> are due to my improper use of SQLite itself, or if the real problem is >> that >>> SQLite is not a good fit for my application. >>> >>> My application runs on Linux (ubuntu 13.10), and is driven by a bash >> script >>> that spawns many (~60 on a 64 core workstation) instances of a serial, >> C++ >>> program, each of which opens its own connection to the database and >>> performs reads and writes. >>> >>> *An example SELECT query from my program looks like:* >>> //open db connection >>> sqlite3 *db; >>> char *zErrMsg = 0; >>> SQLITE3 sql(dbase.c_str()); >>> >>> statement = "SELECT * from configs_table WHERE id='31'"; >>> sql.exe(statement.c_str()); >>> if( sql.vcol_head.size() > 0 ){ >>> //do things with sql.vdata[] >>> }//end query returned results >>> >>> *An example of a write statement looks like:* >>> statement = "UPDATE configs_table SET searched='2' WHERE id='31'"; >>> sql.exe(statement.c_str()); >>> >>> About 97% of the time, the select statement works fine, but in the other >> 3% >>> of cases, I see a 'database is locked' error in the log file of my >> program. >>> About 50% of the time, the write statement returns 'database is locked'. >>> >>> Additionally, if this application is running and I try to query the >>> database from the terminal, I almost always get a 'database is locked' >>> error. >>> >>> Thus, I am wondering if I am doing something wrong in my implementation >> of >>> the C++ --> SQLite interaction, or if the real problem is that this >>> application is not well suited to use with SQLite (I went through the >>> checklist before implementing it and thought my application passed the >>> suitability requirements). >>> >>> Lastly: >>> A. if it seems like this is an implementation issue, rather than a >>> capability issue, if I were to scale up my method to spawn say 500-1000 >>> processes at a time (on a supercomputing cluster), would there be any >>> concern about SQLite scaling to that level? >>> B. If SQLite is not a good fit for my program, do you have any >> suggestions >>> of an alternative database engine that is free or might be free or >>> inexpensive for academic use? >>> >>> Thanks in advance, >>> Kathleen >>> ___ >>> sqlite-users mailing list >>> sqlite-users at mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >> >> -- >> -- >> VerifEye Technologies Inc. >> 151 Whitehall Dr. Unit 2 >> Markham, ON >> L3R 9T1 >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users