多人操作sqlite3數據庫沖突問題解決方法，sqlite3數據庫

多人操作sqlite3數據庫沖突問題解決方法，sqlite3數據庫

問題描述：sqlite3數據放置在某一台電腦的某個共享文件夾下，操作數據庫的應用程序安裝在同一局域網下的很多台電腦上，由於存在多人同時使用該應用程序，所以存在多人同時操作數據庫的情況。經過測試發現，最常見的情況是當兩人或者多人往數據庫中寫入數據時，只有其中一個寫入成功，其他數據都寫入失敗。

解決方案分析：

由於本人編寫程序未MFC應用程序，所以嘗試使用windows互斥量mutex，具體的使用方法如下：

bool CMFCApplication2Dlg::Lock()

{

　　m_pMutex = CreateMutex(NULL, false, L"txt_mutex");

　　if (NULL == m_pMutex)

　　{

　　　　return false;

　　}

　　DWORD nRet = WaitForSingleObject(m_pMutex, INFINITE);

　　if (nRet != WAIT_OBJECT_0)

　　{

　　　　return false;

　　}

　　return true;

}

bool CMFCApplication2Dlg::UnLock()

{

　　return ReleaseMutex(&m_pMutex);

}

在某用戶開始進行寫入操作時，先調用Lock()獲取mutxt，寫入完成之後調用UnLock()釋放mutxt。然並卵，該方法並不奏效。（可能由於本人對windows多線程/多進程編程這一塊太過生疏，所以無法利用這方面的知識來解決這個問題，如果有大神知道解決方法，求不吝賜教）

所以經過一番思考之後，決定使用在共享盤的那台電腦上跑一個服務端小程序來防止數據庫的操作沖突。

具體實現方法如下：

思路分析：在服務端每收到一個客戶端的連接請求之後，都創建一個新的線程來處理相應的操作，新的線程不斷的去獲取客戶端發來的消息，當客戶端發來的消息是”開始操作”時，線程將嘗試獲取互斥量mutxt，獲取成功之後將給客戶端發送回復消息，當該線程接收到”操作結束”的消息時，線程將釋放互斥量mutxt,並且會斷開該客戶端與服務端的socket連接。

服務端代碼：

#include <WinSock2.h>
#include <Windows.h>
#include <iostream>
#include <string>
#pragma comment(lib, "ws2_32.lib")
using namespace std;

static HANDLE m_mutex = INVALID_HANDLE_VALUE;

DWORD WINAPI AnswerThread(LPVOID  lparam)
{
    SOCKET ClientSocket = (SOCKET)(LPVOID)lparam;
    int bytesRecv;
    while (1)
    {
        bytesRecv = SOCKET_ERROR;
        char sendbuff[3] = "ok";
        char recvbuf[20] = "";
        for (int i = 0; i<(int)strlen(recvbuf); i++)
        {
            recvbuf[i] = '\0';
        }
        while (bytesRecv == SOCKET_ERROR)
        {
            bytesRecv = recv(ClientSocket, recvbuf, sizeof(recvbuf), 0);
        }
        string recved = recvbuf;
        if (recved == "op_begin")
        {
            WaitForSingleObject(m_mutex, INFINITE);
            cout << "op_begin" << endl;
            send(ClientSocket, sendbuff, sizeof(sendbuff), 0);
        }
        if (recved == "op_end")
        {
            cout << "op_end" << endl;
            ReleaseMutex(&m_mutex);
            closesocket(ClientSocket);    
            return 0;
        }
    }
    return  0;
}
int main()
{
    WSADATA  wsaData;
    int  iRet = WSAStartup(MAKEWORD(2, 2), &wsaData);
    if (iRet != NO_ERROR)
        printf("Error at WSAStartup()\n");

    SOCKET  m_socket;
    m_socket = socket(AF_INET, SOCK_STREAM, 0);
    if (m_socket == INVALID_SOCKET)
    {
        printf("Error at socket():%ld\n", WSAGetLastError());
        WSACleanup();
        return  0;
    }
    SOCKADDR_IN  service;
    service.sin_family = AF_INET;
    service.sin_addr.S_un.S_addr = htonl(INADDR_ANY);
    service.sin_port = htons(2501);

    if (bind(m_socket, (SOCKADDR*)&service, sizeof(service)) == SOCKET_ERROR)
    {
        printf("bind() failed.\n");
        closesocket(m_socket);
        return  0;
    }
    else
        printf("bind ok.\n");

    if (listen(m_socket, 20) == SOCKET_ERROR)
        printf("Error listening on socket.\n");
    else
        printf("listening ok.\n");

    SOCKET  AcceptSocket;
    printf("waiting for a client to connect...\n");
    m_mutex = CreateMutex(NULL, FALSE, L"Mutex");
    if (!m_mutex)
    {
        cout << "Failed to CreateMutex !" << endl;
        return 0;
    }
    int count = 0;
    while (1)
    {
        AcceptSocket = SOCKET_ERROR;
        while (AcceptSocket == SOCKET_ERROR)
        {
            AcceptSocket = accept(m_socket, NULL, NULL);
        }
        count++;
        printf("client num %d connected.\n", count);


        DWORD  dwThreadId;
        HANDLE  hThread;

        hThread = CreateThread(NULL, NULL, AnswerThread, (LPVOID)AcceptSocket, 0, &dwThreadId);
        if (hThread == NULL)
        {
            printf("CreatThread AnswerThread() failed.\n");
        }
        else
        {
            printf("create thread %d ok.\n", count);
        }
        CloseHandle(hThread);
    }
    closesocket(m_socket);
    WSACleanup();
}

客戶端代碼：(進入數據庫操作前)

WSADATA wsaData;

if (WSAStartup(MAKEWORD(2, 2), &wsaData) != 0)

{

　　TRACE("Failed to load Winsock");

　　return;

}

string txtPath = save_path + "\\ip.txt";

ifstream infile(txtPath);

string ip;

getline(infile, ip);

infile.close();

 

SOCKADDR_IN addrSrv;

addrSrv.sin_family = AF_INET;

addrSrv.sin_port = htons(2501);

addrSrv.sin_addr.S_un.S_addr = inet_addr(ip.c_str());

 

sockClient = socket(AF_INET, SOCK_STREAM, 0);

if (SOCKET_ERROR == sockClient){

　　TRACE("Socket() error:%d", WSAGetLastError());

　　return;

}

if (connect(sockClient, (SOCKADDR*)&addrSrv, sizeof(addrSrv)) == INVALID_SOCKET){

　　TRACE("Connect failed:%d", WSAGetLastError());

　　return;

}

char buff[9] = "op_begin";

send(sockClient, buff, sizeof(buff), 0);

int bytesRecv = SOCKET_ERROR;

char recvbuf[3] = "";

for (int i = 0; i<(int)strlen(recvbuf); i++)

{

　　recvbuf[i] = '\0';

}

while (bytesRecv == SOCKET_ERROR)

{

　　CMessageDlg message;

　　message.DoModal();

　　bytesRecv = recv(sockClient, recvbuf, sizeof(recvbuf), 0);

}

客戶端代碼：(數據庫操作完成之後)

char buff[7] = "op_end";

send(sockClient, buff, sizeof(buff), 0);

closesocket(sockClient);

注：由於經驗不足，本方法可能有很多內存釋放，資源利用等細節沒有考慮到，所以本方法僅供參考。另外有關socket編程部分的代碼參考：

http://blog.csdn.net/chence19871/article/details/44019633