Java自学者论坛

 找回密码
 立即注册

手机号码,快捷登录

恭喜Java自学者论坛(https://www.javazxz.com)已经为数万Java学习者服务超过8年了!积累会员资料超过10000G+
成为本站VIP会员,下载本站10000G+会员资源,会员资料板块,购买链接:点击进入购买VIP会员
查看: 12|回复: 0

多人操作sqlite3数据库冲突问题解决方法

[复制链接]
  • TA的每日心情
    奋斗
    昨天 19:16
  • 签到天数: 66 天

    [LV.6]常住居民II

    1348

    主题

    1348

    帖子

    3万

    积分

    管理员

    Rank: 9Rank: 9Rank: 9

    积分
    32858
    发表于 2021-6-10 21:35:36 | 显示全部楼层 |阅读模式

    问题描述: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 = '\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 = '\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

    哎...今天够累的,签到来了1...
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    QQ|手机版|小黑屋|Java自学者论坛 ( 声明:本站文章及资料整理自互联网,用于Java自学者交流学习使用,对资料版权不负任何法律责任,若有侵权请及时联系客服屏蔽删除 )

    GMT+8, 2021-6-22 21:02 , Processed in 0.055751 second(s), 29 queries .

    Powered by Discuz! X3.4

    Copyright © 2001-2020, Tencent Cloud.

    快速回复 返回顶部 返回列表