Re: [sqlite] Deadlock when doing threaded updates and inserts
"D. Richard Hipp" <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] wrote: > > What I'm seeing is a few tasks doing lots of writing to the database (with > > and without explicit transactions) preventing a reader task from getting a > > chance to read. A SELECT can block for a *very* long time (my 60 second > > timeout expires). > > > > What you describe is not writer starvation. It is not clear to me how > what you describe is occurring. Hmmm... Ok, well I'll have to investigate further. I had thought I was seeing a "known problem" and switching to 3.0 would solve the problem. Now I have a different sort of work to do, tracking this down. Thanks, Derrell
Re: [sqlite] Deadlock when doing threaded updates and inserts
[EMAIL PROTECTED] wrote: > What I'm seeing is a few tasks doing lots of writing to the database (with > and without explicit transactions) preventing a reader task from getting a > chance to read. A SELECT can block for a *very* long time (my 60 second > timeout expires). > What you describe is not writer starvation. It is not clear to me how what you describe is occurring. Only one task can write at a time. During its COMMIT, no other task can access the database. When the COMMIT finishes, locks are removed and all tasks have an opportunity to access the database again. Writers and readers have equal opportunity and so none should starve. Writer starvation occurs when multiple readers are using the database. The readers always overlap (the next reader starts before the previous finishes) so that there is always a shared lock on the database. This prevents a writer from ever getting a turn. The new PENDING lock prevents writer starvation. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Deadlock when doing threaded updates and inserts
"D. Richard Hipp" <[EMAIL PROTECTED]> writes: > Version 2 had a problem with writer starvation. I'm not familiar with > the reader starvation problem. Can you describe your situation? Maybe I misremembered the terminology. What I'm seeing is a few tasks doing lots of writing to the database (with and without explicit transactions) preventing a reader task from getting a chance to read. A SELECT can block for a *very* long time (my 60 second timeout expires). > The PRAGMA would still using the PENDING lock idea so I think the > writer starvation problem would still be resolved. The PRAGMA would > just disallow multi-statement read-only transactions. Ok, great. Now is what I just described above what you had called "writer starvation"? Cheers, Derrell
Re: [sqlite] Deadlock when doing threaded updates and inserts
[EMAIL PROTECTED] wrote: I'll look into adding a PRAGMA that makes BEGIN TRANSACTION acquire a RESERVED lock immediately. That will reduce the amount of confusion about this issue, I suppose. Does enabling this PRAGMA regenerate the reader starvation problem of 2.8.x, or is that problem solved elsewise in 3.0? Version 2 had a problem with writer starvation. I'm not familiar with the reader starvation problem. Can you describe your situation? The PRAGMA would still using the PENDING lock idea so I think the writer starvation problem would still be resolved. The PRAGMA would just disallow multi-statement read-only transactions. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Deadlock when doing threaded updates and inserts
"D. Richard Hipp" <[EMAIL PROTECTED]> writes: > Tito Ciuro wrote: >> I'm definitely not happy about this... >> Let me get this right... it seems that you're cruising along fine with >> SQLITE_OK's all over the place when suddenly one of your threads/processes >> get a SQLITE_BUSY signal in the middle of a transaction. In order to solve >> the crisis, one of the transactions *must* be rolled back: >> Questions: >> 1) Which one? Do I toss a coin? > > Rollback the one that returned SQLITE_BUSY In the earlier examples, multiple threads each received an SQLITE_BUSY indication. If they both (all) rollback and retry, the deadlock condition will likely recur... I'm currently encountering the reader starvation problem in 2.8.x, and am eager to switch to 3.0 to solve it, but this rollback/retry issue has me waiting on switching. I will need to do a fair amount of redesign work to accommodate this procedure due to the nature of the transactions (many operations invoked by a number of functions), unless... > I'll look into adding a PRAGMA that makes BEGIN TRANSACTION acquire > a RESERVED lock immediately. That will reduce the amount of confusion > about this issue, I suppose. Does enabling this PRAGMA regenerate the reader starvation problem of 2.8.x, or is that problem solved elsewise in 3.0? Derrell
Re: [sqlite] Deadlock when doing threaded updates and inserts
Tito Ciuro wrote: I'm definitely not happy about this... Let me get this right... it seems that you're cruising along fine with SQLITE_OK's all over the place when suddenly one of your threads/processes get a SQLITE_BUSY signal in the middle of a transaction. In order to solve the crisis, one of the transactions *must* be rolled back: Questions: 1) Which one? Do I toss a coin? Rollback the one that returned SQLITE_BUSY 2) At the time when SQLITE_BUSY pops up, the app may very well be too deep in a transaction. Dr. Hipp suggests retrying the transaction that was rolled back, a solution I believe should be handled by the engine. Who is then responsible to keep track of the operations that make up a currently openened transaction? The app I suppose? It will add an amazing amount of ugly code testing/retesting/solving a SQLITE_BUSY signal. The SQLITE_BUSY will be returned the first time you try to modify the database. Any prior statements in the tranaction will have been only queries. (Assuming you have a busy_handler registered that handles conflicts in acquiring a PENDING lock.) If you start each transaction with database change of some kind (perhaps an UPDATE that doesn't really update anything) then it will immediately attempt to acquire a RESERVED lock and either succeed or return SQLITE_BUSY to let you know that you need to try the whole transaction again later. An example of an UPDATE that doesn't really change the database might be something like this: UPDATE table1 SET rowid=rowid WHERE rowid<0; I'll look into adding a PRAGMA that makes BEGIN TRANSACTION acquire a RESERVED lock immediately. That will reduce the amount of confusion about this issue, I suppose. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Deadlock when doing threaded updates and inserts
On Aug 11, 2004, at 3:48 PM, Dave Hayden wrote: Since only one of the competing threads will have completed a write (right?), can't the others "postpone" their transactions somehow until they can get a write lock? That is, postpone the "begin transaction" action. Since they haven't really done any transaction business yet, anyway, because they haven't made a write. -D
Re: [sqlite] Deadlock when doing threaded updates and inserts
I'm definitely not happy about this... Let me get this right... it seems that you're cruising along fine with SQLITE_OK's all over the place when suddenly one of your threads/processes get a SQLITE_BUSY signal in the middle of a transaction. In order to solve the crisis, one of the transactions *must* be rolled back: Questions: 1) Which one? Do I toss a coin? 2) At the time when SQLITE_BUSY pops up, the app may very well be too deep in a transaction. Dr. Hipp suggests retrying the transaction that was rolled back, a solution I believe should be handled by the engine. Who is then responsible to keep track of the operations that make up a currently openened transaction? The app I suppose? It will add an amazing amount of ugly code testing/retesting/solving a SQLITE_BUSY signal. Richard Boulton staterd earlier: The change in 3.0.4 means that when using a busy handler (e.g. sqlite3_busy_timeout) the threads trying to get RESERVED locks will not retry, but instead will return immediately with SQLITE_BUSY. If these transactions are rolled back the thread with the PENDING lock is free to proceed when the busy handler retries the lock. I would assume that every thread/process has its own journal, right? Or that there is a way of knowing which set of operations has a thread/process performed. If this is so, a thread/process could receive SQLITE_BUSY signals while an EXCLUSIVE lock is in place, then have SQLite re-execute the series of statements collected in the journal, that is, an auto-retry. If an error occurs, then the app can attempt to solve the issue. Is this something that can be done, or are there other impediments? Best regards, -- Tito On Aug 12, 2004, at 00:48, Dave Hayden wrote: On Aug 11, 2004, at 6:49 AM, D. Richard Hipp wrote: Oops. The db1 should do a ROLLBACK, not a COMMIT. Or db2 can do an END TRANSACTION (since it never made any changes) and allow db1 to complete instead. The point is that when two threads or processes are trying to write at the same time, one of the two must back off, abandon their transaction (using ROLLBACK) and let the other proceed. Wow. That adds a whole lot of complexity to my code. Every transaction would be inside a loop that checks for a busy return from any statement within. And most of the places I'm using a transaction, I'm doing a few hundred inserts or updates from a number of different functions. This really is something I'd expect to run under the hood. Since only one of the competing threads will have completed a write (right?), can't the others "postpone" their transactions somehow until they can get a write lock? For now, I've solved the problem by adding my own locks to exclude simultaneous transactions on the same database file. I'm only using transactions for writes (is there any reason for a read-only transaction?) so if there's no way to resolve two opened write transactions, you shouldn't be able to open two in the first place. Please let me know if there's something I'm missing here.. Thanks, -Dave
Re: [sqlite] Deadlock when doing threaded updates and inserts
Good point... I have several different tasks(vxworks) Perhaps, this is why its working well for me... Tezo. - Original Message - From: "Dave Hayden" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, August 11, 2004 4:30 PM Subject: Re: [sqlite] Deadlock when doing threaded updates and inserts > On Aug 11, 2004, at 4:05 PM, tezozomoc wrote: > > > I have solved this problem by writing a wrappers around sql_exec and > > sql_query, sql_step, etc... > > In these wrappers I handle the waiting for busy and the lock file > > issue... > > I was doing the same, calling usleep() whenever I got a SQLITE_BUSY > return and trying the command again, but it doesn't help in the case > where two threads are both in a transaction and trying to write.. > > -D > >
Re: [sqlite] Deadlock when doing threaded updates and inserts
For now, I've solved the problem by adding my own locks to exclude simultaneous transactions on the same database file. Ok, but this works only if your app is the only one that can access the DB. If some other app tries to access the same DB you can go in the usual deadlock. Paolo
Re: [sqlite] Deadlock when doing threaded updates and inserts
On Aug 11, 2004, at 4:05 PM, tezozomoc wrote: I have solved this problem by writing a wrappers around sql_exec and sql_query, sql_step, etc... In these wrappers I handle the waiting for busy and the lock file issue... I was doing the same, calling usleep() whenever I got a SQLITE_BUSY return and trying the command again, but it doesn't help in the case where two threads are both in a transaction and trying to write.. -D
Re: [sqlite] Deadlock when doing threaded updates and inserts
I have solved this problem by writing a wrappers around sql_exec and sql_query, sql_step, etc... In these wrappers I handle the waiting for busy and the lock file issue... It is not elegant but it allowed me to preserve the interface the same way without having to do it at the application level. Tezozomoc. - Original Message - From: "Dave Hayden" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, August 11, 2004 3:48 PM Subject: Re: [sqlite] Deadlock when doing threaded updates and inserts > On Aug 11, 2004, at 6:49 AM, D. Richard Hipp wrote: > > > Oops. The db1 should do a ROLLBACK, not a COMMIT. Or db2 can > > do an END TRANSACTION (since it never made any changes) and allow > > db1 to complete instead. The point is that when two threads or > > processes are trying to write at the same time, one of the two > > must back off, abandon their transaction (using ROLLBACK) and let > > the other proceed. > > Wow. That adds a whole lot of complexity to my code. Every transaction > would be inside a loop that checks for a busy return from any statement > within. And most of the places I'm using a transaction, I'm doing a few > hundred inserts or updates from a number of different functions. > > This really is something I'd expect to run under the hood. Since only > one of the competing threads will have completed a write (right?), > can't the others "postpone" their transactions somehow until they can > get a write lock? > > For now, I've solved the problem by adding my own locks to exclude > simultaneous transactions on the same database file. I'm only using > transactions for writes (is there any reason for a read-only > transaction?) so if there's no way to resolve two opened write > transactions, you shouldn't be able to open two in the first place. > > Please let me know if there's something I'm missing here.. > > Thanks, > -Dave > >
Re: [sqlite] Deadlock when doing threaded updates and inserts
On Aug 11, 2004, at 6:49 AM, D. Richard Hipp wrote: Oops. The db1 should do a ROLLBACK, not a COMMIT. Or db2 can do an END TRANSACTION (since it never made any changes) and allow db1 to complete instead. The point is that when two threads or processes are trying to write at the same time, one of the two must back off, abandon their transaction (using ROLLBACK) and let the other proceed. Wow. That adds a whole lot of complexity to my code. Every transaction would be inside a loop that checks for a busy return from any statement within. And most of the places I'm using a transaction, I'm doing a few hundred inserts or updates from a number of different functions. This really is something I'd expect to run under the hood. Since only one of the competing threads will have completed a write (right?), can't the others "postpone" their transactions somehow until they can get a write lock? For now, I've solved the problem by adding my own locks to exclude simultaneous transactions on the same database file. I'm only using transactions for writes (is there any reason for a read-only transaction?) so if there's no way to resolve two opened write transactions, you shouldn't be able to open two in the first place. Please let me know if there's something I'm missing here.. Thanks, -Dave
Re: [sqlite] Deadlock when doing threaded updates and inserts
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Roth wrote: | Maybe a solution would be something like this: | | All open transactions should have the same chance to commit. The first | transaction that commits, will win. After a transaction won, all other | transaction should return BUSY. I thought a little bit more about it, and found that the above poliy is unfair when you have long running transactions and short running transactions and maybe won't fit very well to the sqlite3 pager. The policy maybe could be: All transactions start always. A transaction which first wrote to the database, will always win when it commits. After a transaction is selected to win, all other open transactions always returns BUSY, no matter if they execute a select or an update/insert, but this isn't true for the first statement in an transaction. The first statement in a transaction, no matter if it is a select or an update/insert, will call the busy handler if there is currently an other transaction that will win, i.e. an other transaction wrote already to the database. Only when the transaction selected to win commits or rollback, new transactions continue, if they don't time out in the busy handler. Some errors in my idea? -BEGIN PGP SIGNATURE- Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBGo8XSIrOxc3jOmoRAlFMAJ43qJRHdpM7J2C7Y97YW8rkH26mKwCfQ1fN GMCO8ueu/nzpcmnG7O5oIhg= =GCo1 -END PGP SIGNATURE-
Re: [sqlite] Deadlock when doing threaded updates and inserts
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Paolo Vernazza wrote: | But doing in that way, you can have this behaviour (and this is what | happends to me): | | db1: BEGIN TRANSACTION;-> SQLITE_OK | db2: BEGIN TRANSACTION;-> SQLITE_OK | | db1: INSERT INTO test VALUES ( 1 );-> SQLITE_BUSY | db2: INSERT INTO test VALUES ( 1 );-> SQLITE_BUSY | | db1: -> ROLLBACK -> SQLITE_OK | db2: -> ROLLBACK -> SQLITE_OK | | and then again | | db1: BEGIN TRANSACTION;-> SQLITE_OK | db2: BEGIN TRANSACTION;-> SQLITE_OK | | db1: INSERT INTO test VALUES ( 1 );-> SQLITE_BUSY | db2: INSERT INTO test VALUES ( 1 );-> SQLITE_BUSY | | db1: -> ROLLBACK -> SQLITE_OK | db2: -> ROLLBACK -> SQLITE_OK Maybe a solution would be something like this: All open transactions should have the same chance to commit. The first transaction that commits, will win. After a transaction won, all other transaction should return BUSY. This will result to the following: case a.) db1: BEGIN TRANSACTION; -> SQLITE_OK db2: BEGIN TRANSACTION; -> SQLITE_OK db1: INSERT ...; -> SQLITE_OK db2: INSERT ...; -> SQLITE_OK db1: COMMIT; -> SQLITE_OK db2: COMMIT; -> SQLITE_BUSY case b.) db1: BEGIN TRANSACTION; -> SQLITE_OK db2: BEGIN TRANSACTION; -> SQLITE_OK db2: INSERT ...; -> SQLITE_OK db2: COMMIT; -> SQLITE_OK db1: INSERT ...; -> SQLITE_BUSY db1: ROLLBACK; -> SQLITE_OK case c.) db1: BEGIN TRANSACTION; -> SQLITE_OK db2: BEGIN TRANSACTION; -> SQLITE_OK db2: INSERT ...; -> SQLITE_OK db1: INSERT ...; -> SQLITE_OK db1: ROLLBACK;-> SQLITE_OK db2: COMMIT; -> SQLITE_OK case d.) db1: BEGIN TRANSACTION; -> SQLITE_OK db2: BEGIN TRANSACTION; -> SQLITE_OK db1: SELECT ...; -> SQLITE_OK db2: INSERT ...; -> SQLITE_OK db2: COMMIT; -> SQLITE_OK db1: COMMIT; -> SQLITE_OK -BEGIN PGP SIGNATURE- Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBGognSIrOxc3jOmoRAkm1AJ9NJb5GHanjL2kMCtVK4Wu7V7df6ACaA+IE k6UiJvLi5U18REV6zTaXegs= =vfJu -END PGP SIGNATURE-
Re: [sqlite] Deadlock when doing threaded updates and inserts
> > The point is that when two threads or > > processes are trying to write at the same time, one of the two > > must back off, abandon their transaction (using ROLLBACK) and let > > the other proceed. > > And how can this be done? What if there are more threads involved? Who > decides? > I found the document http://www.sqlite.org/lockingv3.html provides a very good discussion of the locking for Version 3. Only one thread can hold a PENDING lock, this is the thread that is trying to commit it's transaction (i.e. get an EXCLUSIVE lock). Other threads trying to update can only have SHARED locks and be trying to get RESERVED locks. The change in 3.0.4 means that when using a busy handler (e.g. sqlite3_busy_timeout) the threads trying to get RESERVED locks will not retry, but instead will return immediately with SQLITE_BUSY. If these transactions are rolled back the thread with the PENDING lock is free to proceed when the busy handler retries the lock.
Re: [sqlite] Deadlock when doing threaded updates and inserts
Hello, The point is that when two threads or processes are trying to write at the same time, one of the two must back off, abandon their transaction (using ROLLBACK) and let the other proceed. And how can this be done? What if there are more threads involved? Who decides? -- Tito On Aug 11, 2004, at 15:49, D. Richard Hipp wrote: Paolo Vernazza wrote: D. Richard Hipp wrote: Dave Hayden wrote: I'm running into a deadlock, db1: BEGIN TRANSACTION; db2: BEGIN TRANSACTION; db1: INSERT INTO test VALUES ( 1 ); At this point, both of these return SQLITE_BUSY: db2: UPDATE test SET num = 2 WHERE num = 1; db1: END TRANSACTION; Is this a bug? Or do I have to do something with sqlite 3 I didn't with 2? After the db1 transaction ends, the db2 UPDATE should be able to complete. In version 2, db2 would have blocked when it tried to begin the transaction. Version 3 allows db2 to continue future, but you still cannot have two threads changing the same database at the same time, so it also eventually blocks. Works as designed. But db1 transaction never ends it will ever return SQLITE_BUSY! Paolo Oops. The db1 should do a ROLLBACK, not a COMMIT. Or db2 can do an END TRANSACTION (since it never made any changes) and allow db1 to complete instead. The point is that when two threads or processes are trying to write at the same time, one of the two must back off, abandon their transaction (using ROLLBACK) and let the other proceed. In version 2, it was impossible for two threads to hold a transaction at the same time, so this issue never came up. Version 3 allows other threads to do read transactions while one thread is doing a write transaction provided that the read transactions all finish before the write transaction commits. If a reader tries to write, it gets SQLITE_BUSY. If the writer tries to commit before all the readers finish, it gets SQLITE_BUSY. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Deadlock when doing threaded updates and inserts
D. Richard Hipp wrote: Paolo Vernazza wrote: D. Richard Hipp wrote: Dave Hayden wrote: I'm running into a deadlock, db1: BEGIN TRANSACTION; db2: BEGIN TRANSACTION; db1: INSERT INTO test VALUES ( 1 ); At this point, both of these return SQLITE_BUSY: db2: UPDATE test SET num = 2 WHERE num = 1; db1: END TRANSACTION; Is this a bug? Or do I have to do something with sqlite 3 I didn't with 2? After the db1 transaction ends, the db2 UPDATE should be able to complete. In version 2, db2 would have blocked when it tried to begin the transaction. Version 3 allows db2 to continue future, but you still cannot have two threads changing the same database at the same time, so it also eventually blocks. Works as designed. But db1 transaction never ends it will ever return SQLITE_BUSY! Paolo Oops. The db1 should do a ROLLBACK, not a COMMIT. Or db2 can do an END TRANSACTION (since it never made any changes) and allow db1 to complete instead. The point is that when two threads or processes are trying to write at the same time, one of the two must back off, abandon their transaction (using ROLLBACK) and let the other proceed. But doing in that way, you can have this behaviour (and this is what happends to me): db1: BEGIN TRANSACTION;-> SQLITE_OK db2: BEGIN TRANSACTION;-> SQLITE_OK db1: INSERT INTO test VALUES ( 1 );-> SQLITE_BUSY db2: INSERT INTO test VALUES ( 1 );-> SQLITE_BUSY db1: -> ROLLBACK -> SQLITE_OK db2: -> ROLLBACK -> SQLITE_OK and then again db1: BEGIN TRANSACTION;-> SQLITE_OK db2: BEGIN TRANSACTION;-> SQLITE_OK db1: INSERT INTO test VALUES ( 1 );-> SQLITE_BUSY db2: INSERT INTO test VALUES ( 1 );-> SQLITE_BUSY db1: -> ROLLBACK -> SQLITE_OK db2: -> ROLLBACK -> SQLITE_OK Paolo
Re: [sqlite] Deadlock when doing threaded updates and inserts
Paolo Vernazza wrote: D. Richard Hipp wrote: Dave Hayden wrote: I'm running into a deadlock, db1: BEGIN TRANSACTION; db2: BEGIN TRANSACTION; db1: INSERT INTO test VALUES ( 1 ); At this point, both of these return SQLITE_BUSY: db2: UPDATE test SET num = 2 WHERE num = 1; db1: END TRANSACTION; Is this a bug? Or do I have to do something with sqlite 3 I didn't with 2? After the db1 transaction ends, the db2 UPDATE should be able to complete. In version 2, db2 would have blocked when it tried to begin the transaction. Version 3 allows db2 to continue future, but you still cannot have two threads changing the same database at the same time, so it also eventually blocks. Works as designed. But db1 transaction never ends it will ever return SQLITE_BUSY! Paolo Oops. The db1 should do a ROLLBACK, not a COMMIT. Or db2 can do an END TRANSACTION (since it never made any changes) and allow db1 to complete instead. The point is that when two threads or processes are trying to write at the same time, one of the two must back off, abandon their transaction (using ROLLBACK) and let the other proceed. In version 2, it was impossible for two threads to hold a transaction at the same time, so this issue never came up. Version 3 allows other threads to do read transactions while one thread is doing a write transaction provided that the read transactions all finish before the write transaction commits. If a reader tries to write, it gets SQLITE_BUSY. If the writer tries to commit before all the readers finish, it gets SQLITE_BUSY. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Deadlock when doing threaded updates and inserts
I ran into a similar problem, I used the sqlite3_busy_timeout so that SQLite
automatically retries the locks. In 3.0.4 a change was made so that
SQLite doesn't retry a RESERVED lock (to avoid the deadlock), therefore if I
get a SQLITE_BUSY
return code I rollback the offending transaction and retry it, the
sqlite3_busy_timeout takes care of the other thread/process that's trying to
get the EXCLUSIVE lock.
I use sqlite3_prepare/sqlite3_step so I'm guessing it'll be similar with
sqlite3_exec. Here's an example of the logic I'm using:
sqlite3_busy_timeout(db, 5000)
do {
rc = execQuery(db, "BEGIN TRANSACTION;");
if (rc == SQLITE_DONE) rc = execQuery(db, "UPDATE test SET num = 2 WHERE
num = 1;");
if (rc != SQLITE_DONE) {
execQuery(db, "ROLLBACK TRANSACTION;");
} else {
execQuery(db, "COMMIT TRANSACTION;");
}
if (rc = SQLITE_BUSY) sleep(1);
} while(rc = SQLITE_BUSY)
I'm new to SQLite so I don't know if this is the "right" way to handle this,
maybe someone can comment on this?
Hope this helps,
Richard
- Original Message -
From: "Dave Hayden" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, August 11, 2004 2:12 AM
Subject: [sqlite] Deadlock when doing threaded updates and inserts
> I'm running into a deadlock, as the subject says, when doing updates on
> a table in one thread while another thread is inserting into the same
> table. (Oh, and this is on 3.0.4, compiled with --enable-threadsafe)
>
> The update thread returns from its UPDATE command (within a
> transaction) with SQLITE_BUSY when it sees a pending lock. The insert
> thread returns SQLITE_BUSY from END TRANSACTION when it can't get an
> exclusive lock.
>
> Attached is a simple C program that demonstrates this. I open two
> database handles on the same file (with a table "test" with a single
> column "num") and do:
>
> db1: BEGIN TRANSACTION;
> db2: BEGIN TRANSACTION;
> db1: INSERT INTO test VALUES ( 1 );
>
> At this point, both of these return SQLITE_BUSY:
>
> db2: UPDATE test SET num = 2 WHERE num = 1;
> db1: END TRANSACTION;
>
> Is this a bug? Or do I have to do something with sqlite 3 I didn't with
> 2?
>
> Thanks,
> -Dave
>
>
Re: [sqlite] Deadlock when doing threaded updates and inserts
D. Richard Hipp wrote: Dave Hayden wrote: I'm running into a deadlock, db1: BEGIN TRANSACTION; db2: BEGIN TRANSACTION; db1: INSERT INTO test VALUES ( 1 ); At this point, both of these return SQLITE_BUSY: db2: UPDATE test SET num = 2 WHERE num = 1; db1: END TRANSACTION; Is this a bug? Or do I have to do something with sqlite 3 I didn't with 2? After the db1 transaction ends, the db2 UPDATE should be able to complete. In version 2, db2 would have blocked when it tried to begin the transaction. Version 3 allows db2 to continue future, but you still cannot have two threads changing the same database at the same time, so it also eventually blocks. Works as designed. But db1 transaction never ends it will ever return SQLITE_BUSY! Paolo
Re: Re: [sqlite] Deadlock when doing threaded updates and inserts
Hello SQLite Users, Hello Dr. Hipp, > After the db1 transaction ends, the db2 UPDATE should be able to > complete. In version 2, db2 would have blocked when it tried to > begin the transaction. Version 3 allows db2 to continue future, > but you still cannot have two threads changing the same database > at the same time, so it eventually blocks. In v2.8x I was using sqlite_busy_timeout to escape this. Unfortunately, it seems that sqlite3_busy_timeout has no effect: it doesn't wait for x milliseconds for the database to become unlocked, but it returns immediately reporting SQLITE_BUSY. What to do ? Regards, George Ionescu
Re: [sqlite] Deadlock when doing threaded updates and inserts
Dave Hayden wrote: I'm running into a deadlock, as the subject says, when doing updates on a table in one thread while another thread is inserting into the same table. (Oh, and this is on 3.0.4, compiled with --enable-threadsafe) The update thread returns from its UPDATE command (within a transaction) with SQLITE_BUSY when it sees a pending lock. The insert thread returns SQLITE_BUSY from END TRANSACTION when it can't get an exclusive lock. Attached is a simple C program that demonstrates this. I open two database handles on the same file (with a table "test" with a single column "num") and do: db1: BEGIN TRANSACTION; db2: BEGIN TRANSACTION; db1: INSERT INTO test VALUES ( 1 ); At this point, both of these return SQLITE_BUSY: db2: UPDATE test SET num = 2 WHERE num = 1; db1: END TRANSACTION; Is this a bug? Or do I have to do something with sqlite 3 I didn't with 2? After the db1 transaction ends, the db2 UPDATE should be able to complete. In version 2, db2 would have blocked when it tried to begin the transaction. Version 3 allows db2 to continue future, but you still cannot have two threads changing the same database at the same time, so it also eventually blocks. Works as designed. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] Deadlock when doing threaded updates and inserts
I'm running into a deadlock, as the subject says, when doing updates on
a table in one thread while another thread is inserting into the same
table. (Oh, and this is on 3.0.4, compiled with --enable-threadsafe)
The update thread returns from its UPDATE command (within a
transaction) with SQLITE_BUSY when it sees a pending lock. The insert
thread returns SQLITE_BUSY from END TRANSACTION when it can't get an
exclusive lock.
Attached is a simple C program that demonstrates this. I open two
database handles on the same file (with a table "test" with a single
column "num") and do:
db1: BEGIN TRANSACTION;
db2: BEGIN TRANSACTION;
db1: INSERT INTO test VALUES ( 1 );
At this point, both of these return SQLITE_BUSY:
db2: UPDATE test SET num = 2 WHERE num = 1;
db1: END TRANSACTION;
Is this a bug? Or do I have to do something with sqlite 3 I didn't with
2?
Thanks,
-Dave
#include
#include
#include
#include "sqlite3.h"
int execQuery(sqlite3* db, char* query)
{
char* err;
int rc = sqlite3_exec(db, query, NULL, NULL, &err);
if ( rc != SQLITE_OK )
{
printf("sqlite3_exec error: %s\n", err);
sqlite3_free(err);
return 0;
}
return 1;
}
int main()
{
sqlite3* db1;
sqlite3* db2;
int rc;
unlink("./test.db");
unlink("./test.db-journal");
rc = sqlite3_open("test.db", &db1);
if ( rc != SQLITE_OK )
{
printf("thread: Couldn't open database1\n");
exit(-1);
}
execQuery(db1, "CREATE TABLE test ( num int );");
rc = sqlite3_open("test.db", &db2);
if ( rc != SQLITE_OK )
{
printf("thread: Couldn't open database2\n");
exit(-1);
}
execQuery(db1, "BEGIN TRANSACTION;");
execQuery(db2, "BEGIN TRANSACTION;");
execQuery(db1, "INSERT INTO test VALUES ( 1 );");
while ( !execQuery(db2, "UPDATE test SET num = 2 WHERE num = 1;") &&
!execQuery(db2, "END TRANSACTION;") &&
!execQuery(db1, "END TRANSACTION;") )
usleep(1000);
execQuery(db2, "END TRANSACTION;");
return 0;
}
