Re: [sqlite] Table locked when trying to delete a record whilst a cursor to the table is open
One work around is to load the results of your SELECT into a TEMP table, then loop through the TEMP table to do your DELETEs. SQLite does allow you to delete, update, or insert tables from within the middle of a select as long as the tables being updated, deleted, or inserted are distinct from those being queried. Hi, Thanks I think I will indeed use a temporary table instead. It will probably use less memory and less hassle than me caching all the results myself. Thanks again Andrew -- Andrew Clark
Re: [sqlite] Table locked when trying to delete a record whilst a cursor to the table is open
Hi, My program model in effect was a Component based model whereby I created an Object Enumerator which is currently implemented as an SQLite Select Query and whilst enumerating the objects I was then calling Delete() on some objects to tell them to destroy. The problem with SQLite was that i wasn't able to delete because the query was still active. I believe the best way to implement this is going to be to cache the results after creating the enumerator and then use the results cache to return an object from the enumerator. Although i suppose I could try and do as you said and cache the objects to be deleted into a list and then destroy them at the end of the enumerator. It's a pity it couldn't have been implemented in SQLite though to be more flexible. Thanks anyway Andrew -- Andrew Clark
Re: [sqlite] Table locked when trying to delete a record whilst a cursor to the table is open
Clay Dowling wrote: Andrew, What you're trying to do won't work. Clay is correct. The btree code in SQLite will get confused if you delete (or otherwise modify) entries in a table while SQLite is trying to scan that table. This means that you cannot do a DELETE on the table in the middle of a SELECT on the same table. This, in turn, means that you cannot do cursor-like things in SQLite. One work around is to load the results of your SELECT into a TEMP table, then loop through the TEMP table to do your DELETEs. SQLite does allow you to delete, update, or insert tables from within the middle of a select as long as the tables being updated, deleted, or inserted are distinct from those being queried. There is currently some discussion of enhancing the btree layer so that it can support a DELETE on a table that is simultaneously being queried (provided that both happen in the same database connection - this is not a form of concurrency.) This can get very tricky, though, if the SELECT is really a join. If an when such changes are made, Andrew's code should start working. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] Table locked when trying to delete a record whilst a cursor to the table is open
Hi, Sorry I forgot to say.. I do know that the error occurs because there are read locks on the table, but is there any reason why say a single process should not be able to modify the table anyway? Or is there something in the Cursor enumeration code that would not be happy if a record was deleted after it had encountered that record. Andrew -- Andrew Clark
[sqlite] Table locked when trying to delete a record whilst a cursor to the table is open
Hi, First of all here the simple example of what i am doing would be: "SELECT id FROM test" Get first Result DELETE FROM test WHERE id=xxx Close Cursor - When i call the DELETE command I get an error of TABLE LOCKED which for those that actually know something about the source code might understand, but not being to familiar about it I am not. Now I know that some of you will reply with... "Why not call the DELETE command with just the id's that we want to delete". Well, esentially my program is not wanting to do that.. without any further debates about it could we please discuss what we could do in this situation. I read somewhere in the documentation about if a transaction fails for whatever reason then in the future the transaction will be retried after say the table has been unlocked. Does this exist yet either in Beta development or something? If not, any help with either solving this problem or telling me why it shouldn't be fixed would be of great help. As I said I am not that knowledgable about SQLite but I see the error occurs from within a call to fileBtreeDelete which itself is called from the OP_DELETE part of vdbe Could someone please tell me if this is a simple thing to solve, and even if whether it should be solved or not. I may indeed look at changing my app design to call DELETE commands directly if this cannot be resolved quickly. Thanks in advance Andrew -- Andrew Clark