Re: [sqlite] multiple thread concurrency problem with exclusive transaction locks

[Eli Burke]

2. disable asserts. I don't know how this is done in C, but I assume 
that in
a release build, asserts are off.
Disable asserts() using -DNDEBUG=1.  This more than doubles the speed
of SQLite.  Asserts() are turned off in release builds.
We build sqlite3.08 as follows: ./configure --disable-shared 
--enable-threadsafe

I removed and rebuilt sqlite3, and rebuilt the test application just to 
be sure. With a cpu-bound process maxing out each virtual CPU on a 
hyperthreaded system, the test app ran 10 times in succession without 
failing. As soon as I killed off 1 or both of the processes, the test 
runs started to fail in the manner described in my original post. 
(hardly scientific, but at least highly suspicious)

I also recompiled to turn off NDEBUG and enable assertions (This 
required editing Makefile.in; using the configure setting 
--disable-releasemode didn't seem to do anything) and achieved the exact 
same results.

Mike C asked whether we were using multi-cpu kernels. The answer is, I 
don't know. All of our kernels are stock, but I was under the impression 
that at least as far as hyperthreading is concerned, it is the kernel 
that causes it to appear as though there are two CPUs, so by virtue of 
the fact that two are visible, multi-cpu support must be present. 
Nonetheless, I will check into possibly recompiling the kernel on one or 
more of our test machines.

-Eli

Re: [sqlite] multiple thread concurrency problem with exclusive transaction locks

[D. Richard Hipp]

mike cariotoglou wrote:
2. disable asserts. I don't know how this is done in C, but I assume that in
a release build, asserts are off.
Disable asserts() using -DNDEBUG=1.  This more than doubles the speed
of SQLite.  Asserts() are turned off in release builds.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- http://www.hwaci.com/drh/

RE: [sqlite] multiple thread concurrency problem with exclusive transaction locks

[mike cariotoglou]

Correction: I misread the code in os_unix.c. There is NO simplistic mutex
mechanism, just the pthread_mutex_lock call.
So, the only way for your code to fail because of this would be to 

1. compile without THREADSAFE
And
2. disable asserts. I don't know how this is done in C, but I assume that in
a release build, asserts are off.

Also, it would, of course, fail, if the pthread_mutex_lock call does not
work properly..

RE: [sqlite] multiple thread concurrency problem with exclusive transaction locks

[mike cariotoglou]

I am not running on *ix, so I cannot test.however, your mentioning multi-cpu
machines, brings something to mind.
In windows, most synchronization functions rely on some form of INTERLOCKED
operation. Now, these operations are implemented differently on multi-cpu
(and hyperthreading counts as multi-cpu in this context), than on single-cpu
machines.
Namely, in the multi-cpu kernel, each interlocked operation is prefixed by a
bus LOCK instruction, whereas in the single-cpu kernel, it is prefixed by a
NOP. As a result, if multi-threaded code runs on a machine that has a
single-cpu kernel, but multiple-cpus, all hell breaks loose (it can happen,
due to a bad installation). Your problem is suspiciously similar.

here are some things to check for:

1. is your *ix kernel compiled for multi-cpu ? I think that linux requires a
special build of the kernel for multi-cpu machines.
2. is your sqlite code compiled with SQLITE_UNIX_THREADS enabled (and
THREADSAFE enabled) ? If not, the default
mechanism used for mutexes in os_unix.c will certainly FAIL on multi-cpu
machines, as it does not have the atomic LOCK prefixes.
If yes, then the functionality of the mutexes (sqlite3OsEnterMutex) depends
on how well the POSIX (or whatever) lock mechanism works. From the comments
on the beginning of the file, I suspect that locking in Linux is horribly
unstable...

So, I would first check that the mutex mechanism works as expected in your
environment before looking for a bug in the sqlite code (unless the default,
simplistic mutex mechanism in sqlite3OsEnterMutex counts as a bug)

-Original Message-
From: Eli Burke [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 12, 2005 6:34 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] multiple thread concurrency problem with exclusive
transaction locks

I hate to beat on a tired horse (threads and db locking issues), but I am
running into what I believe is a bug as we scale up the number of threads in
our application. A little background: there is a main scheduler thread that
does most of the processing, and client threads to handle remote
connections. These clients don't touch the database very often, but on
occasion (in particular when they exit) they request an exclusive lock to
remove themselves from the database.

The problem that I see is that with multiple threads all attempting to
"BEGIN EXCLUSIVE", they will occasionally *all* fail, calling the busy
handler repeatedly until it finally returns SQL_BUSY. Let me re-state for
clarity's sake: 10 threads all try "BEGIN EXCLUSIVE" at the same time. One
succeeds, processes, and COMMITs. The other 9 will sometimes repeatedly call
the busy handler over and over until they fail with SQLITE_BUSY, even though
the database *should be* available to start a new exclusive transaction.