RE: [sqlite] Concurrency handling question

[Ned Batchelder]

You can't simply loop on retrying the last sqlite3_* call.  You need to
rollback one of the transactions and retry the entire transaction.  Your two
threads are deadlocked because (roughly) each has acquired a lock that the
other needs.  One needs to release the locks it holds.  Rolling back is the
way to do that.

--Ned.
http://nedbatchelder.com
 

-----Original Message-----
From: Doug Nebeker [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, 30 November, 2005 11:04 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Concurrency handling question

I've written a C++ wrapper for SQLite which has been working great.
Lately though I've started getting what might be deadlocks when running
two threads against the same database.  One thread is a reader and the
other is a writer (at the moment, I'm getting 'database locked' errors,
but that's after many hours of failures so not sure where it started).
It's possible to have two or three readers and/or writers, but not more
than that.

Each thread has it's own database handle, however, I keep the database
handle open for many calls (where each 'call' opens a transaction,
executes SQL statements, then closes the transaction).

I've read about concurrency, and it sounds like the best way to work is
just to retry the last call if SQLITE_BUSY is ever returned, but that
doesn't seem to be working for me in this case.  I've stripped my main
processing loop down to make it concise (it is not completely correct
nor will it compile as shown, but it shows the flow).

Can anyone see what I'm doing wrong?  Is the fact that I keep the
database handle open between calls and between retries the problem?  If
the retry loops finally fails, sqlite3_finalize gets called,
sqlite3_close and then sqlite3_open16 to re-initialize the state.

Thanks in advance for any insight.

        do
        {
                long prepareLoopCount = 0;

PrepareLoop:
                if(SQLITE_BUSY == (res = sqlite3_prepare16(m_hDB,
nextStatement, -1, &pStmt, &tail)))
                {
                        if(prepareLoopCount++ < 200)
                        {
                                Sleep(300 ms);
                                goto PrepareLoop;
                        }
                }

                if((SQLITE_OK == res) && (NULL != pStmt)) 
                {
                        long stepLoopCount = 0;
StepLoop:
                        res = sqlite3_step(pStmt);
                        if(SQLITE_BUSY == res)
                        {
                                if(stepLoopCount++ < 200)
                                {
                                        Sleep(300 ms);
                                        goto StepLoop;
                                }
                        }
                        else do other processing like fetching the
rows....

                }

                if(NULL != pStmt)
                {
                        sqlite3_finalize(pStmt);
                        pStmt = NULL;
                }
        }
        while(NULL != nextStatement)


To find out more about Reuters visit www.about.reuters.com

Any views expressed in this message are those of the individual sender,
except where the sender specifically states them to be the views of Reuters
Ltd.