[TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[Warren Young]

This is a big-push attempt at a version of Cygwin SQLite that will make 
everyone happy (ha!) whether they want POSIX advisory locking behavior 
or Windows mandatory locking behavior.  My part of the effort is being 
stubborn on this point and doing the basic testing and packaging.  The 
real magic was added by Corinna to yesterday's 1.7.19 cygwin1.dll snapshot.

This build relies on a new feature in that snapshot, so you must install 
it first.  If you want to try this SQLite build, check with uname -r 
that you are in fact running the new DLL, else it will *silently* cope 
with the error that results.  (This is a feature.)

This test build of Cygwin SQLite does several things at once:

- It switches back to a "Unix mode" build, as we tried in 3.7.12.1-1. 
As a result, the hacky patch to make it use the Unix mode /tmp path 
selection logic has been removed.

- By default, it requests mandatory locking using the feature added in 
yesterday's Cygwin 1.7.19 snapshot.  This should make it cooperate with 
native Windows programs also using SQLite despite running in Unix mode.

I have no STC for this, so I don't know if it is effective.  The only 
test I know of requires running Tortoise SVN with all the Explorer 
integration features enabled, and I simply don't want to do that to my 
development box.  The test, for those who have done this to *their* box, 
is to try using Cygwin svn on a directory while that same directory is 
open in Explorer.  Bounce back and forth between the two svn clients. 
If they don't seem to interfere, the new feature in the Cygwin DLL is 
probably working as expected.

- This mandatory-locks-by-default feature of this SQLite build can be 
disabled by setting the new CYGWIN_SQLITE_LOCKING environment variable 
to "posix".  (The value is not case-sensitive, so you can spell it 
"POSIX" if that makes you feel better.)  This should finally allow those 
like Achim Gratz to use the official Cygwin SQLite build, as it 
effectively makes it behave like a stock Unix mode build.

- This build also switches the temporary storage strategy to 2, which 
makes it always use in-memory temporary files.  Although running SQLite 
in pure Unix mode by setting the above environment variable should 
obviate the need for this, I want to try shipping SQLite so people don't 
need to do that for things like monotone to work.  I'll consider 
reverting this change if there is some need for huge DB files which no 
longer open with this change.

Downloads are at http://etr-usa.com/cygwin/sqlite3/

Broadly speaking, you want the files with "3.7.17-1" in their name.  The 
one(s) you want may be buried a level deep, since that tree mirrors the 
Cygwin repository mirror structure.

If you want to test using the interactive sqlite3.exe program, it's in 
the package at the top level.

If you want to test instead using existing binaries dynamically linked 
to cygsqlite3-0.dll, you should only need to download the one package in 
the libsqlite3_0 subdirectory.

You shouldn't need the -devel and -debuginfo packages.  Your current 
-devel should suffice to build against the new DLL.

--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[David Rothenberger]

On 6/3/2013 1:11 PM, Warren Young wrote:
> This is a big-push attempt at a version of Cygwin SQLite that will make
> everyone happy (ha!) whether they want POSIX advisory locking behavior
> or Windows mandatory locking behavior.  My part of the effort is being
> stubborn on this point and doing the basic testing and packaging.  The
> real magic was added by Corinna to yesterday's 1.7.19 cygwin1.dll snapshot.

Thank you (and thank you Corinna!) for all your hard work and
perseverance with this issue.

Unfortunately...

> - By default, it requests mandatory locking using the feature added in
> yesterday's Cygwin 1.7.19 snapshot.  This should make it cooperate with
> native Windows programs also using SQLite despite running in Unix mode.
> 
> - This mandatory-locks-by-default feature of this SQLite build can be
> disabled by setting the new CYGWIN_SQLITE_LOCKING environment variable
> to "posix".

... initial results with the Subversion test suite (for 1.8.0-rc2) show
that most tests fail with a "sqlite: database is locked (S5)" error
unless CYGWIN_SQLITE_LOCKING=posix.

The good news is that the test cases that relied on temporary tables are
now passing.

I'll try the 1.7.10 test suite shortly and report back on the results,
although I expect them to be the same.

-- 
David Rothenberger  ----  daveroth@acm.org

Water, taken in moderation cannot hurt anybody.
                -- Mark Twain

--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[David Rothenberger]

On 6/3/2013 5:58 PM, David Rothenberger wrote:
> I'll try the 1.7.10 test suite shortly and report back on the results,
> although I expect them to be the same.

Confirmed. And I get problems in practice using subversion without
setting the locking mode to posix.

-- 
David Rothenberger  ----  daveroth@acm.org

"I don't know where we come from,
 Don't know where we're going to,
 And if all this should have a reason,
 We would be the last to know.

 So let's just hope there is a promised land,
 And until then,
 ...as best as you can."
                -- Steppenwolf, "Rock Me Baby"

--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[Corinna Vinschen]

On Jun  3 17:58, David Rothenberger wrote:
> On 6/3/2013 1:11 PM, Warren Young wrote:
> > This is a big-push attempt at a version of Cygwin SQLite that will make
> > everyone happy (ha!) whether they want POSIX advisory locking behavior
> > or Windows mandatory locking behavior.  My part of the effort is being
> > stubborn on this point and doing the basic testing and packaging.  The
> > real magic was added by Corinna to yesterday's 1.7.19 cygwin1.dll snapshot.
> 
> Thank you (and thank you Corinna!) for all your hard work and
> perseverance with this issue.
> 
> Unfortunately...
> 
> > - By default, it requests mandatory locking using the feature added in
> > yesterday's Cygwin 1.7.19 snapshot.  This should make it cooperate with
> > native Windows programs also using SQLite despite running in Unix mode.
> > 
> > - This mandatory-locks-by-default feature of this SQLite build can be
> > disabled by setting the new CYGWIN_SQLITE_LOCKING environment variable
> > to "posix".
> 
> ... initial results with the Subversion test suite (for 1.8.0-rc2) show
> that most tests fail with a "sqlite: database is locked (S5)" error
> unless CYGWIN_SQLITE_LOCKING=posix.

The question now is: Why?  The problem here is that the semantics of
POSIX locks and Windows locks is so very different.  I guess that
sqlite, when build in POSIX mode, (rightfully) assumes that the POSIX
locks behave like POSIX locks and so uses them accordingly.  This
collides with the way Windows locks work.

If so, mandatory locking via fcntl locks is pretty much useless in this
scenario.  The application using it has to know that the semantics are
different and so create another code path which respects the annoying
Windows lock behaviour (like the fact that a write lock blocks another
write lock even if both are requested by the same process using the same
HANDLE).

A potential workaround is to use BSD flock locks in sqlite.  Given that
they lock the entire file, the behaviour is not so prone to the problems
of Windows locks.
It's easy enough to add that to Cygwin, so I'll do that within the hour.
That requires another sqlite test release, obviouly.


Corinna

-- 
Corinna Vinschen                  Please, send mails regarding Cygwin to
Cygwin Maintainer                 cygwin AT cygwin DOT com
Red Hat

--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[Corinna Vinschen]

On Jun  4 10:41, Corinna Vinschen wrote:
> On Jun  3 17:58, David Rothenberger wrote:
> > On 6/3/2013 1:11 PM, Warren Young wrote:
> > > This is a big-push attempt at a version of Cygwin SQLite that will make
> > > everyone happy (ha!) whether they want POSIX advisory locking behavior
> > > or Windows mandatory locking behavior.  My part of the effort is being
> > > stubborn on this point and doing the basic testing and packaging.  The
> > > real magic was added by Corinna to yesterday's 1.7.19 cygwin1.dll snapshot.
> > [...]
> > ... initial results with the Subversion test suite (for 1.8.0-rc2) show
> > that most tests fail with a "sqlite: database is locked (S5)" error
> > unless CYGWIN_SQLITE_LOCKING=posix.
> 
> The question now is: Why?

IOW: It would be nice to have a simple testcase (plain C, only Cygwin
POSIX calls, self-contained, yada yada) to see what sqlite expects in
POSIX lock mode.

> The problem here is that the semantics of
> POSIX locks and Windows locks is so very different.  I guess that
> sqlite, when build in POSIX mode, (rightfully) assumes that the POSIX
> locks behave like POSIX locks and so uses them accordingly.  This
> collides with the way Windows locks work.
> 
> If so, mandatory locking via fcntl locks is pretty much useless in this
> scenario.  The application using it has to know that the semantics are
> different and so create another code path which respects the annoying
> Windows lock behaviour (like the fact that a write lock blocks another
> write lock even if both are requested by the same process using the same
> HANDLE).
> 
> A potential workaround is to use BSD flock locks in sqlite.  Given that
> they lock the entire file, the behaviour is not so prone to the problems
> of Windows locks.
> It's easy enough to add that to Cygwin, so I'll do that within the hour.
> That requires another sqlite test release, obviouly.

As for "potential": If sqlite tries to convert an existing flock lock
into another type (read lock <-> write lock), this, too, will fail with
Windows locks underneath.  Windows locks require that the existing lock
is unlocked first.

Which gives me an idea.  What if each call to F_SETLK{W} first calls
NtUnlockFile on the given offset and length parameters?  This would
allow overwriting locks held by the same descriptor.  This should at
least help the flock case and often even in the fcntl case.  The fcntl
case which is not solvable is splitting and merging of existing locks.

[...time passes...]

On the other hand, this is non-atomic.  What could happen then is that a
flock call unlocks the existing lock, a task switch occurs, another
process locks the file successfully, and the flock call fails with an
unlocked file (LOCK_NB), or waits until further notice.  And then the
application might not find what it's expecting.

That's SO sick.


Corinna

-- 
Corinna Vinschen                  Please, send mails regarding Cygwin to
Cygwin Maintainer                 cygwin AT cygwin DOT com
Red Hat

--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[Corinna Vinschen]

On Jun  4 10:41, Corinna Vinschen wrote:
> On Jun  3 17:58, David Rothenberger wrote:
> > On 6/3/2013 1:11 PM, Warren Young wrote:
> > > This is a big-push attempt at a version of Cygwin SQLite that will make
> > > everyone happy (ha!) whether they want POSIX advisory locking behavior
> > > or Windows mandatory locking behavior.  My part of the effort is being
> > > stubborn on this point and doing the basic testing and packaging.  The
> > > real magic was added by Corinna to yesterday's 1.7.19 cygwin1.dll snapshot.
> > 
> > Thank you (and thank you Corinna!) for all your hard work and
> > perseverance with this issue.
> > 
> > Unfortunately...
> > 
> > > - By default, it requests mandatory locking using the feature added in
> > > yesterday's Cygwin 1.7.19 snapshot.  This should make it cooperate with
> > > native Windows programs also using SQLite despite running in Unix mode.
> > > 
> > > - This mandatory-locks-by-default feature of this SQLite build can be
> > > disabled by setting the new CYGWIN_SQLITE_LOCKING environment variable
> > > to "posix".
> > 
> > ... initial results with the Subversion test suite (for 1.8.0-rc2) show
> > that most tests fail with a "sqlite: database is locked (S5)" error
> > unless CYGWIN_SQLITE_LOCKING=posix.
> 
> The question now is: Why?  The problem here is that the semantics of
> POSIX locks and Windows locks is so very different.  I guess that
> sqlite, when build in POSIX mode, (rightfully) assumes that the POSIX
> locks behave like POSIX locks and so uses them accordingly.  This
> collides with the way Windows locks work.
> 
> If so, mandatory locking via fcntl locks is pretty much useless in this
> scenario.  The application using it has to know that the semantics are
> different and so create another code path which respects the annoying
> Windows lock behaviour (like the fact that a write lock blocks another
> write lock even if both are requested by the same process using the same
> HANDLE).
> 
> A potential workaround is to use BSD flock locks in sqlite.  Given that
> they lock the entire file, the behaviour is not so prone to the problems
> of Windows locks.
> It's easy enough to add that to Cygwin, so I'll do that within the hour.
> That requires another sqlite test release, obviouly.

Despite the Windows shortcomings outlined in my other mail, I created
this patch.  It enables mandatory locking for flock and lockf.  The way
to enable it is still the F_LCK_MANDATORY flag.

I uploaded a new 32 bit snapshot 2013-06-04 and a 1.7.19-10 64 bit tesst
release.  Please test.


Thanks,
Corinna

-- 
Corinna Vinschen                  Please, send mails regarding Cygwin to
Cygwin Maintainer                 cygwin AT cygwin DOT com
Red Hat

--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[David Rothenberger]

On 6/4/2013 2:37 AM, Corinna Vinschen wrote:
> On Jun  4 10:41, Corinna Vinschen wrote:
>> On Jun  3 17:58, David Rothenberger wrote:
>>> On 6/3/2013 1:11 PM, Warren Young wrote:
>>>> This is a big-push attempt at a version of Cygwin SQLite that will make
>>>> everyone happy (ha!) whether they want POSIX advisory locking behavior
>>>> or Windows mandatory locking behavior.  My part of the effort is being
>>>> stubborn on this point and doing the basic testing and packaging.  The
>>>> real magic was added by Corinna to yesterday's 1.7.19 cygwin1.dll snapshot.
>>> [...]
>>> ... initial results with the Subversion test suite (for 1.8.0-rc2) show
>>> that most tests fail with a "sqlite: database is locked (S5)" error
>>> unless CYGWIN_SQLITE_LOCKING=posix.
>>
>> The question now is: Why?
> 
> IOW: It would be nice to have a simple testcase (plain C, only Cygwin
> POSIX calls, self-contained, yada yada) to see what sqlite expects in
> POSIX lock mode.

This may take some time or be completely over my head given all the
layers involved with the Subversion test suite (python bindings,
subversion code, libapr1). I probably won't get to it for a few days or
longer.

-- 
David Rothenberger  ----  daveroth@acm.org

Kliban's First Law of Dining:
        Never eat anything bigger than your head.

--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[Corinna Vinschen]

On Jun  4 08:25, David Rothenberger wrote:
> On 6/4/2013 2:37 AM, Corinna Vinschen wrote:
> > On Jun  4 10:41, Corinna Vinschen wrote:
> >> On Jun  3 17:58, David Rothenberger wrote:
> >>> On 6/3/2013 1:11 PM, Warren Young wrote:
> >>>> This is a big-push attempt at a version of Cygwin SQLite that will make
> >>>> everyone happy (ha!) whether they want POSIX advisory locking behavior
> >>>> or Windows mandatory locking behavior.  My part of the effort is being
> >>>> stubborn on this point and doing the basic testing and packaging.  The
> >>>> real magic was added by Corinna to yesterday's 1.7.19 cygwin1.dll snapshot.
> >>> [...]
> >>> ... initial results with the Subversion test suite (for 1.8.0-rc2) show
> >>> that most tests fail with a "sqlite: database is locked (S5)" error
> >>> unless CYGWIN_SQLITE_LOCKING=posix.
> >>
> >> The question now is: Why?
> > 
> > IOW: It would be nice to have a simple testcase (plain C, only Cygwin
> > POSIX calls, self-contained, yada yada) to see what sqlite expects in
> > POSIX lock mode.
> 
> This may take some time or be completely over my head given all the
> layers involved with the Subversion test suite (python bindings,
> subversion code, libapr1). I probably won't get to it for a few days or
> longer.

Thanks for the offer.  But really, it's not at all pressing, since it
would probably be mainly for curiosity.  There's just no way to improve
Windows locking.


Corinna

-- 
Corinna Vinschen                  Please, send mails regarding Cygwin to
Cygwin Maintainer                 cygwin AT cygwin DOT com
Red Hat

--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[David Stacey]

On 04/06/13 01:58, David Rothenberger wrote:
> ... initial results with the Subversion test suite (for 1.8.0-rc2) show
> that most tests fail with a "sqlite: database is locked (S5)" error
> unless CYGWIN_SQLITE_LOCKING=posix.

It appears that much water has already passed under the proverbial 
bridge, but I gave this a test and also suffered from the same problems. 
Environment was WinXP SP3 with Cygwin snapshot 2013-06-03 and 
TortoiseSVN 1.7.13. I found that a reverse merge crashed quite reliably:

$ svn merge -r 5606:5605 file.txt
svn: E200033: database is locked, executing statement 'RELEASE   s0'

$ export CYGWIN_SQLITE_LOCKING=posix

$ svn merge -r 5606:5605 file.txt
svn: E200030: disk I/O error, executing statement 'RELEASE   s12'
svn: E200030: sqlite: disk I/O error
svn: E200030: sqlite: disk I/O error
svn: E200030: no such savepoint: s13, executing statement 'RELEASE s13'
svn: E200030: no such savepoint: s13, executing statement 'ROLLBACK TO s13'

Cheers,

Dave.


--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[Achim Gratz]

I'll try to free some time on the weekend for testing.

David Rothenberger writes:
> ... initial results with the Subversion test suite (for 1.8.0-rc2) show
> that most tests fail with a "sqlite: database is locked (S5)" error
> unless CYGWIN_SQLITE_LOCKING=posix.

This is in all likelihood a good thing since it finally recognizes why
it fails instead of defaulting to "disk I/O error".  The Windows code in
SQLite also has an exponential backoff / retry that probably needs to be
ported to the UNIX VFS code for Cygwin.

> The good news is that the test cases that relied on temporary tables are
> now passing.

Yes, but that  is unrelated to the locking.


Regards,
Achim.
-- 
+<[Q+ Matrix-12 WAVE#46+305 Neuron microQkb Andromeda XTk Blofeld]>+

Factory and User Sound Singles for Waldorf rackAttack:
http://Synth.Stromeko.net/Downloads.html#WaldorfSounds


--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[Achim Gratz]

Warren Young writes:
> This is a big-push attempt at a version of Cygwin SQLite that will
> make everyone happy (ha!) whether they want POSIX advisory locking
> behavior or Windows mandatory locking behavior.  My part of the effort
> is being stubborn on this point and doing the basic testing and
> packaging.  The real magic was added by Corinna to yesterday's 1.7.19
> cygwin1.dll snapshot.

I hope to find some time for testing over the weekend.


Regards,
Achim.
-- 
+<[Q+ Matrix-12 WAVE#46+305 Neuron microQkb Andromeda XTk Blofeld]>+

Wavetables for the Terratec KOMPLEXER:
http://Synth.Stromeko.net/Downloads.html#KomplexerWaves


--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[Warren Young]

On 6/4/2013 02:41, Corinna Vinschen wrote:
> On Jun  3 17:58, David Rothenberger wrote:
>>
>> ... initial results with the Subversion test suite (for 1.8.0-rc2) show
>> that most tests fail with a "sqlite: database is locked (S5)" error
>> unless CYGWIN_SQLITE_LOCKING=posix.
>
> The question now is: Why?  The problem here is that the semantics of
> POSIX locks and Windows locks is so very different.  I guess that
> sqlite, when build in POSIX mode, (rightfully) assumes that the POSIX
> locks behave like POSIX locks and so uses them accordingly.  This
> collides with the way Windows locks work.

This is certainly true.  We may well end up deciding that, ugly as it 
is, the Win32-specific code in SQLite's unhacked Cygwin build actually 
makes sense after all, insofar as it encodes the correct way to do 
locking on Windows *for SQLite*.

If that ends up being the case, I may be able to exploit SQLite's VFS 
fallback mechanism to get a SQLite build with both the preexisting 
Cygwin Win32 locking VFS as well as the POSIX locking VFS in a single 
executable.  That would let me switch between them at run time using the 
environment variable I've already defined.

> It's easy enough to add that to Cygwin, so I'll do that within the hour.
> That requires another sqlite test release, obviouly.

Okay, I've just released a set of -2 packages.

It required a spelunking expedition, complete with yak wagons and a team 
of five sherpas, one of which had to be left behind with a broken leg, 
but I figured out how to compile in the BSD locking VFS without 
disabling the POSIX one, then how to make SQLite switch between them at 
runtime.  (This is what gives me hope for Plan C, above.)

The CYGWIN_SQLITE_LOCKING variable now has three possible states:

CYGWIN_SQLITE_LOCKING=posix  -- same as before

CYGWIN_SQLITE_LOCKING=posixmand -- equivalent to leaving it undefined in 
the 3.7.17-1 build

CYGWIN_SQLITE_LOCKING=AnythingElse -- BSD flock() based mandatory locking.

(Yes, this means there's no way to make it use BSD advisory locks.  I 
couldn't come up with a good reason to add that fourth code path, but if 
it's needed, I'll add it.)

Same download path as before: http://etr-usa.com/cygwin/sqlite3/

--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[Warren Young]

On 6/4/2013 03:37, Corinna Vinschen wrote:
>
> It would be nice to have a simple testcase (plain C, only Cygwin
> POSIX calls, self-contained, yada yada) to see what sqlite expects in
> POSIX lock mode.

SQLite locking is a hairball.  It is spread over a range of about 2.5 
kLOC within the 140 kLOC sqlite3.c file, and those routines are behind 
about three layers of indirection from the mainline SQLite code.

Mind, I'm not talking about the *callers* of this code here, just the 
locking routines themselves.  Add in all the callers and *their* 
expectations and...well...<shudder>

I think we have to define the problem as "SQLite locking requires what 
SQLite requires today."

Luckily, while creating the -2 packages, I stumbled across a STC in SQL 
that replicates the reported problem:

$ ./sqlite3 foo.db 'create table fred(id integer, barney text)'
$ CYGWIN_SQLITE_LOCKING=posixmand ./sqlite3 foo.db \
   'insert into fred(barney) values("wilma")'

If you change the locking mode to either "bsd" or "posix", it doesn't 
fail.  This gives hope that your new flock() implementation may work for us.

Here's hoping you can get the info you want from running the second 
command under strace.

I can tell you that it doesn't fail if you don't do a DB write.  For 
example, this succeeds regardless of locking strategy:

$ ./sqlite3 foo.db .q

You can see it open the DB file and turn on mandatory locking, but since 
it doesn't write to the DB file, it doesn't actually call flock().  The 
same goes for the other two locking strategies.

--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[Warren Young]

On 6/5/2013 13:22, Achim Gratz wrote:
>
> David Rothenberger writes:
>> The good news is that the test cases that relied on temporary tables are
>> now passing.
>
> Yes, but that  is unrelated to the locking.

I was assuming that the switch from disk-based temp tables to in-memory 
temp tables helped because it avoided the need to do disk file locking. 
  In that sense, it is a way to fix part of the problem.

--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[David Rothenberger]

On 6/5/2013 12:50 PM, Warren Young wrote:
> It required a spelunking expedition, complete with yak wagons and a team
> of five sherpas, one of which had to be left behind with a broken leg,
> but I figured out how to compile in the BSD locking VFS without
> disabling the POSIX one, then how to make SQLite switch between them at
> runtime.  (This is what gives me hope for Plan C, above.)

Thanks again for all your effort here.

> The CYGWIN_SQLITE_LOCKING variable now has three possible states:
> 
> CYGWIN_SQLITE_LOCKING=posix  -- same as before
> 
> CYGWIN_SQLITE_LOCKING=posixmand -- equivalent to leaving it undefined in
> the 3.7.17-1 build
> 
> CYGWIN_SQLITE_LOCKING=AnythingElse -- BSD flock() based mandatory locking.

I think the "AnythingElse" setting is what you intended for Subversion
users that want to interoperate with Windows?

The Subversion test suite passes with that setting. I don't know about
issues with TortoiseSVN since I don't use it personally.

-- 
David Rothenberger  ----  daveroth@acm.org

"Experience has proved that some people indeed know everything."
                -- Russell Baker

--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[Warren Young]

On 6/5/2013 16:27, David Rothenberger wrote:
> I think the "AnythingElse" setting is what you intended for Subversion
> users that want to interoperate with Windows?

Yes, though there is a chance that my SQL STC will clue someone into the 
needed fix for posixmand mode so SQLite can work in this role with that 
locking mode enabled, too.

--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[Corinna Vinschen]

On Jun  5 14:06, Warren Young wrote:
> On 6/4/2013 03:37, Corinna Vinschen wrote:
> >
> >It would be nice to have a simple testcase (plain C, only Cygwin
> >POSIX calls, self-contained, yada yada) to see what sqlite expects in
> >POSIX lock mode.
> 
> SQLite locking is a hairball.  It is spread over a range of about
> 2.5 kLOC within the 140 kLOC sqlite3.c file, and those routines are
> behind about three layers of indirection from the mainline SQLite
> code.
> 
> Mind, I'm not talking about the *callers* of this code here, just
> the locking routines themselves.  Add in all the callers and *their*
> expectations and...well...<shudder>
> 
> I think we have to define the problem as "SQLite locking requires
> what SQLite requires today."
> 
> Luckily, while creating the -2 packages, I stumbled across a STC in
> SQL that replicates the reported problem:
> 
> $ ./sqlite3 foo.db 'create table fred(id integer, barney text)'
> $ CYGWIN_SQLITE_LOCKING=posixmand ./sqlite3 foo.db \
>   'insert into fred(barney) values("wilma")'

So, here's what happens.  The below list contains the lock requests
of sqlite up to the point where it fails.  "r" is a read lock request,
"w" a write lock request, "u" an unlock request.  The next two values
are the byte offset and byte length of the lock, the last is the
status code after calling the Windows Lock/Unlock function:

  A.  r 1073741824 1    STATUS_SUCCESS
  B.  r 1073741826 510  STATUS_SUCCESS
  C.  u 1073741824 1    STATUS_SUCCESS
  D.  u          0 0    STATUS_RANGE_NOT_LOCKED ==> STATUS_SUCCESS
  E.  r 1073741824 1    STATUS_SUCCESS
  F.  r 1073741826 510  STATUS_SUCCESS
  G.  u 1073741824 1    STATUS_SUCCESS
  H.  w 1073741825 1    STATUS_SUCCESS
  I.  w 1073741824 1    STATUS_SUCCESS
  J.  w 1073741826 510  STATUS_LOCK_NOT_GRANTED
  K.  r 1073741826 510  STATUS_SUCCESS
  L.  u 1073741824 2    STATUS_RANGE_NOT_LOCKED ==> STATUS_SUCCESS
  M.  u          0 0    STATUS_RANGE_NOT_LOCKED ==> STATUS_SUCCESS

There are two problems:

The lazy unlock request D tells the system to unlock all locks on the
entire file.  This works fine with POSIX locks, but it does not work
with Windows locks.  These require to unlock a lock exactly as it has
been created.  This means, after this unlock, the "r 1073741826 510"
lock is still present.  Since a failed unlock practically doesn't exist
on POSIX, Cygwin converts STATUS_RANGE_NOT_LOCKED to STATUS_SUCCESS,
so fcntl returns 0, success.  Same for L and M, with L failing
because the call tries to use a single unlock on a range holding two
locks.  The locks would have to be unlocked each by itself.

Ultimately the write lock request J fails, because there are already
the read locks B and F present.  POSIX and BSD locks can simply
replace another lock in the same spot if the existing lock is hold
by the same process (POSIX) or file object (BSD).  Windows locks
add up, and require to unlock all locks in the same area before
allowing another lock type to be placed.  No atomic replacement of an
existing read lock with a write lock or vice versa.

In theory this scenario could be worked around in Cygwin by bookkeeping
the present locks, plus a piece of code which unlocks all existing locks
in the given range when a lock or unlock request is coming in.  However,
the really dismal fact is, that an unlock before a lock would never be
atomic.  If the F_SETLK request unlocks an existing lock and then
another process gets a lock in the requested range, the first process
ends up with a failed fcntl call and no lock at all.


Corinna

-- 
Corinna Vinschen                  Please, send mails regarding Cygwin to
Cygwin Maintainer                 cygwin AT cygwin DOT com
Red Hat

--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[Achim Gratz]

Corinna Vinschen writes:
> In theory this scenario could be worked around in Cygwin by bookkeeping
> the present locks, plus a piece of code which unlocks all existing locks
> in the given range when a lock or unlock request is coming in.  However,
> the really dismal fact is, that an unlock before a lock would never be
> atomic.  If the F_SETLK request unlocks an existing lock and then
> another process gets a lock in the requested range, the first process
> ends up with a failed fcntl call and no lock at all.

Thanks for the explanation, I'm beginning to see what the backoff / retry
code in SQLite on WIndows is supposed to be doing (hopefully).


Regards,
Achim.
-- 
+<[Q+ Matrix-12 WAVE#46+305 Neuron microQkb Andromeda XTk Blofeld]>+

Factory and User Sound Singles for Waldorf Blofeld:
http://Synth.Stromeko.net/Downloads.html#WaldorfSounds


--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[Warren Young]

On 6/6/2013 11:22, Corinna Vinschen wrote:
>
> The lazy unlock request D tells the system to unlock all locks on the
> entire file.  This works fine with POSIX locks, but it does not work
> with Windows locks.  These require to unlock a lock exactly as it has
> been created.

I wouldn't be upset if you decided that was grounds for removing the 
code that tries to support mandatory locking for POSIX locks.  As far as 
I'm concerned, this is very much an experimental feature, and 
experiments often fail.  The failure already told us what to try next 
(BSD locks) and according to the one report received so far, it looks 
like it might fix it.

--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[David Stacey]

On 05/06/13 23:27, David Rothenberger wrote:
> On 6/5/2013 12:50 PM, Warren Young wrote:
>> The CYGWIN_SQLITE_LOCKING variable now has three possible states:
>>
>> CYGWIN_SQLITE_LOCKING=posix  -- same as before
>>
>> CYGWIN_SQLITE_LOCKING=posixmand -- equivalent to leaving it undefined in
>> the 3.7.17-1 build
>>
>> CYGWIN_SQLITE_LOCKING=AnythingElse -- BSD flock() based mandatory locking.
> I think the "AnythingElse" setting is what you intended for Subversion
> users that want to interoperate with Windows?
>
> The Subversion test suite passes with that setting. I don't know about
> issues with TortoiseSVN since I don't use it personally.

Ah, that'll be me then ;-)

I gave sqlite3-3.7.17-2 a try with Cygwin snapshot 2013-06-04, on a PC 
with TortoiseSVN 1.7.13. With CYGWIN_SQLITE_LOCKING set to AnythingElse, 
I ran a few tests (reverse merges, revert, etc.) that I thought would 
exercise the local database.

Whilst I haven't had time to put it through a very thorough examination, 
it all appeared to work just fine. I'll try to run some more tests tomorrow.

Thanks to Warren and Corinna for their considerable effort in getting 
this working.

Dave.


--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[Corinna Vinschen]

On Jun  6 12:58, Warren Young wrote:
> On 6/6/2013 11:22, Corinna Vinschen wrote:
> >
> >The lazy unlock request D tells the system to unlock all locks on the
> >entire file.  This works fine with POSIX locks, but it does not work
> >with Windows locks.  These require to unlock a lock exactly as it has
> >been created.
> 
> I wouldn't be upset if you decided that was grounds for removing the
> code that tries to support mandatory locking for POSIX locks.  As
> far as I'm concerned, this is very much an experimental feature, and
> experiments often fail.  The failure already told us what to try
> next (BSD locks) and according to the one report received so far, it
> looks like it might fix it.

Well, after all it's still record locking, so it's kind of weird to
support a flock-like file lock but no record locks.  If an application
uses this carefully with Windows semantics in mind, it might even be
useful.

However, what's missing in the long run is documentation.  I'm just
about to add a few words to the docs.


Corinna

-- 
Corinna Vinschen                  Please, send mails regarding Cygwin to
Cygwin Maintainer                 cygwin AT cygwin DOT com
Red Hat

--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple

Re: [TEST] sqlite3-3.7.17-1 (Cygwin 1.7.19 locking feature)

[Warren Young]

On 6/6/2013 16:10, David Stacey wrote:
>
> Thanks to Warren and Corinna for their considerable effort in getting
> this working.

Thank you, Davids, for your testing. :)

I'm now nervously considering RFU'ing a -3 version, differing only in 
removing the "posixmand" stuff.  You'll have two choices only again: BSD 
type mandatory locking, or pure POSIX mode.

I might RFU it as test-only until Achim and Yaakov try it, then promote 
it to curr if they are happy with it.

--
Problem reports:       http://cygwin.com/problems.html
FAQ:                   http://cygwin.com/faq/
Documentation:         http://cygwin.com/docs.html
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple