多人操作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