Kursus MariaDB / Menguji Isolasi, Kunci, dan Deadlock dengan Dua Koneksi

Isolasi menentukan perubahan bersamaan yang dapat dibaca transaksi, sedangkan kunci mengoordinasikan operasi yang saling bertentangan. Dua koneksi terpisah dengan urutan terkontrol dapat memperlihatkan REPEATABLE READ, READ COMMITTED, batas waktu tunggu kunci, dan deadlock sebagai perilaku yang berbeda.

Siapkan dua koneksi mandiri A dan B

Buka dua klien baris perintah ke server belajar dan basis data shopcourse yang sama. Panduan klien MariaDB menjelaskan koneksinya. Pastikan CONNECTION_ID() berbeda; menempelkan kedua sisi ke satu klien tidak menciptakan operasi bersamaan. Gunakan hanya data latihan dan pahami batas START TRANSACTION, COMMIT, dan ROLLBACK.

Jalankan blok berikut secara terpisah pada A dan B. Setiap koneksi menyimpan tingkat isolasi serta waktu tunggunya sendiri untuk dipulihkan. Periksa versi server dengan SELECT VERSION();. Pengaturan latihan berlaku pada SESSION, bukan GLOBAL.

SELECT CONNECTION_ID(),DATABASE(),@@SESSION.tx_isolation;
SET @saved_isolation=@@SESSION.tx_isolation;
SET @saved_wait=@@SESSION.innodb_lock_wait_timeout;

Buat tabel berikut hanya pada A. Pastikan accounts_36_lab belum ada dengan SHOW TABLES. Jika sudah ada, periksa definisi serta isinya dan cadangkan data yang diperlukan sebelum memperlakukannya sebagai objek latihan yang boleh dibuang.

USE shopcourse;
CREATE TABLE accounts_36_lab (
 account_id INT PRIMARY KEY,balance INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO accounts_36_lab VALUES(1,1000),(2,2000);
Kasus Aturan yang diamati Perbedaan penting
SELECT biasa pada REPEATABLE READ Pembacaan konsisten dari snapshot transaksi Berbeda dari pembacaan yang mengunci
SELECT biasa pada READ COMMITTED Versi data dikomit yang terlihat bagi setiap perintah Hasil pembacaan berulang dapat berubah
Menunggu kunci Menunggu pelepasan kunci yang bertentangan Waktu tunggu habis tidak sama dengan deadlock

Bandingkan REPEATABLE READ dalam urutan A → B → A

Mulailah dari saldo 1000 untuk akun 1 pada persiapan. Ikuti label koneksi: B baru bekerja setelah pembacaan pertama A selesai. Snapshot adalah pandangan data yang konsisten untuk pembacaan transaksi.

A-1: bentuk snapshot

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;

B-1: komit perubahan

START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;

A-2: baca kembali dalam transaksi yang sama

SELECT balance FROM accounts_36_lab WHERE account_id=1;

A-1 dan A-2 sama-sama menghasilkan 1000. SELECT biasa kedua dari A tetap membaca snapshot meskipun B sudah mengomit saldo 1100. Perbandingan ini memakai SELECT tanpa penguncian. Menggantinya dengan FOR UPDATE merupakan percobaan berbeda.

A-3: akhiri transaksi lalu baca kembali

ROLLBACK;
SELECT balance FROM accounts_36_lab WHERE account_id=1;

Pembacaan setelah ROLLBACK menghasilkan 1100. Referensi SET TRANSACTION MariaDB menjelaskan cakupan isolasi dan perbedaan akses biasa dari akses yang mengunci.

Ulangi READ COMMITTED setelah penyiapan ulang yang jelas

B-0: pulihkan saldo percobaan sebelumnya

ROLLBACK;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=1000 WHERE account_id=1;
COMMIT;

A-1: pembacaan READ COMMITTED pertama

ROLLBACK;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;

B-1: komit saldo 1100

START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;

A-2: pembacaan kedua dan penyelesaian

SELECT balance FROM accounts_36_lab WHERE account_id=1;
ROLLBACK;

A membaca 1000 terlebih dahulu lalu 1100. Setiap SELECT biasa menggunakan data dikomit yang terlihat untuk perintah tersebut. Perbedaan ini saja tidak menentukan pilihan isolasi aplikasi; periksa juga konflik penulisan serta aturan konsistensi yang diperlukan.

Bedakan kunci yang ditahan dari batas waktu tunggu

A-1: tahan kunci pada baris 1

START TRANSACTION;
SELECT account_id,balance FROM accounts_36_lab
WHERE account_id=1 FOR UPDATE;

B-1: coba ubah baris yang sama

SET SESSION innodb_lock_wait_timeout=3;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;

Biarkan transaksi A tetap terbuka. B seharusnya mencapai batas waktu tunggu setelah sekitar tiga detik, biasanya dengan kesalahan 1205. Pengaturan tersebut membatasi waktu tunggu sesi dan tidak menjamin waktu eksekusi persis. Jangan menganggap kesalahan otomatis mengakhiri seluruh transaksi B.

B-2: batalkan percobaan dan pulihkan waktu tunggu

ROLLBACK;
SET SESSION innodb_lock_wait_timeout=@saved_wait;

A-2: lepaskan kunci

ROLLBACK;

Kunci pada percobaan diperoleh dalam transaksi eksplisit. Pertahankan batas tersebut saat mengulang. Referensi FOR UPDATE MariaDB menjelaskan persyaratan transaksi InnoDB. Menunggu satu ketergantungan berbeda dari lingkaran saling menunggu pada kasus berikut.

Buat deadlock melalui empat langkah berselang

Deadlock atau kebuntuan terjadi ketika transaksi saling menunggu dalam sebuah lingkaran. Pastikan kedua transaksi sebelumnya sudah selesai. Berpindahlah antara jendela dalam urutan A-1 → B-1 → A-2 → B-2; jangan menjalankan semua langkah sebagai satu skrip berurutan.

Langkah Koneksi Tindakan
1 A Ubah dan kunci baris 1
2 B Ubah dan kunci baris 2
3 A Minta baris 2 lalu menunggu
4 B Minta baris 1 dan membentuk lingkaran

A-1: kunci hanya baris pertama

START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;

B-1: kunci baris lain

START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;

A-2: minta baris yang ditahan B

UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;

A menunggu pada tahap ini. Beralih ke B dan jalankan perintah berikut tanpa menunggu A selesai.

B-2: minta baris yang ditahan A

UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;

Jika pendeteksian deadlock InnoDB aktif, satu transaksi dapat menerima Deadlock found, biasanya kesalahan 1213. Koneksi yang dipilih sebagai korban tidak tetap. Operasi lainnya dapat berjalan setelah kunci dilepaskan. Jalankan ROLLBACK; secara terpisah pada kedua jendela agar percobaan tidak meninggalkan penambahan saldo permanen.

Aplikasi perlu membedakan kasus ini dari batas waktu tunggu dan menerapkan pengulangan seluruh transaksi secara terbatas dengan perlindungan terhadap operasi ganda. Mengulang hanya satu perintah atau mencoba kembali tanpa batas tidak mendefinisikan pemulihan yang benar. Referensi kesalahan 1213 MariaDB menjelaskan deadlock dan pengulangan transaksi.

Pulihkan sesi dan gunakan urutan penguncian yang konsisten

Mengunci beberapa baris berdasarkan urutan kunci yang konsisten serta menjaga transaksi singkat mengurangi peluang lingkaran menunggu. Kondisi yang terlalu luas dan indeks yang tidak memadai dapat memperbesar cakupan kunci. Periksa kueri, log, serta urutan transaksi; bedakan deadlock dari kegagalan koneksi umum.

ROLLBACK;
SET SESSION tx_isolation=@saved_isolation;
SET SESSION innodb_lock_wait_timeout=@saved_wait;
SELECT @@SESSION.tx_isolation,@@SESSION.innodb_lock_wait_timeout;

Jalankan blok pemulihan pada A dan B, lalu bandingkan dengan nilai yang disimpan masing-masing pada awal. Variabel pengguna hilang ketika koneksi ditutup. Pada koneksi baru, periksa pengaturan kembali dan jangan menganggap variabel lama masih ada.

Akhiri kedua transaksi sebelum menghapus tabel berikut dan pastikan objek tetap merupakan data latihan yang boleh dibuang. DROP TABLE menghapus struktur beserta baris dan tidak dibalik dengan ROLLBACK biasa.

DROP TABLE shopcourse.accounts_36_lab;