How to handle such situation

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

How to handle such situation

Igor Korot
Hi, list,
Have a following question.

I am writing an application in which I will be using transactions. At
the end of the application I will close the connection.
The application will verify every single call to SQLite for an error.

If I start transaction, all queries were successful, but issuing "COMMIT" fails.
On such failure I am going to present an error, but then what happens
if I go and try to close the connection?

I think that the call to sqlite3_close() will fail since there is an
open transaction, right? But then there is no error at all.
Unless I misunderstands the nature of the connection close failure.

So, how do I differentiate such situation from a legitimate
sqlite3_close() failure?

TIA!
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to handle such situation

Jens Alfke-2


> On Oct 4, 2017, at 2:20 PM, Igor Korot <[hidden email]> wrote:
>
> If I start transaction, all queries were successful, but issuing "COMMIT" fails.
> On such failure I am going to present an error, but then what happens
> if I go and try to close the connection?

If COMMIT fails, you should execute a ROLLBACK to abort the transaction, then continue with failure handling. Otherwise yes, the transaction remains open and will cause trouble when you try to close the connection.

—Jens

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to handle such situation

J. King-3
See also:
<http://sqlite.org/lang_conflict.html>

On October 4, 2017 6:15:55 PM EDT, Jens Alfke <[hidden email]> wrote:

>
>
>> On Oct 4, 2017, at 2:20 PM, Igor Korot <[hidden email]> wrote:
>>
>> If I start transaction, all queries were successful, but issuing
>"COMMIT" fails.
>> On such failure I am going to present an error, but then what happens
>> if I go and try to close the connection?
>
>If COMMIT fails, you should execute a ROLLBACK to abort the
>transaction, then continue with failure handling. Otherwise yes, the
>transaction remains open and will cause trouble when you try to close
>the connection.
>
>—Jens
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to handle such situation

Igor Korot
In reply to this post by Jens Alfke-2
Hi, Jens,

On Wed, Oct 4, 2017 at 6:15 PM, Jens Alfke <[hidden email]> wrote:
>
>
>> On Oct 4, 2017, at 2:20 PM, Igor Korot <[hidden email]> wrote:
>>
>> If I start transaction, all queries were successful, but issuing "COMMIT" fails.
>> On such failure I am going to present an error, but then what happens
>> if I go and try to close the connection?
>
> If COMMIT fails, you should execute a ROLLBACK to abort the transaction, then continue with failure handling. Otherwise yes, the transaction remains open and will cause trouble when you try to close the connection.

And if the ROLLBACK fails?
Because it is possible for ROLLBACK to fail. Not just because COMMIT fails.

Thank you.

>
> —Jens
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to handle such situation

Igor Korot
In reply to this post by J. King-3
Hi,

On Wed, Oct 4, 2017 at 6:19 PM, J. King <[hidden email]> wrote:
> See also:
> <http://sqlite.org/lang_conflict.html>

This is good to know but I don't think it is applicable in this case
as I explicitly said
that this is inside transaction which fails.

Thank you.

>
> On October 4, 2017 6:15:55 PM EDT, Jens Alfke <[hidden email]> wrote:
>>
>>
>>> On Oct 4, 2017, at 2:20 PM, Igor Korot <[hidden email]> wrote:
>>>
>>> If I start transaction, all queries were successful, but issuing
>>"COMMIT" fails.
>>> On such failure I am going to present an error, but then what happens
>>> if I go and try to close the connection?
>>
>>If COMMIT fails, you should execute a ROLLBACK to abort the
>>transaction, then continue with failure handling. Otherwise yes, the
>>transaction remains open and will cause trouble when you try to close
>>the connection.
>>
>>—Jens
>>
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to handle such situation

J. King-3
Perhaps I need to be more explicit:

If the transaction fails because of a constraint violation, then using either the ROLLBACK, IGNORE, or REPLACE conflict resolution strategies would commit or roll back the transaction and automatically close it. If you add conflict clauses to table schemata, it wouldn't even require changes to the logic of your application.

On October 4, 2017 10:20:01 PM EDT, Igor Korot <[hidden email]> wrote:

>Hi,
>
>On Wed, Oct 4, 2017 at 6:19 PM, J. King <[hidden email]> wrote:
>> See also:
>> <http://sqlite.org/lang_conflict.html>
>
>This is good to know but I don't think it is applicable in this case
>as I explicitly said
>that this is inside transaction which fails.
>
>Thank you.
>
>>
>> On October 4, 2017 6:15:55 PM EDT, Jens Alfke <[hidden email]>
>wrote:
>>>
>>>
>>>> On Oct 4, 2017, at 2:20 PM, Igor Korot <[hidden email]> wrote:
>>>>
>>>> If I start transaction, all queries were successful, but issuing
>>>"COMMIT" fails.
>>>> On such failure I am going to present an error, but then what
>happens
>>>> if I go and try to close the connection?
>>>
>>>If COMMIT fails, you should execute a ROLLBACK to abort the
>>>transaction, then continue with failure handling. Otherwise yes, the
>>>transaction remains open and will cause trouble when you try to close
>>>the connection.
>>>
>>>—Jens
>>>
>>>_______________________________________________
>>>sqlite-users mailing list
>>>[hidden email]
>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> --
>> Sent from my Android device with K-9 Mail. Please excuse my brevity.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to handle such situation

Jens Alfke-2
In reply to this post by Igor Korot


> On Oct 4, 2017, at 7:16 PM, Igor Korot <[hidden email]> wrote:
>
> And if the ROLLBACK fails?

Then AFAIK, something’s really wrong; treat it as a fatal error, probably.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to handle such situation

Igor Korot
Jens,
You mean like have some kind of flag and display an error on disconnect
only if not set?

Thank you.


On Oct 5, 2017 11:32 AM, "Jens Alfke" <[hidden email]> wrote:

>
>
> > On Oct 4, 2017, at 7:16 PM, Igor Korot <[hidden email]> wrote:
> >
> > And if the ROLLBACK fails?
>
> Then AFAIK, something’s really wrong; treat it as a fatal error, probably.
>
> —Jens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to handle such situation

Jens Alfke-2


> On Oct 5, 2017, at 8:35 AM, Igor Korot <[hidden email]> wrote:
>
> You mean like have some kind of flag and display an error on disconnect only if not set?

That’s up to you; it depends on how your program is structured. If you treat it as a fatal error, you don’t need to worry about closing the database since your process is going to exit anyway.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to handle such situation

Simon Slavin-3
In reply to this post by Igor Korot


On 5 Oct 2017, at 4:35pm, Igor Korot <[hidden email]> wrote:

> You mean like have some kind of flag and display an error on disconnect
> only if not set?

If ROLLBACK fails, it’s probably because of a hardware failure or your database being corrupt.  You can’t do anything else useful with the database connection since it has produced an unrecoverable error.

Display an error message to your user and quit immediately.  That way you don’t risk corrupting your dataset by doing more operations which assume that that ROLLBACK succeeded.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to handle such situation

Igor Korot
Simon
My question here is about ROLLBACK failure vs sqlite3_close() failure.
Like I said when the app closes I'm calling the latter and if it failed I
again display an error and just quit.

But if the former fails calling _close() does not make much sense, right?

Thank you.

On Oct 5, 2017 11:50 AM, "Simon Slavin" <[hidden email]> wrote:

>
>
> On 5 Oct 2017, at 4:35pm, Igor Korot <[hidden email]> wrote:
>
> > You mean like have some kind of flag and display an error on disconnect
> > only if not set?
>
> If ROLLBACK fails, it’s probably because of a hardware failure or your
> database being corrupt.  You can’t do anything else useful with the
> database connection since it has produced an unrecoverable error.
>
> Display an error message to your user and quit immediately.  That way you
> don’t risk corrupting your dataset by doing more operations which assume
> that that ROLLBACK succeeded.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to handle such situation

Simon Slavin-3


On 5 Oct 2017, at 6:42pm, Igor Korot <[hidden email]> wrote:

> My question here is about ROLLBACK failure vs sqlite3_close() failure.
> Like I said when the app closes I'm calling the latter and if it failed I
> again display an error and just quit.
>
> But if the former fails calling _close() does not make much sense, right?

If you get an unexpected failure on _execute() or on _step() then I don’t think you need to worry about calling _close().  Just quit.

The question of what _close() would actually do after an error probably depends on what the error was.  But since you can’t do anything if _close() fails I refuse to worry about it.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to handle such situation

Igor Korot
Hi Simon et al,
So I shouldn't card if _close() fail either way?
Just give an error and quit?

Thank you.


On Oct 5, 2017 2:01 PM, "Simon Slavin" <[hidden email]> wrote:



On 5 Oct 2017, at 6:42pm, Igor Korot <[hidden email]> wrote:

> My question here is about ROLLBACK failure vs sqlite3_close() failure.
> Like I said when the app closes I'm calling the latter and if it failed I
> again display an error and just quit.
>
> But if the former fails calling _close() does not make much sense, right?

If you get an unexpected failure on _execute() or on _step() then I don’t
think you need to worry about calling _close().  Just quit.

The question of what _close() would actually do after an error probably
depends on what the error was.  But since you can’t do anything if _close()
fails I refuse to worry about it.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to handle such situation

Keith Medcalf
In reply to this post by Igor Korot

Check your return codes.  They will usually tell you thr cause of yhe faiure.

<div>-------- Original message --------</div><div>From: Jens Alfke <[hidden email]> </div><div>Date:2017-10-05  09:32  (GMT-07:00) </div><div>To: SQLite mailing list <[hidden email]> </div><div>Cc: General Discussion of SQLite Database <[hidden email]> </div><div>Subject: Re: [sqlite] How to handle such situation </div><div>
</div>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to handle such situation

Keith Medcalf
In reply to this post by Igor Korot
The return code will tell you the cause of the failure  Love them.  Check them.  Every time.

<div>-------- Original message --------</div><div>From: Simon Slavin <[hidden email]> </div><div>Date:2017-10-05  09:50  (GMT-07:00) </div><div>To: SQLite mailing list <[hidden email]> </div><div>Subject: Re: [sqlite] How to handle such situation </div><div>
</div>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to handle such situation

Igor Korot
Hi Keith,

On Oct 6, 2017 12:59 PM, "Keith Medcalf" <[hidden email]> wrote:

The return code will tell you the cause of the failure  Love them.  Check
them.  Every time.


I'm checking them.
But if there is a ROLLBACK failure I will tell the user and when the app
will close sqlite3_close() will be called.
I guess I will just throw an error anyway and exit.

Thank you.

<div>-------- Original message --------</div><div>From: Simon Slavin <
[hidden email]> </div><div>Date:2017-10-05  09:50  (GMT-07:00)
</div><div>To: SQLite mailing list <[hidden email]>
</div><div>Subject: Re: [sqlite] How to handle such situation </div><div>
</div>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users