Re: [sqlite] database is locked

[Simon Slavin]

On 20 Oct 2018, at 1:19pm, p...@geniais.com wrote:

> I'm using exec() to simply get true/false result (just reading) and I'm 
> receiving this error, even in WAL mode:
> SQLite3::exec(): database is locked in ... on line 47
> 
> Any help will be apreciated

I'm assuming that you are aware that something else is acessing the same 
database at the same time.

Set a timeout using either of these:



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

[sqlite] database is locked

[p...@geniais.com]

Hello,

I'm using exec() to simply get true/false result (just reading) and I'm 
receiving this error, even in WAL mode:

SQLite3::exec(): database is locked in ... on line 47

Any help will be apreciated

Thank you

Ismael


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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Chris Locke]

> For the record, "delete the journal file" is terrible advice

Agreed.  In normal production environment, I wouldn't suggest that.  The
user was testing a database, and in my own developemtn cycle, its common
when developing for a database to be in all manners of chaos states.  It
was purely a 'gotcha' that has caught me out before - a journal file
lingers and locks the system.


On Mon, Oct 15, 2018 at 4:03 AM Rowan Worth  wrote:

> On Sat, 13 Oct 2018 at 00:21, Chris Locke 
> wrote:
>
> > > Database is locked
> >
> > Close your application.  Is there a xxx-journal file in the same
> directory
> > as the database? (where xxx is the name of the database)
> > Try deleting this file.
> >
>
> For the record, "delete the journal file" is terrible advice and a great
> way to corrupt a database. In the case where a program crashes
> mid-transaction, the journal contains information which is crucial for
> recovering to a correct database state. And in non-crash scenarios, the
> journal should be cleaned up¹. So when you can see a journal file it's
> likely that either:
>
> 1. some program is currently using the DB, or
> 2. there was a crash mid-transaction
>
> Either way, deleting the journal is a wrong move.
>
> ¹ unless the DB is configured with PRAGMA journal_mode set to TRUNCATE or
> PERSIST, in which case you've asked for the rollback journal to linger
> around.
>
> -Rowan
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[R Smith]

On 2018/10/15 5:05 AM, Rowan Worth wrote:

On Sat, 13 Oct 2018 at 02:20, Lars Frederiksen  wrote:


Any ideas?


Not sure if this got resolved off-list, but based on the code you've posted
I wonder if there is an "fdqGloser" as well as an "fdqGloser2", and if so
what state it is in.


Update: This was indeed resolved off-list.
Thanks for the added thoughts.

The problem turned out to be related to FireDAC automagic and moving 
some DB connecting/opening code out of the form's OnCreate and into a 
more sensible event call did the trick.


SQLite was innocent. :)


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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Rowan Worth]

On Sat, 13 Oct 2018 at 02:20, Lars Frederiksen  wrote:

> I type info into some labeledits and by pressing "Append to DB" button
> this code is executed (fdqGoser2 is a FDQuery)
>
>   fdqGloser2.Open;
>   fdqGloser2.Append;
>   fdqGloser2.FieldByName('Graesk').AsString:= ledGræsk.Text;
> ...
>   fdqGloser2.FieldByName('Graesk_ren').AsString:=
> Trykløs_SmåBogstaver(fdqGloser2.FieldByName('Graesk').AsString);
>   fdqGloser2.Post; [ here the error is triggered]
>
> I wonder if some kind of property i fdqGloser2 is set wrong. I think the
> only thing I have set i SQL ('SELECT * FROM Gloser2').
>
> Any ideas?
>

Not sure if this got resolved off-list, but based on the code you've posted
I wonder if there is an "fdqGloser" as well as an "fdqGloser2", and if so
what state it is in.

It reads like you're trying to update the database with fdqGloser2 -
another reason this would fail with "database is locked" is if you're
holding a separate read transaction open.

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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Rowan Worth]

On Sat, 13 Oct 2018 at 00:21, Chris Locke  wrote:

> > Database is locked
>
> Close your application.  Is there a xxx-journal file in the same directory
> as the database? (where xxx is the name of the database)
> Try deleting this file.
>

For the record, "delete the journal file" is terrible advice and a great
way to corrupt a database. In the case where a program crashes
mid-transaction, the journal contains information which is crucial for
recovering to a correct database state. And in non-crash scenarios, the
journal should be cleaned up¹. So when you can see a journal file it's
likely that either:

1. some program is currently using the DB, or
2. there was a crash mid-transaction

Either way, deleting the journal is a wrong move.

¹ unless the DB is configured with PRAGMA journal_mode set to TRUNCATE or
PERSIST, in which case you've asked for the rollback journal to linger
around.

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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[R Smith]

On 2018/10/12 8:19 PM, Lars Frederiksen wrote:

I type info into some labeledits and by pressing "Append to DB" button this 
code is executed (fdqGoser2 is a FDQuery)

   fdqGloser2.Open;
   fdqGloser2.Append;
   fdqGloser2.FieldByName('Graesk').AsString:= ledGræsk.Text;
   fdqGloser2.FieldByName('Dansk').AsString:= ledDansk.Text;
   fdqGloser2.FieldByName('Lektion').AsString:= ledLektion.Text;
   fdqGloser2.FieldByName('Note').AsString:= ledNote.Text;
   fdqGloser2.FieldByName('Kategori').AsString:= ledKategori.Text;
   fdqGloser2.FieldByName('Langenscheidt').AsString:= ledLangenscheidt.Text;
   fdqGloser2.FieldByName('Ordtype').AsString:= ledOrdtype.Text;
   fdqGloser2.FieldByName('Boejning').AsString:= ledBøjning.Text;
   fdqGloser2.FieldByName('Graesk_ren').AsString:= 
Trykløs_SmåBogstaver(fdqGloser2.FieldByName('Graesk').AsString);
   fdqGloser2.Post; [ here the error is triggered]

I wonder if some kind of property i fdqGloser2 is set wrong. I think the only 
thing I have set i SQL ('SELECT * FROM Gloser2').

Any ideas?


This is a kind of novice way to do it, but should work fine (I'm not 
judging, it's just that I'm not 100% sure if Append starts a transaction 
or not, or if there is another command to follow Append after the field 
values are set, like AddRecord or CloseRecord or some such, before Post 
can be called - Prefer doing these lower down and have some control over 
it, but happy to do a little trouble-shooting).


What is fdqGloser2?    I mean what is its Delphi Type? (T..something... )

I'm guessing fdq is for Fire-DAC-Query, which makes it seem like this is 
from some tutorial.


Actually, this question is veering further and further away from an 
SQLite question - perhaps we should not spam everyone and move off-list 
(along with whomever wish to join).
Send me off-list the entire Unit perhaps and the DB file (at least the 
schema if the content is sensitive), then I can have a look at what goes 
wrong.
If someone else is interested in contributing to this, send me a mail 
too to include in the reply chain.




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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Lars Frederiksen]

I type info into some labeledits and by pressing "Append to DB" button this 
code is executed (fdqGoser2 is a FDQuery)

  fdqGloser2.Open;
  fdqGloser2.Append;
  fdqGloser2.FieldByName('Graesk').AsString:= ledGræsk.Text;
  fdqGloser2.FieldByName('Dansk').AsString:= ledDansk.Text;
  fdqGloser2.FieldByName('Lektion').AsString:= ledLektion.Text;
  fdqGloser2.FieldByName('Note').AsString:= ledNote.Text;
  fdqGloser2.FieldByName('Kategori').AsString:= ledKategori.Text;
  fdqGloser2.FieldByName('Langenscheidt').AsString:= ledLangenscheidt.Text;
  fdqGloser2.FieldByName('Ordtype').AsString:= ledOrdtype.Text;
  fdqGloser2.FieldByName('Boejning').AsString:= ledBøjning.Text;
  fdqGloser2.FieldByName('Graesk_ren').AsString:= 
Trykløs_SmåBogstaver(fdqGloser2.FieldByName('Graesk').AsString);
  fdqGloser2.Post; [ here the error is triggered]

I wonder if some kind of property i fdqGloser2 is set wrong. I think the only 
thing I have set i SQL ('SELECT * FROM Gloser2').

Any ideas?

/Lars

-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Stephen Chrzanowski
Sendt: 12. oktober 2018 19:47
Til: General Discussion of SQLite Database
Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

FSCK is File System ChecK, so the equivalent in Windows Scandisk.

Trace through the code to find out when exactly the SQLite is reporting the
lock.  Is it at a query, on opening, is it multi-thread or
multi-application access?  Is the query taking too long?  If you're
debugging and everything is working through the transactions, I'd look at
how you're setting the timeout and maybe it isn't being passed through
correctly.  As mentioned, depending on the size of the DB, your AV may be
holding on and doing a scan of the file that is much longer than the 5
seconds.  Set breakpoints at every point where you do something with SQL,
then first step-over the break point to validate that specific function
isn't causing the lock, then resume until the next break point.  When/if
you do hit a break point that causes a lock, restart the app, then
step-into the function and see where the lock is happening.

Transactions may also cause some grief, but I defer to those with more
experience on that.  I know that there are blocks in play for writes, not
100% sure if you open a transaction if you'll get a locked message from
another call.

Another thing you could do is, again depending on the size of your database
and memory constraints, is "backup" the database to memory, if you're only
using a single-thread & single-application, this should be valid.

For a short bit, turn off all your AV software, or, get an AV free OS up
and running to test your application on.  Disconnect your Ethernet if
you're truly paranoid about getting a rogue application hitting your
machine while you've got AV off.  (Been there with Melissa Virus)
Another option is start up a new application and just have it do some data
massaging on a scrap database.  SQLite Expert I think has a demo DB you can
play with, or, just start from scratch with a single file, single table
database.  If your new application can do work without your AV having a
problem, then I'd be looking at what exactly your code is doing.  I'm sure
there's a SQLite3 Northwind database here:
https://github.com/jpwhite3/northwind-SQLite3

I use the free version of Berlin and Tokyo, and I've never had a lock issue
that couldn't be explained by another process taking too long with the
data.  I typically write single-threaded applications, but sometimes there
are multiple applications touching the database.  (IE: SQLite Expert;
sqlite3.exe, my applications, etc).  I've only ever run into database
locked issues is when my app is writing the data pretty quickly to the DB
and SQLite Expert runs a query.  I'll get a timeout somewhere, but its
easily explained.  Expanding the timeout on both applications usually fixes
the locked issue.

On Fri, Oct 12, 2018 at 1:20 PM Lars Frederiksen  wrote:

> Thank you for your response. To answer your questions:
>


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

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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Keith Medcalf]

This would seem to imply that BitDefender is not the cause of your woes.  Do 
you have write permission on the database file?


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Lars Frederiksen
>Sent: Friday, 12 October, 2018 06:26
>To: 'SQLite mailing list'
>Subject: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database
>is locked
>
>That is: I have disabled ALL protecting parameters in BitDefender to
>find the culprit module - without luck.
>
>-Oprindelig meddelelse-
>Fra: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] På vegne af Radovan Antloga
>Sendt: 12. oktober 2018 10:10
>Til: sqlite-users@mailinglists.sqlite.org
>Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is
>locked
>
>Try to change database name (extension).
>
>For example:
>mydb.sqlite
>mydb.sdb
>mydb.db3
>
>I would use .sqlite extension and exclude that
>files from antivirus check.
>
>Regards
>
>Lars Frederiksen je 12.10.2018 ob 9:11 napisal:
>> Hello,
>>
>> I have made (in Delphi) a program that writes to a sqlite database.
>This is
>> a simple stand-alone solution. Now something is blocking for the
>programs
>> attempt to writing to the databse. There is a an error message:
>> [FireDAC][Phys][SQLite] Database is locked.
>>
>> A friend of mine has analysed the scenario and tells me that
>BitDefender is
>> the Problem.  In BitDefender "Application Access" I have given my
>program
>> permission to "Work".
>>
>> Any solution on how to "tell" BitDefender that it is OK to make
>changes in
>> my database? (I have tried BitDefender support - but no ticket
>answer!)
>>
>> Maybe also somebody knows an antivirus/anti-malware solution which
>are not
>> so "hard" as BitDefender? (and with a userfriendly GUI).
>>
>> Thank you so much for any help. I am rather "desperate" for some
>help!
>>
>> Best Regards
>>
>> Lars
>>
>>
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Stephen Chrzanowski]

FSCK is File System ChecK, so the equivalent in Windows Scandisk.

Trace through the code to find out when exactly the SQLite is reporting the
lock.  Is it at a query, on opening, is it multi-thread or
multi-application access?  Is the query taking too long?  If you're
debugging and everything is working through the transactions, I'd look at
how you're setting the timeout and maybe it isn't being passed through
correctly.  As mentioned, depending on the size of the DB, your AV may be
holding on and doing a scan of the file that is much longer than the 5
seconds.  Set breakpoints at every point where you do something with SQL,
then first step-over the break point to validate that specific function
isn't causing the lock, then resume until the next break point.  When/if
you do hit a break point that causes a lock, restart the app, then
step-into the function and see where the lock is happening.

Transactions may also cause some grief, but I defer to those with more
experience on that.  I know that there are blocks in play for writes, not
100% sure if you open a transaction if you'll get a locked message from
another call.

Another thing you could do is, again depending on the size of your database
and memory constraints, is "backup" the database to memory, if you're only
using a single-thread & single-application, this should be valid.

For a short bit, turn off all your AV software, or, get an AV free OS up
and running to test your application on.  Disconnect your Ethernet if
you're truly paranoid about getting a rogue application hitting your
machine while you've got AV off.  (Been there with Melissa Virus)
Another option is start up a new application and just have it do some data
massaging on a scrap database.  SQLite Expert I think has a demo DB you can
play with, or, just start from scratch with a single file, single table
database.  If your new application can do work without your AV having a
problem, then I'd be looking at what exactly your code is doing.  I'm sure
there's a SQLite3 Northwind database here:
https://github.com/jpwhite3/northwind-SQLite3

I use the free version of Berlin and Tokyo, and I've never had a lock issue
that couldn't be explained by another process taking too long with the
data.  I typically write single-threaded applications, but sometimes there
are multiple applications touching the database.  (IE: SQLite Expert;
sqlite3.exe, my applications, etc).  I've only ever run into database
locked issues is when my app is writing the data pretty quickly to the DB
and SQLite Expert runs a query.  I'll get a timeout somewhere, but its
easily explained.  Expanding the timeout on both applications usually fixes
the locked issue.

On Fri, Oct 12, 2018 at 1:20 PM Lars Frederiksen  wrote:

> Thank you for your response. To answer your questions:
>


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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Lars Frederiksen]

Thank you for your response. To answer your questions:

Ryan: 
It's sad to see someone get dismayed with what is usually a flawless working 
system. It's not the database that's tricky, it's the reason the file is 
locked. This would be a problem with a string based file too.
- I agree Ryan, but I get SO totally frustrated [and locked myself :-)] not 
being able to go on with my favorite hobby!!!

Do you have it open in any DB manager or other CLI or such?
- I have just tried SQLite Manager and in fact I am able to edit in the db 
through this program

What's the actual path of the folder containing the file on your drive?
- My database (called flashcard.db) is placed in a subfolder to %programdata% 
(c:\programdata\my name\flashcard_data

This leaves other possible culprits as Windows Virtualization 
- Personally I do not know much about Windows Virtualization. I will try a 
search!!
and possible other programs/processes on your machine locking the DB file.
- I am totally empty about what kind of program that might be??

Thomas:
Could the problem arise due to filesystem corruption? Have you tried an fsck?
- no I have not tried an fsck [is this not a Linux prg that checks the files?]

Chris:
Close your application.  
Is there a xxx-journal file in the same directory as the database? (where xxx 
is the name of the database) Try deleting this file.
- there is no such file in the database directory


Best Regards
Lars



-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Chris Locke
Sendt: 12. oktober 2018 18:22
Til: SQLite mailing list
Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

> Database is locked

Close your application.  Is there a xxx-journal file in the same directory
as the database? (where xxx is the name of the database)
Try deleting this file.


Thanks,
Chris



On Fri, Oct 12, 2018 at 4:54 PM Thomas Kurz  wrote:
>
>> Could the problem arise due to filesystem corruption? Have you tried an
>> fsck?
>>
>>
>> - Original Message -
>> From: R Smith 
>> To: sqlite-users@mailinglists.sqlite.org <
>> sqlite-users@mailinglists.sqlite.org>
>> Sent: Friday, October 12, 2018, 17:35:28
>> Subject: [sqlite] error message: [FireDAC][Phys][SQLite] Database is
>> locked
>>
>> On 2018/10/12 2:44 PM, Lars Frederiksen wrote:
>> > I have tried that too! - I must admit that right now I am turning to a
>> KISS solution: String-based database with functions and procedures in a
>> class that handles the different jobs you do on a table. After all these
>> old-fashioned DB's are not so picky :-)
>>
>> It's sad to see someone get dismayed with what is usually a flawless
>> working system.
>> It's not the database that's tricky, it's the reason the file is locked.
>> This would be a problem with a string based file too.
>>
>> Reading the above, I'm no longer convinced it's your anti-virus' fault
>> either.
>>
>> Using Delphi FireDAC quite a bit, I can promise you that its SQLite
>> connectivity is working just fine, and while an Antivirus can lock a
>> file for the time it takes to check it, unless your database is
>> Gigabytes in size, that lock should be released within milliseconds,
>> unless found to be unsafe - but then the log should speak of it (which I
>> assume it doesn't).
>>
>> This leaves other possible culprits as Windows Virtualization and
>> possible other programs/processes on your machine locking the DB file.
>> Do you have it open in any DB manager or other CLI or such?
>> What's the actual path of the folder containing the file on your drive?
>>
>>
>> Cheers,
>> Ryan
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Chris Locke]

> Database is locked

Close your application.  Is there a xxx-journal file in the same directory
as the database? (where xxx is the name of the database)
Try deleting this file.


Thanks,
Chris



On Fri, Oct 12, 2018 at 4:54 PM Thomas Kurz  wrote:
>
>> Could the problem arise due to filesystem corruption? Have you tried an
>> fsck?
>>
>>
>> - Original Message -
>> From: R Smith 
>> To: sqlite-users@mailinglists.sqlite.org <
>> sqlite-users@mailinglists.sqlite.org>
>> Sent: Friday, October 12, 2018, 17:35:28
>> Subject: [sqlite] error message: [FireDAC][Phys][SQLite] Database is
>> locked
>>
>> On 2018/10/12 2:44 PM, Lars Frederiksen wrote:
>> > I have tried that too! - I must admit that right now I am turning to a
>> KISS solution: String-based database with functions and procedures in a
>> class that handles the different jobs you do on a table. After all these
>> old-fashioned DB's are not so picky :-)
>>
>> It's sad to see someone get dismayed with what is usually a flawless
>> working system.
>> It's not the database that's tricky, it's the reason the file is locked.
>> This would be a problem with a string based file too.
>>
>> Reading the above, I'm no longer convinced it's your anti-virus' fault
>> either.
>>
>> Using Delphi FireDAC quite a bit, I can promise you that its SQLite
>> connectivity is working just fine, and while an Antivirus can lock a
>> file for the time it takes to check it, unless your database is
>> Gigabytes in size, that lock should be released within milliseconds,
>> unless found to be unsafe - but then the log should speak of it (which I
>> assume it doesn't).
>>
>> This leaves other possible culprits as Windows Virtualization and
>> possible other programs/processes on your machine locking the DB file.
>> Do you have it open in any DB manager or other CLI or such?
>> What's the actual path of the folder containing the file on your drive?
>>
>>
>> Cheers,
>> Ryan
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Thomas Kurz]

Could the problem arise due to filesystem corruption? Have you tried an fsck?


- Original Message - 
From: R Smith 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Friday, October 12, 2018, 17:35:28
Subject: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

On 2018/10/12 2:44 PM, Lars Frederiksen wrote:
> I have tried that too! - I must admit that right now I am turning to a KISS 
> solution: String-based database with functions and procedures in a class that 
> handles the different jobs you do on a table. After all these old-fashioned 
> DB's are not so picky :-)

It's sad to see someone get dismayed with what is usually a flawless 
working system.
It's not the database that's tricky, it's the reason the file is locked. 
This would be a problem with a string based file too.

Reading the above, I'm no longer convinced it's your anti-virus' fault 
either.

Using Delphi FireDAC quite a bit, I can promise you that its SQLite 
connectivity is working just fine, and while an Antivirus can lock a 
file for the time it takes to check it, unless your database is 
Gigabytes in size, that lock should be released within milliseconds, 
unless found to be unsafe - but then the log should speak of it (which I 
assume it doesn't).

This leaves other possible culprits as Windows Virtualization and 
possible other programs/processes on your machine locking the DB file. 
Do you have it open in any DB manager or other CLI or such?
What's the actual path of the folder containing the file on your drive?


Cheers,
Ryan

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

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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[R Smith]

On 2018/10/12 2:44 PM, Lars Frederiksen wrote:

I have tried that too! - I must admit that right now I am turning to a KISS 
solution: String-based database with functions and procedures in a class that 
handles the different jobs you do on a table. After all these old-fashioned 
DB's are not so picky :-)


It's sad to see someone get dismayed with what is usually a flawless 
working system.
It's not the database that's tricky, it's the reason the file is locked. 
This would be a problem with a string based file too.


Reading the above, I'm no longer convinced it's your anti-virus' fault 
either.


Using Delphi FireDAC quite a bit, I can promise you that its SQLite 
connectivity is working just fine, and while an Antivirus can lock a 
file for the time it takes to check it, unless your database is 
Gigabytes in size, that lock should be released within milliseconds, 
unless found to be unsafe - but then the log should speak of it (which I 
assume it doesn't).


This leaves other possible culprits as Windows Virtualization and 
possible other programs/processes on your machine locking the DB file. 
Do you have it open in any DB manager or other CLI or such?

What's the actual path of the folder containing the file on your drive?


Cheers,
Ryan

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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Radovan Antloga]

Try this:

1. check file atribute if it is not read only
  maybe is not BitDefender

2. copy your database to some test database in
  same folder. Try to edit with simple editor
  and save that file to see if is possible.

R.A.

Lars Frederiksen je 12.10.2018 ob 14:23 napisal:

I have tried this, unfortunately without any luck.
I think the problem might be in the sqlite DB-system. I have disabled ALL 
protecting parameters to find the culprit module - without luck.
My sqlite version is: SQLite version 3.19.3 2017-06-08 14:26:16
There might be a newer and better version without this error?

/Lars


-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Radovan Antloga
Sendt: 12. oktober 2018 10:10
Til: sqlite-users@mailinglists.sqlite.org
Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

Try to change database name (extension).

For example:
mydb.sqlite
mydb.sdb
mydb.db3

I would use .sqlite extension and exclude that
files from antivirus check.

Regards

Lars Frederiksen je 12.10.2018 ob 9:11 napisal:

Hello,

I have made (in Delphi) a program that writes to a sqlite database. This is
a simple stand-alone solution. Now something is blocking for the programs
attempt to writing to the databse. There is a an error message:
[FireDAC][Phys][SQLite] Database is locked.

A friend of mine has analysed the scenario and tells me that BitDefender is
the Problem.  In BitDefender "Application Access" I have given my program
permission to "Work".

Any solution on how to "tell" BitDefender that it is OK to make changes in
my database? (I have tried BitDefender support - but no ticket answer!)

Maybe also somebody knows an antivirus/anti-malware solution which are not
so "hard" as BitDefender? (and with a userfriendly GUI).

Thank you so much for any help. I am rather "desperate" for some help!

Best Regards

Lars

   

   


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

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

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


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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Lars Frederiksen]

I have tried that too! - I must admit that right now I am turning to a KISS 
solution: String-based database with functions and procedures in a class that 
handles the different jobs you do on a table. After all these old-fashioned 
DB's are not so picky :-)

-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Stephen Chrzanowski
Sendt: 12. oktober 2018 14:37
Til: General Discussion of SQLite Database
Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

How about moving the databases elsewhere?  Sometimes applications are picky
when it comes where files are being written to (IE: Program Files).

On Fri, Oct 12, 2018 at 8:30 AM Lars Frederiksen  wrote:

> Setting the Busy Timeout (in FireDAC) to 5000 ms does not solve the
> problem - unfortunately!
>
> -Oprindelig meddelelse-
> Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> På vegne af Stephen Chrzanowski
> Sendt: 12. oktober 2018 14:25
> Til: General Discussion of SQLite Database
> Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is
> locked
>
> Change the timeout value on the connection to 5 seconds or so.  The DB will
> retry.  The AV has its grip on it, doing a scan, and won't let anything
> else touch it until its done.
>
> On Fri, Oct 12, 2018 at 3:11 AM Lars Frederiksen 
> wrote:
>
> > Hello,
> >
> > I have made (in Delphi) a program that writes to a sqlite database. This
> is
> > a simple stand-alone solution. Now something is blocking for the programs
> > attempt to writing to the databse. There is a an error message:
> > [FireDAC][Phys][SQLite] Database is locked.
> >
> > A friend of mine has analysed the scenario and tells me that BitDefender
> is
> > the Problem.  In BitDefender "Application Access" I have given my program
> > permission to "Work".
> >
> > Any solution on how to "tell" BitDefender that it is OK to make changes
> in
> > my database? (I have tried BitDefender support - but no ticket answer!)
> >
> > Maybe also somebody knows an antivirus/anti-malware solution which are
> not
> > so "hard" as BitDefender? (and with a userfriendly GUI).
> >
> > Thank you so much for any help. I am rather "desperate" for some help!
> >
> > Best Regards
> >
> > Lars
> >
> >
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Stephen Chrzanowski]

How about moving the databases elsewhere?  Sometimes applications are picky
when it comes where files are being written to (IE: Program Files).

On Fri, Oct 12, 2018 at 8:30 AM Lars Frederiksen  wrote:

> Setting the Busy Timeout (in FireDAC) to 5000 ms does not solve the
> problem - unfortunately!
>
> -Oprindelig meddelelse-
> Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> På vegne af Stephen Chrzanowski
> Sendt: 12. oktober 2018 14:25
> Til: General Discussion of SQLite Database
> Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is
> locked
>
> Change the timeout value on the connection to 5 seconds or so.  The DB will
> retry.  The AV has its grip on it, doing a scan, and won't let anything
> else touch it until its done.
>
> On Fri, Oct 12, 2018 at 3:11 AM Lars Frederiksen 
> wrote:
>
> > Hello,
> >
> > I have made (in Delphi) a program that writes to a sqlite database. This
> is
> > a simple stand-alone solution. Now something is blocking for the programs
> > attempt to writing to the databse. There is a an error message:
> > [FireDAC][Phys][SQLite] Database is locked.
> >
> > A friend of mine has analysed the scenario and tells me that BitDefender
> is
> > the Problem.  In BitDefender "Application Access" I have given my program
> > permission to "Work".
> >
> > Any solution on how to "tell" BitDefender that it is OK to make changes
> in
> > my database? (I have tried BitDefender support - but no ticket answer!)
> >
> > Maybe also somebody knows an antivirus/anti-malware solution which are
> not
> > so "hard" as BitDefender? (and with a userfriendly GUI).
> >
> > Thank you so much for any help. I am rather "desperate" for some help!
> >
> > Best Regards
> >
> > Lars
> >
> >
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Lars Frederiksen]

Setting the Busy Timeout (in FireDAC) to 5000 ms does not solve the problem - 
unfortunately!

-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Stephen Chrzanowski
Sendt: 12. oktober 2018 14:25
Til: General Discussion of SQLite Database
Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

Change the timeout value on the connection to 5 seconds or so.  The DB will
retry.  The AV has its grip on it, doing a scan, and won't let anything
else touch it until its done.

On Fri, Oct 12, 2018 at 3:11 AM Lars Frederiksen  wrote:

> Hello,
>
> I have made (in Delphi) a program that writes to a sqlite database. This is
> a simple stand-alone solution. Now something is blocking for the programs
> attempt to writing to the databse. There is a an error message:
> [FireDAC][Phys][SQLite] Database is locked.
>
> A friend of mine has analysed the scenario and tells me that BitDefender is
> the Problem.  In BitDefender "Application Access" I have given my program
> permission to "Work".
>
> Any solution on how to "tell" BitDefender that it is OK to make changes in
> my database? (I have tried BitDefender support - but no ticket answer!)
>
> Maybe also somebody knows an antivirus/anti-malware solution which are not
> so "hard" as BitDefender? (and with a userfriendly GUI).
>
> Thank you so much for any help. I am rather "desperate" for some help!
>
> Best Regards
>
> Lars
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Lars Frederiksen]

That is: I have disabled ALL protecting parameters in BitDefender to find the 
culprit module - without luck.

-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Radovan Antloga
Sendt: 12. oktober 2018 10:10
Til: sqlite-users@mailinglists.sqlite.org
Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

Try to change database name (extension).

For example:
mydb.sqlite
mydb.sdb
mydb.db3

I would use .sqlite extension and exclude that
files from antivirus check.

Regards

Lars Frederiksen je 12.10.2018 ob 9:11 napisal:
> Hello,
>
> I have made (in Delphi) a program that writes to a sqlite database. This is
> a simple stand-alone solution. Now something is blocking for the programs
> attempt to writing to the databse. There is a an error message:
> [FireDAC][Phys][SQLite] Database is locked.
>
> A friend of mine has analysed the scenario and tells me that BitDefender is
> the Problem.  In BitDefender "Application Access" I have given my program
> permission to "Work".
>
> Any solution on how to "tell" BitDefender that it is OK to make changes in
> my database? (I have tried BitDefender support - but no ticket answer!)
>
> Maybe also somebody knows an antivirus/anti-malware solution which are not
> so "hard" as BitDefender? (and with a userfriendly GUI).
>
> Thank you so much for any help. I am rather "desperate" for some help!
>
> Best Regards
>
> Lars
>
>   
>
>   
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Stephen Chrzanowski]

Change the timeout value on the connection to 5 seconds or so.  The DB will
retry.  The AV has its grip on it, doing a scan, and won't let anything
else touch it until its done.

On Fri, Oct 12, 2018 at 3:11 AM Lars Frederiksen  wrote:

> Hello,
>
> I have made (in Delphi) a program that writes to a sqlite database. This is
> a simple stand-alone solution. Now something is blocking for the programs
> attempt to writing to the databse. There is a an error message:
> [FireDAC][Phys][SQLite] Database is locked.
>
> A friend of mine has analysed the scenario and tells me that BitDefender is
> the Problem.  In BitDefender "Application Access" I have given my program
> permission to "Work".
>
> Any solution on how to "tell" BitDefender that it is OK to make changes in
> my database? (I have tried BitDefender support - but no ticket answer!)
>
> Maybe also somebody knows an antivirus/anti-malware solution which are not
> so "hard" as BitDefender? (and with a userfriendly GUI).
>
> Thank you so much for any help. I am rather "desperate" for some help!
>
> Best Regards
>
> Lars
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Lars Frederiksen]

I have tried this, unfortunately without any luck. 
I think the problem might be in the sqlite DB-system. I have disabled ALL 
protecting parameters to find the culprit module - without luck.
My sqlite version is: SQLite version 3.19.3 2017-06-08 14:26:16
There might be a newer and better version without this error?

/Lars


-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Radovan Antloga
Sendt: 12. oktober 2018 10:10
Til: sqlite-users@mailinglists.sqlite.org
Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

Try to change database name (extension).

For example:
mydb.sqlite
mydb.sdb
mydb.db3

I would use .sqlite extension and exclude that
files from antivirus check.

Regards

Lars Frederiksen je 12.10.2018 ob 9:11 napisal:
> Hello,
>
> I have made (in Delphi) a program that writes to a sqlite database. This is
> a simple stand-alone solution. Now something is blocking for the programs
> attempt to writing to the databse. There is a an error message:
> [FireDAC][Phys][SQLite] Database is locked.
>
> A friend of mine has analysed the scenario and tells me that BitDefender is
> the Problem.  In BitDefender "Application Access" I have given my program
> permission to "Work".
>
> Any solution on how to "tell" BitDefender that it is OK to make changes in
> my database? (I have tried BitDefender support - but no ticket answer!)
>
> Maybe also somebody knows an antivirus/anti-malware solution which are not
> so "hard" as BitDefender? (and with a userfriendly GUI).
>
> Thank you so much for any help. I am rather "desperate" for some help!
>
> Best Regards
>
> Lars
>
>   
>
>   
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Radovan Antloga]

Try to change database name (extension).

For example:
mydb.sqlite
mydb.sdb
mydb.db3

I would use .sqlite extension and exclude that
files from antivirus check.

Regards

Lars Frederiksen je 12.10.2018 ob 9:11 napisal:

Hello,

I have made (in Delphi) a program that writes to a sqlite database. This is
a simple stand-alone solution. Now something is blocking for the programs
attempt to writing to the databse. There is a an error message:
[FireDAC][Phys][SQLite] Database is locked.

A friend of mine has analysed the scenario and tells me that BitDefender is
the Problem.  In BitDefender "Application Access" I have given my program
permission to "Work".

Any solution on how to "tell" BitDefender that it is OK to make changes in
my database? (I have tried BitDefender support - but no ticket answer!)

Maybe also somebody knows an antivirus/anti-malware solution which are not
so "hard" as BitDefender? (and with a userfriendly GUI).

Thank you so much for any help. I am rather "desperate" for some help!

Best Regards

Lars

  

  


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


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

[sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Lars Frederiksen]

Hello, 

I have made (in Delphi) a program that writes to a sqlite database. This is
a simple stand-alone solution. Now something is blocking for the programs
attempt to writing to the databse. There is a an error message:
[FireDAC][Phys][SQLite] Database is locked. 

A friend of mine has analysed the scenario and tells me that BitDefender is
the Problem.  In BitDefender "Application Access" I have given my program
permission to "Work".  

Any solution on how to "tell" BitDefender that it is OK to make changes in
my database? (I have tried BitDefender support - but no ticket answer!)

Maybe also somebody knows an antivirus/anti-malware solution which are not
so "hard" as BitDefender? (and with a userfriendly GUI). 

Thank you so much for any help. I am rather "desperate" for some help!  

Best Regards 

Lars 

 

 

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

Re: [sqlite] Database is locked

[Frank Millman]

On 2/28/18 3:18 PM, Frank Millman wrote:

> 
> On 2/28/18 2:53 PM, Richard Damon wrote:
> 
> > 
> > On 2/28/18 6:59 AM, Frank Millman wrote:
> > > Hi all
> > >
> > > I am using Python 3.6.0 and sqlite3 3.20.1. I am getting the message 
> > > ‘database is locked’ which, from reading the docs, I think is coming from 
> > > an SQL_BUSY error.
> > >
> > > It does not behave in the way I expect.
> > 
[...]
> > 
> > Your description, seeming to imply a total order, has an issue. If this 
> > is happening in a single thread, then if it uses a second connection to 
> > try and insert, that will block and the thread will never get to step 3, 
> > so the commit will not happen. You have a dead lock.
> > 
> 
> Of course! I should have thought of that.
> I am using python’s asyncio module, so everything is happening in a single 
> thread.
> I understand what is happening now. Thanks very much.

>

For the record, in case anyone runs into a similar situation, I found an 
effective solution.

I try to keep my transactions as short as possible, but with multiple 
connections running in the same thread it is possible that one of them starts a 
transaction before another one has committed.

To control that, I created an asyncio.Lock(), which is acquired at the start of 
the transaction and released after the commit. Problem solved.

I tested with 10 concurrent connections, and they all updated perfectly, with 
hardly any slowdown.

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

Re: [sqlite] Database is locked

[Frank Millman]

On 2/28/18 2:53 PM, Richard Damon wrote:

> 
> On 2/28/18 6:59 AM, Frank Millman wrote:
> > Hi all
> >
> > I am using Python 3.6.0 and sqlite3 3.20.1. I am getting the message 
> > ‘database is locked’ which, from reading the docs, I think is coming from 
> > an SQL_BUSY error.
> >
> > It does not behave in the way I expect.
> 
[...]
> 
> Your description, seeming to imply a total order, has an issue. If this 
> is happening in a single thread, then if it uses a second connection to 
> try and insert, that will block and the thread will never get to step 3, 
> so the commit will not happen. You have a dead lock.
> 

Of course! I should have thought of that.
I am using python’s asyncio module, so everything is happening in a single 
thread.
I understand what is happening now. Thanks very much.
Frank
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Richard Damon]

On 2/28/18 6:59 AM, Frank Millman wrote:

Hi all

I am using Python 3.6.0 and sqlite3 3.20.1. I am getting the message ‘database 
is locked’ which, from reading the docs, I think is coming from an SQL_BUSY 
error.

It does not behave in the way I expect. I tested using two concurrent 
connections – call them conn_1 and conn_2.

1. conn_1 performs an INSERT.

2. conn_2 performs an INSERT.

3. conn_1 sleeps for 1 second, then performs a COMMIT.

4. conn_2 sleeps for 1 second, then performs a COMMIT.

After step 2, the database is locked, which I understand. I don’t know which 
connection is holding the lock, but I don’t think that matters.

What I expected to happen was that, after one second, whichever connection was 
holding the lock would COMMIT, which would free up the other connection, which 
would then complete normally.

AFAICT, what happens in practice is that the lock is held for the default 
timeout of 5 seconds, then the connection which did *not* hold the lock fails 
with ‘database is locked’, and the connection which *did* hold the lock 
completes normally.

Is there any way to get the behaviour that I am looking for?

Thanks

Frank Millman
Your description, seeming to imply a total order, has an issue. If this 
is happening in a single thread, then if it uses a second connection to 
try and insert, that will block and the thread will never get to step 3, 
so the commit will not happen. You have a dead lock.


If steps 1 and 3 are in one thread, and 2 and 4 are in a different 
thread then this should normally work. The only possible issue would be 
if the Python wrapper for sqlite doesn't let the first thread run when 
the second thread blocks waiting to get access to perform the insert.


The description here would be described as (note, T1 and T2 are the two 
threads, and ordering between them is generally weak except as enforced 
by locks)


T1-1 conn_1 performs an INSERT
T1-2 conn_1 sleeps for 1 second
T2-1 conn_2 attempts to perfom an INSERT, but blocks
T1-3 conn_1 wakes up and performs a commit
T2-1a conn_2 wakes up and performs an INSERT
T2-2 conn_2 sleeps for 1 second
T2-3 conn_2 wakes up and performs a commit

Note, it is was just a single thread, then your description forces what 
I call T2-1 to complete (in T2-1a) before you get to T1-2, and thus 
before T1-3, but by the lock, T1-3 must complete before you get to 
T2-1a, so you deadlock.


--
Richard Damon

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

[sqlite] Database is locked

[Frank Millman]

Hi all

I am using Python 3.6.0 and sqlite3 3.20.1. I am getting the message ‘database 
is locked’ which, from reading the docs, I think is coming from an SQL_BUSY 
error.

It does not behave in the way I expect. I tested using two concurrent 
connections – call them conn_1 and conn_2.

1. conn_1 performs an INSERT.

2. conn_2 performs an INSERT.

3. conn_1 sleeps for 1 second, then performs a COMMIT.

4. conn_2 sleeps for 1 second, then performs a COMMIT.

After step 2, the database is locked, which I understand. I don’t know which 
connection is holding the lock, but I don’t think that matters.

What I expected to happen was that, after one second, whichever connection was 
holding the lock would COMMIT, which would free up the other connection, which 
would then complete normally.

AFAICT, what happens in practice is that the lock is held for the default 
timeout of 5 seconds, then the connection which did *not* hold the lock fails 
with ‘database is locked’, and the connection which *did* hold the lock 
completes normally.

Is there any way to get the behaviour that I am looking for?

Thanks

Frank Millman
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "Database is locked" in diagnostic tools C#

[Wojciech Slabik (Voitec)]

Hi everyone,

I have managed  to successfully replace SQLCE with System.Data.SQLite in a 
mid-size application. Everything works fine but now that I started optimizing 
the old code and replaced own connection pooling with SQLite connection pooling 
I am getting an occasional Program Output "Database is locked" from the C++ dll 
being displayed in the VS Diagnostic Tools. This is not throwing an exception 
in C# though, so I am wondering if this just means that the transaction has 
slept and was re-executed successfully? I tried to replicate this issue in a 
small app but transaction just waits 30 seconds (default busy timeout?) and 
throws an exception, there is nothing showing up in the Diagnostic Tools.

Thanks in advance,
Wojciech

[Micromine PL]

Micromine PL
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Hick Gunter]

Not directly, but you can query the locks on the file as per documentation:

struct flockv_pending;   // = { F_WRLCK, SEEK_SET, PENDING_BYTE  ,  
 1, 0};
struct flockv_reserved;  // = { F_WRLCK, SEEK_SET, RESERVED_BYTE ,  
 1, 0};
struct flockv_shared;// = { F_WRLCK, SEEK_SET, SHARED_FIRST  , 
SHARED_SIZE, 0};
int v_fd= -1;
int v_ret   = -1;

memset( &v_pending, 0, sizeof( v_pending));
v_pending.l_type= F_WRLCK;
v_pending.l_whence  = SEEK_SET;
v_pending.l_start   = PENDING_BYTE;
v_pending.l_len = 1;
v_pending.l_pid = 0;

memset( &v_reserved, 0, sizeof( v_reserved));
v_reserved.l_type   = F_WRLCK;
v_reserved.l_whence = SEEK_SET;
v_reserved.l_start  = RESERVED_BYTE;
v_reserved.l_len= 1;
v_reserved.l_pid= 0;

memset( &v_shared, 0, sizeof( v_shared));
v_shared.l_type = F_WRLCK;
v_shared.l_whence   = SEEK_SET;
v_shared.l_start= SHARED_FIRST;
v_shared.l_len  = SHARED_SIZE;
v_shared.l_pid  = 0;

/* check for a PENDING lock */
if (fcntl(v_fd,F_GETLK,&v_pending) == -1) {

};
/* check for a RESERVED lock */
if (fcntl(v_fd,F_GETLK,&v_reserved) == -1) {
};
/* check for a SHARED/EXCLUSIVE lock */
if (fcntl(v_fd,F_GETLK,&v_shared) == -1) {
};

if (v_pending.l_type == F_RDLCK)
printf("%s File:%s, Process %d PENDING (SHARED)\n"   ,g_mode[v_mode] 
,v_file, (v_ret = v_pending .l_pid));

if (v_shared .l_type == F_RDLCK)
printf("%s File:%s, Process %d SHARED\n" ,g_mode[v_mode] 
,v_file, (v_ret = v_shared  .l_pid));

switch (v_reserved.l_type) {
case F_WRLCK:
case F_RDLCK:
printf("%s File:%s, Process %d RESERVED\n"   ,g_mode[v_mode] 
,v_file, (v_ret = v_reserved.l_pid));
break;
default: break;
}

if (v_pending.l_type == F_WRLCK)
printf("%s File: %s,Process %d PENDING (EXCLUSIVE)\n",g_mode[v_mode] 
,v_file, (v_ret = v_pending .l_pid));

if (v_shared .l_type == F_WRLCK)
printf("%s File %s, Process %d EXCLUSIVE\n"  ,g_mode[v_mode] 
,v_file, (v_ret = v_shared  .l_pid));

if (v_ret == -1)
printf("%s File:%s, \n",g_mode[v_mode] 
,v_file);

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Bob Friesenhahn
Gesendet: Mittwoch, 01. März 2017 21:45
An: SQLite mailing list 
Betreff: Re: [sqlite] Database is locked

On Wed, 1 Mar 2017, Stephen Chrzanowski wrote:

> Where is the database being stored?  What OS is the software running
> under?  If Windows, I'd suggest looking up SysInternals and
> downloading the package to see what has its grubby hands on the
> database.  If Linux, as root, run an LSOF (Lower case) and grep for
> the database or process accessing the database.

This is an embedded Linux system.  Due to available resources, lsof is not 
available, but we can learn about open file descriptors from the /proc 
filesystem (via 'ls -l /proc/[pid]/fd').

Many of our processes have a database connection open all the time.  I am 
assuming that the problem is a hung (not commited or rolled back) transaction.  
I am hoping that sqlite provides a way to know what process is currently 
performing an update transaction.

Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Database is locked

[Simon Slavin]

On 1 Mar 2017, at 9:17pm, Bob Friesenhahn  wrote:

> I am not sure of all of the conditions which might result in "Database is 
> locked".  For example, if the results of a query are being iterated through 
> and the reader stops iterating part way through the result set, might this 
> cause "Database is locked" for writers?

If you do a sqlite3_step() for a statement then you absolutely must do a 
sqlite3_reset() or sqlite3_finalize() or both for that statement.  Without it 
you haven’t finished processing the statement.

Not doing this properly may not necessarily result in a lock which prevents 
writers (though it might), but it can can cause problems later.  For instance, 
you can’t close a connection which still has active statements, and that means 
your database may still be open when your program quits.

> Some of our C software is very good at doing this, and some C code is not 
> quite as good.  Our Python code should be robust at reporting problems since 
> we are using APSW and Python exceptions.

If possible, fix your C code.  But you knew that.  And there can be reasons why 
that’s not possible (legacy code you’re not allowed to change, libraries you 
can’t recompile).

> We do capture output from sqlite's tracing facility, including error reports.

Unfortunately, the tracing facility is triggered by your program doing an API 
call.  If you don’t do the API call, you don’t get an error report saying what 
you did wrong.

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

Re: [sqlite] Database is locked

[Bob Friesenhahn]

On Wed, 1 Mar 2017, Simon Slavin wrote:



On 1 Mar 2017, at 7:27pm, Bob Friesenhahn  wrote:


What is the recommended approach to diagnosing "Database is locked" errors and 
determining the guilty party?


Are you checking the result codes returned by all SQLite commands you give to 
make sure they are SQLITE_OK ?


Some of our C software is very good at doing this, and some C code is 
not quite as good.  Our Python code should be robust at reporting 
problems since we are using APSW and Python exceptions.


We do capture output from sqlite's tracing facility, including error 
reports.


I am not sure of all of the conditions which might result in "Database 
is locked".  For example, if the results of a query are being iterated 
through and the reader stops iterating part way through the result 
set, might this cause "Database is locked" for writers?


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Simon Slavin]

On 1 Mar 2017, at 7:27pm, Bob Friesenhahn  wrote:

> What is the recommended approach to diagnosing "Database is locked" errors 
> and determining the guilty party?

Are you checking the result codes returned by all SQLite commands you give to 
make sure they are SQLITE_OK ?

I’m not talking about checking them only after you’re getting "Database is 
locked" I’m talking about putting the checks in all your code which calls the 
SQLite API.  If SQLite is working the way it’s meant to, you could get a bad 
result from one of those calls before you start getting "Database is locked" 
from everything.

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

Re: [sqlite] Database is locked

[Bob Friesenhahn]

On Wed, 1 Mar 2017, Stephen Chrzanowski wrote:


Where is the database being stored?  What OS is the software running
under?  If Windows, I'd suggest looking up SysInternals and downloading the
package to see what has its grubby hands on the database.  If Linux, as
root, run an LSOF (Lower case) and grep for the database or process
accessing the database.


This is an embedded Linux system.  Due to available resources, lsof is 
not available, but we can learn about open file descriptors from the 
/proc filesystem (via 'ls -l /proc/[pid]/fd').


Many of our processes have a database connection open all the time.  I 
am assuming that the problem is a hung (not commited or rolled back) 
transaction.  I am hoping that sqlite provides a way to know what 
process is currently performing an update transaction.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Stephen Chrzanowski]

Where is the database being stored?  What OS is the software running
under?  If Windows, I'd suggest looking up SysInternals and downloading the
package to see what has its grubby hands on the database.  If Linux, as
root, run an LSOF (Lower case) and grep for the database or process
accessing the database.

On Wed, Mar 1, 2017 at 2:27 PM, Bob Friesenhahn <
bfrie...@simple.dallas.tx.us> wrote:

> We are sometimes encountering permanent "Database is locked" errors from
> sqlite 3.10.2.  After the database gets in this state it is not possible to
> write to it or read from it.  It is not possible to use sqlite's built in
> backup mechanism since it produces a backup file of zero bytes.
>
> What is the recommended approach to diagnosing "Database is locked" errors
> and determining the guilty party?
>
> Is there a way to clear the condition which causes "Database is locked"
> errors?
>
> Bob
> --
> Bob Friesenhahn
> bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
> GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database is locked

[Bob Friesenhahn]

We are sometimes encountering permanent "Database is locked" errors 
from sqlite 3.10.2.  After the database gets in this state it is not 
possible to write to it or read from it.  It is not possible to use 
sqlite's built in backup mechanism since it produces a backup file of 
zero bytes.


What is the recommended approach to diagnosing "Database is locked" 
errors and determining the guilty party?


Is there a way to clear the condition which causes "Database is 
locked" errors?


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Richard Hipp]

You may not DETACH while a query is running.  This is to prevent the
DETACH from closing a database connection out from under the running
query.

On 11/9/16, Jean-Baptiste Gardette  wrote:
> On Window 7 with tcl/tk 8.6.6 and SQLite 3.13.0
>
> The following tcl script craches :
>
>  package require sqlite3
>
>  sqlite3 db1 dbfile1
>
>  db1 eval "SELECT A FROM TableA" {
>
>  db1 eval "ATTACH DATABASE dbfile2 AS 'dbfile2'"
>
>  db1 eval "SELECT B FROM dbfile2.TableB"
>
>  db1 eval "DETACH DATABASE dbfile2"
>  }
>
> Error message :
> database dbfile2 is locked
>  while executing
> "db1 eval "DETACH DATABASE dbfile2""
>
> Please, if anyone could explain to me what's wrong.
>
> Thanks
> Jean-Baptiste
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database is locked

[Jean-Baptiste Gardette]

On Window 7 with tcl/tk 8.6.6 and SQLite 3.13.0

The following tcl script craches :

package require sqlite3

sqlite3 db1 dbfile1

db1 eval "SELECT A FROM TableA" {

db1 eval "ATTACH DATABASE dbfile2 AS 'dbfile2'"

db1 eval "SELECT B FROM dbfile2.TableB"

db1 eval "DETACH DATABASE dbfile2"
}

Error message :
database dbfile2 is locked
while executing
"db1 eval "DETACH DATABASE dbfile2""

Please, if anyone could explain to me what's wrong.

Thanks
Jean-Baptiste
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Igor Korot]

Dan,

On Sun, Jul 10, 2016 at 11:57 AM, Dan Kennedy  wrote:
> On 07/10/2016 06:21 AM, Igor Korot wrote:
>>
>> Simon,
>>
>> On Sat, Jul 9, 2016 at 7:09 PM, Simon Slavin  wrote:
>>>
>>> On 10 Jul 2016, at 12:06am, Igor Korot  wrote:
>>>
 I'm trying to write some software in C{++}. Everything works fine except
 when I exit the program exit I get the error "Database is locked".
 I am only trying to retrieve the information about the database (queries
 on
 sqlite_master).
>>>
>>> What command are you executing when you get "Database is locked" in
>>> return ?
>>
>> sqlite3_close();
>>
>>> Have you terminated your query properly ?  Did you call sqlite_finalize()
>>> on it ?  Did you get SQLITE_OK back from that call ?
>>
>> Yes, everything is finalized. And no error on finalization is produced.
>> That's why I'm asking if there is a tool that can check what is open.
>
>
> As I think you have surmised, you most likely have an unfinalized statement
> handle hanging around somewhere. After sqlite3_close() fails, use this:
>
>   https://www.sqlite.org/c3ref/next_stmt.html
>
> to loop through any unfinalized statement handles. If you find such a
> handle, this:
>
>   https://www.sqlite.org/c3ref/sql.html
>
> can be useful for figuring out where it came from.

Thank you.
The culprit was identified.

Now it is time to fix it.

>
> Dan.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Dan Kennedy]

On 07/10/2016 06:21 AM, Igor Korot wrote:

Simon,

On Sat, Jul 9, 2016 at 7:09 PM, Simon Slavin  wrote:

On 10 Jul 2016, at 12:06am, Igor Korot  wrote:


I'm trying to write some software in C{++}. Everything works fine except
when I exit the program exit I get the error "Database is locked".
I am only trying to retrieve the information about the database (queries on
sqlite_master).

What command are you executing when you get "Database is locked" in return ?

sqlite3_close();


Have you terminated your query properly ?  Did you call sqlite_finalize() on it 
?  Did you get SQLITE_OK back from that call ?

Yes, everything is finalized. And no error on finalization is produced.
That's why I'm asking if there is a tool that can check what is open.


As I think you have surmised, you most likely have an unfinalized 
statement handle hanging around somewhere. After sqlite3_close() fails, 
use this:


  https://www.sqlite.org/c3ref/next_stmt.html

to loop through any unfinalized statement handles. If you find such a 
handle, this:


  https://www.sqlite.org/c3ref/sql.html

can be useful for figuring out where it came from.

Dan.

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

Re: [sqlite] Database is locked

[Igor Korot]

Simon,

On Sat, Jul 9, 2016 at 7:09 PM, Simon Slavin  wrote:
>
> On 10 Jul 2016, at 12:06am, Igor Korot  wrote:
>
>> I'm trying to write some software in C{++}. Everything works fine except
>> when I exit the program exit I get the error "Database is locked".
>> I am only trying to retrieve the information about the database (queries on
>> sqlite_master).
>
> What command are you executing when you get "Database is locked" in return ?

sqlite3_close();

>
> Have you terminated your query properly ?  Did you call sqlite_finalize() on 
> it ?  Did you get SQLITE_OK back from that call ?

Yes, everything is finalized. And no error on finalization is produced.
That's why I'm asking if there is a tool that can check what is open.

Thank you.

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

Re: [sqlite] Database is locked

[Simon Slavin]

On 10 Jul 2016, at 12:06am, Igor Korot  wrote:

> I'm trying to write some software in C{++}. Everything works fine except
> when I exit the program exit I get the error "Database is locked".
> I am only trying to retrieve the information about the database (queries on
> sqlite_master).

What command are you executing when you get "Database is locked" in return ?

Have you terminated your query properly ?  Did you call sqlite_finalize() on it 
?  Did you get SQLITE_OK back from that call ?

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

[sqlite] Database is locked

[Igor Korot]

Hi,
Here is my situation.

I'm trying to write some software in C{++}. Everything works fine except
when I exit the program exit I get the error "Database is locked".
I am only trying to retrieve the information about the database (queries on
sqlite_master).

So here are my questions:
1. When the database file is locked does this mean that there is a
byte set somewhere in the db header? Most likely no, but I just want
to confirm
2. If the answer is no - is there a tool which will help identify which handle
is not closed/released?

Thank you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite database is locked when query SQLITE_MASTER table

[Simon Slavin]

Are you programming in a programming language ?  If so, which language are you 
using and which library are you using to execute SQLite commands ?

If you are scripting instead please tell us which scripting language you're 
using.

If you are using multi-threading or multi-processing, please tell us whether 
you are using one shared or many independent database connections.

Please try to execute some of your commands using the SQLite shell tool.  You 
can download the shell tool from the SQLite Download page as one of the 
"Precompiled Binaries" for your platform.  You can use the shell tool as one or 
both of your processes.

The SQLite shell tool is maintained by the same team that maintains SQLite.  If 
you can demonstrate a problem using the SQLite shell tool then it'll probably 
get fixed.  If it turns out that your problem occurs only with your own 
programming then we might be able to help anyway.

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

[sqlite] sqlite database is locked when query SQLITE_MASTER table

[Bhavesh Patel]

I have the huge size of the database file (approx 5.5GB).

I have two processes.

1.The first process:
It executes master table query. (Ex. "SELECT "type", "tbl_name" FROM 
"SQLite_MASTER").
It creates the new table using the dump of the existing table and database file 
size has been increased after dump ( approx  5.7 GB).
For Example:
SQL Statement: "CREATE TABLE "XYZ" AS SELECT * FROM ABC "

2. The second process:
It deletes tables which have been created by the first process.
delete query takes 4 minutes.

I have tested the three test scenario.

1. The first scenario : database is locked when I have executed "SELECT "type", 
"tbl_name" FROM "SQLite_MASTER"".

The first process execution is completed.
The second process starts and it is still running.
I execute the first process while the second process is already running.
sqlite throw error: "SELECT "type", "tbl_name" FROM "SQLite_MASTER"" in the 
first process.

2. The second scenario: database is locked when I have executed "CREATE TABLE 
XYZ AS SELECT "name" FROM "ABC".

The first process execution is completed.
The second process starts and it is still running.
I execute the first process while the second process is already running.
In the case of master table query executed successfully ("SELECT "type", 
"tbl_name" FROM "SQLite_MASTER"").
sqlite throw error: "CREATE TABLE XYZ AS SELECT "name" FROM "TEST"" in the 
first process.

3. The third scenario: There is no error.

The first process execution is completed.
The second process starts and it is still running.
I will wait for the end of execution of the second process.
After that, I start the first process for execution.
There is no error when we are execute two processes one by one.


I want to run the second process in the background.

Execute first process
Execute the second process as soon as first is executed.
While the second process is running, I want to start the first process.

I cannot combine first and second process because deleting take 4 minutes and 
my application gets busy.

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

[sqlite] database is locked when using SQLite3 and MPI to generate different databases

[Rowan Worth]

On 8 May 2016 at 10:14, just_rookie <925345468 at qq.com> wrote:

> Obviously, I did not do incompatible things with a database at the same
> time.
>

You are attempting to drop a table in databases 300_500.db and 600_900.db.
Obviously another process must create that table, since your test code
isn't.

Dropping a table involves writing to the database. To write the database it
must be locked. If another process already has a write-lock your MPI tasks
won't be able to obtain one and will fail with "database is locked". If
another process is holding a read-lock, your MPI tasks will obtain a
write-lock but won't be able to COMMIT until the read-lock is relinquished.

If you're getting "database is locked" even with a busy timeout of 10
seconds, you have something other connection to the DB holding on to a
lock. It could be that your other code is not calling sqlite3_finalize on
every sqlite3_stmt, or you just have long running transactions.

-Rowan

[sqlite] ?????? database is locked when using SQLite3 and MPI togenerate different databases

[just_rookie]

Hi Simon,


Thank you for your reply. I tired your solution, but I still got the same error 
message, even when I set PRAGMA busy_timeout = 1.




--  --
???: "Simon Slavin";;
: 2016?5?8?(???) ??11:22
???: "SQLite mailing list"; 

??: Re: [sqlite] database is locked when using SQLite3 and MPI togenerate 
different databases




On 8 May 2016, at 3:14am, just_rookie <925345468 at qq.com> wrote:

> But when I ran the program in Linux, I got an error message from 
> sqlite3_exec() that ?database is locked?.

You have not set a timeout, so SQLite is quitting as soon as it is told that 
another thread/process has the database locked.  Set a timeout by executing

PRAGMA busy_timeout = 5000

immediately after opening your connection to the database.

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

[sqlite] database is locked when using SQLite3 and MPI to generate different databases

[just_rookie]

Hi all,


I would like to use SQLite3 and MPI to generate many databases, and different 
processes produce and process different databases. But when I ran the program 
in Linux, I got an error message from sqlite3_exec() that ?database is locked?. 
I know this message means I tried to do incompatible things with a database at 
the same time from the same database connection. Obviously, I did not do 
incompatible things with a database at the same time. I have Googled this error 
message, but none of these web pages is about SQLite3 and MPI. The version of 
SQLite3 is 3.11.0. I am new to sqlite, and any help and information will be 
greatly appreciated! Thank you in advance!


Here is my simple code which can reproduce this error:


#include 
#include 
#include "sqlite3.h"
#include 
#include 

using namespace std;

int testFunc(int firstBreakPt, int secondBreakPt, char* saveFile);

int main(int argc, char* argv[])
{
MPI_Init(&argc,&argv);
char *saveFile = "test";
int breakPt[2][2]={{300,500},{600,900}};
int myid, numprocs, taskNum = 2;
int paraSent[2];
MPI_Status status;
MPI_Comm_rank(MPI_COMM_WORLD,&myid);
MPI_Comm_size(MPI_COMM_WORLD,&numprocs);
if (myid == 0)  // master;
{
// sending parameters to slaves;MPI_Bcast(saveFile, 
sizeof(saveFile), MPI_CHAR, 0, MPI_COMM_WORLD);
for(int i =0; i

[sqlite] database is locked when using SQLite3 and MPI to generate different databases

[Simon Slavin]

On 8 May 2016, at 3:14am, just_rookie <925345468 at qq.com> wrote:

> But when I ran the program in Linux, I got an error message from 
> sqlite3_exec() that ?database is locked?.

You have not set a timeout, so SQLite is quitting as soon as it is told that 
another thread/process has the database locked.  Set a timeout by executing

PRAGMA busy_timeout = 5000

immediately after opening your connection to the database.

Simon.

[sqlite] Database is locked (wal) - purely from read-only connections/queries

[Vince Scafaria]

Jim, I am running simple select statements against views; I am not inserting 
into them or anything unusual like that.
Also, my earlier comment re attach database was a red herring. I hit the 
"statement aborts" without that.

Lastly, to be clear, the "SQLite error (17): statement aborts..." is debug 
output that I see in Visual Studio. No actual error is thrown, though it is of 
course disconcerting.

Thank you.

Vince Scafaria

[sqlite] Database is locked (wal) - purely from read-only connections/queries

[Vince Scafaria]

Jim, I am running simple select statements against views; I am not inserting 
into them or anything unusual like that.
Also, my earlier comment re attach database was a red herring. I hit the 
"statement aborts" without that.

Lastly, to be clear, the "SQLite error (17): statement aborts..." is debug 
output that I see in Visual Studio. No actual error is thrown, though it is of 
course disconcerting.

Thank you.

Vince Scafaria

[sqlite] Database is locked (wal) - purely from read-only connections/queries

[Richard Hipp]

On 2/29/16, Vince Scafaria  wrote:
> Richard, I can confirm that having a writable connection open first, prior
> to trying any reads, does avoid the "database is locked" error.  However, I
> still do get "SQLite error (17): statement aborts" errors.  What are the
> rules I must follow to avoid getting these SQLITE_SCHEMA errors?  I am not
> running any SQL that I would generally consider to be altering the schema.
> I'm simply doing multiple concurrent reads on read-only connections and
> running INSERT/UPDATE (not CREATE/DROP) statements on the writable
> connection.  Thank you.
>

Some other process might be changing the schema.  ATTACH and DETACH
also change the schema, and require a reparse, as do creating new
application-defined functions or collating sequences or virtual
tables.

The query should automatically reparse and restart with no action on
the part of your code.  This is not something you need to worry over.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Database is locked (wal) - purely from read-only connections/queries

[Vince Scafaria]

One possibility: Does ATTACH DATABASE count as a schema change?


From: Vince Scafaria
Sent: Monday, February 29, 2016 10:31 PM
To: 'sqlite-users at mailinglists.sqlite.org' 
Subject: Database is locked (wal) - purely from read-only connections/queries

Richard, I can confirm that having a writable connection open first, prior to 
trying any reads, does avoid the "database is locked" error.  However, I still 
do get "SQLite error (17): statement aborts" errors.  What are the rules I must 
follow to avoid getting these SQLITE_SCHEMA errors?  I am not running any SQL 
that I would generally consider to be altering the schema. I'm simply doing 
multiple concurrent reads on read-only connections and running INSERT/UPDATE 
(not CREATE/DROP) statements on the writable connection.  Thank you.

Vince Scafaria

[sqlite] Database is locked (wal) - purely from read-only connections/queries

[Vince Scafaria]

Richard, I can confirm that having a writable connection open first, prior to 
trying any reads, does avoid the "database is locked" error.  However, I still 
do get "SQLite error (17): statement aborts" errors.  What are the rules I must 
follow to avoid getting these SQLITE_SCHEMA errors?  I am not running any SQL 
that I would generally consider to be altering the schema. I'm simply doing 
multiple concurrent reads on read-only connections and running INSERT/UPDATE 
(not CREATE/DROP) statements on the writable connection.  Thank you.

Vince Scafaria

[sqlite] Database is locked (wal) - purely from read-only connections/queries

[Jim Callahan]

Are you using any SQL VIEWs?
"You cannot DELETE, INSERT, or UPDATE a view.
Views are read-only in SQLite."
http://sqlite.org/lang_createview.html

Jim

On Mon, Feb 29, 2016 at 10:31 PM, Vince Scafaria  wrote:

> Richard, I can confirm that having a writable connection open first, prior
> to trying any reads, does avoid the "database is locked" error.  However, I
> still do get "SQLite error (17): statement aborts" errors.  What are the
> rules I must follow to avoid getting these SQLITE_SCHEMA errors?  I am not
> running any SQL that I would generally consider to be altering the schema.
> I'm simply doing multiple concurrent reads on read-only connections and
> running INSERT/UPDATE (not CREATE/DROP) statements on the writable
> connection.  Thank you.
>
> Vince Scafaria
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Database is locked (wal) - purely from read-only connections/queries

[Richard Hipp]

On 2/26/16, Vince Scafaria  wrote:
> I am using System.Data.SQLite in .NET and encountering "database is locked"
> with wal using multiple threads from the same process running simple select
> statements with (separate) read-only connections.

The first database connection (in any process) that opens a WAL-mode
database file must create the *-wal and *-shm files used for
transaction control.  (Likewise, the last connection to close a
WAL-mode database will delete those files.)

But because new files must be created when opening, that means that a
read-only database connection cannot be the first connection to open a
WAL-mode database.  A read-only database connection can open and read
a WAL-mode database as long as some other (read/write) thread or
process has already opened it, but the read-only connection cannot be
the first to open it.

Try this experiment:

Before running your test program, connection to the database file
using the sqlite3.exe command-line client and type ".schema".  Leave
sqlite3.exe connected to the database - just setting there doing
nothing.  Then while sqlite3.exe is running, rerun your test program.
Let us know if that clears your problem.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Database is locked (wal) - purely from read-only connections/queries

[Vince Scafaria]

I am using System.Data.SQLite in .NET and encountering "database is locked" 
with wal using multiple threads from the same process running simple select 
statements with (separate) read-only connections. Please see the link below and 
note the Visual Studio output window when it runs.

https://drive.google.com/open?id=0By9M2uwoQgnKUnN6Z2NoWDZLS2s

Here is the bulk of the code in case more helpful than the download:

class Program
{
/// 
/// Configure for x64 and hit F5 in Visual Studio
/// Check the output window and note "database is locked"
/// 
/// 
static void Main(string[] args)
{
// the db file was copied to bin
// note: the db was last opened with wal
var dbPath = new 
FileInfo(Assembly.GetExecutingAssembly().Location).Directory.FullName;
var dbFile = Path.Combine(dbPath, "Simple.db3");

var csb = new SQLiteConnectionStringBuilder();
csb.DataSource = dbFile;
csb.ReadOnly = true;
var connStr = csb.ConnectionString;

RunTest(connStr);
}

private static void RunTest(string connStr)
{
var f1 = Task.Factory.StartNew(() => DoReadTest(connStr), 
TaskCreationOptions.LongRunning);
var f2 = Task.Factory.StartNew(() => DoReadTest(connStr), 
TaskCreationOptions.LongRunning);
var f3 = Task.Factory.StartNew(() => DoReadTest(connStr), 
TaskCreationOptions.LongRunning);
var f4 = Task.Factory.StartNew(() => DoReadTest(connStr), 
TaskCreationOptions.LongRunning);
Task.WaitAll(new List { f1, f2, f3, f4 }.ToArray());
}

private static void DoReadTest(string connStr)
{
var untilTime = DateTime.UtcNow.AddSeconds(10);

int numRuns = 0;
long totalMS = 0;

// hammer the db with reads
do
{
var sw = new Stopwatch();
sw.Start();

ReadTestWorker(connStr);

numRuns++;
totalMS += sw.ElapsedMilliseconds;

} while (DateTime.UtcNow < untilTime);

Console.WriteLine($"Thread {Thread.CurrentThread.ManagedThreadId} 
avg ms: { totalMS / numRuns }, Total runs {numRuns }");
}

private static void ReadTestWorker(string connStr)
{
const string sql = "SELECT MAX(Id) AS MaxId FROM TestTable;";

using (var connection = new SQLiteConnection(connStr))
{
connection.Open();

using (var cmd = connection.CreateCommand())
{
cmd.CommandText = sql;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var msg = $"Thread 
{Thread.CurrentThread.ManagedThreadId} says max id is : {reader[0]}";
//Console.WriteLine(msg);
}
}
}
}
}

}

Thank you,

Vince

[sqlite] Database is locked

[Werner Kleiner]

Hello Richard,
I am not a SQLITE expert.
As I understand WAL it is especially made for transactions?
But our application do not have transactions, just normal SQL queries like
Select, Insert and Updates.

So does WAL makes sense for that?



2016-01-14 14:20 GMT+01:00 Richard Hipp :

> On 1/14/16, Werner Kleiner  wrote:
> > Hello
> > I have written a small Wep application with PHP (PDO Apache,). This web
> app
> > uses a sqlite db3 database. Also there is a Windows application which
> uses
> > the same database file.
> > Now we heard one time that there war a problem and in a log file we saw
> the
> > error code
> > "Database is locked"
> > My question now is:
> > Could this be a problem when Apache or PHP uses same database and also a
> > windows application?
> > For example if PHP writes to table "users" and windows application reads
> > from "users" at same time?
>
> Set WAL-mode to work around that.  https://www.sqlite.org/wal.html
>
> Also set a busy timeout using "PRAGMA busy_timeout=1;" or similar
> (https://www.sqlite.org/pragma.html#pragma_busy_timeout).
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Database is locked

[Simon Slavin]

On 15 Jan 2016, at 7:51am, Werner Kleiner  wrote:

> Takes your suggestion with PDO setAttribute(PDO:: ATTR_TIMEOUT,  the same
> effect as Richards with PRAGMA busy_timeout?
> For example I do a:
> 
> $dbConnection =$db->query('PRAGMA busy_timeout=6') ;
> 
> instead of
> 
> $dbConnection->setAttribute(PDO:: ATTR_TIMEOUT, 60);
> 
> is this the same?

I haven't tested it (I use the sqlite3 library in PHP rather than PDO) but 
that's how I'd have implemented it had I written in.

I gave you the PDO-style solution because I guessed that if you've using PDO 
you probably want code which is useful in many databases, not just SQLite.

Simon.

[sqlite] Database is locked

[Werner Kleiner]

Thanks a lot Simon. Now I understand a little bit better.
Last question: (hopefully :-) )

Takes your suggestion with PDO setAttribute(PDO:: ATTR_TIMEOUT,  the same
effect as Richards with PRAGMA busy_timeout?
For example I do a:

$dbConnection =$db->query('PRAGMA busy_timeout=6') ;

instead of

$dbConnection->setAttribute(PDO:: ATTR_TIMEOUT, 60);

is this the same?

Werner



2016-01-14 16:00 GMT+01:00 Simon Slavin :

>
> On 14 Jan 2016, at 1:42pm, Werner Kleiner  wrote:
>
> > The windows application is written in C# and uses the
> sqlite.systemData.dll.
>
> I'm sure someone here can tell you how to set a timeout in that.
>
> > What does the timeout mean in detail for sqlite ?
> > Is this time (in your example 5 minutes) for each SQL query which is
> > executed?
>
> SQLite contains its own backoff-and-retry procedure for use when the
> database is locked.  By default the timeout value is 0 which means SQLite
> never gets to use it.  But instead you can set a timout value.
>
> Then if SQLite tries the command and finds that the database is locked it
> will sleep a while, try again, sleep a little longer, try again, sleep even
> longer, try again ...  and it will keep doing this until the timeout value
> has been reached.  Only if it is still failing at that time will SQLite
> return SQLITE_BUSY or SQLITE_LOCKED.  At that point the error is final, and
> there's no need to implement your own system for backoff-and-retry.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Database is locked

[Richard Hipp]

On 1/15/16, Werner Kleiner  wrote:
> Hello Richard,
> I am not a SQLITE expert.
> As I understand WAL it is especially made for transactions?
> But our application do not have transactions, just normal SQL queries like
> Select, Insert and Updates.

Every "normal SQL query" is a transaction unto itself, unless you take
explicit steps to group two or more such queries together into a
single transaction.

So, if you are using SQLite, then you are using transactions.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Database is locked

[Simon Slavin]

On 14 Jan 2016, at 1:42pm, Werner Kleiner  wrote:

> The windows application is written in C# and uses the sqlite.systemData.dll.

I'm sure someone here can tell you how to set a timeout in that.

> What does the timeout mean in detail for sqlite ?
> Is this time (in your example 5 minutes) for each SQL query which is
> executed?

SQLite contains its own backoff-and-retry procedure for use when the database 
is locked.  By default the timeout value is 0 which means SQLite never gets to 
use it.  But instead you can set a timout value.

Then if SQLite tries the command and finds that the database is locked it will 
sleep a while, try again, sleep a little longer, try again, sleep even longer, 
try again ...  and it will keep doing this until the timeout value has been 
reached.  Only if it is still failing at that time will SQLite return 
SQLITE_BUSY or SQLITE_LOCKED.  At that point the error is final, and there's no 
need to implement your own system for backoff-and-retry.

Simon.

[sqlite] Database is locked

[Werner Kleiner]

Hello Simon,
thank you for help.
The windows application is written in C# and uses the sqlite.systemData.dll.

What does the timeout mean in detail for sqlite ?
Is this time (in your example 5 minutes) for each SQL query which is
executed?

2016-01-14 14:25 GMT+01:00 Simon Slavin :

>
> On 14 Jan 2016, at 10:57am, Werner Kleiner  wrote:
>
> > I have written a small Wep application with PHP (PDO Apache,). This web
> app
> > uses a sqlite db3 database. Also there is a Windows application which
> uses
> > the same database file.
> > Now we heard one time that there war a problem and in a log file we saw
> the
> > error code
> > "Database is locked"
> > My question now is:
> > Could this be a problem when Apache or PHP uses same database and also a
> > windows application?
>
> Yes.  In both applications you should set a timeout.  The way it is done
> in Windows depends on the language but for PHP PDO use you should set
> PDO::ATTR_TIMEOUT using
>
> 
>
> Example code:
>
> $dbConnection->setAttribute(PDO:: ATTR_TIMEOUT, 5*60);  // five minutes
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Database is locked

[Simon Slavin]

On 14 Jan 2016, at 10:57am, Werner Kleiner  wrote:

> I have written a small Wep application with PHP (PDO Apache,). This web app
> uses a sqlite db3 database. Also there is a Windows application which uses
> the same database file.
> Now we heard one time that there war a problem and in a log file we saw the
> error code
> "Database is locked"
> My question now is:
> Could this be a problem when Apache or PHP uses same database and also a
> windows application?

Yes.  In both applications you should set a timeout.  The way it is done in 
Windows depends on the language but for PHP PDO use you should set 
PDO::ATTR_TIMEOUT using



Example code:

$dbConnection->setAttribute(PDO:: ATTR_TIMEOUT, 5*60);  // five minutes

Simon.

[sqlite] Database is locked

[Stephen Chrzanowski]

As with the other suggestions, have a read of
https://www.sqlite.org/howtocorrupt.html

If you've got to distinct machines/OS's trying to touch the same file, you
might be looking at other issues.  Also, if you are indeed working over a
network, your timeout might be set so low that SQLite just times out
regardless of what PHP or Windows is doing.

On Thu, Jan 14, 2016 at 5:57 AM, Werner Kleiner 
wrote:

> Hello
> I have written a small Wep application with PHP (PDO Apache,). This web app
> uses a sqlite db3 database. Also there is a Windows application which uses
> the same database file.
> Now we heard one time that there war a problem and in a log file we saw the
> error code
> "Database is locked"
> My question now is:
> Could this be a problem when Apache or PHP uses same database and also a
> windows application?
> For example if PHP writes to table "users" and windows application reads
> from "users" at same time?
> Or what else can cause this error?
>
> best regards
> Werner
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Database is locked

[Werner Kleiner]

Hello
I have written a small Wep application with PHP (PDO Apache,). This web app
uses a sqlite db3 database. Also there is a Windows application which uses
the same database file.
Now we heard one time that there war a problem and in a log file we saw the
error code
"Database is locked"
My question now is:
Could this be a problem when Apache or PHP uses same database and also a
windows application?
For example if PHP writes to table "users" and windows application reads
from "users" at same time?
Or what else can cause this error?

best regards
Werner

[sqlite] Database is locked

[Richard Hipp]

On 1/14/16, Werner Kleiner  wrote:
> Hello
> I have written a small Wep application with PHP (PDO Apache,). This web app
> uses a sqlite db3 database. Also there is a Windows application which uses
> the same database file.
> Now we heard one time that there war a problem and in a log file we saw the
> error code
> "Database is locked"
> My question now is:
> Could this be a problem when Apache or PHP uses same database and also a
> windows application?
> For example if PHP writes to table "users" and windows application reads
> from "users" at same time?

Set WAL-mode to work around that.  https://www.sqlite.org/wal.html

Also set a busy timeout using "PRAGMA busy_timeout=1;" or similar
(https://www.sqlite.org/pragma.html#pragma_busy_timeout).

-- 
D. Richard Hipp
drh at sqlite.org

Re: [sqlite] database is locked

[Roman Fleysher]

Wow, thank you Hick! I will try it.

Roman


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Hick Gunter [h...@scigames.at]
Sent: Thursday, January 15, 2015 1:34 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] database is locked

Use the following code snippet (add error checking, set v_file to the full path 
name of your SQLite db file) to check.

It attempts to take the same locks as SQLite would, but prints the pid of the 
blocking process. It also prints the journal mode.

#include 
#include 
#ifdef AIX64
#include 
#endif
#include 
#include 
#include 

#include "sqliteInt.h"

static const char  *g_mode[] = {
"[0/0]", "[del]", "[WAL]", "[?/?]"
};

charv_buff[256];
int v_mode;

struct flockv_pending;   // = { F_WRLCK, SEEK_SET, PENDING_BYTE  ,  
 1, 0};
struct flockv_reserved;  // = { F_WRLCK, SEEK_SET, RESERVED_BYTE ,  
 1, 0};
struct flockv_shared;// = { F_WRLCK, SEEK_SET, SHARED_FIRST  , 
SHARED_SIZE, 0};
int v_fd= -1;
int v_ret   = -1;

memset( &v_pending, 0, sizeof( v_pending));
v_pending.l_type= F_WRLCK;
v_pending.l_whence  = SEEK_SET;
v_pending.l_start   = PENDING_BYTE;
v_pending.l_len = 1;
v_pending.l_pid = 0;

memset( &v_reserved, 0, sizeof( v_reserved));
v_reserved.l_type   = F_WRLCK;
v_reserved.l_whence = SEEK_SET;
v_reserved.l_start  = RESERVED_BYTE;
v_reserved.l_len= 1;
v_reserved.l_pid= 0;

memset( &v_shared, 0, sizeof( v_shared));
v_shared.l_type = F_WRLCK;
v_shared.l_whence   = SEEK_SET;
v_shared.l_start= SHARED_FIRST;
v_shared.l_len  = SHARED_SIZE;
v_shared.l_pid  = 0;

/* open the file readonly */
v_fd = open(v_file,O_RDONLY);

// read the file header
read(v_fd, v_buff, 100);

// interpret the bytes
switch (v_buff[18]) {
case  0: v_mode = (v_buff[18] == v_buff[19]) ? 0 : 3; break; // empty
case  1: v_mode = (v_buff[18] == v_buff[19]) ? 1 : 3; break; // 
journal_mode=delete
case  2: v_mode = (v_buff[18] == v_buff[19]) ? 2 : 3; break; // 
journal_mode=wal
default: v_mode =  3; break; // invalid
};

/* check for a PENDING lock */
fcntl(v_fd,F_GETLK,&v_pending);

/* check for a RESERVED lock */
fcntl(v_fd,F_GETLK,&v_reserved);

/* check for a SHARED/EXCLUSIVE lock */
fcntl(v_fd,F_GETLK,&v_shared);

/* print in ascending restrictivity */
if (v_pending.l_type == F_RDLCK)
printf("%s File:%s, Process %d PENDING (SHARED)\n"   ,g_mode[v_mode] 
,v_file, (v_ret = v_pending .l_pid));

if (v_shared .l_type == F_RDLCK)
printf("%s File:%s, Process %d SHARED\n" ,g_mode[v_mode] 
,v_file, (v_ret = v_shared  .l_pid));

switch (v_reserved.l_type) {
case F_WRLCK:
case F_RDLCK:
printf("%s File:%s, Process %d RESERVED\n"   ,g_mode[v_mode] 
,v_file, (v_ret = v_reserved.l_pid));
break;
default: break;
}

if (v_pending.l_type == F_WRLCK)
printf("%s File: %s,Process %d PENDING (EXCLUSIVE)\n",g_mode[v_mode] 
,v_file, (v_ret = v_pending .l_pid));

if (v_shared .l_type == F_WRLCK)
printf("%s File %s, Process %d EXCLUSIVE\n"  ,g_mode[v_mode] 
,v_file, (v_ret = v_shared  .l_pid));

if (v_ret == -1)
printf("%s File:%s, \n",g_mode[v_mode] 
,v_file);


-Ursprüngliche Nachricht-
Von: Roman Fleysher [mailto:roman.fleys...@einstein.yu.edu]
Gesendet: Mittwoch, 14. Jänner 2015 18:54
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] database is locked

SQLite shell version 3.7.2
on Linux 2.6.18
NTFS




From: sqlite-users-boun...@sqlite.org<mailto:sqlite-users-boun...@sqlite.org> 
[sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 14, 2015 12:50 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked

On 1/14/15, Roman Fleysher 
mailto:roman.fleys...@einstein.yu.edu>> wrote:
> Dear SQLiters,
>
> There has been a lot of discussion, I remember, on this subject by others.
> Please forgive me for asking this for a millionth time.
>
> I somehow got my database in a locked state. I updated a table
> yesterday and I am rather sure that no one on our multi-user system is 
> updating it now.
> The time stamp on the file is from yesterday, showing correct time. I
> need to update a table (I use shell) and I get "database is locked".
> Is there a way to figur

Re: [sqlite] database is locked

[Hick Gunter]

Use the following code snippet (add error checking, set v_file to the full path 
name of your SQLite db file) to check.

It attempts to take the same locks as SQLite would, but prints the pid of the 
blocking process. It also prints the journal mode.

#include 
#include 
#ifdef AIX64
#include 
#endif
#include 
#include 
#include 

#include "sqliteInt.h"

static const char  *g_mode[] = {
"[0/0]", "[del]", "[WAL]", "[?/?]"
};

charv_buff[256];
int v_mode;

struct flockv_pending;   // = { F_WRLCK, SEEK_SET, PENDING_BYTE  ,  
 1, 0};
struct flockv_reserved;  // = { F_WRLCK, SEEK_SET, RESERVED_BYTE ,  
 1, 0};
struct flockv_shared;// = { F_WRLCK, SEEK_SET, SHARED_FIRST  , 
SHARED_SIZE, 0};
int v_fd= -1;
int v_ret   = -1;

memset( &v_pending, 0, sizeof( v_pending));
v_pending.l_type= F_WRLCK;
v_pending.l_whence  = SEEK_SET;
v_pending.l_start   = PENDING_BYTE;
v_pending.l_len = 1;
v_pending.l_pid = 0;

memset( &v_reserved, 0, sizeof( v_reserved));
v_reserved.l_type   = F_WRLCK;
v_reserved.l_whence = SEEK_SET;
v_reserved.l_start  = RESERVED_BYTE;
v_reserved.l_len= 1;
v_reserved.l_pid= 0;

memset( &v_shared, 0, sizeof( v_shared));
v_shared.l_type = F_WRLCK;
v_shared.l_whence   = SEEK_SET;
v_shared.l_start= SHARED_FIRST;
v_shared.l_len  = SHARED_SIZE;
v_shared.l_pid  = 0;

/* open the file readonly */
v_fd = open(v_file,O_RDONLY);

// read the file header
read(v_fd, v_buff, 100);

// interpret the bytes
switch (v_buff[18]) {
case  0: v_mode = (v_buff[18] == v_buff[19]) ? 0 : 3; break; // empty
case  1: v_mode = (v_buff[18] == v_buff[19]) ? 1 : 3; break; // 
journal_mode=delete
case  2: v_mode = (v_buff[18] == v_buff[19]) ? 2 : 3; break; // 
journal_mode=wal
default: v_mode =  3; break; // invalid
};

/* check for a PENDING lock */
fcntl(v_fd,F_GETLK,&v_pending);

/* check for a RESERVED lock */
fcntl(v_fd,F_GETLK,&v_reserved);

/* check for a SHARED/EXCLUSIVE lock */
fcntl(v_fd,F_GETLK,&v_shared);

/* print in ascending restrictivity */
if (v_pending.l_type == F_RDLCK)
printf("%s File:%s, Process %d PENDING (SHARED)\n"   ,g_mode[v_mode] 
,v_file, (v_ret = v_pending .l_pid));

if (v_shared .l_type == F_RDLCK)
printf("%s File:%s, Process %d SHARED\n" ,g_mode[v_mode] 
,v_file, (v_ret = v_shared  .l_pid));

switch (v_reserved.l_type) {
case F_WRLCK:
case F_RDLCK:
printf("%s File:%s, Process %d RESERVED\n"   ,g_mode[v_mode] 
,v_file, (v_ret = v_reserved.l_pid));
break;
default: break;
}

if (v_pending.l_type == F_WRLCK)
printf("%s File: %s,Process %d PENDING (EXCLUSIVE)\n",g_mode[v_mode] 
,v_file, (v_ret = v_pending .l_pid));

if (v_shared .l_type == F_WRLCK)
printf("%s File %s, Process %d EXCLUSIVE\n"  ,g_mode[v_mode] 
,v_file, (v_ret = v_shared  .l_pid));

if (v_ret == -1)
printf("%s File:%s, \n",g_mode[v_mode] 
,v_file);


-Ursprüngliche Nachricht-
Von: Roman Fleysher [mailto:roman.fleys...@einstein.yu.edu]
Gesendet: Mittwoch, 14. Jänner 2015 18:54
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] database is locked

SQLite shell version 3.7.2
on Linux 2.6.18
NTFS




From: sqlite-users-boun...@sqlite.org<mailto:sqlite-users-boun...@sqlite.org> 
[sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 14, 2015 12:50 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked

On 1/14/15, Roman Fleysher 
mailto:roman.fleys...@einstein.yu.edu>> wrote:
> Dear SQLiters,
>
> There has been a lot of discussion, I remember, on this subject by others.
> Please forgive me for asking this for a millionth time.
>
> I somehow got my database in a locked state. I updated a table
> yesterday and I am rather sure that no one on our multi-user system is 
> updating it now.
> The time stamp on the file is from yesterday, showing correct time. I
> need to update a table (I use shell) and I get "database is locked".
> Is there a way to figure out what is happening? Clear the lock?

What operating system and filesystem are you using?  And are you specifying an 
alternative VFS for SQLite or using the default?

>
> Thank you for your help,
>
> Roman
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org<

Re: [sqlite] database is locked

[Roman Fleysher]

Thank you!!

Roman

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 14, 2015 1:31 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked

On 1/14/15, Roman Fleysher  wrote:
> Thank you, Richard.
>
> You are correct, I made a typo: we have NFS not NTFS and I know they are
> buggy. I always use the same node on our compute cluster to minimize
> buffering issue. So, are you saying I can not clear the database lock and
> must rebuild the database?

Probably you can just restart the NFS lock manager.  Rebuilding the
database seems a bit extreme.


>
> Roman
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Richard Hipp [d...@sqlite.org]
> Sent: Wednesday, January 14, 2015 1:26 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked
>
> On 1/14/15, Roman Fleysher  wrote:
>> SQLite shell version 3.7.2
>> on Linux 2.6.18
>> NTFS
>
> On Linux, SQLite uses posix advisory locks.  All locks are
> automatically released when the process dies (if they haven't been
> already).  If you have stuck locks, that indicates that you either
> have a stuck process or a busted filesystem.  I'm not sure how well
> NTFS plays with Linux.  I thought linux could only read NTFS.
>
> If you are using a network filesystem, all bets are off.  Those are
> usually pretty buggy with respect to locking.
>
>>
>>
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>> on
>> behalf of Richard Hipp [d...@sqlite.org]
>> Sent: Wednesday, January 14, 2015 12:50 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] database is locked
>>
>> On 1/14/15, Roman Fleysher  wrote:
>>> Dear SQLiters,
>>>
>>> There has been a lot of discussion, I remember, on this subject by
>>> others.
>>> Please forgive me for asking this for a millionth time.
>>>
>>> I somehow got my database in a locked state. I updated a table yesterday
>>> and
>>> I am rather sure that no one on our multi-user system is updating it
>>> now.
>>> The time stamp on the file is from yesterday, showing correct time. I
>>> need
>>> to update a table (I use shell) and I get "database is locked". Is there
>>> a
>>> way to figure out what is happening? Clear the lock?
>>
>> What operating system and filesystem are you using?  And are you
>> specifying an alternative VFS for SQLite or using the default?
>>
>>>
>>> Thank you for your help,
>>>
>>> Roman
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> 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
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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
>


--
D. Richard Hipp
d...@sqlite.org
___
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] database is locked

[Richard Hipp]

On 1/14/15, Roman Fleysher  wrote:
> Thank you, Richard.
>
> You are correct, I made a typo: we have NFS not NTFS and I know they are
> buggy. I always use the same node on our compute cluster to minimize
> buffering issue. So, are you saying I can not clear the database lock and
> must rebuild the database?

Probably you can just restart the NFS lock manager.  Rebuilding the
database seems a bit extreme.


>
> Roman
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Richard Hipp [d...@sqlite.org]
> Sent: Wednesday, January 14, 2015 1:26 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked
>
> On 1/14/15, Roman Fleysher  wrote:
>> SQLite shell version 3.7.2
>> on Linux 2.6.18
>> NTFS
>
> On Linux, SQLite uses posix advisory locks.  All locks are
> automatically released when the process dies (if they haven't been
> already).  If you have stuck locks, that indicates that you either
> have a stuck process or a busted filesystem.  I'm not sure how well
> NTFS plays with Linux.  I thought linux could only read NTFS.
>
> If you are using a network filesystem, all bets are off.  Those are
> usually pretty buggy with respect to locking.
>
>>
>>
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>> on
>> behalf of Richard Hipp [d...@sqlite.org]
>> Sent: Wednesday, January 14, 2015 12:50 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] database is locked
>>
>> On 1/14/15, Roman Fleysher  wrote:
>>> Dear SQLiters,
>>>
>>> There has been a lot of discussion, I remember, on this subject by
>>> others.
>>> Please forgive me for asking this for a millionth time.
>>>
>>> I somehow got my database in a locked state. I updated a table yesterday
>>> and
>>> I am rather sure that no one on our multi-user system is updating it
>>> now.
>>> The time stamp on the file is from yesterday, showing correct time. I
>>> need
>>> to update a table (I use shell) and I get "database is locked". Is there
>>> a
>>> way to figure out what is happening? Clear the lock?
>>
>> What operating system and filesystem are you using?  And are you
>> specifying an alternative VFS for SQLite or using the default?
>>
>>>
>>> Thank you for your help,
>>>
>>> Roman
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> 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
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked

[Roman Fleysher]

Thank you, Richard.

You are correct, I made a typo: we have NFS not NTFS and I know they are buggy. 
I always use the same node on our compute cluster to minimize buffering issue. 
So, are you saying I can not clear the database lock and must rebuild the 
database? 

Roman

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 14, 2015 1:26 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked

On 1/14/15, Roman Fleysher  wrote:
> SQLite shell version 3.7.2
> on Linux 2.6.18
> NTFS

On Linux, SQLite uses posix advisory locks.  All locks are
automatically released when the process dies (if they haven't been
already).  If you have stuck locks, that indicates that you either
have a stuck process or a busted filesystem.  I'm not sure how well
NTFS plays with Linux.  I thought linux could only read NTFS.

If you are using a network filesystem, all bets are off.  Those are
usually pretty buggy with respect to locking.

>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Richard Hipp [d...@sqlite.org]
> Sent: Wednesday, January 14, 2015 12:50 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked
>
> On 1/14/15, Roman Fleysher  wrote:
>> Dear SQLiters,
>>
>> There has been a lot of discussion, I remember, on this subject by
>> others.
>> Please forgive me for asking this for a millionth time.
>>
>> I somehow got my database in a locked state. I updated a table yesterday
>> and
>> I am rather sure that no one on our multi-user system is updating it now.
>> The time stamp on the file is from yesterday, showing correct time. I
>> need
>> to update a table (I use shell) and I get "database is locked". Is there
>> a
>> way to figure out what is happening? Clear the lock?
>
> What operating system and filesystem are you using?  And are you
> specifying an alternative VFS for SQLite or using the default?
>
>>
>> Thank you for your help,
>>
>> Roman
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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
>


--
D. Richard Hipp
d...@sqlite.org
___
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] database is locked

[Richard Hipp]

On 1/14/15, Roman Fleysher  wrote:
> SQLite shell version 3.7.2
> on Linux 2.6.18
> NTFS

On Linux, SQLite uses posix advisory locks.  All locks are
automatically released when the process dies (if they haven't been
already).  If you have stuck locks, that indicates that you either
have a stuck process or a busted filesystem.  I'm not sure how well
NTFS plays with Linux.  I thought linux could only read NTFS.

If you are using a network filesystem, all bets are off.  Those are
usually pretty buggy with respect to locking.

>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Richard Hipp [d...@sqlite.org]
> Sent: Wednesday, January 14, 2015 12:50 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked
>
> On 1/14/15, Roman Fleysher  wrote:
>> Dear SQLiters,
>>
>> There has been a lot of discussion, I remember, on this subject by
>> others.
>> Please forgive me for asking this for a millionth time.
>>
>> I somehow got my database in a locked state. I updated a table yesterday
>> and
>> I am rather sure that no one on our multi-user system is updating it now.
>> The time stamp on the file is from yesterday, showing correct time. I
>> need
>> to update a table (I use shell) and I get "database is locked". Is there
>> a
>> way to figure out what is happening? Clear the lock?
>
> What operating system and filesystem are you using?  And are you
> specifying an alternative VFS for SQLite or using the default?
>
>>
>> Thank you for your help,
>>
>> Roman
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked

[Simon Slavin]

On 14 Jan 2015, at 5:30pm, Roman Fleysher  
wrote:

> Is there a way to figure out what is happening? Clear the lock?

We would need specifics of your system to answer this absolutely correctly.  
But you can try these things in turn until one of them works:

Unmount the volume the database is on, then mount it again.
Log out, then log in again.
Reboot the computer.

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

Re: [sqlite] database is locked

[Roman Fleysher]

SQLite shell version 3.7.2
on Linux 2.6.18
NTFS




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 14, 2015 12:50 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked

On 1/14/15, Roman Fleysher  wrote:
> Dear SQLiters,
>
> There has been a lot of discussion, I remember, on this subject by others.
> Please forgive me for asking this for a millionth time.
>
> I somehow got my database in a locked state. I updated a table yesterday and
> I am rather sure that no one on our multi-user system is updating it now.
> The time stamp on the file is from yesterday, showing correct time. I need
> to update a table (I use shell) and I get "database is locked". Is there a
> way to figure out what is happening? Clear the lock?

What operating system and filesystem are you using?  And are you
specifying an alternative VFS for SQLite or using the default?

>
> Thank you for your help,
>
> Roman
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
d...@sqlite.org
___
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] database is locked

[Richard Hipp]

On 1/14/15, Roman Fleysher  wrote:
> Dear SQLiters,
>
> There has been a lot of discussion, I remember, on this subject by others.
> Please forgive me for asking this for a millionth time.
>
> I somehow got my database in a locked state. I updated a table yesterday and
> I am rather sure that no one on our multi-user system is updating it now.
> The time stamp on the file is from yesterday, showing correct time. I need
> to update a table (I use shell) and I get "database is locked". Is there a
> way to figure out what is happening? Clear the lock?

What operating system and filesystem are you using?  And are you
specifying an alternative VFS for SQLite or using the default?

>
> Thank you for your help,
>
> Roman
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database is locked

[Roman Fleysher]

Dear SQLiters,

There has been a lot of discussion, I remember, on this subject by others. 
Please forgive me for asking this for a millionth time.

I somehow got my database in a locked state. I updated a table yesterday and I 
am rather sure that no one on our multi-user system is updating it now. The 
time stamp on the file is from yesterday, showing correct time. I need to 
update a table (I use shell) and I get "database is locked". Is there a way to 
figure out what is happening? Clear the lock?

Thank you for your help,

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

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Simon Slavin]

On 4 Dec 2014, at 5:36pm, Jonathan Moules  wrote:

> Depending on the application, an end user likely won't see the error code, 
> but instead just the error message 

SQlite is not a program.  It's an API, intended for use by a programmer.  Those 
error codes should not be reported to the end user.  They are intended for the 
user of the API: the programmer.  An end user is not expected to know what 
something like

SQLITE_BUSY_SNAPSHOT 

means.  What the programmer has their program do about them is the heart of the 
matter.

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

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Jonathan Moules]

Thanks for the comments.
I appreciate there are nuances of their differences that are likely obvious to 
a developer during development based on the current phrasing, but I'm putting 
this forward from a user's perspective.

Depending on the application, an end user likely won't see the error code, but 
instead just the error message (at least the application where I encountered 
this issue). They then google for "sqlite database locked" but get back 
information about SQLITE_LOCKED which isn't the same thing as SQLITE_BUSY. It 
doesn't particularly matter too much what the message is so long as it helps 
the user correctly find what it actually relates to in the documentation.

Using my simplistic understanding of the differences, what about these two:

SQLITE_BUSY - "Database is busy, locked by another connection" (some use of 
"busy" which should make googling for the actual problem easier to find).
SQLITE_LOCKED - "Database table is locked"

Cheers,
Jonathan

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Keith Medcalf
Sent: Wednesday, December 03, 2014 3:21 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] "database is locked" for SQLITE_BUSY

>From https://www.sqlite.org/rescode.html#busy

In both cases there are specific extended codes that may further pinpoint the 
source just in case you do not know what you are doing at the time the result 
code was returned.  Interpretation is only difficult if you do not know what 
you are doing when the result code was returned.


(5) SQLITE_BUSY
The SQLITE_BUSY result code indicates that the database file could not be 
written (or in some cases read) because of concurrent activity by some other 
database connection, usually a database connection in a separate process.

For example, if process A is in the middle of a large write transaction and at 
the same time process B attempts to start a new write transaction, process B 
will get back an SQLITE_BUSY result because SQLite only supports one writer at 
a time. Process B will need to wait for process A to finish its transaction 
before starting a new transaction. The sqlite3_busy_timeout() and 
sqlite3_busy_handler() interfaces and the busy_timeout pragma are available to 
process B to help it deal with SQLITE_BUSY errors.

An SQLITE_BUSY error can occur at any point in a transaction: when the 
transaction is first started, during any write or update operations, or when 
the transaction commits. To avoid encountering SQLITE_BUSY errors in the middle 
of a transaction, the application can use BEGIN IMMEDIATE instead of just BEGIN 
to start a transaction. The BEGIN IMMEDIATE command might itself return 
SQLITE_BUSY, but if it succeeds, then SQLite guarantees that no subsequent 
operations on the same database through the next COMMIT will return SQLITE_BUSY.

See also: SQLITE_BUSY_RECOVERY and SQLITE_BUSY_SNAPSHOT.

The SQLITE_BUSY result code differs from SQLITE_LOCKED in that SQLITE_BUSY 
indicates a conflict with a separate database connection, probably in a 
separate process, whereas SQLITE_LOCKED indicates a conflict within the same 
database connection (or sometimes a database connection with a shared cache).

(6) SQLITE_LOCKED
The SQLITE_LOCKED result code indicates that a write operation could not 
continue because of a conflict within the same database connection or a 
conflict with a different database connection that uses a shared cache.

For example, a DROP TABLE statement cannot be run while another thread is 
reading from that table on the same database connection because dropping the 
table would delete the table out from under the concurrent reader.

The SQLITE_LOCKED result code differs from SQLITE_BUSY in that SQLITE_LOCKED 
indicates a conflict on the same database connection (or on a connection with a 
shared cache) whereas SQLITE_BUSY indicates a conflict with a different 
database connection, probably in a different process.

(261) SQLITE_BUSY_RECOVERY
The SQLITE_BUSY_RECOVERY error code is an extended error code for SQLITE_BUSY 
that indicates that an operation could not continue because another process is 
busy recovering a WAL mode database file following a crash. The 
SQLITE_BUSY_RECOVERY error code only occurs on WAL mode databases.

(517) SQLITE_BUSY_SNAPSHOT
The SQLITE_BUSY_SNAPSHOT error code is an extended error code for SQLITE_BUSY 
that occurs on WAL mode databases when a database connection tries to promote a 
read transaction into a write transaction but finds that another database 
connection has already written to the database and thus invalidated prior reads.

The following scenario illustrates how an SQLITE_BUSY_SNAPSHOT error might 
arise:

Process A starts a read transaction on the database and does one or more 
SELECT statement. Process A keeps the transaction open.
Process B updates the database

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Simon Slavin]

On 3 Dec 2014, at 3:20pm, Richard Hipp  wrote:

> https://www.sqlite.org/rescode.html#busy

Thanks, Richard.  I have somehow never seen that.

I had no idea that the difference between _BUSY and _LOCKED was purely about 
whether the conflicting access was from the same connection.

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

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Keith Medcalf]

heory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Stephen Chrzanowski
>Sent: Wednesday, 3 December, 2014 07:47
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] "database is locked" for SQLITE_BUSY
>
>To me, a BUSY state would mean that everything up to actually reading or
>writing the data out is valid, but, the response time coming back was
>just
>too long, so a timeout hit which might mean that a retry later might be
>appropriate.  To me, a timeout = busy, but, locked != busy.  When
>something
>is locked, you're basically denied being able to perform the function for
>one reason or another.  If the file or connection is R/O, that'd be a
>valid
>locked error result for write functions.  If the connection was alive, a
>write to the database was asked, but it took too long to complete, then,
>BUSY would make sense to me.  If the connection was able to send out one
>successful write to the database/WAL, but later down the road the
>transaction took too long to complete, then maybe a BUSY error back would
>be appropriate, but, if the first write failed, then LOCKED might be
>appropriate.
>
>If anything was written to the the DB itself, or the journal file, then
>during the life of that transaction, if write attempt takes too long, the
>result would be BUSY.  If no write was done but took too long, then a
>LOCKED error is the error result.
>
>Disclaimer: I've not had my pot of coffee yet, so I might be missing a
>few
>tidbits of information both in what I've written above, as well as the
>mental think-through when I wrote it. :]
>
>On Wed, Dec 3, 2014 at 9:23 AM, Simon Slavin 
>wrote:
>
>>
>> On 3 Dec 2014, at 2:20pm, Stephen Chrzanowski 
>wrote:
>>
>> > Although I think there is already an error result, one situation
>might be
>> > when the DB is in a read only state.
>>
>> I just thought of the database /file/ being marked 'read-only'.  But it
>> turns out that there's a different SQLite result code for that
>situation.
>>
>> 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



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

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Richard Hipp]

On Wed, Dec 3, 2014 at 10:18 AM, Simon Slavin  wrote:

>
> On 3 Dec 2014, at 3:10pm, Hick Gunter  wrote:
>
> > SQLITE_BUSY means that some connection is BUSY with a write transaction
> and has locked the database file; presumably, it will be possible to write
> to the database when the current writer has finished, just not now or
> within the specified busy timeout.
> >
> > SQLITE_LOCKED otoh means that the calling application is in error and
> has specified two or more transactions whose table access modes are
> incompatible and whose table access orders differ. This situation is
> resolvable only if at least one involved transaction is rolled back.
>
> This is very illuminating and far better information than I managed to
> find in the official SQLite documentation.  It would be really helpful if
> something like this could be incorporated in an appropriate place.
>
>
https://www.sqlite.org/rescode.html#busy

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Simon Slavin]

On 3 Dec 2014, at 3:10pm, Hick Gunter  wrote:

> SQLITE_BUSY means that some connection is BUSY with a write transaction and 
> has locked the database file; presumably, it will be possible to write to the 
> database when the current writer has finished, just not now or within the 
> specified busy timeout.
> 
> SQLITE_LOCKED otoh means that the calling application is in error and has 
> specified two or more transactions whose table access modes are incompatible 
> and whose table access orders differ. This situation is resolvable only if at 
> least one involved transaction is rolled back.

This is very illuminating and far better information than I managed to find in 
the official SQLite documentation.  It would be really helpful if something 
like this could be incorporated in an appropriate place.

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

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Hick Gunter]

I think the error messages are distinct enough as is.

SQLITE_BUSY means that some connection is BUSY with a write transaction and has 
locked the database file; presumably, it will be possible to write to the 
database when the current writer has finished, just not now or within the 
specified busy timeout.

SQLITE_LOCKED otoh means that the calling application is in error and has 
specified two or more transactions whose table access modes are incompatible 
and whose table access orders differ. This situation is resolvable only if at 
least one involved transaction is rolled back.


const char *sqlite3ErrStr(int rc){
  static const char* const aMsg[] = {
...
/* SQLITE_BUSY*/ "database is locked",
/* SQLITE_LOCKED  */ "database table is locked",
...
  };

-Ursprüngliche Nachricht-
Von: Jonathan Moules [mailto:j.mou...@hrwallingford.com]
Gesendet: Mittwoch, 03. Dezember 2014 12:01
An: 'General Discussion of SQLite Database'
Betreff: [sqlite] "database is locked" for SQLITE_BUSY

Hi,
Just a quick request/suggestion.

Currently SQLITE_BUSY events return an error of "Database is locked". Is it 
possible to change this to "Database is busy" or something similar?
I ask because when someone then goes googling for "SQLite database locked", 
they'll end up thinking they're hitting the SQLITE_LOCKED event which as best I 
can tell is very different to the SQLITE_BUSY event.
I'm sure I can't be the only person who has been tripped up by that one.

Thanks,
Jonathan



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom Registered in 
England No. 02562099


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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Stephen Chrzanowski]

To me, a BUSY state would mean that everything up to actually reading or
writing the data out is valid, but, the response time coming back was just
too long, so a timeout hit which might mean that a retry later might be
appropriate.  To me, a timeout = busy, but, locked != busy.  When something
is locked, you're basically denied being able to perform the function for
one reason or another.  If the file or connection is R/O, that'd be a valid
locked error result for write functions.  If the connection was alive, a
write to the database was asked, but it took too long to complete, then,
BUSY would make sense to me.  If the connection was able to send out one
successful write to the database/WAL, but later down the road the
transaction took too long to complete, then maybe a BUSY error back would
be appropriate, but, if the first write failed, then LOCKED might be
appropriate.

If anything was written to the the DB itself, or the journal file, then
during the life of that transaction, if write attempt takes too long, the
result would be BUSY.  If no write was done but took too long, then a
LOCKED error is the error result.

Disclaimer: I've not had my pot of coffee yet, so I might be missing a few
tidbits of information both in what I've written above, as well as the
mental think-through when I wrote it. :]

On Wed, Dec 3, 2014 at 9:23 AM, Simon Slavin  wrote:

>
> On 3 Dec 2014, at 2:20pm, Stephen Chrzanowski  wrote:
>
> > Although I think there is already an error result, one situation might be
> > when the DB is in a read only state.
>
> I just thought of the database /file/ being marked 'read-only'.  But it
> turns out that there's a different SQLite result code for that situation.
>
> 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] "database is locked" for SQLITE_BUSY

[Simon Slavin]

On 3 Dec 2014, at 2:20pm, Stephen Chrzanowski  wrote:

> Although I think there is already an error result, one situation might be
> when the DB is in a read only state.

I just thought of the database /file/ being marked 'read-only'.  But it turns 
out that there's a different SQLite result code for that situation.

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

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Stephen Chrzanowski]

Although I think there is already an error result, one situation might be
when the DB is in a read only state.

On Wed, Dec 3, 2014 at 6:15 AM, RSmith  wrote:

>
> On 2014/12/03 13:00, Jonathan Moules wrote:
>
>> Hi,
>> Just a quick request/suggestion.
>>
>> Currently SQLITE_BUSY events return an error of "Database is locked". Is
>> it possible to change this to "Database is busy" or something similar?
>> I ask because when someone then goes googling for "SQLite database
>> locked", they'll end up thinking they're hitting the SQLITE_LOCKED event
>> which as best I can tell is very different to the SQLITE_BUSY event.
>> I'm sure I can't be the only person who has been tripped up by that one.
>>
>
> I see your point, but is it not in principle the exact same thing? How is
> it "very different"? i.e. - Why else would a DB be "Locked" other than
> being explicitly busy? Or, why else would it be "Busy" other than being
> specifically locked?
>
> Is there an event for which the DB can be "Locked" but not necessarily
> because it is busy?  If so I would like to second this request, but I am
> unaware currently that the case exists.
>
> (Would the case where a DB was opened exclusively by another connection
> return a different error?  I have not checked this yet).
>
>
> ___
> 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] "database is locked" for SQLITE_BUSY

[RSmith]

On 2014/12/03 13:00, Jonathan Moules wrote:

Hi,
Just a quick request/suggestion.

Currently SQLITE_BUSY events return an error of "Database is locked". Is it possible to 
change this to "Database is busy" or something similar?
I ask because when someone then goes googling for "SQLite database locked", 
they'll end up thinking they're hitting the SQLITE_LOCKED event which as best I can tell 
is very different to the SQLITE_BUSY event.
I'm sure I can't be the only person who has been tripped up by that one.


I see your point, but is it not in principle the exact same thing? How is it "very different"? i.e. - Why else would a DB be 
"Locked" other than being explicitly busy? Or, why else would it be "Busy" other than being specifically locked?


Is there an event for which the DB can be "Locked" but not necessarily because it is busy?  If so I would like to second this 
request, but I am unaware currently that the case exists.


(Would the case where a DB was opened exclusively by another connection return 
a different error?  I have not checked this yet).

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

[sqlite] "database is locked" for SQLITE_BUSY

[Jonathan Moules]

Hi,
Just a quick request/suggestion.

Currently SQLITE_BUSY events return an error of "Database is locked". Is it 
possible to change this to "Database is busy" or something similar?
I ask because when someone then goes googling for "SQLite database locked", 
they'll end up thinking they're hitting the SQLITE_LOCKED event which as best I 
can tell is very different to the SQLITE_BUSY event.
I'm sure I can't be the only person who has been tripped up by that one.

Thanks,
Jonathan



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099


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

Re: [sqlite] Database is locked exceptions

[Sohail Somani]

On 2014-10-29, 12:13 PM, Mike McWhinney wrote:

System.Windows.Forms.Control.ControlNativeWindow.OnThreadException(Exception
  e)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
  IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&
  msg)
at
System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32
  dwComponentID, Int32 reason, Int32 pvLoopData)
at


I'm not very knowledgeable about Windows forms but if you are writing 
the SQLite database from multiple threads, that could be the reason. The 
main reasons I've seen this happen are when something is trying to write 
to the database when something else has it open for a read.


If you are using threads, then ensure that reader threads exhaust their 
result sets. Unfortunately some third party libraries do lazy loading 
which doesn't work well with SQLite. Pseudo-code:


# guithread.pseudo
def fetchResults(query):
  while(query.hasMore())
query.fetchMore() # without this, the sqlite result is active...

# writethread.pseudo
def execQuery(query):
  query.exec() # ...which would lock this

If you are not using threads, then it is possible that having the DB 
itself on a network share is causing the problem. I seem to recall some 
issues along these lines in the docs but I don't have any personal 
experience.


Sohail

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

Re: [sqlite] Database is locked exceptions

[Simon Slavin]

On 29 Oct 2014, at 4:13pm, Mike McWhinney  wrote:

> "URI=file:mydb.db; default timeout=10; Pooling=True; Max Pool Size=100;";

Can you please change your timeout to 1 (really, 1ms == 10 seconds) and 
see if this makes the problems go away ?

It may not be necessary to leave the setting like that.  But the information 
about whether this does or doesn't solve the problem may tell us what the 
problem is.

> One thing is that I have tried a method where I open the database and leave 
> it open through out the life
> of the program (open it when I start the program and close on exit). Should I 
> perhaps change this so that 
> I open the database, perform the query, then close it right away?

One usually keeps the database open for the life of the program, the way you 
have it working already.  So lets try other solutions first.

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

[sqlite] Database is locked exceptions

[Mike McWhinney]

Hello,

I have written a program in C# Visual Studio Winforms. In general the SQLite 
database is working quite well.
However, lately we have been plagued with database locking errors:


Here is the exeption:

database is locked
database is locked
   at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, 
CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior 
behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior 
behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
   at WindowsApplication1.Form1.logExceptionToTable()
   at WindowsApplication1.Form1.MyExceptionHandler(Object sender, 
ThreadExceptionEventArgs e)
   at 
System.Windows.Forms.Application.ThreadContext.OnThreadException(Exception
 t)
   at System.Windows.Forms.Control.WndProcException(Exception e)
   at 
System.Windows.Forms.Control.ControlNativeWindow.OnThreadException(Exception
 e)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
 IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&
 msg)
   at 
System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32
 dwComponentID, Int32 reason, Int32 pvLoopData)
   at 
System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32
 reason, ApplicationContext context)
   at 
System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 
reason, ApplicationContext context)
   at System.Windows.Forms.Application.RunDialog(Form form)
   at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
   at System.Windows.Forms.Form.ShowDialog()
   at 
WindowsApplication1.LabTestPatientForm.enterTestDataButton_Click(Object 
sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at DevComponents.DotNetBar.ButtonX.OnClick(EventArgs e)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, 
MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at DevComponents.DotNetBar.ButtonX.WndProc(Message& m)
   at 
System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& 
m)
   at 
System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
 IntPtr wparam, IntPtr lparam)



The database resides on a network on a Windows Server 2008 R2 system. The users 
have full read/write
permissions to the database file.

The code that I'm using to access the database is:

 OMConnection.sqConn = new SQLiteConnection(

"URI=file:mydb.db; default timeout=10; Pooling=True; Max Pool Size=100;";


sqConn.Open();

  

string sql = "SELECT " +
"*" +
" FROM APPOINTMENTTYPES";


 
SQLiteCommand cmd = OMConnection.sqConn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;

SQLiteDataReader dr = cmd.ExecuteReader();
DataTable myTable = new DataTable();
myTable.Load(dr);
//put the categories into the
for (int rowIndex = 0; rowIndex < myTable.Rows.Count; rowIndex++)
{
dataSet1.Tables["ReasonForVisit"].Rows.Add("");

dataSet1.Tables["ReasonForVisit"].Rows[rowIndex]["ReasonForVisitName"] =
 myTable.Rows[rowIndex]["Name"].ToString().Trim();
dataSet1.Tables["ReasonForVisit"].Rows[rowIndex]["Duration"] =
 myTable.Rows[rowIndex]["Duration"];
   
}
myTable.Dispose();

OMConnection.sqConn.Close();
OMConnection.sqConn.Dispose();
OMConnection.sqConn = null;


The thing is, the database locking errors are random and do not always occur. I 
am using the 

c# ADO provider, version 1.0.94.0 (version 3.8.6 of SQLite).  

One thing is that I have tried a method where I open the database and leave it 
open through out the life
of the program (open it when I start the program and close on exit). Should I 
perhaps change this so that 
I open the database, perform the query, then close it right away?

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

Re: [sqlite] Database is locked !

[Fabrice MAUPIN]

I found the problem : In fact I forgot well one "close" call.

Sorry

Fabrice


-Message d'origine-
De : sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] De la part de Fabrice MAUPIN
Envoyé : mercredi 27 août 2014 15:27
À : 'General Discussion of SQLite Database'
Cc : 'Alex Trucy'
Objet : Re: [sqlite] Database is locked !

For information, you will find the "SQLiteJdbc" class used.

I verified (normally) after each statement there is a db.close().

Fabrice


-Message d'origine-
De : sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] De la part de J Decker Envoyé :
mercredi 27 août 2014 15:19 À : General Discussion of SQLite Database Cc :
Alex Trucy Objet : Re: [sqlite] Database is locked !


Sounds like you have statements laying around that weren't properly
destroyed... you say executed correctly, but then also released correctly?


On Wed, Aug 27, 2014 at 6:14 AM, Fabrice MAUPIN  wrote:

> Hi all,
>
>
>
> I try to execute an "INSERT" query and I obtain this message : 
> "Database is locked".
>
>
>
> I verified all the previous connections which were opened were closed 
> and all the previous statements were correctly executed (UPDATE, 
> INSERT,
.).
>
>
>
> The volume of data to be handled is very low.
>
>
>
> For information my configuration :
>
>
> Windows 7 Pro french x64
>
> Eclipse LUNA (4.4) x64
>
> JavaFX8
>
> Sqlite-jdbc-3.7.15-M1.jar
>
>
>
> What are the use cases which could reveal this message (to part them 
> missing
> "close") ?
>
>
>
> If you have a idea.
>
>
>
> Thanks
>
>
>
> Fabrice
>
>
>
>
>
>
>
>
>
> ___
> 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

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

Re: [sqlite] Database is locked !

[Fabrice MAUPIN]

For information, you will find the "SQLiteJdbc" class used.

I verified (normally) after each statement there is a db.close().

Fabrice


-Message d'origine-
De : sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] De la part de J Decker
Envoyé : mercredi 27 août 2014 15:19
À : General Discussion of SQLite Database
Cc : Alex Trucy
Objet : Re: [sqlite] Database is locked !


Sounds like you have statements laying around that weren't properly
destroyed... you say executed correctly, but then also released correctly?


On Wed, Aug 27, 2014 at 6:14 AM, Fabrice MAUPIN  wrote:

> Hi all,
>
>
>
> I try to execute an "INSERT" query and I obtain this message : 
> "Database is locked".
>
>
>
> I verified all the previous connections which were opened were closed 
> and all the previous statements were correctly executed (UPDATE, INSERT,
.).
>
>
>
> The volume of data to be handled is very low.
>
>
>
> For information my configuration :
>
>
> Windows 7 Pro french x64
>
> Eclipse LUNA (4.4) x64
>
> JavaFX8
>
> Sqlite-jdbc-3.7.15-M1.jar
>
>
>
> What are the use cases which could reveal this message (to part them 
> missing
> "close") ?
>
>
>
> If you have a idea.
>
>
>
> Thanks
>
>
>
> Fabrice
>
>
>
>
>
>
>
>
>
> ___
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked !

[J Decker]

Sounds like you have statements laying around that weren't properly
destroyed... you say executed correctly, but then also released correctly?


On Wed, Aug 27, 2014 at 6:14 AM, Fabrice MAUPIN  wrote:

> Hi all,
>
>
>
> I try to execute an "INSERT" query and I obtain this message : "Database is
> locked".
>
>
>
> I verified all the previous connections which were opened were closed and
> all the previous statements were correctly executed (UPDATE, INSERT, .).
>
>
>
> The volume of data to be handled is very low.
>
>
>
> For information my configuration :
>
>
> Windows 7 Pro french x64
>
> Eclipse LUNA (4.4) x64
>
> JavaFX8
>
> Sqlite-jdbc-3.7.15-M1.jar
>
>
>
> What are the use cases which could reveal this message (to part them
> missing
> "close") ?
>
>
>
> If you have a idea.
>
>
>
> Thanks
>
>
>
> Fabrice
>
>
>
>
>
>
>
>
>
> ___
> 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

[sqlite] Database is locked !

[Fabrice MAUPIN]

Hi all,

 

I try to execute an "INSERT" query and I obtain this message : "Database is
locked".

 

I verified all the previous connections which were opened were closed and
all the previous statements were correctly executed (UPDATE, INSERT, .).

 

The volume of data to be handled is very low.

 

For information my configuration :


Windows 7 Pro french x64

Eclipse LUNA (4.4) x64

JavaFX8

Sqlite-jdbc-3.7.15-M1.jar

 

What are the use cases which could reveal this message (to part them missing
"close") ?

 

If you have a idea.

 

Thanks

 

Fabrice

 

 

 

 

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

Re: [sqlite] database is locked with create

[Choi, David]

Hi Richard,

Thank you Richard. 

I get the latest source(sqlite-amalgamation-3071502.zip) and cross-compile. And 
follow your instruction:
sqlite3 -vfs unix-none ex1

That issue is gone.

Really appreciated for your quick and exact response.

David J. Choi


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, February 19, 2013 4:04 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked with create

On Tue, Feb 19, 2013 at 6:37 PM, Choi, David  wrote:

> Hi Richard,
>
> It seems that my version is not accept the command. By the way, what 
> is the version that you are referring to?
>

Get the latest: 3.7.15.1.  There is no reason not to.


>
> Here is my operation:
> /home # sqlite3 --help
> Usage: sqlite3 [OPTIONS] FILENAME [SQL] FILENAME is the name of an 
> SQLite database. A new database is created if the file does not 
> previously exist.
> OPTIONS include:
>-helpshow this message
>-init filename   read/process named file
>-echoprint commands before execution
>-[no]header  turn headers on or off
>-bailstop after hitting an error
>-interactive force interactive I/O
>-batch   force batch I/O
>-column  set output mode to 'column'
>-csv set output mode to 'csv'
>-htmlset output mode to HTML
>-lineset output mode to 'line'
>-listset output mode to 'list'
>-separator 'x'   set output field separator (|)
>-nullvalue 'text'set text string for NULL values
>-version show SQLite version
> /home # sqlite3 -version
> 3.6.23.1
> /home # sqlite3 -vfs unix-none ex1
> sqlite3: Error: unknown option: -vfs
> Use -help for a list of options.
>
>
> Regards,
> David J. Choi
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Tuesday, February 19, 2013 1:44 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked with create
>
> On Tue, Feb 19, 2013 at 4:39 PM, Choi, David 
> wrote:
>
> > Hi Richard,
> >
> > I think your guess is highly possible because the error comes from 
> > sqliteErrorFromPosixError(). In that case, how can I fix the issue?
> >
>
> Try adding the -vfs unix-none command-line option:
>
>  sqlite3 -vfs unix-none ex1
>
> That will disable file locking.  Be warned, however, that with file 
> locking disabled, two processes trying to access the database at the 
> same time can lead to problems.-- D. Richard Hipp 
> drh@sqlite.org___
> 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
>



--
D. Richard Hipp
d...@sqlite.org
___
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] database is locked with create

[Richard Hipp]

On Tue, Feb 19, 2013 at 6:37 PM, Choi, David  wrote:

> Hi Richard,
>
> It seems that my version is not accept the command. By the way, what is
> the version that you are referring to?
>

Get the latest: 3.7.15.1.  There is no reason not to.


>
> Here is my operation:
> /home # sqlite3 --help
> Usage: sqlite3 [OPTIONS] FILENAME [SQL]
> FILENAME is the name of an SQLite database. A new database is created
> if the file does not previously exist.
> OPTIONS include:
>-helpshow this message
>-init filename   read/process named file
>-echoprint commands before execution
>-[no]header  turn headers on or off
>-bailstop after hitting an error
>-interactive force interactive I/O
>-batch   force batch I/O
>-column  set output mode to 'column'
>-csv set output mode to 'csv'
>-htmlset output mode to HTML
>-lineset output mode to 'line'
>-listset output mode to 'list'
>-separator 'x'   set output field separator (|)
>-nullvalue 'text'set text string for NULL values
>-version show SQLite version
> /home # sqlite3 -version
> 3.6.23.1
> /home # sqlite3 -vfs unix-none ex1
> sqlite3: Error: unknown option: -vfs
> Use -help for a list of options.
>
>
> Regards,
> David J. Choi
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Tuesday, February 19, 2013 1:44 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked with create
>
> On Tue, Feb 19, 2013 at 4:39 PM, Choi, David 
> wrote:
>
> > Hi Richard,
> >
> > I think your guess is highly possible because the error comes from
> > sqliteErrorFromPosixError(). In that case, how can I fix the issue?
> >
>
> Try adding the -vfs unix-none command-line option:
>
>  sqlite3 -vfs unix-none ex1
>
> That will disable file locking.  Be warned, however, that with file
> locking disabled, two processes trying to access the database at the same
> time can lead to problems.-- D. Richard Hipp 
> drh@sqlite.org___
> 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
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked with create

[Choi, David]

Hi Richard,

It seems that my version is not accept the command. By the way, what is the 
version that you are referring to?

Here is my operation:
/home # sqlite3 --help
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
   -helpshow this message
   -init filename   read/process named file
   -echoprint commands before execution
   -[no]header  turn headers on or off
   -bailstop after hitting an error
   -interactive force interactive I/O
   -batch   force batch I/O
   -column  set output mode to 'column'
   -csv set output mode to 'csv'
   -htmlset output mode to HTML
   -lineset output mode to 'line'
   -listset output mode to 'list'
   -separator 'x'   set output field separator (|)
   -nullvalue 'text'set text string for NULL values
   -version show SQLite version
/home # sqlite3 -version
3.6.23.1
/home # sqlite3 -vfs unix-none ex1
sqlite3: Error: unknown option: -vfs
Use -help for a list of options.


Regards,
David J. Choi


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, February 19, 2013 1:44 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked with create

On Tue, Feb 19, 2013 at 4:39 PM, Choi, David  wrote:

> Hi Richard,
>
> I think your guess is highly possible because the error comes from 
> sqliteErrorFromPosixError(). In that case, how can I fix the issue?
>

Try adding the -vfs unix-none command-line option:

 sqlite3 -vfs unix-none ex1

That will disable file locking.  Be warned, however, that with file locking 
disabled, two processes trying to access the database at the same time can lead 
to problems.-- D. Richard Hipp d...@sqlite.org 
___
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] database is locked with create

[Richard Hipp]

On Tue, Feb 19, 2013 at 4:39 PM, Choi, David  wrote:

> Hi Richard,
>
> I think your guess is highly possible because the error comes from
> sqliteErrorFromPosixError(). In that case, how can I fix the issue?
>

Try adding the -vfs unix-none command-line option:

 sqlite3 -vfs unix-none ex1

That will disable file locking.  Be warned, however, that with file locking
disabled, two processes trying to access the database at the same time can
lead to problems.--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked with create

[Choi, David]

Hi Richard,

I think your guess is highly possible because the error comes from 
sqliteErrorFromPosixError(). In that case, how can I fix the issue?

Regards,
David J. Choi


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, February 19, 2013 9:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked with create

On Tue, Feb 19, 2013 at 12:01 PM, Choi, David  wrote:

> Hi all,
>
> I am new to sqlite. I build sqlite with buildroot without any issue. 
> After loading sqlite on my embedded board, I always get one error 
> message:"database is locked".
>
> Here is my operation on  my board:
>
> #sqlite3 ex1
> SQLite version 3.6.23.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table tbl1(one varchar(10), two smallint);
> Error: database is locked
> sqlite >
>
> Any suggestion will be highly appreciated.
>

I'm guessing that whatever embedded operating system you are using does not
(correctly) support posix advisory locks.   But that's just a guess.

--
D. Richard Hipp
d...@sqlite.org
___
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