Re: [sqlite] Database is locked error
Thanks for the response Pavel. The order that the events were getting logged wasn't accurate enough so I increased the timing precision for my logging. I didn't leave anything out, but some commands were logged slightly out of order. Thread1 is doing reads and writes for a while, with Thread2 attempting to insert. Thread2 ends up in the BusyHandler for a while and then the following occurs in this order: Thread Command LockStep 1 RELEASE SAVEPOINT Thread1None1 2 INSERT INTO TableA Exclusive 2 2 RELEASE SAVEPOINT Thread2None3 2 SAVEPOINT Thread2 None 4 2 INSERT INTO TableA Exclusive 5 1 SAVEPOINT Thread1 None 6 2 RELEASE SAVEPOINT Thread2None7 1 SELECT FROM TableA Shared 8 2 SAVEPOINT Thread2 None 9 1 SELECT FROM TableB Shared 10 2 INSERT INTO TableA Reserved? 11 1 INSERT INTO TableB * 12 Step 1 - The transaction is closed on thread1, so it no longer has a lock. Step 2 - This is the insert that was failing, with Thread2 ending up in the busyhandler. When thread2 first tried to insert, it obtained a reserved lock. Now that thread1 released its lock, thread2 gets an exclusive lock and the insert finally succeeds at this point. I think what is going wrong is this: Step 11 - Thread2 tries to do an insert. Since Thread1 has a shared lock, thread2 acquires a reserved lock but it cannot be promoted to Exclusive. Step 12 - BusyHandler is not called. Database is locked error is returned. Thread1's shared lock cannot be promoted to a reserved lock, since Thread2 already has one. Does that seem correct? If so, my options are: 1) rollback/commit one of the transactions 2) use begin exclusive I don't think the second one will work, since I need nested transactions and the savepoint syntax doesn't seem to support the exclusive option. Thanks, Chris 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
[sqlite] Database is locked error
I am running into a database is locked error and I don't quite understand what is going on. If someone could explain what is happening, I'd appreciate it. (I am using sqlite version 3.6.17 on Windows XP.) A busy handler callback has been set up with sqlite3_busy_handler(). In addition to some logging, the callback simply checks the number of retries and either gives up (by returning 0) if it reaches the max retry count or sleeps for 50 msecs then returns 1. I have two threads (each with their own connection) that are trying to access the database. The 1st thread is doing some reads and writes and the other one is just writing. For a while, things work as expected. Thread1 does some reads and writes within explicit transactions (using Savepoint and Release Savepoint syntax). Thread2 attempts to write to the database and can't, so the busyhandler callback is called. Eventually, Thread2 is able to get the exclusive lock and write to the database (again within an explicit transaction). Thread1 does some more reads and writes, etc. Here's where it stops making sense. I'm paraphrasing what is logged out by our app. The first column is the number of msecs since the process started. The second obviously is the active thread. 3rd is the query (simplified for easy reading). The 4th indicates success or failure. The 5th contains notes as to what appears to be happening. 13875 Thread2 SAVEPOINT Thread2 success (No locks acquired) 13875 Thread1 SAVEPOINT Thread1 success (No locks acquired) 13880 Thread2 INSERT INTO TableB success (exclusive lock) 13890 Thread2 RELEASE SAVEPOINT Thread2 fail (the busyhandler callback is called here) 13890 Thread1 SELECT blah FROM TableB success (shared lock???) 13906 Thread1 SELECT blah FROM TableC success (shared lock???) 13906 Thread1 INSERT INTO TableD fail (busyhandler is NOT called - database is locked) The call to sqlite3_step succeeds for the query INSERT INTO TableB at 13880 msecs. I believe this means that an exclusive lock must have been obtained for the connection on thread2. Thread2 attempts to release the savepoint right around the same time as thread1 attempts to read from TableB (at 13890 msecs). Thread1 is able to read from TableB, then is able to read from TableC. This seems to indicate that the connection on thread1 acquired a shared lock. I don't see how this is possible, since thread2 should have had an exclusive lock at that point. I am assuming that sqlite thinks that a deadlock will occur when thread1 tries to write to the database at 13906 msecs and that is why the busyhandler callback is not invoked. Also I'm not sure if it matters, but I am using sqlite3_exec to execute the SAVEPOINT and RELEASE SAVEPOINT statements. All other queries are executed using prepared statements and calls to sqlite3_step. Note that there are NO nested transactions created. Can anyone shed some light on this? Thanks, Chris 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
Re: [sqlite] Slow performance with Sum function
Do not be tempted by the incremental vacuum feature. Incremental vacuum will reduce the database size as content is deleted, but it will not reduce fragmentation. In fact, incremental vacuum will likely increase fragmentation. Incremental vacuum is just a variation on auto_vacuum. It is designed for flash memory with zero seek latency. D. Richard Hipp d...@hwaci.com Thanks for the reply, but I am confused again. Is incremental vacuum different from the vacuum command? It seems like vacuum would defragment the database according to the description here: http://www.sqlite.org/lang_vacuum.html The VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure. If incremental vacuum and vacuum are the same, then I am still uncertain of what to do about my original problem. Any ideas on why the sum function is slow on my existing table, but it is fast on a copy of the table? Also, after calling vacuum, sum is fast on the original table. Here's my original question: I am trying to use the Sum function on a column in a table with ~450K rows in it. Select sum(Col4) from Table1 Where Table1 looks like this: Create TABLE Table1 ( Col1 INTEGER NOT NULL, Col2 INTEGER NOT NULL, Col3 INTEGER NOT NULL, Col4 BIGINT NOT NULL, Col5 BIGINT NOT NULL, Col6 BLOB NOT NULL, Col7 CHAR(1) DEFAULT '0', Col8 NUMERIC(2) NOT NULL, Col9 NUMERIC(2) NOT NULL, Col10 INTEGER NOT NULL, Col11 INTEGER NOT NULL, CONSTRAINT FK_1 FOREIGN KEY (Col1) REFERENCES Table2 (Col1) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT PK_1 PRIMARY KEY (Col10, Col11, Col1, Col3 DESC) ); It takes over 2 minutes to execute when using the original table. I created an exact copy of the table with the same indices and constraints and inserted all the data from the original table into it. Summing that column on the copied table only takes a few seconds. I am guessing that using the copied table is faster because it has all of its data arranged contiguously, but that is just a guess. Can anyone shed some light on this? Making a copy of the table is not an option, so is there anything I can do to get better performance from the original table? Thanks 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
[sqlite] Slow performance with Sum function
I am trying to use the Sum function on a column in a table with ~450K rows in it. Select sum(Col4) from Table1 Where Table1 looks like this: Create TABLE Table1 ( Col1 INTEGER NOT NULL, Col2 INTEGER NOT NULL, Col3 INTEGER NOT NULL, Col4 BIGINT NOT NULL, Col5 BIGINT NOT NULL, Col6 BLOB NOT NULL, Col7 CHAR(1) DEFAULT '0', Col8 NUMERIC(2) NOT NULL, Col9 NUMERIC(2) NOT NULL, Col10 INTEGER NOT NULL, Col11 INTEGER NOT NULL, CONSTRAINT FK_1 FOREIGN KEY (Col1) REFERENCES Table2 (Col1) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT PK_1 PRIMARY KEY (Col10, Col11, Col1, Col3 DESC) ); It takes over 2 minutes to execute when using the original table. I created an exact copy of the table with the same indices and constraints and inserted all the data from the original table into it. Summing that column on the copied table only takes a few seconds. I am guessing that using the copied table is faster because it has all of its data arranged contiguously, but that is just a guess. Can anyone shed some light on this? Making a copy of the table is not an option, so is there anything I can do to get better performance from the original table? Thanks 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
Re: [sqlite] Slow performance with Sum function
After running vacuum, sum is fast on the original table. However, running vacuum took a long time, so I'm not sure if that is a feasible solution. Is there any way to prevent fragmentation in the first place? If not for the whole database, then for a specific table? (e.g. is it possible to preallocate space for a table?) Thanks -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Tuesday, March 03, 2009 2:00 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Slow performance with Sum function Trainor, Chris chris.trai...@ironmountain.com wrote: I am trying to use the Sum function on a column in a table with ~450K rows in it. Select sum(Col4) from Table1 It takes over 2 minutes to execute when using the original table. I created an exact copy of the table with the same indices and constraints and inserted all the data from the original table into it. Summing that column on the copied table only takes a few seconds. Try running VACUUM on your database. Your original table is probably badly fragmented and results in excessive disk seeking. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 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
Re: [sqlite] Slow performance with Sum function
I'm not sure how much we can do about preventing adds and deletes. It *may* be possible to replace them with updates, but I am not sure yet. These adds and deletes are happening in a different table than the one being summed. This other table contains a large blob column. Would changing to updates help or will updates fragment the database as much as adds and deletes? The second option is the one I am considering. It looks like there might be a good time to run vacuum. I need to do some more timings to tell for sure. Thanks for the suggestions. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor Sent: Tuesday, March 03, 2009 7:41 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Slow performance with Sum function On Tue, Mar 3, 2009 at 6:36 PM, Greg Palmer gregorylpal...@netscape.net wrote: Trainor, Chris wrote: After running vacuum, sum is fast on the original table. However, running vacuum took a long time, so I'm not sure if that is a feasible solution. Is there any way to prevent fragmentation in the first place? If not for the whole database, then for a specific table? (e.g. is it possible to preallocate space for a table?) Thanks I'm not an expert on SQLite but generally speaking fragmentation in a database is usually a result of records being added and deleted. Are you doing a lot of these and if so, can you change your algorithm to cut down on this activity? exactly the right approach. Even better, make your application do the vaccuming when your users are away, much like housekeeping in a hotel. Regards, Greg ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 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
Re: [sqlite] Slow performance with Sum function
The blob is fairly small but is referenced very often, so it wouldn't be feasible to move it to another table. Does the blob make a difference in this case? I thought that since the blob column is to the right of the column being summed that it would never get read during the summation. Thanks -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Machin Sent: Tuesday, March 03, 2009 7:53 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Slow performance with Sum function On 4/03/2009 5:52 AM, Trainor, Chris wrote: I am trying to use the Sum function on a column in a table with ~450K rows in it. Select sum(Col4) from Table1 Where Table1 looks like this: Create TABLE Table1 ( Col1 INTEGER NOT NULL, Col2 INTEGER NOT NULL, Col3 INTEGER NOT NULL, Col4 BIGINT NOT NULL, Col5 BIGINT NOT NULL, Col6 BLOB NOT NULL, What is the min/max/average size of this blob and how often do you need to access it? If the answer tends towards huge and rarely, consider putting it in a separate table. Col7 CHAR(1) DEFAULT '0', Col8 NUMERIC(2) NOT NULL, Col9 NUMERIC(2) NOT NULL, Col10 INTEGER NOT NULL, Col11 INTEGER NOT NULL, CONSTRAINT FK_1 FOREIGN KEY (Col1) REFERENCES Table2 (Col1) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT PK_1 PRIMARY KEY (Col10, Col11, Col1, Col3 DESC) ); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 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
