Need SQLite datafiles to be locked on OS level (Windows)

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

Need SQLite datafiles to be locked on OS level (Windows)

Jiří Matějka
We need to lock our SQLite datafiles at the filesystem level, because
our application frequently crashes with "database is locked" error,
which is probably due to other processes opening our files. At least we
identified Windows Search indexing service and McAffee antivirus.

I have tried exclusive locking mode by
PRAGMA locking_mode=EXCLUSIVE;
but this does not prevent reading and modifying the file from other
processes while they are open by SQLite in our process.

After digging in SQLite sources it seems not possible. VFS for Windows
always sends these share mode flags to CreateFile.
dwShareMode = FILE_SHARE_READ | FILE_SHARE_WRITE;

Is there any way to achieve this?

We use C# and System.Data.SQLite but there is no problem to call the
SQLite functions directly. Desktop application, datafiles are stored in
%APP_DATA% subfolder.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Need SQLite datafiles to be locked on OS level (Windows)

Rowan Worth-2
On 24 November 2017 at 16:44, Jiří Matějka <[hidden email]> wrote:

> We need to lock our SQLite datafiles at the filesystem level, because our
> application frequently crashes with "database is locked" error, which is
> probably due to other processes opening our files. At least we identified
> Windows Search indexing service and McAffee antivirus.
>

sqlite does try to work around this kind of interference (see winLock()
"Try 3 times to get the pending lock" and winRetryIoerr() used all over the
place)


> I have tried exclusive locking mode by
> PRAGMA locking_mode=EXCLUSIVE;
> but this does not prevent reading and modifying the file from other
> processes while they are open by SQLite in our process.
>

Right, but it should still prevent OS level "database is locked" errors by
virtue of the fact that SQLite no longer relinquishes its locks in this
mode. So it will only call LockFile/LockFileEx a handful of times before
escalating to the highest possible lock (which _will_ prevent other SQLite
instances from operating on the database until your process closes it).

Do you have more than one connection to the database? Is this error coming
from sqlite3_close? Are you in WAL mode? These introduce other
opportunities for "database is locked" which have nothing to do with the
operating environment.

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

Re: Need SQLite datafiles to be locked on OS level (Windows)

Stephen Chrzanowski
In reply to this post by Jiří Matějka
You might be able to set the timeout to something higher.

https://sqlite.org/pragma.html#pragma_busy_timeout
https://sqlite.org/c3ref/busy_timeout.html


On Fri, Nov 24, 2017 at 3:44 AM, Jiří Matějka <[hidden email]> wrote:

> We need to lock our SQLite datafiles at the filesystem level, because our
> application frequently crashes with "database is locked" error, which is
> probably due to other processes opening our files. At least we identified
> Windows Search indexing service and McAffee antivirus.
>
> I have tried exclusive locking mode by
> PRAGMA locking_mode=EXCLUSIVE;
> but this does not prevent reading and modifying the file from other
> processes while they are open by SQLite in our process.
>
> After digging in SQLite sources it seems not possible. VFS for Windows
> always sends these share mode flags to CreateFile.
> dwShareMode = FILE_SHARE_READ | FILE_SHARE_WRITE;
>
> Is there any way to achieve this?
>
> We use C# and System.Data.SQLite but there is no problem to call the
> SQLite functions directly. Desktop application, datafiles are stored in
> %APP_DATA% subfolder.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Need SQLite datafiles to be locked on OS level (Windows)

Nelson, Erik - 2
In reply to this post by Jiří Matějka
Jirí Matejka Sent on Friday, November 24, 2017 3:45 AM

>but this does not prevent reading and modifying the file from other
>processes while they are open by SQLite in our process.

Does antivirus or Windows randomly modify other applications' files?  It seems like no program could reliably execute under such circumstances.


----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Need SQLite datafiles to be locked on OS level (Windows)

Richard Damon
On 11/24/17 12:20 PM, Nelson, Erik - 2 wrote:
> Jirí Matejka Sent on Friday, November 24, 2017 3:45 AM
>
>> but this does not prevent reading and modifying the file from other
>> processes while they are open by SQLite in our process.
> Does antivirus or Windows randomly modify other applications' files?  It seems like no program could reliably execute under such circumstances.
>
An antivirus program is unlikely to actually modify the files (unless it
thinks it is virus infected and is quarantining the file). What can
happen is the file gets a read lock on it and the application attempts
to get a write lock, and THAT is refused due to the read locks by the
scanner/indexer/backup program.

A basic rule is that even if you don't think anyone else might be
accessing a file, you need to allow for a temporary failure to lock a
file for writting, and retry several times with a timeout.

--
Richard Damon

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

Re: Need SQLite datafiles to be locked on OS level(Windows)

Joe Mistachkin-3

Richard Damon wrote:
>
> A basic rule is that even if you don't think anyone else might be
> accessing a file, you need to allow for a temporary failure to lock a
> file for writting, and retry several times with a timeout.
>

Yes, the Win32 VFS does this.  However, the original poster may wish to
increase the default retry values using the SQLITE_FCNTL_WIN32_AV_RETRY
file control with the sqlite3_file_control() function, see:

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

--
Joe Mistachkin @ https://urn.to/r/mistachkin


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

Re: Need SQLite datafiles to be locked on OS level (Windows)

Keith Medcalf
In reply to this post by Nelson, Erik - 2

No.  

The Windows Search Indexer or Windows Defender only processes files when they are "closed" (that is, the act of closing a file adds it to the queue of files to be processed).  As long as they are "open" they are not enqueued for processing.  There are a few exceptions, but they all require "application support" to behave in the non-default "do not fiddle-faddle with open files".

To do otherwise would be completely foolish (or more correctly, brain-dead).

Of course, if the software in question is a third-party thing such as from Slimeytech, McCrappy, or another brain dead vendor, then it is possible that it fiddle-faddles with files that are "in use" because it requires a certain minimum (but very low) wattage to realize what a really truly bad idea (ie, completely and utterly brain-dead) that is.

---
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-
>[hidden email]] On Behalf Of Nelson, Erik - 2
>Sent: Friday, 24 November, 2017 10:20
>To: SQLite mailing list
>Subject: Re: [sqlite] Need SQLite datafiles to be locked on OS level
>(Windows)
>
>Jirí Matejka Sent on Friday, November 24, 2017 3:45 AM
>
>>but this does not prevent reading and modifying the file from other
>>processes while they are open by SQLite in our process.
>
>Does antivirus or Windows randomly modify other applications' files?
>It seems like no program could reliably execute under such
>circumstances.
>
>
>---------------------------------------------------------------------
>-
>This message, and any attachments, is for the intended recipient(s)
>only, may contain information that is privileged, confidential and/or
>proprietary and subject to important terms and conditions available
>at http://www.bankofamerica.com/emaildisclaimer.   If you are not the
>intended recipient, please delete this message.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: Need SQLite datafiles to be locked on OS level (Windows)

Jiří Matějka
Thanks for your responses.
Actually we have two problems:

a) "database is locked" which in majority comes from sqlite3_step doing
BEGIN IMMEDIATE which indeed is obtaining a write lock
Basically we have just one connection per one datafile except one case
when we have two connections for one datafile (one is read/write and the
other is read only).
Now I see that all "database is locked" problems comes from these shared
datafile connections. So maybe this problem is actually somewhere inside
our application.

We have WAL but I don't understand how WAL introduces opportunity for
"database is locked" errors.

We have no busy timeout set because we use System.Data.SQlite library
which has its own waiting mechanism (repeating the
sqlite3_reset/sqlite3_step within 30 secons).

b) "database disk image is malformed", which is sometimes really caused
by AV software. Our application is an email client, so the antivirus
could theoretically found a virus signature inside datafile (but why
would it just modify the file instead of moving to the quarantine as
whole?).
We also have many reports with "database schema has changed" errors.


I have implemented locking_mode=exclusive (along with shared cache) and
will check if this makes difference.

Best regards,
Jiri

On 24.11.2017 20:08:34, "Keith Medcalf" <[hidden email]> wrote:

>
>No.
>
>The Windows Search Indexer or Windows Defender only processes files
>when they are "closed" (that is, the act of closing a file adds it to
>the queue of files to be processed). As long as they are "open" they
>are not enqueued for processing. There are a few exceptions, but they
>all require "application support" to behave in the non-default "do not
>fiddle-faddle with open files".
>
>To do otherwise would be completely foolish (or more correctly,
>brain-dead).
>
>Of course, if the software in question is a third-party thing such as
>from Slimeytech, McCrappy, or another brain dead vendor, then it is
>possible that it fiddle-faddles with files that are "in use" because it
>requires a certain minimum (but very low) wattage to realize what a
>really truly bad idea (ie, completely and utterly brain-dead) that is.
>
>---
>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-
>>[hidden email]] On Behalf Of Nelson, Erik - 2
>>Sent: Friday, 24 November, 2017 10:20
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Need SQLite datafiles to be locked on OS level
>>(Windows)
>>
>>Jirí Matejka Sent on Friday, November 24, 2017 3:45 AM
>>
>>>but this does not prevent reading and modifying the file from other
>>>processes while they are open by SQLite in our process.
>>
>>Does antivirus or Windows randomly modify other applications' files?
>>It seems like no program could reliably execute under such
>>circumstances.
>>
>>
>>---------------------------------------------------------------------
>>-
>>This message, and any attachments, is for the intended recipient(s)
>>only, may contain information that is privileged, confidential and/or
>>proprietary and subject to important terms and conditions available
>>at http://www.bankofamerica.com/emaildisclaimer. If you are not the
>>intended recipient, please delete this message.
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Need SQLite datafiles to be locked on OS level (Windows)

Simon Slavin-3


On 27 Nov 2017, at 12:41pm, Jiří Matějka <[hidden email]> wrote:

> We have no busy timeout set because we use System.Data.SQlite library which has its own waiting mechanism (repeating the sqlite3_reset/sqlite3_step within 30 secons).

I’m hoping that Joe Mistachkin, or someone else who understands that library sees this, just to check that it works the way you’re expecting it to.

> b) "database disk image is malformed", which is sometimes really caused by AV software. Our application is an email client, so the antivirus could theoretically found a virus signature inside datafile (but why would it just modify the file instead of moving to the quarantine as whole?).
> We also have many reports with "database schema has changed" errors.

There are millions of installations on Wintel computers which have AV software installed.  We do not have millions of complaints about this problem.  This suggests that your setup is unusual.  As you write, AV software is expected to handle a SQLite database file as a whole: it could delete it, put protection on it, or move it.  Since AV software doesn’t understand the format of SQLite databases it should not be modifying the contents of the file leading to "schema has changed" but not "database corrupt".

Can you investigate further ?  Or tell us more about what you already know ?

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

Re: Need SQLite datafiles to be locked on OS level(Windows)

Joe Mistachkin-3

Simon Slavin wrote:
>
> I'm hoping that Joe Mistachkin, or someone else who understands
> that library sees this, just to check that it works the way
> you're expecting it to.
>

Judging from the description so far, I think that both SQLite and
System.Data.SQLite are working correctly.

It is true that System.Data.SQLite has an internal retry mechanism
that will wait 150ms between retries.   The PrepareRetries property
for the SQLiteConnection object is the maximum number of retries.
By default, it is 3, which means the total wait should be around
450ms by default.

That being said, I have not personally seen any built-in Windows
application or system service prevent SQLite from access a file
for more than a fraction of a second.

Maybe things are different if the Windows AV subsystem actually
finds something "bad" in the file?

--
Joe Mistachkin


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

Re: Need SQLite datafiles to be locked on OS level(Windows)

Simon Slavin-3
On 27 Nov 2017, at 4:51pm, Joe Mistachkin <[hidden email]> wrote:

> Judging from the description so far, I think that both SQLite and
> System.Data.SQLite are working correctly.
>
> It is true that System.Data.SQLite has an internal retry mechanism
> that will wait 150ms between retries.   The PrepareRetries property
> for the SQLiteConnection object is the maximum number of retries.
> By default, it is 3, which means the total wait should be around
> 450ms by default.

Good to have someone who actually understands System.Data.SQLite contribute.

> That being said, I have not personally seen any built-in Windows
> application or system service prevent SQLite from access a file
> for more than a fraction of a second.
>
> Maybe things are different if the Windows AV subsystem actually
> finds something "bad" in the file?

It’s complicated.  Windows has its own AV system which scans files.  That one cannot cause "schema changed", since it does things to the file (change protection, move, rename, delete).

But the OP may also have installed another AV system.  And there are lots of those and one or more may think they should be making chances inside a SQLite database.

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

Re: Need SQLite datafiles to be locked on OS level(Windows)

Nelson, Erik - 2

Simon Slavin wrote on  Monday, November 27, 2017 12:39 PM

> Subject: Re: [sqlite] Need SQLite datafiles to be locked on OS level(Windows)
>
> On 27 Nov 2017, at 4:51pm, Joe Mistachkin <[hidden email]> wrote:
>
> > Judging from the description so far, I think that both SQLite and
> > System.Data.SQLite are working correctly.
> >
> > It is true that System.Data.SQLite has an internal retry mechanism
> > that will wait 150ms between retries.   The PrepareRetries property
> > for the SQLiteConnection object is the maximum number of retries.
> > By default, it is 3, which means the total wait should be around
> > 450ms by default.

> But the OP may also have installed another AV system.  And there are lots of
> those and one or more may think they should be making chances inside a
> SQLite database.

It's inconceivable to me that any non-malicious application (including AV system) would randomly change the contents of files it didn't own.  I usually find the source of these kinds of things to be errors in my own program.

(granted that the word may not mean what I think it means)

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Need SQLite datafiles to be locked on OS level(Windows)

David Raymond
You sir win the quote award for today.

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Nelson, Erik - 2
Sent: Monday, November 27, 2017 12:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] Need SQLite datafiles to be locked on OS level(Windows)


It's inconceivable ...

(granted that the word may not mean what I think it means)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users