Solusi dari Query Deadlock

Simple tapi kalo ga tau ya rempong juga.. Bisa googling sana sini, padahal solusinya cuma sebaris ๐Ÿ˜€

Pernah mengalami query deadlock??

Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Penyebab query deadlock macem2. Mulai dari query yang kurang optimized sampai dengan indexing yang kurang tepat.
Daripada rempong menggunakan WITH (NOLOCK) di setiap table lebih baik gunakan syntax berikut di awal query :

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

FYI, syntax tersebut akan berfungsi sebelum ketemu dengan statemen GO.
Misalkan :

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT A FROM TBL_KARYAWAN
UPDATE TBL_KARYAWAN SET NIP = 112233445 WHERE NIP =12345678
SELECT NIP, NAMA FROM TBL_KARYAWAN
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM TBL_DIVISI
UPDATE TBL_ DIVISI SET NAMA = โ€™DIVISI ITโ€™ WHERE KODE = 334577

Maka statemen tersebut hanya berfungsi sampai baris ke 4. Sedangkan pada baris ke 6 perlu ditambahkan lagi agar query dibawahnya tidak saling nge-lock.ย Lebih detailnya silahkan baca http://msdn.microsoft.com/en-us/library/ms173763.aspx

Okey smoga membantu ๐Ÿ™‚

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s