[sqlite] 2 different SQLite versions inside the same process space

[Hick Gunter]

To get this effect you need to have 2 (shareable) images, each with their own 
and very private copy of sqlite, loaded into the same process.

With the mentioned #defines that make all sqlite3 symbols become static, it is 
quite easy to compile sometool.c and sqlite3.c into a single sometool.so (or 
.dll for windows) file that initializes sqlite's global variables.

If I now compile myapp.c and sqlite3.c (same or different version does not 
matter) into myapp.so (again or .dll for windows), which initializes the global 
variabels visible to myapps' copy of sqlite. These are distinct from sometool's 
sqlite global variables.

I may find sometool quite handy and decide to call it from within myapp code by 
dynamically linking to the shareable object/dll. This makes 2 copies of sqlite 
active in one process. This is probably not a problem, because sometool is 
presumably using sqlite to access ist own db files, of which myapp has no 
knowledge. But if myapp's function is to "explore" sqlite dbs it may stumble 
over sometools' db file and open a second filehande to it. Now and only now is 
there potential for corruption.


-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Steve 
Schow
Gesendet: Mittwoch, 11. Mai 2016 17:04
An: SQLite mailing list 
Betreff: Re: [sqlite] 2 different SQLite versions inside the same process space

Ok?starting to sound safer.  :-)

at a minimum this problem only occurs when multi-threading is being used to 
access a sqlite DB file.  but I think its probably even more specific then that?

When you say ?two copies of sqlite in the same address space?, this is the part 
I am getting confused about I guess.  I guess you mean the single-file sqlite 
dot c file is compiled into a single executable more then once, somehow 
avoiding namespace conflicts.

In such a case, then each of those code-instances would have what it thinks is 
a global to keep track of the db file lock, but in actuality they are each 
using their own private global.

Do I understand it correctly now?



On May 11, 2016, at 8:36 AM, Richard Hipp  wrote:

> On 5/11/16, Steve Schow  wrote:
>>
>> Typically concurrency happens when two different users execute their
>> program that has sqlite compiled into it;?.. concurrently.
>
> The problem only comes up with two different copies of SQLite are
> running within the same process.  The same program being run twice is
> fine.
>
> The problem is a bug in the posix APIs for file locking.  I say "bug".
> It is not an implementation problem.  The implementation is correct on
> all major unix systems.  The problem is in the *design*.
>
> In posix, suppose you open a file "xyzzy" and take a lock on that file
> using the file descriptor returned by open().  That works fine.
>
> But then if another thread in the same process (technically: another
> thread with the same result from getpid()) does this:
>
> close(open("xyzzy"))
>
> The close() operation cancels all locks held on the "xyzzy" file, even
> locks created by different threads using different file descriptors.
>
> This is crazy.  Everybody acknowledges that it is crazy. But it is the
> posix standard.
>
> In order to work around the problem, SQLite has to use global
> variables to track every close() operation and defer those that are
> against files with locks held by other file descriptors.  But if you
> have two copies of SQLite in the same address space, they will use
> different global variables and may not know about each other, and
> hence locks can get cancelled.
> --
> 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-users mailing list
sqlite-users at 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: hick at 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] 2 different SQLite versions inside the same process space

[Richard Hipp]

On 5/11/16, Steve Schow  wrote:
>
> Typically concurrency happens when two different users execute their program
> that has sqlite compiled into it;?.. concurrently.

The problem only comes up with two different copies of SQLite are
running within the same process.  The same program being run twice is
fine.

The problem is a bug in the posix APIs for file locking.  I say "bug".
It is not an implementation problem.  The implementation is correct on
all major unix systems.  The problem is in the *design*.

In posix, suppose you open a file "xyzzy" and take a lock on that file
using the file descriptor returned by open().  That works fine.

But then if another thread in the same process (technically: another
thread with the same result from getpid()) does this:

 close(open("xyzzy"))

The close() operation cancels all locks held on the "xyzzy" file, even
locks created by different threads using different file descriptors.

This is crazy.  Everybody acknowledges that it is crazy. But it is the
posix standard.

In order to work around the problem, SQLite has to use global
variables to track every close() operation and defer those that are
against files with locks held by other file descriptors.  But if you
have two copies of SQLite in the same address space, they will use
different global variables and may not know about each other, and
hence locks can get cancelled.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] 2 different SQLite versions inside the same process space

[Steve Schow]

Ok?starting to sound safer.  :-)

at a minimum this problem only occurs when multi-threading is being used to 
access a sqlite DB file.  but I think its probably even more specific then that?

When you say ?two copies of sqlite in the same address space?, this is the part 
I am getting confused about I guess.  I guess you mean the single-file sqlite 
dot c file is compiled into a single executable more then once, somehow 
avoiding namespace conflicts.

In such a case, then each of those code-instances would have what it thinks is 
a global to keep track of the db file lock, but in actuality they are each 
using their own private global.

Do I understand it correctly now?



On May 11, 2016, at 8:36 AM, Richard Hipp  wrote:

> On 5/11/16, Steve Schow  wrote:
>> 
>> Typically concurrency happens when two different users execute their program
>> that has sqlite compiled into it;?.. concurrently.
> 
> The problem only comes up with two different copies of SQLite are
> running within the same process.  The same program being run twice is
> fine.
> 
> The problem is a bug in the posix APIs for file locking.  I say "bug".
> It is not an implementation problem.  The implementation is correct on
> all major unix systems.  The problem is in the *design*.
> 
> In posix, suppose you open a file "xyzzy" and take a lock on that file
> using the file descriptor returned by open().  That works fine.
> 
> But then if another thread in the same process (technically: another
> thread with the same result from getpid()) does this:
> 
> close(open("xyzzy"))
> 
> The close() operation cancels all locks held on the "xyzzy" file, even
> locks created by different threads using different file descriptors.
> 
> This is crazy.  Everybody acknowledges that it is crazy. But it is the
> posix standard.
> 
> In order to work around the problem, SQLite has to use global
> variables to track every close() operation and defer those that are
> against files with locks held by other file descriptors.  But if you
> have two copies of SQLite in the same address space, they will use
> different global variables and may not know about each other, and
> hence locks can get cancelled.
> -- 
> 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] 2 different SQLite versions inside the same process space

[Roger Binns]

On 10/05/16 23:43, Dominique Devienne wrote:
> That explains how to avoid symbol mixups, to have two or more SQLite 
> "instances"
> in a single process, but doesn't address the concern about POSIX locks
> DRH mentioned.
> if more than one of those same-process instances access the same file. --DD

As in your last line, it is only a concern if the same file is accessed.
 The OP clearly said:

  Both Addins have their own separate databases and do not share
  any database connections or anything like that.

So the approach will work well for them.

Roger


-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 181 bytes
Desc: OpenPGP digital signature
URL: 

[sqlite] 2 different SQLite versions inside the same process space

[Dominique Devienne]

On Wed, May 11, 2016 at 4:16 AM, Roger Binns  wrote:
> On 10/05/16 10:42, Andrey Gorbachev wrote:
>> I am a bit worried that the initialisation of 2 different versions of SQLite 
>> would interfere with one another. Any advice?
>
> There is a way to do it, [...]  create a .c file that near the top has these 
> lines:
>
> #define SQLITE_API static
> #define SQLITE_EXTERN  static
> #include "sqlite3.c"
>
> That causes all the SQLite non-private symbols to only have the scope of
> that .c file, and not leak outside of it.

That explains how to avoid symbol mixups, to have two or more SQLite "instances"
in a single process, but doesn't address the concern about POSIX locks
DRH mentioned.
if more than one of those same-process instances access the same file. --DD

[sqlite] 2 different SQLite versions inside the same process space

[Steve Schow]

On May 11, 2016, at 8:22 AM, Steve Schow  wrote:
> 
> Oh that actually makes more sense?but also even more concerning in a way, 
> unless I?m still misunderstanding the conundrum.   

Just thinking out loud?.is this problem related to specifically when people try 
to compile sqlite into a shared library and then use that shared lib from two 
different running processes at the same time?

[sqlite] 2 different SQLite versions inside the same process space

[Steve Schow]

On May 10, 2016, at 4:15 PM, Richard Hipp  wrote:

> On Tue, 10 May 2016 22:47 +0100, Tim Streater  wrote:
>> 
>> I read it as two different *copies*. It doesn't sound to me as if the
>> versions have anything to do with it.
>> 
> 
> Correct.  Two different *copies*of the library.  They can both have
> the same version number - that doesn't matter.
> ? 


Oh that actually makes more sense?but also even more concerning in a way, 
unless I?m still misunderstanding the conundrum.   

Typically concurrency happens when two different users execute their program 
that has sqlite compiled into it;?.. concurrently.   Is this problem unique to 
some library I am not aware about or would it effect any two people trying to 
use the sqlite3 binary at the same time (each one calling their own instance of 
it)?or say?calling fossil at the same time, for example?

Each user instance starts a  process running, with its own globals?and they 
could each try to hit a certain sqlite DB concurrently?from different 
processes?  But I thought this was supposed to be the whole point of 
sqlite?that it can handle that?it just may force one or the other of them to 
wait until the file is unlocked..  So I was under the impression that sqlite 
lite was safe, but slow, for concurrent attempts to write to a DB file.  But it 
sounds like you?re saying its not safe?though its still not entirely clear to 
me if I am understanding the problem right or how to avoid it.

[sqlite] 2 different SQLite versions inside the same process space

[Simon Slavin]

On 10 May 2016, at 10:29pm, Steve Schow  wrote:

> are you saying that on UNIX, if two different versions of the sqlite3 binary 
> attempt to access a DB file at the same time?then the globals that are used 
> in the sqlite3 binaries related to locking may be different in the two 
> different binaries, and may result in DB corruption?

I'd just like to note a difference between 'two different versions' which you 
wrote, and 'two different instances' as Richard wrote.  The problem is not 
because a protocol has changed between versions of SQLite.

Simon.

[sqlite] 2 different SQLite versions inside the same process space

[Tim Streater]

On 10 May 2016 at 22:29, Steve Schow  wrote: 

> I would like to understand this issue a little bit better?

> On May 10, 2016, at 2:31 PM, Richard Hipp  wrote:
>>
>> In unix, SQLite has to use global variables to work around the
>> well-known design bugs in posix advisory locks.  And so if you have
>> two different instances of SQLite running on unix, they will use
>> different global variables, causing them to break each others locks
>> and you will get database corruption.

> are you saying that on UNIX, if two different versions of the sqlite3 binary
> attempt to access a DB file at the same time?then the globals that are used
> in the sqlite3 binaries related to locking may be different in the two
> different binaries, and may result in DB corruption?

I read it as two different *copies*. It doesn't sound to me as if the versions 
have anything to do with it.


--
Cheers  --  Tim

[sqlite] 2 different SQLite versions inside the same process space

[Roger Binns]

On 10/05/16 10:42, Andrey Gorbachev wrote:
> I am a bit worried that the initialisation of 2 different versions of SQLite 
> would interfere with one another. Any advice?

There is a way to do it, and I use it for my Python APSW extension as
the recommended build instructions.  It is especially helpful on MacOS
as system components like Core Data use SQLite, and the loaders tend to
force the system SQLite library to always be loaded.  As a bonus, the
approach below also results in faster code.

What you need to do is create a .c file that near the top has these lines:

#define SQLITE_API static
#define SQLITE_EXTERN  static
#include "sqlite3.c"

That causes all the SQLite non-private symbols to only have the scope of
that .c file, and not leak outside of it.

In the rest of the .c file put your C code that uses the SQLite API, and
it will use the static included version (only).

You are done.  The reason this also gives faster code is that the
compiler can inline the heck out of SQLite code since it knows it won't
be used outside of the compilation unit.  It does sometimes make
debugging interesting though.

Roger

-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 181 bytes
Desc: OpenPGP digital signature
URL: 

[sqlite] 2 different SQLite versions inside the same process space

[Andrey Gorbachev]

Hi,

Microsoft Excel allows custom "Addins", which are all run in the same process 
space. We use Bloomberg Excel Addin, which uses SQLite 3.7.x and we have no 
control over what version of SQLite they use. We have our own Addin which uses 
SQLite 3.11.x and we do not want to use the same version as Bloomberg. Both 
Addins have their own separate databases and do not share any database 
connections or anything like that.

I am a bit worried that the initialisation of 2 different versions of SQLite 
would interfere with one another. Any advice?

Many thanks,
Gorby

[sqlite] 2 different SQLite versions inside the same process space

[Richard Hipp]

On Tue, 10 May 2016 22:47 +0100, Tim Streater  wrote:
>
> I read it as two different *copies*. It doesn't sound to me as if the
> versions have anything to do with it.
>

Correct.  Two different *copies*of the library.  They can both have
the same version number - that doesn't matter.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] 2 different SQLite versions inside the same process space

[Richard Hipp]

On 5/10/16, Andrey Gorbachev  wrote:
> Hi,
>
> Microsoft Excel allows custom "Addins", which are all run in the same
> process space. We use Bloomberg Excel Addin, which uses SQLite 3.7.x and we
> have no control over what version of SQLite they use. We have our own Addin
> which uses SQLite 3.11.x and we do not want to use the same version as
> Bloomberg. Both Addins have their own separate databases and do not share
> any database connections or anything like that.
>
> I am a bit worried that the initialisation of 2 different versions of SQLite
> would interfere with one another. Any advice?
>

In unix, SQLite has to use global variables to work around the
well-known design bugs in posix advisory locks.  And so if you have
two different instances of SQLite running on unix, they will use
different global variables, causing them to break each others locks
and you will get database corruption.

But as far as I know, that is not a problem on Windows.

On the other hand, this is not something that we have ever tested.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] 2 different SQLite versions inside the same process space

[Steve Schow]

I would like to understand this issue a little bit better?


On May 10, 2016, at 2:31 PM, Richard Hipp  wrote:
> 
> In unix, SQLite has to use global variables to work around the
> well-known design bugs in posix advisory locks.  And so if you have
> two different instances of SQLite running on unix, they will use
> different global variables, causing them to break each others locks
> and you will get database corruption.


are you saying that on UNIX, if two different versions of the sqlite3 binary 
attempt to access a DB file at the same time?then the globals that are used in 
the sqlite3 binaries related to locking may be different in the two different 
binaries, and may result in DB corruption?

If that is the case, then although the internal DB file format may be backwards 
compatible between versions of sqlite3, its very important that I take care not 
to allow two different versions of the SQLITE executable code attempt to access 
the DB file at the same time.  As long as they are totally separate 
non-concurrent accesses, it sounds like it should be fine?but if they attempt 
concurrently, then concurrency locking between them can?t be garaunteed due to 
changes in the way you are handling it with globals as the code has evolved.  
On UNIX anyway.  Do I have that right?

That?s a very important thing to keep in mind with so many different versions 
of sqlite3 executable code floating around..its built into python a lot older 
then the sqlite3 binary I have installed, which might be different from what is 
compiled into fossil, etc..  

[sqlite] 2 different SQLite versions inside the same process space

[Scott Hess]

IF you have two different versions of SQLite linked into the same
executable, both accessing the same database, then the problem that
the globals work around can happen.  It won't happen if different
processes use different versions of SQLite (say two versions of the
sqlite3 binary, or sqlite3 versus Python, etc), nor is there an issue
if a single process with multiple versions linked is used to access
different database files (say if one version is part of nss used to
access the system certificate database and the other version is what
you compile in to access your databases).

The gist of the problem is that if a single process has two file
descriptors open on the same file, and one holds a lock, closing the
other file descriptor clears the lock.  SQLite's Unix VFS puts the
close in a global queue to close later to prevent this.

-scott


On Tue, May 10, 2016 at 2:29 PM, Steve Schow  wrote:
>
> I would like to understand this issue a little bit better?
>
>
> On May 10, 2016, at 2:31 PM, Richard Hipp  wrote:
>>
>> In unix, SQLite has to use global variables to work around the
>> well-known design bugs in posix advisory locks.  And so if you have
>> two different instances of SQLite running on unix, they will use
>> different global variables, causing them to break each others locks
>> and you will get database corruption.
>
>
> are you saying that on UNIX, if two different versions of the sqlite3 binary 
> attempt to access a DB file at the same time?then the globals that are used 
> in the sqlite3 binaries related to locking may be different in the two 
> different binaries, and may result in DB corruption?
>
> If that is the case, then although the internal DB file format may be 
> backwards compatible between versions of sqlite3, its very important that I 
> take care not to allow two different versions of the SQLITE executable code 
> attempt to access the DB file at the same time.  As long as they are totally 
> separate non-concurrent accesses, it sounds like it should be fine?but if 
> they attempt concurrently, then concurrency locking between them can?t be 
> garaunteed due to changes in the way you are handling it with globals as the 
> code has evolved.  On UNIX anyway.  Do I have that right?
>
> That?s a very important thing to keep in mind with so many different versions 
> of sqlite3 executable code floating around..its built into python a lot older 
> then the sqlite3 binary I have installed, which might be different from what 
> is compiled into fossil, etc..
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users