Re: [sqlite] locked - what am I doing wrong?
David Swigger wrote: I open two connections: pConn1 = sqlite_open(m_sDbPath.c_str(),0,NULL); pConn2 = sqlite_open(m_sDbPath.c_str(),0,NULL); I do the query and compile a query on conn1, then start stepping through the rows of results. If I try to do any execs using pConn2 - I always get an error code saying the database is locked. As pointed out by others, the query is doing a read-lock on the databae which prevents writes to the database from other connections. Several things you can do here: 1: Use sqlite_get_table() or the equivalent to load the entire result set into memory. This will release the read-lock quickly and allow you to write as you step throught the result set. 2: Copy the result set into a temporary table then step through the temporary table. Like this: CREATE TABLE results AS SELECT ; SELECT * FROM results; Read-locks are not held when reading from temporary tables so you should be able to write to the main database while the SELECT of the temporary tables is on-going. 3: Sometimes an ORDER BY clause on the SELECT statement will cause the entire result set to be read into memory. (This happens if the ORDER BY clause cannot be satisfied by an index.) This is usually considered a bug, but in your case, you might be able to employ this property as a feature. As soon as the result set is entirely in memory, the read-lock is released and other threads can begin writing to the database. 4: If you only do a single sqlite_open() and then compile the query and the inserts from that one connection, then you can read and write the database at the same time. You still won't be able to read and write the same table at the same time, but you can read and write separate tables in the same database. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] locked - what am I doing wrong?
When you first call sqlite_step() for your SELECT statement, SQLite grabs a read-lock on the database file. It doesn't release this lock until you call sqlite_finalize(). When you execute your UPDATE, SQLite needs to get a write-lock on the database file. It can't get the write-lock while another connection (including one from the same process) is holding a read-lock. So there is no way you can perform an UPDATE, INSERT or DELTE operation in the middle of a SELECT. Unfortunately, I think you'll need to structure your program differently. Dan.
Re: [sqlite] locked - what am I doing wrong?
- Forwarded by Ben Carlyle/AU/IRSA/Rail on 09/01/2004 10:44 AM - Ben Carlyle 09/01/2004 09:35 AM To: "David Swigger" <[EMAIL PROTECTED]>@CORP cc: Subject:Re: [sqlite] locked - what am I doing wrong? G'day, "David Swigger" <[EMAIL PROTECTED]> 09/01/2004 12:04 AM To: <[EMAIL PROTECTED]> cc: Subject:[sqlite] locked - what am I doing wrong? > I am having a bit of a hard time understanding something: > When I am in the middle of stepping (sqlite_step) through the > results of a table query, the database is locked. Is this normal? Have > I missed something? This is discussed in Question 7 of the FAQ, but I think the first paragraph is a little misleading. I think that: "Multiple processes can have the same database open at the same time. Multiple processes can be doing a select at the same time. But only one process can be making changes to the database at once." should read: "Multiple processes can have the same database open at the same time. Multiple processes can be doing a select at the same time. But a process can't make changes to the database while any readers or other writers are active." The important thing to understand here, is that you're not stepping through a set of results that you have in memory and that were extracted from the database. You are (at least for simple queries) stepping through data that is still in the database. Because you're still actively reading the database the writer thread can't get access until your query is finished. If, instead, you want to load the whole results set into memory before stepping through them consider the sqlite_get_table_* functions. Alternatively you can collect the results into memory any way you like before stepping through the structures you've created yourself. Benjamin. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
