請問各位, 我用TADOTable元件,用Sleep(10) 的時間差,連續寫入一萬筆資料到MS SQL,但寫到一陣子時, 會出現 "資料儲存體中的資料列之關鍵值已被變更或刪除。現在已將區域資料列刪除。" 我是用 ADOTable1->Insert(); //....寫入值 ADOTable1->Post(); 望各位幫忙解決

我查到的是連續寫入時, 會造成table lock住,,變得無法寫資料, 請問因如何解決呢?

把 LockType 改為 ltPessimistic 試試 或是 LockType 改為ltBatchOptimistic, 然後 每 1000 筆 補一個 ADOTable.UpdateBatch

malanlk 你好, 我用Thread來負責寫table 並把 LockType 改為 ltPessimistic ,一樣會發生此問題; LockType 改為ltBatchOptimistic的話,因我用Thread好像不太適合也? 我是用clientsocket接收資料並將資料寫到buffer中, 用thread寫到db的.

請問你的 ADOConnection 也是放在 Thread 中嗎? 如果不是, 你有用 Synchronize 來做同步控制嗎?

malanlk 你好, ADOConnection不是放在thread,而我也有用Synchronize,,只要連續寫多筆資料時,table就會發生沒法寫入,被lock住了

方便將程式(或部分程式) 貼上來 或 放到 會員求助區嗎?

我的寫檔程式 note: 我要寫的data有可能會重覆; 但寫入時,發生錯誤訊息時,會由buffer中刪除.     

 
//---------------------------------------------------------------------------
void __fastcall DBThread::Execute()
{
  //---- Place thread code here ----
  while(!Terminated)
  {
    Synchronize(WriteDB);
    Sleep(10);
  }
}
//---------------------------------------------------------------------------
void __fastcall DBThread::WriteDB()
{
  if(!Form1->ADOConnection1->Connected) return;      if(Form1->GPSBuffer.size() > 0)
  {
    AnsiString DAY;
    int lonInt,latInt;
    float lonSouce,lonFloat,latSouce,latFloat;
    vector::iterator Ptr = Form1->GPSBuffer.begin();        try
    {
      DAY = AnsiString(Ptr->Timer,2) "/" 
            AnsiString(Ptr->Timer 2,2) "/" 
            AnsiString(Ptr->Timer 4,2) " " 
            AnsiString(Ptr->Timer 6,2) ":" 
            AnsiString(Ptr->Timer 8,2) ":" 
            AnsiString(Ptr->Timer 10,2);
      lonSouce = StrToFloatDef(AnsiString(Ptr->WGS_LON,sizeof(Ptr->WGS_LON)),0);
      lonInt = lonSouce/100;
      lonFloat = (lonSouce-lonInt*100)/60;
      latSouce = StrToFloatDef(AnsiString(Ptr->WGS_LAT,sizeof(Ptr->WGS_LAT)),0);
      latInt = latSouce/100;
      latFloat = (latSouce-latInt*100)/60;          Form1->ADOTableGPS->Insert();
      Form1->ADOTableGPS->FieldByName("Unit_id")->AsString = AnsiString(Ptr->ID,Max_GDTID_Len/2);
      Form1->ADOTableGPS->FieldByName("DateTime")->AsDateTime = StrToDateTime(DAY);
      Form1->ADOTableGPS->FieldByName("WGS_LON")->AsFloat = (float)lonInt lonFloat;
      Form1->ADOTableGPS->FieldByName("WGS_LAT")->AsFloat = (float)latInt latFloat;
      Form1->ADOTableGPS->FieldByName("Heading")->AsFloat = StrToFloatDef(AnsiString(Ptr->Heading,sizeof(Ptr->Heading)),0);
      Form1->ADOTableGPS->FieldByName("Speed")->AsFloat = StrToFloatDef(AnsiString(Ptr->Speed,sizeof(Ptr->Speed)),0)*1.853;
      Form1->ADOTableGPS->FieldByName("Sat")->AsInteger = (StrToIntDef(AnsiString(Ptr->Sat,sizeof(Ptr->Sat)),0) >= 9)?9:StrToIntDef(AnsiString(Ptr->Sat,sizeof(Ptr->Sat)),0);
      Form1->ADOTableGPS->FieldByName("IO1")->AsString = AnsiString(Ptr->IO1);
      Form1->ADOTableGPS->FieldByName("IO2")->AsString = AnsiString(Ptr->IO2);
      Form1->ADOTableGPS->FieldByName("IO3")->AsString = AnsiString(Ptr->IO3);
      Form1->ADOTableGPS->FieldByName("Data_type")->AsString = AnsiString(Ptr->Type);
      Form1->ADOTableGPS->Post();
    }
    catch(Exception &e)
    {
      AnsiString errMsg = AnsiString((char*)Ptr,sizeof(TGPSType));
      Form1->MsgWriteFile("Write DB Error.{" errMsg "}" e.Message);
    }
    Form1->GPSBuffer.erase(Ptr);
  }
}
//---------------------------------------------------------------------------

你確定 Insert ... Post 之間的 資料轉換都不會出問題嗎?    如果 資料轉換出問題 , Post 就會被跳過去, 也就是 ADOTableGPS 會維持 Insert 狀態, 造成 Lock ,所以你要在 except 中 檢查...    

    catch(Exception &e)
    {
      AnsiString errMsg = AnsiString((char*)Ptr,sizeof(TGPSType));
      Form1->MsgWriteFile("Write DB Error.{" errMsg "}" e.Message);
      if (ADOTableGPS->State==dsInsert) 
      {
        Form1->ADOTableGPS.Cancel();
      }
    }

謝謝malanlk,,幫我解決此問題,, 請問我只有在thread 才有用到adotable 需要用Synchronize嗎? 感覺多筆資料時,會有畫面lay

如果畫面不想 delay 試試看在 WriteDB() 內加 Application->ProcessMessages();