ApsaraDB RDS:Cara menangani deadlock di ApsaraDB RDS for SQL Server

Deskripsi masalah

Ketika sebuah aplikasi sering membaca dari atau menulis ke tabel atau resource yang sama, deadlock dapat terjadi. Saat hal ini terjadi, SQL Server menghentikan salah satu transaksi dan mengembalikan pesan error ke klien, seperti berikut:

Error Message: Msg 1205, Level 13, State 47, Line 1Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Solusi

1. Hubungkan ke instans dari klien. Untuk informasi selengkapnya, lihat Connect to an ApsaraDB RDS for SQL Server instance.

2. Pantau tampilan (view) yang relevan.

   1. Jalankan pernyataan SQL berikut untuk memantau SYS.SYSPROCESSES secara berkala.

      WHILE 1 = 1
      BEGIN
      SELECT * FROM SYS.SYSPROCESSES WHERE BLOCKED <> 0;
      WAITFOR DELAY '[$Time]';
      END;

      Catatan

      Contoh ini menggunakan [$Time]. Anda dapat menyesuaikan interval polling, misalnya 00:00:01.

      Dalam hasil dari beberapa iterasi, SPID 53 sedang menunggu lock bertipe LCK_M_X, dan SPID 56 sedang menunggu lock bertipe LCK_M_S. Nilai blocked untuk SPID 53 adalah 56, dan nilai blocked untuk SPID 56 adalah 53, yang menunjukkan bahwa keduanya saling memblokir. Nilai waittime untuk kedua sesi meningkat pada setiap loop.

      Catatan

      Dalam hasil pemantauan, kolom blocked menampilkan ID sesi yang melakukan pemblokiran, dan kolom waitresource menampilkan resource yang sedang ditunggu oleh sesi yang diblokir. Hasil ini menunjukkan bahwa SPID 53 dan SPID 56 saling memblokir, sehingga membentuk deadlock.

   2. Jalankan pernyataan SQL berikut untuk memantau view seperti sys.dm_tran_locks dan sys.dm_os_waiting_tasks secara berkala.

      while 1=1
          Begin
          SELECT
          db.name DBName,
          tl.request_session_id,
          wt.blocking_session_id,
          OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
          tl.resource_type,
          h1.TEXT AS RequestingText,
          h2.TEXT AS BlockingText,
          tl.request_mode
          FROM sys.dm_tran_locks AS tl
          INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
          INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
          INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
          INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
          INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
          CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
          CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
          waitfor delay '[$Time]'
          End

      Dua sesi saling memblokir, sehingga menciptakan deadlock: request_session_id 56 diblokir oleh blocking_session_id 62 pada objek Tbl2, dengan request_mode X. Secara bersamaan, sesi 62 diblokir oleh sesi 56 pada objek Tbl1, dengan request_mode X. RequestingText untuk kedua sesi menunjukkan pernyataan INSERT, yang mengonfirmasi adanya hubungan pemblokiran siklik yang menyebabkan deadlock.

      Tabel berikut menjelaskan kolom-kolom yang dikembalikan.

      Parameter	Deskripsi
      DBName	Database yang diakses oleh session yang melakukan permintaan.
      request_session_id	ID session yang diblokir.
      blocking_session_id	ID session yang melakukan pemblokiran.
      BlockedObjectName	Objek yang diakses oleh session yang diblokir.
      resource_type	Jenis resource yang sedang ditunggu.
      RequestingText	Pernyataan yang dieksekusi oleh session yang diblokir.
      BlockingText	Pernyataan yang sedang dieksekusi oleh session yang melakukan pemblokiran.
      request_mode	Mode lock yang diminta oleh session yang diblokir.

   3. Jika instans Anda menjalankan ApsaraDB RDS for SQL Server 2012, Anda juga dapat menggunakan SQL Server Profiler untuk memantau dan menangkap graf deadlock.

      Pada kotak dialog Trace Properties, di tab Events Selection, perluas kategori Locks dan pilih event Deadlock graph dan Lock:Deadlock.

      Graf deadlock yang ditangkap:

      Hasil trace SQL Server Profiler menunjukkan dua event Lock:Deadlock Chain: SPID 52 memegang lock eksklusif (5-X) pada database test_shrink (transaksi 1374753), sedangkan SPID 55 memegang lock shared (3-S) (transaksi 1374762). Hal ini kemudian memicu event Lock:Deadlock. Graf deadlock menunjukkan bahwa SPID 52 dan SPID 55 sedang mengunci resource key lock pada indeks i dari test_shrink.dbo.Th12 dan test_shrink.dbo.Th11 masing-masing. Kedua proses tersebut meminta lock yang dipegang oleh pihak lain (Request Mode adalah X/S), sehingga menciptakan ketergantungan melingkar yang mengakibatkan deadlock.

Rekomendasi

Terapkan rekomendasi berikut untuk menyelesaikan dan mencegah deadlock.

• Hentikan sesi yang melakukan pemblokiran untuk segera menyelesaikan deadlock.

• Periksa transaksi jangka panjang dan segera lakukan commit untuk melepas lock.

• Jika deadlock disebabkan oleh shared lock dan aplikasi Anda dapat mentoleransi dirty read, gunakan petunjuk kueri WITH (NOLOCK). Misalnya, tambahkan SELECT * FROM table WITH (NOLOCK); ke dalam kueri Anda. Hal ini memungkinkan kueri dieksekusi tanpa meminta lock, sehingga membantu mencegah deadlock.

• Tinjau logika aplikasi untuk memastikan resource diakses dalam urutan yang konsisten.

Operasi terkait

Anda dapat melihat detail deadlock untuk instans ApsaraDB RDS for SQL Server Anda di Konsol ApsaraDB RDS. Untuk informasi selengkapnya, lihat View deadlock statistics.