Author Topic: Ada yg pernah mengalami yg kayak ngini ?  (Read 1224 times)

0 Members and 1 Guest are viewing this topic.

Offline stone.cold

  • Newbie
  • *
  • Posts: 33
Ada yg pernah mengalami yg kayak ngini ?
« on: April 16, 2015, 02:56:49 PM »
dear mastah,
program saya sering mengalami hal kayak begini...kira-kira kesalahannya di sisi applikasi atau database ya ?
dan apa yg musti saya upgrade/perbaiki/replace baik dari script visual foxpro atau sql servernya ?

* Error Message *
1
 in SQL Command or Script
-----------------------------------------------------------------------------------------------------
 Error number:  0
 Error message:
 Line of code with error: Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
 Line number of error: 0
 Program with error : CATATERROR
 last called sintax : [MICROSOFT][ODBC SQL SERVER DRIVER][SQL SERVER]TRANSACTION (PROCESS ID 60) WAS DEADLOCKED ON LOCK RESOURCES WITH ANOTHER PROCESS AND HAS BEEN CHOSEN AS THE DEADLOCK VICTIM. RERUN THE TRANSACTION.

select
   [dhu_spk]
      ,[dhu_id]
      ,[dhu_mfid]
      ,[dhu_miid]
      ,[dhu_doid]
      ,[dhu_dodate]
      ,[dhu_fakturid]
      ,[dhu_fakturdate]
      ,[dhu_rcvid]
      ,[dhu_rcvdate]
      ,[dhu_duedate]
      ,[dhu_qty]
      ,[dhu_unit]
      ,[dhu_price]
      ,[dhu_prcrec]
      ,[dhu_ongkir]
      ,[dhu_potongan]
      ,[dhu_total]
      ,[dhu_totalrec]
      ,[dhu_posted]
      ,[dhu_printed]
      ,[dhu_spaid]
      ,[dhu_mpaid]
      ,[dhu_notes]
      ,[dhu_lupdate]
      ,[dhu_lupdateby]
    from dbo.dhutang
    left join dbo.hhutang on hhu_id=dhu_id
    where dhu_mfid='20350004'
    and dhu_fakturid='                                                                                                    '
    and dhu_doid='                                                                                                    '
    and dhu_fakturdate='2015-04-14'
    and dhu_miid='151292'
    and hhu_hpoid='04009-15M     '
Generated error on : 14-04-2015 11:05:12


padahal di program/script visual foxpronya menurut saya sudah saya sesuaikan :
contoh script saving recordnya :

   BEGIN TRY
      BEGIN TRANSACTION ;
         DELETE /INSERT/UPDATE [statement]
      COMMIT TRANSACTION;
   END TRY
   BEGIN CATCH
      IF @@TRANCOUNT > 0
         ROLLBACK TRAN
   END CATCH

ada yg salah nggak dengan struktur sintax diatas ?


« Last Edit: April 16, 2015, 02:58:42 PM by stone.cold »

Offline foxy

  • Fox-id M.V.P
  • Hero Member
  • *
  • Posts: 3.605
    • Foxy Land
Re: Ada yg pernah mengalami yg kayak ngini ?
« Reply #1 on: April 16, 2015, 08:01:04 PM »
saya pernah ngalamin. Beberapa kali lah... Tapi pasti gak sampai 10 kali. Penyebab di kasus saya adalah transaction yang numpuk (nested transaction).

Solusi:
1. Periksa lagi code Anda. Renungkan lagi setiap transaksi. Pastikan tidak ada transaksi yang berjalan terlalu lama atau blok transaksi yang terlalu panjang.
2. Cek lagi code di VFP Anda yang menghasilkan query. Contoh kenapa kok bisa ada:
Code: [Select]
    and dhu_fakturid='                                                                                                    '
    and dhu_doid='                                                                                                    '

Pastikan menggunakan <<ALLTRIM(variable)>> supaya gak harus ada space sepanjang itu.

Saya gak bilang bahwa query itu penyebab deadlock, tapi kalau Anda selama ini tidak menganggap itu masalah, kemungkinan besar ada hal-hal di query Anda yang lain yang perlu dioptimalisasi.

hth,
foxy
This post is provided as is. Feel free to use all the codes and information, however understand that I don't have any obligations to fix any bug(s) or follow up this subject.

Offline foxy

  • Fox-id M.V.P
  • Hero Member
  • *
  • Posts: 3.605
    • Foxy Land
This post is provided as is. Feel free to use all the codes and information, however understand that I don't have any obligations to fix any bug(s) or follow up this subject.

Offline stone.cold

  • Newbie
  • *
  • Posts: 33
Re: Ada yg pernah mengalami yg kayak ngini ?
« Reply #3 on: April 24, 2015, 09:17:59 AM »
oh...saya juga curiga itu juga om foxy, sekarang lagi saya scanning semua scriptnya dan diusahakan jgn sampai kayak ngitu lagi (ada blank space yg terlalu panjang).
btw thanks atas info dan masukannya om foxy.
saya pikir cuma saya doang yg mengalami masalah ini.