[sqlite] Multiple Threads with a DROP command

[James Butts]

I have a problem involving multiple threads and trying to create a table after 
its been dropped.  Perhaps I'm doing something wrong.  Here is the jist.


In the main thread, I open a connection to database and create a timer function 
and start a worker thread.  The worker thread establishes its own separate 
connection to database.  The worker thread creates a table and then signals the 
main thread.  The timer function in the main thread drops the table and signals 
back to the worker thread.  The worker thread creates the table again and 
receives no error.  However, if the worker thread tries to insert a record into 
that table, I get back a SQLITE_ERROR error.



I have recreated this under Windows in a minimal test app.  If it fails, it 
will display a message box saying "Why doesn't this work?"



Create a dialog based MFC app using the VC6.0 wizard and add the following 
(assumes the class is called CLITEERRDlg).

In the dialog include file add the following public member functions:

    ~CLITEERRDlg();

    static UINT WorkerFcn(LPVOID pParam);

    static void CALLBACK OnTimerMain(HWND hwnd, UINT uMsg, UINT idEvent, DWORD 
dwTime);

    bool IsDead() const { return m_bWorkerDie; }



And after the declaration of m_hIcon, add the following declarations:

    CWinThread*  m_pWorkerThread;

    HANDLE       m_hWorkerEvent;

    bool         m_bWorkerDie;

    sqlite3*     pMainDB;

    sqlite3*     pWorkerDB;

    bool         m_bDropTable;

    UINT_PTR     m_uDBTimer;

    bool         m_bAddTable;



In the dialog cpp file, add the following to the constructor:

    m_pWorkerThread = NULL;

    m_hWorkerEvent = NULL;

    m_bWorkerDie = false;

    pWorkerDB = NULL;

    m_bDropTable = false;

    m_uDBTimer = 0;

    m_bAddTable = false;



And add the following in the TODO section of the OnInitDialog function:

    int errCode = sqlite3_open("TEST.DB", &pMainDB);

    ASSERT(errCode == SQLITE_OK);



    m_hWorkerEvent = CreateEvent(NULL, FALSE, FALSE, NULL);

    ASSERT(m_hWorkerEvent != NULL);

    m_pWorkerThread = AfxBeginThread(CLITEERRDlg::WorkerFcn, this, 
THREAD_PRIORITY_NORMAL, 0, CREATE_SUSPENDED);

    ASSERT(m_pWorkerThread != NULL);

    m_pWorkerThread->m_bAutoDelete = FALSE; // prevents destroying CWinthread 
object upon thread termination!

    m_pWorkerThread->ResumeThread();



    m_uDBTimer = ::SetTimer(NULL, 0, 500, CLITEERRDlg::OnTimerMain);



And add these three functions:

CLITEERRDlg::~CLITEERRDlg()

{

    ::KillTimer(NULL, m_uDBTimer);

    m_uDBTimer = 0;



    m_bWorkerDie = true;



    if (m_hWorkerEvent != NULL)

        SetEvent(m_hWorkerEvent);



    if (m_pWorkerThread != NULL)

    {

        m_pWorkerThread->ResumeThread();

        // Raise the thread's priority

        m_pWorkerThread->SetThreadPriority(THREAD_PRIORITY_ABOVE_NORMAL);



        // Now wait for the thread to kill itself

        WaitForSingleObject(m_pWorkerThread->m_hThread, INFINITE);



        delete m_pWorkerThread;

    }



    if (m_hWorkerEvent != NULL)

        CloseHandle(m_hWorkerEvent);



    if (pMainDB != NULL)

        sqlite3_close(pMainDB);

}



#define THREAD_SANITY 5000

UINT CLITEERRDlg::WorkerFcn(LPVOID pParam)

{

    CLITEERRDlg* pDB = (CLITEERRDlg*)pParam;



    if (pDB == NULL || pDB->IsDead())

        return 0;



    int errCode = sqlite3_open("TEST.DB", &pDB->pWorkerDB);

    ASSERT (errCode == SQLITE_OK);



    errCode = sqlite3_exec(pDB->pWorkerDB, "CREATE TABLE IF NOT EXISTS 
TESTTABLE (KEY VARCHAR(32))", NULL, NULL, NULL);

    ASSERT(errCode == SQLITE_OK);



    pDB->m_bDropTable = true; // change this to m_bAddTable and there is no 
failure below



    DWORD dwStatus = ::WaitForSingleObject(pDB->m_hWorkerEvent, THREAD_SANITY);

    while (WAIT_OBJECT_0 == dwStatus || WAIT_TIMEOUT == dwStatus)

    {

        if (pDB->IsDead()) break;



        if (pDB->m_bAddTable)

        {

            pDB->m_bAddTable = false;

            errCode = sqlite3_exec(pDB->pWorkerDB, "CREATE TABLE IF NOT EXISTS 
TESTTABLE (KEY VARCHAR(32))",

                                   NULL, NULL, NULL);

            ASSERT(errCode == SQLITE_OK);



            errCode = sqlite3_exec(pDB->pWorkerDB, "INSERT INTO TESTTABLE (KEY) 
VALUES ('123456')",

                                   NULL, NULL, NULL);

            if (errCode != SQLITE_OK) // returned SQLITE_ERROR

            {

                AfxMessageBox("Why doesn't this work?");

                return 0;

            }

        }



        dwStatus = ::WaitForSingleObject(pDB->m_hWorkerEvent, THREAD_SANITY);

    }



    sqlite3_close(pDB->pWorkerDB);



    return 0;

}



void CALLBACK CLITEERRDlg::OnTimerMain(HWND, UINT, UINT, DWORD)

{

    extern CLITEERRApp theApp;

    CLITEERRDlg* pDlg = (CLITEERRDlg*)theApp.m_pMainWnd;

    if (pDlg != NULL)

    {

        if (pDlg->m_bDropTable)

        {

            pDlg->m_bDropTable = false;

            int errCode = sqlite3_exec(pDlg->pMainDB, "DROP TABLE IF EXISTS 
TESTTABLE", NULL, NULL, NULL);

            ASSERT(errCode == SQLITE_OK);

            pDlg->m_bAddTable = true;

            SetEvent(pDlg->m_hWorkerEvent);

        }

    }

}