Re: [sqlite] step() fails with SQLITE_BUSY after BEGIN EXCLUSIVETRANSACTION - SOLVED
Hi, Marcus I'm absolutely sure that the problem is in my code and not a SQLite problem. SQLite works well in MT, if one respects the documented limits. I just have no handle currently to find the problem. As we all know, Multi-threading is tricky ;-) I will strip down my MT code until I get it running, and then add more threads to find out which one causes the problem. My guess is that somewhere a prepared statement or other "per-thread" SQlite element is created in one thread and then used from another. Or something like that. -- Mario -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Marcus Grimm Sent: Monday, April 13, 2009 7:21 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] step() fails with SQLITE_BUSY after BEGIN EXCLUSIVETRANSACTION Mario, I'm sorry but I'm running out of ideas... I can only repeat that sqlite works well with this kind of approach. I'm using it in a database server without a problem so far and there I use the exclusive mode to block the threads. However, when I started using sqlite for this I also run into this kind of problems but all of them where related to bug and missusage of some sqlite api functions. I also was lost in one case and decided to build a little test-code that reflects my implementation-style and allmost immediately I got the right tip by the mailing list. The result can be seen here: http://www.sqlite.org/cvstrac/wiki?p=SampleCode I still suggest, if nothing else helps, that you try to make an extraction of your implementation, as simple as possible, and if this still blocks after a thread has obtained the exclusive lock you may post it here and I'm sure you will get a quick reply about what's wrong... Marcus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step() fails with SQLITE_BUSY after BEGIN EXCLUSIVETRANSACTION
Mario,
I'm sorry but I'm running out of ideas... I can only
repeat that sqlite works well with this kind of approach.
I'm using it in a database server without a problem so far
and there I use the exclusive mode to block the threads.
However, when I started using sqlite for this I also run
into this kind of problems but all of them where related to
bug and missusage of some sqlite api functions. I also was lost
in one case and decided to build a little test-code that reflects
my implementation-style and allmost immediately I got the
right tip by the mailing list. The result can be seen
here: http://www.sqlite.org/cvstrac/wiki?p=SampleCode
I still suggest, if nothing else helps, that you try to
make an extraction of your implementation, as simple as possible,
and if this still blocks after a thread has obtained the exclusive
lock you may post it here and I'm sure you will get a quick
reply about what's wrong...
Marcus
>
> Marcus
>
> thanks for your suggestions. I have of course checked the obvious things
> before posting here.
>
> Both the BEGIN EXCLUSIVE and the COMMIT return SQLITE_OK.
>
> Each thread opens its own db handle with sqlite_open and operates on it.
> These are completely isolated, they don't know about each other and they
> do
> not share any data or sqlite constructs. Both threads work flawlessly as
> long as they don't operate in parallel.
>
> I have compiled SQLite with SQLITE_THREADSAFE=1 and the checks in the
> source
> code of SQLite use the mutexes to protect the library. Looks good that
> far.
>
>
> I cannot post the source code easily because I use my wrapper class, and I
> would have to strip it down to the core 'C' SQLite calls to show you what
> actually is done. This wrapper is in use for quite some time and has been
> tested for a year now.
>
> But what I do in these two threads which cause the trouble is really
> simple:
>
>
> 1: if BEGIN EXCLUSIVE TRANSACTION successful then {
> 2: INSERT INTO...
> 3: DELETE FROM...
> 4: COMMIT
> 5: }
>
> Thread A blocks in 1: because it waits for the transaction.
> Thread B blocks in line 2: but has successfully opened an exclusive
> transaction.
>
> I now wonder why it can block in the INSERT after successfully opening an
> exclusive transaction?
> I could not find an explanation in the online docs for this behavior. My
> impression was that if the BEGIN EXCLUSIVE succeeds, further operations on
> the same db handle cannot block.
>
> The threads may even work on different tables, and still the deadlock
> occurs.
>
>
> -- Mario
>
>
>
> -Original Message-
>
> I have no idea why it doesn't work in that way, it used to in my
> application. however, just a few points:
>
> maybe your BEGIN wasn't successful and you didn't realize ?
> maybe you run the INSERT on a DB handle that was not the one that invoked
> the BEGIN ?
> maybe your COMMIT wasn't successful and you didn't realize ?
> maybe your are not in threadsave mode ?
>
> I suggest that you post at least a part of your code here, or even better
> a
> short example that shows the problem.
> This usually gives the people here a better chance to provide useful
> hints.
>
> hope this helps
>
> Marcus
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step() fails with SQLITE_BUSY after BEGIN EXCLUSIVETRANSACTION
Marcus
thanks for your suggestions. I have of course checked the obvious things
before posting here.
Both the BEGIN EXCLUSIVE and the COMMIT return SQLITE_OK.
Each thread opens its own db handle with sqlite_open and operates on it.
These are completely isolated, they don't know about each other and they do
not share any data or sqlite constructs. Both threads work flawlessly as
long as they don't operate in parallel.
I have compiled SQLite with SQLITE_THREADSAFE=1 and the checks in the source
code of SQLite use the mutexes to protect the library. Looks good that far.
I cannot post the source code easily because I use my wrapper class, and I
would have to strip it down to the core 'C' SQLite calls to show you what
actually is done. This wrapper is in use for quite some time and has been
tested for a year now.
But what I do in these two threads which cause the trouble is really simple:
1: if BEGIN EXCLUSIVE TRANSACTION successful then {
2: INSERT INTO...
3: DELETE FROM...
4: COMMIT
5: }
Thread A blocks in 1: because it waits for the transaction.
Thread B blocks in line 2: but has successfully opened an exclusive
transaction.
I now wonder why it can block in the INSERT after successfully opening an
exclusive transaction?
I could not find an explanation in the online docs for this behavior. My
impression was that if the BEGIN EXCLUSIVE succeeds, further operations on
the same db handle cannot block.
The threads may even work on different tables, and still the deadlock
occurs.
-- Mario
-Original Message-
I have no idea why it doesn't work in that way, it used to in my
application. however, just a few points:
maybe your BEGIN wasn't successful and you didn't realize ?
maybe you run the INSERT on a DB handle that was not the one that invoked
the BEGIN ?
maybe your COMMIT wasn't successful and you didn't realize ?
maybe your are not in threadsave mode ?
I suggest that you post at least a part of your code here, or even better a
short example that shows the problem.
This usually gives the people here a better chance to provide useful hints.
hope this helps
Marcus
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step() fails with SQLITE_BUSY after BEGIN EXCLUSIVETRANSACTION
I have no idea why it doesn't work in that way, it used to in my application. however, just a few points: maybe your BEGIN wasn't successful and you didn't realize ? maybe you run the INSERT on a DB handle that was not the one that invoked the BEGIN ? maybe your COMMIT wasn't successful and you didn't realize ? maybe your are not in threadsave mode ? I suggest that you post at least a part of your code here, or even better a short example that shows the problem. This usually gives the people here a better chance to provide useful hints. hope this helps Marcus > Hi, Dave > > I have read your post. I have also read many of the recent archived posts, > and googled the web for hints. I'm at a loss here. > > The fun thing is that my wrapper class (which I use for a while now in > non-MT environments) allows me to track most of what's going on in SQLite. > And even that does not help. > > My protocol shows (for the same thread!) > > BEGIN EXCLUSIVE TRANSACTION > INSERT... > COMMIT > > BEGIN EXCLUSIVE TRANSACTION > INSERT > < and here it blocks for ever > > > > I wonder why the INSERT blocks after the BEGIN is through, and why it > works > for the first sequence. > From what I can tell, all other threads as suspended, have their > statements > properly reset or finalized etc. Since I use wrappers for statements etc. > they all clean up proper after themselves. > > I would wish I could somehow see _why_ and _where_ SQLite is blocking a > connection. > > I'm hoping that somebody here on the list comes up with a tip :-) > > > -Original Message- > > Wow this sounds exactly like my post of a few days ago titled "Strange > sqlite_busy deadlock behavior". > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step() fails with SQLITE_BUSY after BEGIN EXCLUSIVETRANSACTION
Hi, Dave I have read your post. I have also read many of the recent archived posts, and googled the web for hints. I'm at a loss here. The fun thing is that my wrapper class (which I use for a while now in non-MT environments) allows me to track most of what's going on in SQLite. And even that does not help. My protocol shows (for the same thread!) BEGIN EXCLUSIVE TRANSACTION INSERT... COMMIT BEGIN EXCLUSIVE TRANSACTION INSERT < and here it blocks for ever > I wonder why the INSERT blocks after the BEGIN is through, and why it works for the first sequence. >From what I can tell, all other threads as suspended, have their statements properly reset or finalized etc. Since I use wrappers for statements etc. they all clean up proper after themselves. I would wish I could somehow see _why_ and _where_ SQLite is blocking a connection. I'm hoping that somebody here on the list comes up with a tip :-) -Original Message- Wow this sounds exactly like my post of a few days ago titled "Strange sqlite_busy deadlock behavior". ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
