Can't commit transaction - SQL statements in progress

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Can't commit transaction - SQL statements in progress

Eric Scouten
I'm getting this error periodically when attempting to commit a transaction.

What I believe is happening is that my database abstraction layer has
lost track of one or more compiled statements that it created at some
earlier time. Is there any way to find out *what* transactions are still
operating at the time this error is issued?

That would help my debugging efforts greatly.

-Eric

--
Eric Scouten | [hidden email] | Photography: www.ericscouten.com

Reply | Threaded
Open this post in threaded view
|

Re: Can't commit transaction - SQL statements in progress

Robert L Cochran
You really need to post specific code snippets, you can't expect a
definitive solution including the complete diagnosis and corrected code
from an 11-word problem statement and then a 2-line guess as to the
cause. Please show your code. Others on this forum will help you. (I
lack the expertise.)

Bob Cochran
Greenbelt, Maryland, USA


Eric Scouten wrote:

> I'm getting this error periodically when attempting to commit a
> transaction.
>
> What I believe is happening is that my database abstraction layer has
> lost track of one or more compiled statements that it created at some
> earlier time. Is there any way to find out *what* transactions are
> still operating at the time this error is issued?
>
> That would help my debugging efforts greatly.
>
> -Eric
>

Reply | Threaded
Open this post in threaded view
|

Re: Can't commit transaction - SQL statements in progress

Eric Scouten
If it were easy to boil down to a simple code snippet, I would have
happily done so. :-) Unfortunately, the error is probably caused
somewhere inside a relatively complex home-grown wrapper for SQLite and
is not easily distilled into something I can share here.

The question really boils down to "can SQLite offer enough information
to help me diagnose the problem it's telling me I have?"

Or to put it another way, this is essentially a memory leak problem.
SQLite obviously knows that I've lost track of one or more prepared
statements that haven't run to completion, it isn't telling me *what*
statements those are. I'm wondering if there is any way of getting that
information. Armed with that knowledge, I can probably fix my code
fairly quickly.

At this point, I don't have any reason to suspect that SQLite itself is
failing.

-Eric


Robert L Cochran wrote:

> You really need to post specific code snippets, you can't expect a
> definitive solution including the complete diagnosis and corrected
> code from an 11-word problem statement and then a 2-line guess as to
> the cause. Please show your code. Others on this forum will help you.
> (I lack the expertise.)
>
> Bob Cochran
> Greenbelt, Maryland, USA
>
>
> Eric Scouten wrote:
>
>> I'm getting this error periodically when attempting to commit a
>> transaction.
>>
>> What I believe is happening is that my database abstraction layer has
>> lost track of one or more compiled statements that it created at some
>> earlier time. Is there any way to find out *what* transactions are
>> still operating at the time this error is issued?
>>
>> That would help my debugging efforts greatly.
>>
>> -Eric
>>
>


--
Eric Scouten | [hidden email] | Photography: www.ericscouten.com

Reply | Threaded
Open this post in threaded view
|

Re: Can't commit transaction - SQL statements in progress

Robert L Cochran
Show your prepared statements and the code you are using to execute
them. There is always a simple cause to seemingly complex problems.

Bob

Eric Scouten wrote:

> If it were easy to boil down to a simple code snippet, I would have
> happily done so. :-) Unfortunately, the error is probably caused
> somewhere inside a relatively complex home-grown wrapper for SQLite
> and is not easily distilled into something I can share here.
>
> The question really boils down to "can SQLite offer enough information
> to help me diagnose the problem it's telling me I have?"
>
> Or to put it another way, this is essentially a memory leak problem.
> SQLite obviously knows that I've lost track of one or more prepared
> statements that haven't run to completion, it isn't telling me *what*
> statements those are. I'm wondering if there is any way of getting
> that information. Armed with that knowledge, I can probably fix my
> code fairly quickly.
>
> At this point, I don't have any reason to suspect that SQLite itself
> is failing.
>
> -Eric
>
>
> Robert L Cochran wrote:
>
>> You really need to post specific code snippets, you can't expect a
>> definitive solution including the complete diagnosis and corrected
>> code from an 11-word problem statement and then a 2-line guess as to
>> the cause. Please show your code. Others on this forum will help you.
>> (I lack the expertise.)
>>
>> Bob Cochran
>> Greenbelt, Maryland, USA
>>
>>
>> Eric Scouten wrote:
>>
>>> I'm getting this error periodically when attempting to commit a
>>> transaction.
>>>
>>> What I believe is happening is that my database abstraction layer
>>> has lost track of one or more compiled statements that it created at
>>> some earlier time. Is there any way to find out *what* transactions
>>> are still operating at the time this error is issued?
>>>
>>> That would help my debugging efforts greatly.
>>>
>>> -Eric
>>>
>>
>
>

Reply | Threaded
Open this post in threaded view
|

Re: Can't commit transaction - SQL statements in progress

D. Richard Hipp
In reply to this post by Eric Scouten
Eric Scouten <[hidden email]> wrote:

>
> The question really boils down to "can SQLite offer enough information
> to help me diagnose the problem it's telling me I have?"
>
> Or to put it another way, this is essentially a memory leak problem.
> SQLite obviously knows that I've lost track of one or more prepared
> statements that haven't run to completion, it isn't telling me *what*
> statements those are. I'm wondering if there is any way of getting that
> information. Armed with that knowledge, I can probably fix my code
> fairly quickly.
>

If you have a symbolic debugger and you compile with -g, then you
can look inside the internal SQLite data structures and figure this
out.  Each "sqlite*" connection object contains a linked list of
pending statements.  Just follow the list.  If you compile with
-DSQLITE_DEBUG=1 (I think) then the VDBE code on each statement will
be terminated by a Noop instruction whose P3 argument is the original
text of the SQL statement.  You can use that to figure out which
statement is which.

I'm afraid there is no easier way.

--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Can't commit transaction - SQL statements in progress

Dennis Cote
In reply to this post by Eric Scouten
Eric Scouten wrote:

> The question really boils down to "can SQLite offer enough information
> to help me diagnose the problem it's telling me I have?"
>
> Or to put it another way, this is essentially a memory leak problem.
> SQLite obviously knows that I've lost track of one or more prepared
> statements that haven't run to completion, it isn't telling me *what*
> statements those are. I'm wondering if there is any way of getting
> that information. Armed with that knowledge, I can probably fix my
> code fairly quickly.
>
Eric,

I don't think there is an API to let you check this directly, but one of
the fields in the sqlite3 structure (yes it is supposed to be opaque,
but it can be useful to look inside it for debugging purposes) pointed
at by the pointer returned from sqlite3_open is a pointer to a list of
prepared VMs. It is called pVdbe in the sqlite source.  It is followed
by a count of the number of currently executing VMs, called activeVdbeCnt.

  struct sqlite3 {
  ...
  struct Vdbe *pVdbe;           /* List of active virtual machines */
  int activeVdbeCnt;            /* Number of vdbes currently executing */
  ...
  }
 
You should be able to locate this pointer by checking for a value that
matches the statement pointer returned from sqlite3_prepare(). It should
be about 16 or 17 words (32 bit) into the structure but may be elsewhere
depending upon the compiler. Once you know where this pointer is, you
can use it to locate all the outstanding VMs at any time, including
after your error. Each VM structure returned from sqlite3_prepare()
begins with a pointer to the sqlite3 structure above, and a pointer to
the next VM on this linked list.

  struct Vdbe {
  sqlite3 *db;        /* The whole database */
  Vdbe *pPrev,*pNext; /* Linked list of VDBEs with the same Vdbe.db */
  ...
  }

Simply follow the linked list to get the addresses of the active VMs.
You can compare these to the addresses returned when the statements were
prepared.

It might be easier (if you are using C++ anyway) to simply create a
global vector of structures like this

  struct stmt {
    sqlite3_stmt* s;
    char* file;         // fill with __FILE__
    int line;            //fill with __LINE__
    string sql;
  };

  vector<struct stmt> stmts;

Then simply add each statement to the vector when it is prepared, and
delete it from the vector when the statement is finalized. Anything left
on the vector when you get your error is what you are looking for.

HTH
Dennis Cote

Reply | Threaded
Open this post in threaded view
|

Re: Can't commit transaction - SQL statements in progress

Nathan Kurz
In reply to this post by D. Richard Hipp
On Mon, Dec 12, 2005 at 06:55:24PM -0500, [hidden email] wrote:
> Eric Scouten <[hidden email]> wrote:
> > Or to put it another way, this is essentially a memory leak problem.
> > SQLite obviously knows that I've lost track of one or more prepared
> > statements that haven't run to completion, it isn't telling me *what*
> > statements those are. I'm wondering if there is any way of getting that
> > information. Armed with that knowledge, I can probably fix my code
> > fairly quickly.

If you happen to be running on Linux, you might try using 'valgrind'
(http://www.valgrind.org).  Among other things, it's a link time
memory debugger that does a good job of catching lost pointers.  I've
used it for similar problems with SQLite custom code with great success.

--nate
Reply | Threaded
Open this post in threaded view
|

Re[2]: Can't commit transaction - SQL statements in progress

Teg-3
In reply to this post by Eric Scouten
Hello Eric,

I'd be inclined to throw it on the debugger and see what's going on.
If it's too complicated to debug, then maybe you need to re-think the
design. In my case, my code's littered with assertions and sanity
checks that kick out when I attempt to do something wrong. Sometimes
I'll implement the same thing two different ways, run both and check
them against each other (in debug mode that is).

For example, if I attempt to open a DB with the same object when it's
already open, I get an immediate assertion telling me I'm screwing up.
My transactions commit automatically when they go out of scope and
with assertion, if it fails to commit, the code stops in it's tracks
and I can look at the call stack and parameters. I also trash values
on purpose when something gets destructed so, if I attempt to use it
after destruction, it croaks.

I don't care how slow it runs in debug mode and all of my sanity
checking is removed automatically in a release build.

Just a thought. You know, debug mode logging works well too. It
timing's important, I'll just log it's execution and line numbers to a
disk file and trace through it when something fails.

C



Monday, December 12, 2005, 6:01:45 PM, you wrote:

ES> If it were easy to boil down to a simple code snippet, I would have
ES> happily done so. :-) Unfortunately, the error is probably caused
ES> somewhere inside a relatively complex home-grown wrapper for SQLite and
ES> is not easily distilled into something I can share here.

ES> The question really boils down to "can SQLite offer enough information
ES> to help me diagnose the problem it's telling me I have?"

ES> Or to put it another way, this is essentially a memory leak problem.
ES> SQLite obviously knows that I've lost track of one or more prepared
ES> statements that haven't run to completion, it isn't telling me *what*
ES> statements those are. I'm wondering if there is any way of getting that
ES> information. Armed with that knowledge, I can probably fix my code
ES> fairly quickly.

ES> At this point, I don't have any reason to suspect that SQLite itself is
ES> failing.

ES> -Eric


ES> Robert L Cochran wrote:

>> You really need to post specific code snippets, you can't expect a
>> definitive solution including the complete diagnosis and corrected
>> code from an 11-word problem statement and then a 2-line guess as to
>> the cause. Please show your code. Others on this forum will help you.
>> (I lack the expertise.)
>>
>> Bob Cochran
>> Greenbelt, Maryland, USA
>>
>>
>> Eric Scouten wrote:
>>
>>> I'm getting this error periodically when attempting to commit a
>>> transaction.
>>>
>>> What I believe is happening is that my database abstraction layer has
>>> lost track of one or more compiled statements that it created at some
>>> earlier time. Is there any way to find out *what* transactions are
>>> still operating at the time this error is issued?
>>>
>>> That would help my debugging efforts greatly.
>>>
>>> -Eric
>>>
>>





--
Best regards,
 Teg                            mailto:[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Can't commit transaction - SQL statements in progress

Eric Scouten
In reply to this post by D. Richard Hipp
[hidden email] wrote:

>Eric Scouten <[hidden email]> wrote:
>  
>
>>The question really boils down to "can SQLite offer enough information
>>to help me diagnose the problem it's telling me I have?"
>>
>>Or to put it another way, this is essentially a memory leak problem.
>>SQLite obviously knows that I've lost track of one or more prepared
>>statements that haven't run to completion, it isn't telling me *what*
>>statements those are. I'm wondering if there is any way of getting that
>>information. Armed with that knowledge, I can probably fix my code
>>fairly quickly.
>>
>>    
>>
>
>If you have a symbolic debugger and you compile with -g, then you
>can look inside the internal SQLite data structures and figure this
>out.  Each "sqlite*" connection object contains a linked list of
>pending statements.  Just follow the list.  If you compile with
>-DSQLITE_DEBUG=1 (I think) then the VDBE code on each statement will
>be terminated by a Noop instruction whose P3 argument is the original
>text of the SQL statement.  You can use that to figure out which
>statement is which.
>
>I'm afraid there is no easier way.
>  
>

Richard, thanks. I just filed a low-priority feature request (ticket
#1558) to make this process easier for the next person who encounters
this problem.

In the meantime, this should be enough info to help me find my bug.
Thanks for the tip!

-Eric


--
Eric Scouten | [hidden email] | Photography: www.ericscouten.com

Reply | Threaded
Open this post in threaded view
|

Re: Can't commit transaction - SQL statements in progress

John Stanton-3
In reply to this post by Eric Scouten
You have pointers to the compiled SQL statements.  You should be able to
track when the destination of those pointers changes.  I haven't had the
need to do it, but it is where I would go if careful examination of the
code did not reveal the error.
JS

Eric Scouten wrote:

> If it were easy to boil down to a simple code snippet, I would have
> happily done so. :-) Unfortunately, the error is probably caused
> somewhere inside a relatively complex home-grown wrapper for SQLite and
> is not easily distilled into something I can share here.
>
> The question really boils down to "can SQLite offer enough information
> to help me diagnose the problem it's telling me I have?"
>
> Or to put it another way, this is essentially a memory leak problem.
> SQLite obviously knows that I've lost track of one or more prepared
> statements that haven't run to completion, it isn't telling me *what*
> statements those are. I'm wondering if there is any way of getting that
> information. Armed with that knowledge, I can probably fix my code
> fairly quickly.
>
> At this point, I don't have any reason to suspect that SQLite itself is
> failing.
>
> -Eric
>
>
> Robert L Cochran wrote:
>
>> You really need to post specific code snippets, you can't expect a
>> definitive solution including the complete diagnosis and corrected
>> code from an 11-word problem statement and then a 2-line guess as to
>> the cause. Please show your code. Others on this forum will help you.
>> (I lack the expertise.)
>>
>> Bob Cochran
>> Greenbelt, Maryland, USA
>>
>>
>> Eric Scouten wrote:
>>
>>> I'm getting this error periodically when attempting to commit a
>>> transaction.
>>>
>>> What I believe is happening is that my database abstraction layer has
>>> lost track of one or more compiled statements that it created at some
>>> earlier time. Is there any way to find out *what* transactions are
>>> still operating at the time this error is issued?
>>>
>>> That would help my debugging efforts greatly.
>>>
>>> -Eric
>>>
>>
>
>