Hi folks,
I've got a problem which has been confusing me for a few days, so its time to ask the list for some insight. I've got an application where there are two threads each reading from distinct tables in the database. In the main thread, there is a SELECT statement which returns a lot of rows (500K or so) executing. The 2nd thread executes a SELECT from a different table. Sometimes, both of these threads are running simultaneously, which shouldn't be a problem since these are both SELECT statements, both should be able to run and return data (there should be a shared lock). The problem I'm having is that the 2nd thread is getting the SQLite error 5, "database is locked", and I can't figure out why that would be the case. Looking at my log file, The first SELECT has done the prepare, and it's going through a "fetch loop" (using step) reading the results into a memory hash, but the transaction hasn't been committed yet. The 2nd one tries to execute, waits a while via the busy handler, and eventually gives up. Here's a snippet of my application log which shows the problem: [DEBUG][Lib.Agent.DB.Transactions][19:59:20][1000] - CAgentDBTransactionGuard::c'tor Starting Transaction... [DEBUG][Lib.Agent.DB.Transactions][19:59:20][1000] - CAgentLiteDBTransaction::Start -- BEGIN TRANSACTION BuildPreviousRevisionCache [DEBUG][Lib.Agent.DB.Statements][19:59:20][1000] - SELECT PN.Name AS PathName, FN.Name AS FileName FROM FileName FN INNER JOIN PathName PN ON FN.PathID=PN.PathID; [DEBUG][Lib.Agent.DB.Performance][19:59:20][1000] - Execution took 0.18 seconds. [DEBUG][Lib.Agent.DB.Transactions][20:03:05][9720] - CAgentDBTransactionGuard::c'tor Starting Transaction... [DEBUG][Lib.Agent.DB.Transactions][20:03:05][9720] - CAgentLiteDBTransaction::Start -- BEGIN TRANSACTION GetLatestLogInfo [DEBUG][Lib.Agent.DB.Performance][20:03:05][9720] - Prepare took 0.00 seconds. [DEBUG][Lib.Agent.DB.Statements][20:03:05][9720] - SELECT TrueName, Source, LogType, StartTime, EndTime, Status, Description FROM AgentLogs WHERE EndTime IS NOT NULL AND LogType = 0 ORDER BY EndTime DESC LIMIT 1 [ERROR][Lib.Agent.DB.SQLite][20:04:07][9720] - Sqlite Error Code: 5 [ERROR][Lib.Agent.DB.SQLite][20:04:07][9720] - Sqlite Error: database is locked brief explanation on the log: the "execution took..." like after the select statement tells me that the first row is returned in that amount of time. After that, the next log message would be when we're done fetching the rows and the transaction is committed. The [1000] and [9720] are thread IDs. Also, in the time between 20:03:05: and 20:04:07, we retry the select many times via the busy handler, but give up after a minute. Any suggestions as to why the first SELECT would cause a lock? Thanx! Mike ---- Michael D. Boucher Software Engineer - Connected Agent Iron Mountain Digital 120 Turnpike Rd, Southboro, MA 01772 508-808-7590 michael.bouc...@ironmountain.com The information contained in this email message and its attachments is intended only for the private and confidential use of the recipient(s) named above, unless the sender expressly agrees otherwise. Transmission of email over the Internet is not a secure communications medium. If you are requesting or have requested the transmittal of personal data, as defined in applicable privacy laws by means of email or in an attachment to email you must select a more secure alternate means of transmittal that supports your obligations to protect such personal data. If the reader of this message is not the intended recipient and/or you have received this email in error, you must take no action based on the information in this email and you are hereby notified that any dissemination, misuse, copying, or disclosure of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email and delete the original message. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
