发帖 回复
返回列表
  • 7905阅读
  • 4回复

数据库 多线程并发访问数据库，容易出错？ [复制链接]

 上一主题 下一主题
离线foxgod
骑士
 
只看楼主 倒序阅读 楼主  发表于: 2014-04-08
来一个socket，建立一个thread，在thread的构造函数里面打开数据库，在RUN函数里面执行数据selected 和update，但是有时候总是错误。
复制代码
  1. int main(int argv,char **argc)
  2. {
  3.     QApplication app(argv,argc);
  4.     tcpServer listenSocket;
  5.     if(!listenSocket.listen(QHostAddress::Any,6000))                  //监听本机6000端口，SD专属
  6.         return 0;
  7.     //if(!listenSocket.listen((QHostAddress::Any,8888)))
  8.     return app.exec();
  9. }
  11. //tcpServer.h
  12. #ifndef TCPSERVER_H
  13. #define TCPSERVER_H
  14. #include <QTcpServer>
  15. class tcpServer : public QTcpServer
  16. {
  17.     Q_OBJECT
  18. public:
  19.     tcpServer(QObject *parent = 0);
  20. protected:
  21.     void incomingConnection(int socketDescriptor);
  22. };
  23. #endif // TCPSERVER_H
  25. tcpServer.cpp
  26. #ifndef TCPSERVER_CPP
  27. #define TCPSERVER_CPP
  28. #include <tcpServer.h>
  29. #include <tcpThread.h>
  30. tcpServer::tcpServer(QObject *parent)
  31.     :QTcpServer(parent){
  32. }
  33. void tcpServer::incomingConnection(int socketDescriptor){
  34.     tcpThread *Mythread = new tcpThread(socketDescriptor,this);
  35.     connect(Mythread,SIGNAL(finished()),Mythread,SLOT(deleteLater()));
  36.     Mythread->start();
  37. }
  38. #endif // TCPSERVER_CPP
  40. #ifndef TCPTHREAD_CPP
  41. #define TCPTHREAD_CPP
  42. #include <tcpThread.h>
  43. #include <tcpServer.h>
  44. #include <QDataStream>
  45. #include <QTcpSocket>
  46. #include <QDateTime>
  47. #include <QSqlQuery>
  48. #include <Qtsql>
  49. /**************************构造函数_连接数据库**********************************/
  50. tcpThread::tcpThread(int socketDescriptor, QObject *parent)
  51.     : QThread(parent), socketDescriptor(socketDescriptor){
  52.     if(createConnection())
  53.     {
  54.         qDebug()<<"Mysql Open!";
  55.     }
  56. }
  57. /**************************析构函数_提示线程退出**********************************/
  58. tcpThread::~tcpThread()
  59. {
  60.     emit finished();
  61.     qDebug()<<"A Thread Quit!";
  62. }
  63. /**************************run函数_进行通信及数据库存储**********************************/
  64. void tcpThread ::run(){
  65.     tcpSocket = new QTcpSocket;
  66.     dataTimer = new QTimer;
  67.     cmdTimer = new QTimer;
  68.     tcpSocket->setSocketDescriptor(socketDescriptor);
  69.     dataTimer->start(3500);
  70.     cmdTimer->start(7300);
  71.     sendNum = 1;
  72.     connect(tcpSocket,SIGNAL(readyRead()),this,SLOT(readMessage()));
  73.     connect(dataTimer,SIGNAL(timeout()),this,SLOT(sendSearch()),Qt::DirectConnection);//定时器时间到，发送查询命令。Qt::DirectConnection线程内传递消息
  74.     connect(cmdTimer,SIGNAL(timeout()),this,SLOT(sendCommand()),Qt::DirectConnection);//定时器时间到，搜索数据库中是否有需要发出的命令
  75.     connect(tcpSocket,SIGNAL(disconnected()),this,SLOT(quit()));//socket断开连接，线程退出
  76.     qDebug()<<tcpSocket->peerAddress().toString();
  77.     qDebug()<<tcpSocket->peerPort();
  78.     exec();
  79. }
  80. /**************************数据库连接函数**********************************/
  81. bool tcpThread::createConnection(){
  82.     db =new QSqlDatabase;
  83.     if(QSqlDatabase::contains("db_test"))                       //若db_test已经打开，则使用
  84.         *db=QSqlDatabase::database("db_test");
  85.     else
  86.     {
  87.         *db = QSqlDatabase::addDatabase("QMYSQL","db_test");    //若没打开则添加
  88.         db->setDatabaseName("db_test");
  89.     }
  90.     /*
  91.         db->setUserName("xdt");
  92.         db->setPassword("xdt");
  93.         db->setHostName("219.231.143.195");
  94.     */
  95.     db->setUserName("root");
  96.     db->setPassword("shmily504");
  97.     db->setHostName("localhost");
  98.     if (!db->open())
  99.         return false;
  100.     return true;
  101. }
  102. /**************************读取socket传来数据并写入数据库**********************************/
  103. void tcpThread ::readMessage(){
  104.     QByteArray bytesArrived;
  105.     qDebug()<<tcpSocket->bytesAvailable();
  106.     bytesArrived=tcpSocket->readAll();
  107.     QDateTime dateTime;
  108.     QString currentTime=dateTime.currentDateTime().toString("yyyy-MM-dd hh:mm:ss");
  109.     qDebug()<<currentTime;
  110.     QSqlQuery mysql(*db);
  111.     if(bytesArrived[0] == 0x02 && bytesArrived[1] == 0xFE && bytesArrived[2] == 0xFE)//26个字节数据
  112.     {
  113.         char deviceType = bytesArrived[3];
  114.         int cdNum = bytesArrived[5];
  115.         qint32 temPerature =bytesArrived[7]*256 + bytesArrived[8];
  116.         char count_H = bytesArrived[11];
  117.         char count_L = bytesArrived[12];
  118.         int deviceState = bytesArrived[13];
  119.         char energyEfficiency =bytesArrived[14];
  120.         qint32 count= count_H*256 + count_L;
  121.         /****************************写原始数据表tb_date_r&&tb_sense*****************/
  122.         QString insertInto_tbSense ="insert into tb_sense(irlast,temp,time) values(?,?,?);";
  123.         mysql.prepare(insertInto_tbSense);
  124.         mysql.addBindValue(count);
  125.         mysql.addBindValue(temPerature);
  126.         mysql.addBindValue(currentTime);
  127.         mysql.exec();
  128.         QString insertInto_tbDateR ="insert into tb_date_r(device_num,cd_num,temp,irlast1,irlast2,device_state,efficiency,time) values(?,?,?,?,?,?,?,?);";
  129.         mysql.prepare(insertInto_tbDateR);
  130.         mysql.addBindValue(deviceType);
  131.         mysql.addBindValue(cdNum);
  132.         mysql.addBindValue(temPerature);
  133.         mysql.addBindValue(count_H);
  134.         mysql.addBindValue(count_L);
  135.         mysql.addBindValue(deviceState);
  136.         mysql.addBindValue(energyEfficiency);
  137.         mysql.addBindValue(currentTime);
  138.         mysql.exec();
  139.         /*******************************更新tb_cddate*****************************/
  140.         if(4 == cdNum)
  141.         {
  142.             QString update_tbCddate;
  143.             update_tbCddate="update tb_cddate set temp=?,device_state=?,time=? where cd_num=4;";
  144.             mysql.prepare(update_tbCddate);
  145.             mysql.addBindValue(temPerature);
  146.             mysql.addBindValue(deviceState);
  147.             mysql.addBindValue(currentTime);
  148.             mysql.exec();
  149.         }
  150.         if(3 == cdNum)
  151.         {
  152.             QString update_tbCddate;
  153.             update_tbCddate="update tb_cddate set temp=?,device_state=?,time=? where cd_num=3;";
  154.             mysql.prepare(update_tbCddate);
  155.             mysql.addBindValue(temPerature);
  156.             mysql.addBindValue(deviceState);
  157.             mysql.addBindValue(currentTime);
  158.             mysql.exec();
  159.         }
  160.         if(2 == cdNum)
  161.         {
  162.             QString update_tbCddate;
  163.             update_tbCddate="update tb_cddate set temp=?,device_state=?,time=? where cd_num=2;";
  164.             mysql.prepare(update_tbCddate);
  165.             mysql.addBindValue(temPerature);
  166.             mysql.addBindValue(deviceState);
  167.             mysql.addBindValue(currentTime);
  168.             mysql.exec();
  169.             if(0x00 == deviceState || 0x04 == deviceState || 0x08 == deviceState || 0x0C == deviceState)
  170.             {
  171.                 QString update_tbLight ="update tb_light set state=2,time=? where id=1";
  172.                 mysql.addBindValue(currentTime);
  173.                 mysql.exec();
  174.             }
  175.             if(0x01 == deviceState || 0x05 == deviceState || 0x09 == deviceState || 0x0D == deviceState)
  176.             {
  177.                 QString update_tbLight ="update tb_light set state=1,time=? where id=1";
  178.                 mysql.addBindValue(currentTime);
  179.                 mysql.exec();
  180.             }
  181.         }
  182.         if(1 == cdNum)
  183.         {
  184.             QString update_tbCddate;
  185.             update_tbCddate="update tb_cddate set temp=?,device_state=?,time=? where cd_num=1;";
  186.             mysql.prepare(update_tbCddate);
  187.             mysql.addBindValue(temPerature);
  188.             mysql.addBindValue(deviceState);
  189.             mysql.addBindValue(currentTime);
  190.             mysql.exec();
  191.             if(0x00 == deviceState || 0x04 == deviceState || 0x08 == deviceState || 0x0C == deviceState)
  192.             {
  193.                 QString update_tbLight ="update tb_aircondition set state=2,efficiency=?,time=? where id=1";
  194.                 mysql.addBindValue(energyEfficiency);
  195.                 mysql.addBindValue(currentTime);
  196.                 mysql.exec();
  197.             }
  198.             if(0x01 == deviceState || 0x05 == deviceState || 0x09 == deviceState || 0x0D == deviceState)
  199.             {
  200.                 QString update_tbLight ="update tb_aircondition set state=1,efficiency=?,time=? where id=1";
  201.                 mysql.addBindValue(energyEfficiency);
  202.                 mysql.addBindValue(currentTime);
  203.                 mysql.exec();
  204.             }
  205.         }
  206.     }
  207.     /**************************若返回的是ACK则更新数据库tb_date_t**********************************/
  208.     if(bytesArrived[0] == 0x04 && bytesArrived[1] == 0x55 && bytesArrived[2] == 0x11)
  209.     {
  210.         uint sum = 0;
  211.         QByteArray temp = bytesArrived.toHex().right(2);
  212.         qDebug()<<temp;
  213.         if(temp[0]<='9')
  214.             sum +=(temp[0]-'0')*16;
  215.         else
  216.             sum +=(temp[0]-'a'+10)*16;
  217.         if(temp[1]<='9')
  218.             sum +=temp[1]-'0';
  219.         else
  220.             sum +=temp[1]-'a'+10;
  221.         uint taskId = sum%256;
  222.         qDebug()<<taskId;
  223.         QString update_tbDateT = "update tb_date_t set state=2,time=? where id=? and state=1";
  224.         qDebug()<<update_tbDateT;
  225.         mysql.prepare(update_tbDateT);
  226.         mysql.addBindValue(currentTime);
  227.         mysql.addBindValue(taskId);
  228.         mysql.exec();
  229.     }
  230. }
  231. /**************************发送查询命令**********************************/
  232. void tcpThread::sendSearch(){
  233.     char block[5];
  234.     char cdNum;
  235.     if(1 == sendNum)
  236.     {
  237.         cdNum =0x01;
  238.         sendNum = 2;
  239.     }
  240.     else if(2 == sendNum)
  241.     {
  242.         cdNum =0x02;
  243.         sendNum = 3;
  244.     }
  245.     else if(3 == sendNum)
  246.     {
  247.         cdNum = 0x03;
  248.         sendNum = 4;
  249.     }
  250.     else if(4 == sendNum)
  251.     {
  252.         cdNum = 0x04;
  253.         sendNum =1;
  254.     }
  255.     block[0]=0x44;
  256.     block[1]=0x11;
  257.     block[2]=0x01;
  258.     block[3]=0x07;
  259.     block[4]=cdNum;
  260.     tcpSocket->write(block,5);
  261. }
  262. /**************************发送控制命令**********************************/
  263. void tcpThread::sendCommand(){
  264.     QSqlQuery send(*db);
  265.     QString haveSendFalse="update tb_date_t set state=3,time= where state=1" ;
  266.     QString currentTime = QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");
  267.     send.prepare(haveSendFalse);
  268.     send.addBindValue(currentTime);
  269.     send.exec();
  270.     QString waitSend="select *from tb_date_t where state =0" ;
  271.     send.exec(waitSend);
  272.     while(send.next())
  273.     {
  274.         int i,j=0,t=0;
  275.         QString taskCmd=send.value(2).toString();
  276.         QByteArray sendTask = taskCmd.toLatin1();
  277.         QByteArray sendTaskcmd;
  278.         uchar sendTemp1[10];
  279.         uchar sendTemp2[10];
  280.         qDebug()<<sendTask;
  281.         /**************************STRING_TO_HEX**********************************/
  282.         for(i=0;i<20;i++)
  283.         {
  284.             if(i%2 == 0)
  285.             {
  286.                 switch(sendTask[i])
  287.                 {
  288.                     case 0x30:sendTemp1[j]=0x00;break;
  289.                     case 0x31:sendTemp1[j]=0x10;break;
  290.                     case 0x32:sendTemp1[j]=0x20;break;
  291.                     case 0x33:sendTemp1[j]=0x30;break;
  292.                     case 0x34:sendTemp1[j]=0x40;break;
  293.                     case 0x35:sendTemp1[j]=0x50;break;
  294.                     case 0x36:sendTemp1[j]=0x60;break;
  295.                     case 0x37:sendTemp1[j]=0x70;break;
  296.                     case 0x38:sendTemp1[j]=0x80;break;
  297.                     case 0x39:sendTemp1[j]=0x90;break;
  298.                     case 0x61:sendTemp1[j]=0xa0;break;
  299.                     case 0x62:sendTemp1[j]=0xb0;break;
  300.                     case 0x63:sendTemp1[j]=0xc0;break;
  301.                     case 0x64:sendTemp1[j]=0xd0;break;
  302.                     case 0x65:sendTemp1[j]=0xe0;break;
  303.                     case 0x66:sendTemp1[j]=0xf0;break;
  304.                     default:sendTemp1[j]=0x22;break;
  305.                 }
  306.                 j++;
  307.             }
  308.             else
  309.             {
  310.                 if(sendTask[i] <= 0x39)
  311.                 {
  312.                     sendTemp2[t] = sendTask[i]-0x30;
  313.                 }
  314.                 else
  315.                     sendTemp2[t] = sendTask[i]-0x57;
  316.                 t++;
  317.             }
  318.         }
  319.         for(i=0;i<10;i++)
  320.         {
  321.             sendTaskcmd[i]=sendTemp1[i]+sendTemp2[i];
  322.         }
  323.         tcpSocket->write(sendTaskcmd);
  324.     }
  325. }
  326. #endif // TCPTHREAD_CPP
  328. #ifndef TCPTHREAD_H
  329. #define TCPTHREAD_H
  330. #include <QThread>
  331. #include <QTcpSocket>
  332. #include <QSqlDatabase>
  333. #include <QTimer>
  334. class tcpThread : public QThread{
  335.     Q_OBJECT
  336. public:
  337.     void run();
  338.     tcpThread(int socketDescriptor,  QObject *parent);
  339.     ~tcpThread();
  340.     bool createConnection();
  341. public slots:
  342.     void sendSearch();
  343.     void readMessage();
  344.     void sendCommand();
  345. private:
  346.     int socketDescriptor;
  347.     QTcpSocket* tcpSocket;
  348.     QTimer* dataTimer;
  349.     QTimer* cmdTimer;
  350.     int sendNum;
  351.     QSqlDatabase* db;
  352. };
  353. #endif // TCPTHREAD_H

可是多个客户端一起并发时候，容易产生错误。
回复
举报
分享到 淘江湖 新浪 QQ微博 QQ空间 开心 人人 豆瓣 网易微博 百度 鲜果 白社会 飞信
离线songzhao831
论坛版主
只看该作者 1楼 发表于: 2014-06-17
我也遇到了相同的错误, 多线线程并发访问的时候, 就出现没有响应的情况. 有大神出来解释下吗???????
回复
举报
离线songzhao831
论坛版主
只看该作者 2楼 发表于: 2014-06-17
楼主试过我Qmutex吗?
回复
举报
离线liudianwu
总版主
只看该作者 3楼 发表于: 2015-11-22
我也是遇到这种情况，主线程创建的addDatabase，子线程使用查询增删改，偶尔出现QSqlError(2006, "QMYSQL: 不能提交事务", "MySQL server has gone away")的情况导致后面没数据了，后面在子线程中加了QMutex保护，就好了！
欢迎关注微信公众号：Qt实战/Qt入门和进阶(各种开源作品、经验整理、项目实战技巧，专注Qt/C++软件开发，视频监控、物联网、工业控制、嵌入式软件、国产化系统应用软件开发) QQ：517216493  WX：feiyangqingyun  QQ群：751439350
回复
举报
离线XChinux
总版主
只看该作者 4楼 发表于: 2015-11-22
Qt文档中不是讲了么，不要跨线程使用QSqlDatabase连接，即在哪个线程里创建的连接，就只在该线程中使用这个连接，不要在其他线程中使用该连接。

解决办法有两个，一是每个线程单独创建自己的Sql的连接使用,二是对该数据库操作都放到一个单独线程中。
二笔 openSUSE Vim N9 BB10 XChinux@163.com 网易博客 腾讯微博
承接C++/Qt、Qt UI界面、PHP及预算报销系统开发业务
回复
举报
发帖 回复
返回列表
快速回复
限100 字节
 
上一个 下一个