Re: [sqlite] Avoiding Database Is Locked Error

[Pavel Ivanov]

> In my last post I mentioned that I updated my programs so that I can
> execute an arbitrary query or update, thus eliminating the need to use
> the sqlite command line utility. There really is only one program
> accessing the database now.

Sorry, I've missed this detail. In this case you are safe indeed.


Pavel

On Thu, Jun 17, 2010 at 9:00 AM, Odekirk, Shawn
 wrote:
>>> I am not using BEGIN IMMEDIATE, just BEGIN, but I don't think it is a
>>> problem in my case, since now there really is just one program
> accessing
>>> the database.
>>
>>Did you forget the message this thread was started from? The sqlite3
>>command line utility is a second program, so this could be a problem.
>>
>>Pavel
>
> Thank you for taking the time to respond.
> No, I did not forget the message this thread was started from. I am the
> one who started the thread.
> In my last post I mentioned that I updated my programs so that I can
> execute an arbitrary query or update, thus eliminating the need to use
> the sqlite command line utility. There really is only one program
> accessing the database now.
> Please let me know if you still think that BEGIN IMMEDIATE is something
> I should be using.
> I am a newbie to sqlite and welcome any advice or suggestions.
>
> Thanks,
> Shawn
> ___
> 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] Avoiding Database Is Locked Error

[Odekirk, Shawn]

>> I am not using BEGIN IMMEDIATE, just BEGIN, but I don't think it is a
>> problem in my case, since now there really is just one program
accessing
>> the database.
>
>Did you forget the message this thread was started from? The sqlite3
>command line utility is a second program, so this could be a problem.
>
>Pavel

Thank you for taking the time to respond.
No, I did not forget the message this thread was started from. I am the
one who started the thread.
In my last post I mentioned that I updated my programs so that I can
execute an arbitrary query or update, thus eliminating the need to use
the sqlite command line utility. There really is only one program
accessing the database now.
Please let me know if you still think that BEGIN IMMEDIATE is something
I should be using.
I am a newbie to sqlite and welcome any advice or suggestions.

Thanks,
Shawn
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding Database Is Locked Error

[sub sk79]

> I'm running on SCO OpenServer so I'm not
sure your product would work for me but I'll take a look

StepSqlite compiler's Linux target generates shared objects (.so) that
should work on virtually all Unix-like systems thanks to ELF format.

As an aside, further in SQLite exploration you may find it convenient
to use SQLite loadable extensions
(http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions). StepSqlite
makes it a snap to generate a loadable extension; a simple radio
button choice to generate either a Loadable Extension or a Regular C++
library from *same* PL/SQL source.

-Swapnil Kashikar
support @ metatranz . com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding Database Is Locked Error

[Pavel Ivanov]

> I am not using BEGIN IMMEDIATE, just BEGIN, but I don't think it is a
> problem in my case, since now there really is just one program accessing
> the database.

Did you forget the message this thread was started from? The sqlite3
command line utility is a second program, so this could be a problem.


Pavel

On Wed, Jun 16, 2010 at 9:33 AM, Odekirk, Shawn
 wrote:
> Thank you all for your responses. This discussion has grown a lot more
> than I thought it would.
> Like I said in my original question, my system is made up of several
> programs that communicate by sending messages to each other. I have a
> utility program that can send messages to the programs for testing and
> debugging purposes. I have updated my utility program and the program
> that accesses the database so that I can send arbitrary SQL queries or
> update statements to the database program to execute. Now I don't need
> to use the command line SQLite tool and the locked database problem is
> no longer an issue.
> The discussion of transactions and interleaving queries with updates was
> really good information.
> I am (mis)using transactions not so much to group a unit of work that
> must succeed together, but more to improve the I/O performance by
> causing any updates to be written to disk at the end of the transaction.
> Writing each change individually to disk was taking too much time and
> wrapping things in a transaction reduced that time considerably.
> I am not using BEGIN IMMEDIATE, just BEGIN, but I don't think it is a
> problem in my case, since now there really is just one program accessing
> the database.
> I am probably interleaving queries and updates in at least one part of
> my program, but it doesn't seem to be causing any problems. I'll let you
> know if I get the 1:00 am hotline support call.
> Swapnil, thanks for the link. I'm running on SCO OpenServer so I'm not
> sure your product would work for me but I'll take a look. I was really
> looking for code examples that demonstrate best practices.
>
> Thanks again for all the comments, suggestions and good information.
>
> Shawn
>
>
>> Hi Shawn,
>>
>> > demonstrate SQLite best
>> > practices, including how to correctly handle error conditions, such
> as
>> > the database is locked condition?
>> > If you pre-select and then modify, you have to be aware enough to
>> > realize you MUST wrap the whole process in a manual transaction,
>> > and you still need to know how to deal with all the locking and
>> > busy issues that come with that.
>>
>> One way to assure use of best-practices in dealing with SQLite
>> nitty-gritties of transactions, locking and busy errors would be to
>> use a tool like StepSqlite PL/SQL compiler
>>
>> (https://www.metatranz.com/stepsqlite
>  ).
>>
>> It automates SQLite best-practices (as recommended by Jay, Pavel and
>> Simon in this thread) by wrapping things in transactions, handling
>> BUSY errors and retrying after delay and others like preparing all SQL
>> statements only once etc.
>> SQLite combined with a 4GL like StepSqlite PL/SQL lets you focus on
>> writing your code instead of having to learn to deal with SQLite
>> idiosyncrasies right at the beginning.
>>
>> Full disclosure: I am the creator of StepSqlite.
>>
>> -Swapnil Kashikar
>> support @ metatranz . com
> ___
> 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] Avoiding Database Is Locked Error

[Odekirk, Shawn]

Thank you all for your responses. This discussion has grown a lot more
than I thought it would.
Like I said in my original question, my system is made up of several
programs that communicate by sending messages to each other. I have a
utility program that can send messages to the programs for testing and
debugging purposes. I have updated my utility program and the program
that accesses the database so that I can send arbitrary SQL queries or
update statements to the database program to execute. Now I don't need
to use the command line SQLite tool and the locked database problem is
no longer an issue.
The discussion of transactions and interleaving queries with updates was
really good information.
I am (mis)using transactions not so much to group a unit of work that
must succeed together, but more to improve the I/O performance by
causing any updates to be written to disk at the end of the transaction.
Writing each change individually to disk was taking too much time and
wrapping things in a transaction reduced that time considerably.
I am not using BEGIN IMMEDIATE, just BEGIN, but I don't think it is a
problem in my case, since now there really is just one program accessing
the database.
I am probably interleaving queries and updates in at least one part of
my program, but it doesn't seem to be causing any problems. I'll let you
know if I get the 1:00 am hotline support call.
Swapnil, thanks for the link. I'm running on SCO OpenServer so I'm not
sure your product would work for me but I'll take a look. I was really
looking for code examples that demonstrate best practices.

Thanks again for all the comments, suggestions and good information.

Shawn


> Hi Shawn,
> 
> > demonstrate SQLite best
> > practices, including how to correctly handle error conditions, such
as
> > the database is locked condition?
> > If you pre-select and then modify, you have to be aware enough to
> > realize you MUST wrap the whole process in a manual transaction,
> > and you still need to know how to deal with all the locking and
> > busy issues that come with that.
> 
> One way to assure use of best-practices in dealing with SQLite
> nitty-gritties of transactions, locking and busy errors would be to
> use a tool like StepSqlite PL/SQL compiler
> 
> (https://www.metatranz.com/stepsqlite
 ).
> 
> It automates SQLite best-practices (as recommended by Jay, Pavel and
> Simon in this thread) by wrapping things in transactions, handling
> BUSY errors and retrying after delay and others like preparing all SQL
> statements only once etc.
> SQLite combined with a 4GL like StepSqlite PL/SQL lets you focus on
> writing your code instead of having to learn to deal with SQLite
> idiosyncrasies right at the beginning.
> 
> Full disclosure: I am the creator of StepSqlite.
> 
> -Swapnil Kashikar
> support @ metatranz . com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding Database Is Locked Error

[sub sk79]

Hi Shawn,

>  demonstrate SQLite best
>  practices, including how to correctly handle error conditions, such as
>  the database is locked condition?

>  If you pre-select and then modify, you have to be aware enough to
>  realize you MUST wrap the whole process in a manual transaction,
>  and you still need to know how to deal with all the locking and
>  busy issues that come with that.

One way to assure use of best-practices in dealing with SQLite
nitty-gritties of transactions, locking and busy errors would be to
use a tool like StepSqlite PL/SQL compiler
(https://www.metatranz.com/stepsqlite).
It automates SQLite best-practices (as recommended by Jay, Pavel and
Simon in this thread)  by wrapping things in transactions,  handling
BUSY errors and retrying after delay and others like preparing all SQL
statements only once etc.

SQLite combined with a 4GL like StepSqlite PL/SQL lets you focus on
writing your code instead of having to learn to deal with SQLite
idiosyncrasies right at the beginning.

Full disclosure: I am the creator of StepSqlite.

-Swapnil Kashikar
support @ metatranz . com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding Database Is Locked Error

[Jay A. Kreibich]

On Tue, Jun 15, 2010 at 04:36:15PM +0100, Simon Slavin scratched on the wall:
> 
> On 15 Jun 2010, at 4:23pm, Robert Latest wrote:
> 
> > Instinctively I'd rather first SELECT, store the
> > results, finalize the SELECT statement and then get to work on its
> > result using the stored data. It's just that without intermediate
> > storage it's a bit easier (no need to do any ressource management),
> > and I've found nothing in the docs that says I shouldn't do that.
> 
> On a standard desktop system where RAM is cheap, your principle
> of storing all the SELECT data before beginning your changes is sound. 

  Depends on the database, but in general I suppose that's true.

> Technically there's no reason not to interleave the _step() with
> changing the data, but it requires a detailed understanding of how
> SQLite works,

  Pre-selecting the data doesn't really simplify the situation.  You
  still need to deal with transactions and locking issues.  In
  specific, you need to wrap the whole process-- the read, store, and
  modify steps-- into a single transaction or you risk having the
  database change between the read and modify steps, possibly
  invalidating the modifications you're trying to make.

  Once you go that far, interleaving the select and modify commands is
  more or less the same thing.

> especially if the changes you make would have changed the result of
> the SELECT command. [1]

  OK, yeah, that's an issue.  There isn't any way to do this in a 
  deterministic way when using interleaved select/modify statements.
  
  Most people don't do that, however... If you need to modify the
  table you're scanning, you can usually roll the whole thing up
  into one very large and complex update command.  

> Storing all the SELECT data before making your changes means you
> don't have to understand these technicalities,

  I disagree with this.  Doing the pre-select doesn't get rid of any of
  the transaction issues.  In fact, it makes it worse.  If you
  interleave the commands, the select's autocommit transaction protects
  the whole process.  If the developer never issues or thinks about a
  transaction, it all works correctly and safely.
  
  If you pre-select and then modify, you have to be aware enough to
  realize you MUST wrap the whole process in a manual transaction,
  and you still need to know how to deal with all the locking and
  busy issues that come with that.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding Database Is Locked Error

[Simon Slavin]

On 15 Jun 2010, at 4:23pm, Robert Latest wrote:

> Instinctively I'd rather first SELECT, store the
> results, finalize the SELECT statement and then get to work on its
> result using the stored data. It's just that without intermediate
> storage it's a bit easier (no need to do any ressource management),
> and I've found nothing in the docs that says I shouldn't do that.

On a standard desktop system where RAM is cheap, your principle of storing all 
the SELECT data before beginning your changes is sound.  Technically there's no 
reason not to interleave the _step() with changing the data, but it requires a 
detailed understanding of how SQLite works, especially if the changes you make 
would have changed the result of the SELECT command. [1] Storing all the SELECT 
data before making your changes means you don't have to understand these 
technicalities, and it ensures that your code is engine-agnostic in case you 
ever have to convert to using a different SQL engine.

[1] I did a quick google for documentation on this but didn't find anything.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding Database Is Locked Error

[Robert Latest]

Hello all,

> Oh, I completely forgot that people can do that. So, Robert, you case
> is exactly the case I was talking about. As Simon said your SELECT
> opens read-only transaction and then as you issue your first UPDATE
> this transaction have to be converted to writing one. This is a call
> for problems. So you better to issue "BEGIN IMMEDIATE" before you
> execute your SELECT statement.
>
> BTW, I hope you don't change the table you selected in this scenario?

I'm not sure right now, but I think I've done that every now and then.
With a bad feeling. Instinctively I'd rather first SELECT, store the
results, finalize the SELECT statement and then get to work on its
result using the stored data. It's just that without intermediate
storage it's a bit easier (no need to do any ressource management),
and I've found nothing in the docs that says I shouldn't do that.

robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding Database Is Locked Error

[Pavel Ivanov]

>> Do you mean you're making changes as you call SQLite to _step() through the
>> results of the SELECT ?  Or do you read all the results of the SELECT into
>> memory, then make changes to the database ?
>
> The former.

Oh, I completely forgot that people can do that. So, Robert, you case
is exactly the case I was talking about. As Simon said your SELECT
opens read-only transaction and then as you issue your first UPDATE
this transaction have to be converted to writing one. This is a call
for problems. So you better to issue "BEGIN IMMEDIATE" before you
execute your SELECT statement.

BTW, I hope you don't change the table you selected in this scenario?


Pavel

On Mon, Jun 14, 2010 at 10:43 AM, Robert Latest
 wrote:
> On Mon, Jun 14, 2010 at 4:36 PM, Simon Slavin  wrote:
>
>> Do you mean you're making changes as you call SQLite to _step() through the
>> results of the SELECT ?  Or do you read all the results of the SELECT into
>> memory, then make changes to the database ?
>
> The former.
> robert
> ___
> 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] Avoiding Database Is Locked Error

[Simon Slavin]

On 14 Jun 2010, at 3:43pm, Robert Latest wrote:

> On Mon, Jun 14, 2010 at 4:36 PM, Simon Slavin  wrote:
> 
>> Do you mean you're making changes as you call SQLite to _step() through the
>> results of the SELECT ?  Or do you read all the results of the SELECT into
>> memory, then make changes to the database ?
> 
> The former.

Right.  Well, the SELECT command is creating its own TRANSACTION, and that 
TRANSACTION lasts until you have finished stepping through its response.  So 
that needs to be considered as a reason for your LOCKED, as if you have two 
simultaneous database users.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding Database Is Locked Error

[Jay A. Kreibich]

On Mon, Jun 14, 2010 at 09:42:09AM -0400, Pavel Ivanov scratched on the wall:
> > This is interesting. I often have situations where I SELECT something
> > and then do manipulations (INSERT/DELETE/UPDATE) on the db as I
> > iterate through the results.
> >
> > Is this what you mean by your statement? If yes, how should such
> > situations be avoided, and why?

> If you don't issue BEGIN then your
> SELECT and UPDATE are executed in different transactions

  I'm not sure that's true...  A database connection typically has only
  one transaction state.  Locks, etc., are owned by the connection, not
  by a statement.

  The SELECT is going to create a read-only autocommit transaction.  If
  a write statements are intermixed, they will need to establish an
  autocommit write transaction.  I'm fairly sure this simply upgrades
  the existing autocommit transaction, rather than creating a different
  logical transaction.  Once the write statement finishes, I would
  assume the still-active autocommit transaction remains at the
  elevated level, since there is no way to "degrade" a transaction
  without committing it.  The initial autocommit transaction won't
  actually commit and close until the all active statements are fully
  reset or finalized.

  In short, it will be the same as putting a BEGIN/END around the
  where the SELECT is processed in the code.


  I think.  I have to admit that this gets into some gray areas on
  autocommit transactions that I don't know all that well.  
  
  I do know is that intermixing modifications while walking through
  a SELECT has always worked exactly the way I expected.

   -j



-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding Database Is Locked Error

[Robert Latest]

On Mon, Jun 14, 2010 at 4:36 PM, Simon Slavin  wrote:

> Do you mean you're making changes as you call SQLite to _step() through the
> results of the SELECT ?  Or do you read all the results of the SELECT into
> memory, then make changes to the database ?

The former.
robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding Database Is Locked Error

[Simon Slavin]

On 14 Jun 2010, at 1:44pm, Robert Latest wrote:

> This is interesting. I often have situations where I SELECT something
> and then do manipulations (INSERT/DELETE/UPDATE) on the db as I
> iterate through the results.

Do you mean you're making changes as you call SQLite to _step() through the 
results of the SELECT ?  Or do you read all the results of the SELECT into 
memory, then make changes to the database ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding Database Is Locked Error

[Robert Latest]

Thanks, your and Pavel's clarifications have been very helpful.
robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding Database Is Locked Error

[Jay A. Kreibich]

On Mon, Jun 14, 2010 at 02:44:02PM +0200, Robert Latest scratched on the wall:
> On Fri, Jun 11, 2010 at 5:26 PM, Pavel Ivanov  wrote:
> 
> > 1. Ensure that you have no transactions started with SELECT and
> > continued with INSERT/DELETE/UPDATE
> 
> This is interesting. I often have situations where I SELECT something
> and then do manipulations (INSERT/DELETE/UPDATE) on the db as I
> iterate through the results.
> 
> Is this what you mean by your statement? If yes, how should such
> situations be avoided, and why?

  Not so much... the issue is that if you start a transaction, do a
  bunch of selects (e.g. read-only) and then modify the database in
  some way, the transaction needs to acquire the write lock in order to
  proceed.  If there are going to be locking issues, this is where they
  will manifest themselves.  This is also the specific situation that
  can lead to a deadlock, so you're only real choice is to rollback the
  transaction.

  On the practical side, this shouldn't be a big deal... The transaction
  should be read-only at that point, so rolling back the transaction
  doesn't actually change the database or undo any of the "work" done
  by the application.  As an extension of that, the program logic
  should be fairly easy to deal with, since no actual changes have
  been made.

  But that assumes the program flow and control is designed to handle
  that.  If the application design expects to do a "unit of work" and
  that unit just happens to have a bunch of read stuff before it gets
  to the write stuff, you're still breaking that unit of work in the
  middle.  Depending on the code design, that can be a tricky error
  condition to handle.

  Possible solutions include opening the transaction with a pre-acquired
  reserved lock (or exclusive lock).  That avoids the mid-way
  transition.  There can still be issues, but they'll happen at the
  very beginning of the transaction, when it is usually easier to
  restart.

  Another possibility is to break the work up into two steps-- do all
  the read work, then open the transaction and do all the write work. 
  That can be risky if the two parts are related, however.  Read-only
  transactions are still useful, in that they keep the database from
  changing while you're looking up keys or something critical.


  But your general approach of looping over a select and doing other
  things is completely valid.  You just need to be ready to handle a
  busy condition at the first modification.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding Database Is Locked Error

[Pavel Ivanov]

> This is interesting. I often have situations where I SELECT something
> and then do manipulations (INSERT/DELETE/UPDATE) on the db as I
> iterate through the results.
>
> Is this what you mean by your statement? If yes, how should such
> situations be avoided, and why?

If you issue BEGIN statement before you do your SELECT then yes,
that's what I'm talking about (and I said how to avoid it - issue
BEGIN IMMEDIATE instead of BEGIN). If you don't issue BEGIN then your
SELECT and UPDATE are executed in different transactions (are you
aware of some races that can happen that way?), so my statement is not
applicable.


Pavel

On Mon, Jun 14, 2010 at 8:44 AM, Robert Latest  wrote:
> On Fri, Jun 11, 2010 at 5:26 PM, Pavel Ivanov  wrote:
>
>> 1. Ensure that you have no transactions started with SELECT and
>> continued with INSERT/DELETE/UPDATE
>
> This is interesting. I often have situations where I SELECT something
> and then do manipulations (INSERT/DELETE/UPDATE) on the db as I
> iterate through the results.
>
> Is this what you mean by your statement? If yes, how should such
> situations be avoided, and why?
>
> Regards,
> robert
> ___
> 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] Avoiding Database Is Locked Error

[Robert Latest]

On Fri, Jun 11, 2010 at 5:26 PM, Pavel Ivanov  wrote:

> 1. Ensure that you have no transactions started with SELECT and
> continued with INSERT/DELETE/UPDATE

This is interesting. I often have situations where I SELECT something
and then do manipulations (INSERT/DELETE/UPDATE) on the db as I
iterate through the results.

Is this what you mean by your statement? If yes, how should such
situations be avoided, and why?

Regards,
robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding Database Is Locked Error

[Simon Slavin]

On 11 Jun 2010, at 4:33pm, Jay A. Kreibich wrote:

>  For what it is worth, "Using SQLite" has four or five pages
>  specifically dealing with SQLITE_BUSY errors-- both how to avoid them
>  and how to correctly handle them.

Well, that somewhat increased the chances that I'll check out that book.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding Database Is Locked Error

[Jay A. Kreibich]

On Fri, Jun 11, 2010 at 04:04:59PM +0100, Simon Slavin scratched on the wall:
> 
> On 11 Jun 2010, at 2:50pm, Odekirk, Shawn wrote:
> 
> > Also, are there any code examples that demonstrate SQLite best
> > practices, including how to correctly handle error conditions, such as
> > the database is locked condition? I have been unable to find complete
> > code examples.
> 
> Can I point out that this is yet another person asking for the same
> documentation ?  I don't know enough about SQLite to write it, and
> I would very much like to see someone do so.


  For what it is worth, "Using SQLite" has four or five pages
  specifically dealing with SQLITE_BUSY errors-- both how to avoid them
  and how to correctly handle them.  This section was heavily revised
  in the final editing pass, largely because of the questions on this
  mailing list.

  I can no longer provide a reference, however.  The book has entered
  final production and the online version has been removed from
  O'Reilly's feedback site.



  http://oreilly.com/catalog/9780596521196/
  http://www.amazon.com/Using-SQLite-Jay-Kreibich/dp/0596521189/

   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding Database Is Locked Error

[Pavel Ivanov]

>> Also, are there any code examples that demonstrate SQLite best
>> practices, including how to correctly handle error conditions, such as
>> the database is locked condition? I have been unable to find complete
>> code examples.
>
> Can I point out that this is yet another person asking for the same 
> documentation ?  I don't know enough about SQLite to write it, and I would 
> very much like to see someone do so.

I'd say there's no "best practices" here. Or let's say it another way:
code experiencing "best practices" will be useless in the majority of
use cases. And that kind of code is described in documentation and
repeated on this list several times. I can repeat it once more:
whenever you see "database locked" error rollback your transaction
immediately and depending on your application start it from the
beginning or return error message to the user. This will always work
but it's not very convenient in many cases.

SQLite is that kind of software that has pretty raw reaction on most
events. And all processing of SQLite result codes depends on the
application structure and its use cases. So SQLite user should be well
aware of everything that's happening in his application or can happen
outside (like command line tool) and implement appropriate reactions
accordingly.

For your particular case, Shawn, you can just do 3 things:
1. Ensure that you have no transactions started with SELECT and
continued with INSERT/DELETE/UPDATE (if there are such then start them
with BEGIN IMMEDIATE instead of BEGIN).
2. Set busy_timeout to some reasonable value (let's say 10 seconds).
3. When you work with the command line tool experience extreme
carefulness and always keep in mind how your actions can impact your
application: how long do you keep your transactions uncommitted or how
long your SELECT statements are executed. All that times shouldn't be
longer than busy_timeout you have set in the application.

With all that you won't see "database locked" inside the application.


Pavel

On Fri, Jun 11, 2010 at 11:04 AM, Simon Slavin  wrote:
>
> On 11 Jun 2010, at 2:50pm, Odekirk, Shawn wrote:
>
>> Also, are there any code examples that demonstrate SQLite best
>> practices, including how to correctly handle error conditions, such as
>> the database is locked condition? I have been unable to find complete
>> code examples.
>
> Can I point out that this is yet another person asking for the same 
> documentation ?  I don't know enough about SQLite to write it, and I would 
> very much like to see someone do so.
>
> Simon.
> ___
> 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] Avoiding Database Is Locked Error

[Simon Slavin]

On 11 Jun 2010, at 2:50pm, Odekirk, Shawn wrote:

> Also, are there any code examples that demonstrate SQLite best
> practices, including how to correctly handle error conditions, such as
> the database is locked condition? I have been unable to find complete
> code examples.

Can I point out that this is yet another person asking for the same 
documentation ?  I don't know enough about SQLite to write it, and I would very 
much like to see someone do so.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users