Foto oleh panumas nikhomkhai via Pexels

Deadlock terjadi ketika dua atau lebih transaksi saling menunggu lock yang dipegang satu sama lain, menciptakan kebuntuan di mana tidak ada satu pun transaksi yang bisa melanjutkan prosesnya. SQL Server mendeteksi kondisi ini secara otomatis dan akan membatalkan salah satu transaksi (disebut deadlock victim) untuk memecahkan kebuntuan tersebut.

Deadlock paling sering terjadi ketika dua transaksi mengakses dua atau lebih resource yang sama namun dalam urutan yang berbeda, misalnya Transaksi A mengunci Tabel X lalu mencoba mengunci Tabel Y, sementara Transaksi B sudah mengunci Tabel Y lebih dulu dan mencoba mengunci Tabel X.

Artikel ini membahas penyebab umum deadlock, cara membaca pesan error deadlock yang diberikan SQL Server, serta strategi praktis untuk mencegahnya terjadi berulang kali di aplikasi produksi.

Contoh Skenario Klasik Penyebab Deadlock

Urutan akses tabel yang berbeda memicu deadlock

-- Sesi A BEGIN TRANSACTION; UPDATE Akun SET Saldo = Saldo - 100 WHERE AkunID = 1; UPDATE Akun SET Saldo = Saldo + 100 WHERE AkunID = 2; COMMIT; -- Sesi B (berjalan hampir bersamaan, urutan dibalik) BEGIN TRANSACTION; UPDATE Akun SET Saldo = Saldo - 50 WHERE AkunID = 2; UPDATE Akun SET Saldo = Saldo + 50 WHERE AkunID = 1; COMMIT;
Pada contoh ini, Sesi A mengunci AkunID 1 lalu mencoba mengunci AkunID 2, sementara Sesi B melakukan sebaliknya: mengunci AkunID 2 lebih dulu lalu mencoba mengunci AkunID 1. Jika kedua transaksi berjalan hampir bersamaan, keduanya akan saling menunggu lock yang dipegang satu sama lain, menciptakan deadlock yang harus dipecahkan SQL Server dengan membatalkan salah satunya.

Membaca Pesan Error Deadlock

Pesan error 1205 menandakan deadlock victim

-- Pesan error yang diterima deadlock victim: -- Msg 1205, Level 13, State 51 -- Transaction (Process ID 55) was deadlocked on lock -- resources with another process and has been chosen -- as the deadlock victim. Rerun the transaction.
Error code 1205 secara spesifik menandakan bahwa transaksi tersebut dipilih sebagai deadlock victim dan dibatalkan secara otomatis oleh SQL Server, bukan karena kesalahan logika query itu sendiri. Aplikasi yang menangani error ini sebaiknya mencoba menjalankan ulang (retry) transaksi tersebut, karena kemungkinan besar akan berhasil jika dijalankan ulang tanpa benturan waktu yang sama.

Mencegah Deadlock dengan Konsisten Urutan Akses

Urutan akses konsisten mencegah deadlock

-- Perbaikan: pastikan SEMUA transaksi mengakses -- tabel/baris dengan urutan ID yang sama, misalnya selalu ID kecil dulu BEGIN TRANSACTION; UPDATE Akun SET Saldo = Saldo - 100 WHERE AkunID = 1; -- selalu AkunID terkecil dulu UPDATE Akun SET Saldo = Saldo + 100 WHERE AkunID = 2; COMMIT;
Solusi paling umum dan paling efektif untuk mencegah deadlock akibat skenario di atas adalah memastikan semua transaksi mengakses resource yang sama dengan urutan yang konsisten, misalnya selalu mengurutkan berdasarkan ID terkecil terlebih dahulu. Dengan urutan yang konsisten, satu transaksi akan menunggu secara wajar (blocking biasa) alih-alih terjebak saling menunggu (deadlock).

Menggunakan Retry Logic di Level Aplikasi

Retry logic di aplikasi menangani deadlock victim

int percobaan = 0; while (percobaan < 3) { try { // jalankan transaksi database break; } catch (SqlException ex) when (ex.Number == 1205) { percobaan++; Thread.Sleep(100 * percobaan); } }
Karena deadlock adalah bagian normal dari sistem database dengan concurrency tinggi, praktik yang baik adalah menyiapkan retry logic di level aplikasi khusus untuk error code 1205, dengan jeda singkat yang bertambah di setiap percobaan (exponential backoff) agar tidak langsung bertabrakan lagi pada percobaan berikutnya.

Membaca Deadlock Graph dari Extended Events

Menangkap deadlock graph memakai Extended Events, pengganti trace flag 1222

-- Membuat Extended Events session untuk menangkap deadlock graph CREATE EVENT SESSION CaptureDeadlocks ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET filename = N'DeadlockCapture.xel'); GO ALTER EVENT SESSION CaptureDeadlocks ON SERVER STATE = START; GO -- Setelah deadlock terjadi, baca hasilnya: SELECT XEvent.query('.') AS DeadlockGraph FROM ( SELECT CAST(event_data AS XML) AS XEvent FROM sys.fn_xe_file_target_read_file('DeadlockCapture*.xel', NULL, NULL, NULL) ) AS src; -- Hasil: dokumen XML berisi daftar process, resource-list, dan victim-list

Extended Events dengan event sqlserver.xml_deadlock_report adalah cara standar menangkap deadlock graph di versi SQL Server modern, menggantikan pendekatan lama memakai trace flag 1222 yang menulis ke error log dalam format teks yang lebih sulit dibaca. Deadlock graph yang dihasilkan berupa dokumen XML berisi detail setiap proses yang terlibat, resource apa yang mereka kunci, dan resource apa yang mereka tunggu.

Di dalam deadlock graph, elemen process-list menampilkan setiap sesi yang terlibat beserta query yang sedang dijalankan, sementara elemen resource-list menunjukkan objek atau baris spesifik yang menjadi rebutan, dan elemen victim-list menandai transaksi mana yang dipilih SQL Server sebagai korban untuk di-rollback. SQL Server selalu memilih victim berdasarkan estimasi biaya rollback paling murah, bukan berdasarkan transaksi mana yang lebih dulu dimulai.

Analisis deadlock graph biasanya difokuskan pada urutan perolehan lock oleh masing-masing proses, karena deadlock klasik terjadi ketika dua transaksi mengunci resource dalam urutan yang saling berlawanan; solusi paling umum adalah memastikan seluruh bagian aplikasi mengakses tabel-tabel terkait dengan urutan yang konsisten, sehingga kondisi saling tunggu melingkar tidak pernah terbentuk.

Mengatur DEADLOCK_PRIORITY untuk Mengontrol Transaksi Mana yang Jadi Korban

DEADLOCK_PRIORITY menentukan transaksi mana yang dikorbankan saat deadlock terdeteksi

-- Transaksi penting: beri prioritas lebih tinggi agar TIDAK dipilih jadi victim BEGIN TRANSACTION; SET DEADLOCK_PRIORITY HIGH; UPDATE Accounts SET Balance = Balance - 1000000 WHERE AccountID = 1; -- operasi transfer dana penting, diprioritaskan tetap jalan COMMIT TRANSACTION; -- Transaksi laporan batch: beri prioritas rendah, boleh jadi korban duluan BEGIN TRANSACTION; SET DEADLOCK_PRIORITY LOW; UPDATE ReportCache SET LastRun = GETDATE() WHERE ReportID = 5; COMMIT TRANSACTION; -- Bisa juga memakai nilai numerik -10 sampai 10 untuk gradasi prioritas lebih presisi SET DEADLOCK_PRIORITY -5; -- Default semua sesi adalah NORMAL (setara 0)

Secara default, ketika SQL Server mendeteksi deadlock, ia memilih victim berdasarkan estimasi biaya rollback paling murah di antara transaksi yang terlibat, tanpa mempertimbangkan seberapa penting transaksi tersebut secara bisnis. Pengaturan SESSION DEADLOCK_PRIORITY memungkinkan developer memberi petunjuk eksplisit kepada SQL Server tentang transaksi mana yang sebaiknya tidak dikorbankan ketika deadlock terjadi, sehingga keputusan tersebut tidak lagi semata-mata berdasarkan estimasi biaya teknis.

DEADLOCK_PRIORITY menerima nilai kata kunci LOW, NORMAL, HIGH, atau nilai numerik dari -10 hingga 10 untuk gradasi yang lebih presisi; SQL Server akan memilih sesi dengan prioritas terendah sebagai victim terlebih dahulu, dan hanya jika seluruh transaksi yang terlibat memiliki prioritas sama, barulah estimasi biaya rollback dipakai sebagai penentu, sama seperti perilaku default. Pengaturan ini berlaku per sesi dan hanya aktif selama koneksi tersebut berjalan, sehingga harus diset ulang setiap kali sesi baru dibuka kecuali didefinisikan di awal setiap stored procedure terkait.

Pengaturan ini berguna pada sistem yang menjalankan transaksi dengan tingkat kepentingan berbeda secara bersamaan, misalnya transaksi transfer dana yang harus selalu berhasil diprioritaskan HIGH, sementara job batch pembaruan cache laporan yang bisa diulang kapan saja diberi LOW, sehingga ketika keduanya kebetulan terlibat deadlock, job batch yang dikorbankan dan transaksi finansial tetap berjalan. Kode aplikasi yang memanggil job berprioritas rendah tetap harus siap menangani error deadlock dan melakukan retry otomatis, karena menjadi victim bukan berarti operasi tersebut tidak perlu diselesaikan sama sekali.

Lock Escalation dari Row/Page Lock Menjadi Table Lock

LOCK_ESCALATION mengatur apakah row lock boleh naik menjadi table/partition lock

-- Mengamati lock escalation lewat Extended Events atau sys.dm_tran_locks UPDATE Orders SET Status = 'Archived' WHERE OrderDate < '2020-01-01'; -- Jika baris yang terkena mencapai sekitar 5000 row lock dalam satu statement, -- SQL Server menaikkan (escalate) menjadi satu table lock (atau partition lock) -- Mengatur ambang/perilaku lock escalation per tabel ALTER TABLE Orders SET (LOCK_ESCALATION = AUTO); -- escalate ke partition jika tabel dipartisi ALTER TABLE Orders SET (LOCK_ESCALATION = DISABLE); -- menonaktifkan escalation untuk tabel ini

Lock escalation terjadi ketika satu pernyataan memegang row lock atau page lock dalam jumlah besar, sekitar 5000 lock pada tabel yang sama, sehingga SQL Server menaikkan level penguncian menjadi satu table lock tunggal untuk menghemat memori yang dipakai untuk melacak lock individual, karena setiap lock membutuhkan struktur memori tersendiri.

Konsekuensi lock escalation adalah sesi lain yang ingin mengakses baris mana pun pada tabel yang sama, bahkan baris yang tidak terkait dengan transaksi tersebut, akan terblokir karena seluruh tabel sudah dikunci. Opsi LOCK_ESCALATION = DISABLE mencegah hal ini namun meningkatkan konsumsi memori lock, sedangkan nilai AUTO pada tabel yang dipartisi mengarahkan escalation ke level partisi, bukan ke seluruh tabel. Memecah operasi besar menjadi batch-batch kecil dengan commit terpisah, mirip mitigasi pada transaksi log panjang, juga membantu menghindari lock escalation karena jumlah lock yang dipegang dalam satu statement menjadi jauh lebih sedikit.

Kesalahan Umum yang Sering Terjadi

Tidak Menangani Error 1205 Secara Khusus

Memperlakukan error deadlock sama seperti error lainnya tanpa mekanisme retry akan membuat pengguna aplikasi mengalami kegagalan transaksi yang sebenarnya bisa berhasil hanya dengan mencoba ulang sekali lagi.

Membiarkan Transaksi Terbuka Terlalu Lama

Transaksi yang mencakup operasi I/O lambat seperti pemanggilan API eksternal di tengah-tengah akan menahan lock lebih lama, meningkatkan peluang terjadinya deadlock dengan transaksi lain yang mencoba mengakses resource yang sama.

Mengabaikan Urutan Akses Antar Tabel yang Berbeda

Deadlock tidak hanya terjadi antar baris pada tabel yang sama, tetapi juga antar tabel yang berbeda jika dua transaksi mengaksesnya dengan urutan yang berlawanan, sesuatu yang sering terlewat saat kode ditulis oleh anggota tim berbeda tanpa konvensi yang jelas.

Kapan Pendekatan Ini Tidak Cocok Dipakai

Mencegah deadlock sepenuhnya di sistem dengan banyak transaksi bersamaan hampir mustahil dilakukan, sehingga pendekatan yang realistis adalah meminimalkan frekuensinya melalui urutan akses yang konsisten dan transaksi yang seringkas mungkin, sambil tetap menyiapkan retry logic sebagai jaring pengaman.

Isolation level yang lebih rendah bisa mengurangi kemungkinan deadlock karena lock yang dipegang lebih sedikit, namun ini harus ditimbang dengan kebutuhan konsistensi data — mengorbankan konsistensi hanya demi menghindari deadlock bukan solusi yang tepat untuk operasi yang memang kritikal.

Ringkasan

Deadlock adalah konsekuensi wajar dari sistem database dengan banyak transaksi berjalan bersamaan, bukan tanda bahwa ada yang salah secara fundamental dengan desain aplikasi. Kunci untuk menguranginya adalah urutan akses resource yang konsisten di seluruh aplikasi, transaksi yang ringkas, dan retry logic yang siap menangani deadlock victim dengan baik.

Seri Belajar SQL Server:

← Sebelumnya: Isolation Level dan Locking di SQL Server

→ Selanjutnya: Membaca Execution Plan untuk Optimasi Query