Memindahkan 300 rupee dari Asha ke Ben memerlukan dua pembaruan: kurangi dari satu akun dan tambahkan ke akun lainnya. Jika sistem di antara mereka gagal, uang akan hilang. Transaksi SQL ada untuk mewujudkan hal tersebut, dengan memperlakukan kedua langkah sebagai satu kesatuan.

Dalam panduan ini kita membahas penerapan, pengembalian, dan kegagalan pada tabel akun kecil. Kami menjalankan contoh tersebut di SQLite, dan saldo yang ditampilkan berasal dari proses tersebut. Perintah dasarnya serupa di seluruh database, tetapi perilaku penguncian dan isolasi sangat bervariasi, jadi kami menunjukkan tempat untuk memeriksa dokumentasi database Anda.

Apa itu transaksi?

Transaksi adalah urutan satu atau lebih pernyataan SQL yang diperlakukan database sebagai operasi logis tunggal. Ini meja kami:

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  owner TEXT,
  balance INT CHECK (balance >= 0)
);

INSERT INTO accounts VALUES (1, 'Asha', 1000), (2, 'Ben', 200);

Batasan CHECK adalah aturan bahwa saldo tidak akan pernah berada di bawah nol, yang akan kita gunakan nanti. Perhatikan bahwa sintaks untuk memulai transaksi bervariasi: SQLite dan PostgreSQL menerima BEGIN, MySQL juga memiliki START TRANSACTION, dan SQL Server menggunakan BEGIN TRANSACTION.

Di luar transaksi eksplisit, sebagian besar database menggunakan komit otomatis, di mana setiap pernyataan disimpan segera setelah selesai. Oracle adalah pengecualian penting, karena sesi di sana tetap ada dalam transaksi sampai Anda melakukan commit.

COMMIT: menjadikannya permanen

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
COMMIT;

-- Asha | 700
-- Ben  | 500

Sampai COMMIT dijalankan, perubahannya bersifat sementara. Setelah berhasil kembali, database menjamin bahwa mereka disimpan. Total di kedua akun tetap di angka 1.200, yang merupakan inti dari latihan ini.

ROLLBACK: batalkan semuanya

ROLLBACK membatalkan semuanya sejak transaksi dimulai:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SELECT owner, balance FROM accounts;
-- Asha | 600   (visible inside this transaction)
-- Ben  | 500

ROLLBACK;
SELECT owner, balance FROM accounts;
-- Asha | 700   (back to where it was)
-- Ben  | 500

Rollback juga terjadi secara otomatis jika koneksi terputus atau server crash sebelum Anda melakukan commit, sehingga transaksi setengah jadi tidak dapat bertahan.

Ketika sebuah pernyataan gagal

Ini adalah kasus yang lebih realistis. Asha mencoba mengirim 5000, yang tidak dapat ditampung oleh akun tersebut. Kredit ke Ben berjalan terlebih dahulu, lalu debitnya melanggar aturan CHECK:

BEGIN;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
-- error: CHECK constraint failed: balance >= 0

ROLLBACK;

-- Asha | 700
-- Ben  | 500

Tanpa transaksi tersebut, Ben akan menyimpan 5.000 itu dan pembukuannya akan salah. Perhatikan satu detail penting: pernyataan yang gagal tidak selalu membatalkan transaksi dengan sendirinya. Di SQLite dan MySQL, misalnya, pernyataan sebelumnya mungkin masih tertunda, jadi kode Anda harus melakukan rollback. PostgreSQL malah menandai seluruh transaksi sebagai dibatalkan dan menolak pernyataan lebih lanjut sampai Anda melakukan roll back.

Dalam kode aplikasi, selalu pasangkan transaksi dengan penanganan kesalahan. Inilah ide dalam Python, dan panduan kami untuk penanganan pengecualian Python menjelaskan sintaksisnya:

try:
    conn.execute("BEGIN")
    conn.execute(debit_sql, values)
    conn.execute(credit_sql, values)
    conn.execute("COMMIT")
except Exception:
    conn.execute("ROLLBACK")
    raise

titik simpanan

Savepoint menandai posisi di dalam transaksi, sehingga Anda hanya dapat membatalkan sebagian pekerjaan:

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT bonus;
UPDATE accounts SET balance = balance + 999 WHERE id = 2;
ROLLBACK TO bonus;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

-- Asha | 650
-- Ben  | 550

Bonus 999 dibatalkan, sedangkan sisa transaksi dilanjutkan dan dilakukan. Ini berguna untuk langkah opsional dalam proses yang panjang.

Sifat ACID dijelaskan

Konsistensi sering disalahpahami. Basis data hanya menerapkan aturan yang Anda deklarasikan, seperti batasan dan kunci asing. Ini tidak akan menghentikan kode Anda untuk mentransfer jumlah yang salah. Daya tahan biasanya dicapai dengan log write-ahead, di mana perubahan dicatat dengan aman pada disk sebelum penerapan dikonfirmasi.

Apa yang terjadi dengan transaksi bersamaan?

Kami membuka dua koneksi ke satu file SQLite. Yang pertama memulai transaksi dan memperbarui baris dari 1 menjadi 2, tanpa melakukan apa pun. Inilah yang dilihat oleh koneksi kedua:

Connection B reads:   1                     -- no uncommitted data
Connection B writes:  database is locked    -- it has to wait
(A commits)
Connection B reads:   2                     -- now it sees the change

SQLite hanya mengizinkan satu penulis pada satu waktu, yang lebih sederhana daripada database server, di mana banyak transaksi dapat menulis baris berbeda secara bersamaan. Namun prinsipnya sama: sesi lain tidak akan melihat perubahan yang tidak dikomit dalam pengaturan normal, dan penulisan yang bertentangan harus menunggu. Dalam pengujian kami, kami mengatur koneksi kedua untuk segera menyerah daripada menunggu, sehingga kesalahan langsung muncul.

Baca anomali

Ketika transaksi tumpang tindih, masalah tertentu bisa terjadi. Standar SQL mendefinisikan ini dalam kaitannya dengan apa yang mungkin diamati dalam suatu transaksi:

Masalah lainnya adalah pembaruan yang hilang. Dua sesi membaca saldo 100, masing-masing menambahkan 10 dan masing-masing menulis 110. Satu pembaruan secara efektif hilang, padahal jawabannya seharusnya 120.

Tingkat isolasi SQL

Tingkat isolasi memungkinkan Anda menukar ketelitian dengan konkurensi. Inilah yang diizinkan oleh standar SQL di setiap level:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Sintaks dan perilaku yang tepat bergantung pada database Anda. Standarnya juga berbeda: PostgreSQL dan SQL Server menggunakan komitmen baca, sedangkan InnoDB MySQL menggunakan pembacaan berulang. Beberapa database memberikan jaminan yang lebih kuat dari yang disyaratkan standar, atau memperlakukan satu level sebagai level lainnya. Di PostgreSQL, misalnya, read uncommit berperilaku seperti read commit. Selalu baca dokumentasi database Anda sebelum mengandalkan perilaku tertentu.

Kebuntuan

Kebuntuan terjadi ketika dua transaksi masing-masing memiliki kunci yang dibutuhkan transaksi lainnya. Transaksi A mengunci baris 1 dan menginginkan baris 2, sedangkan transaksi B mengunci baris 2 dan menginginkan baris 1. Tidak ada yang dapat melanjutkan.

Database seperti PostgreSQL, MySQL dan SQL Server mendeteksi hal ini dan membatalkan satu transaksi dengan kesalahan. Itu adalah perilaku normal, dan aplikasi Anda harus siap menghadapinya. Untuk mengurangi kebuntuan, selalu perbarui baris dalam urutan yang konsisten, seperti ID akun terendah terlebih dahulu, persingkat transaksi, dan coba lagi ketika salah satu dipilih sebagai korban.

Praktik terbaik

• Jaga transaksi tetap singkat. Transaksi panjang akan mengunci dan memblokir pengguna lain.
• Jangan pernah menunggu pengguna atau API yang lambat di dalam transaksi. Lakukan pekerjaan itu sebelum Anda memulai, atau setelahnya.
• Selalu tangani kegagalan. Setiap jalur harus diakhiri dengan penerapan atau rollback.
• Tambahkan batasan. Batasan ini memberikan aturan nyata pada database untuk ditegakkan. desain tabel yang bagus juga membantu.
• Gunakan indeks. Pencarian yang lebih cepat berarti penguncian akan ditahan dalam waktu yang lebih singkat.
• Hati-hati saat mencoba ulang. Coba lagi saat kebuntuan, namun pastikan bahwa operasi tidak dapat dilakukan dua kali.
• Menguji konkurensi dengan sengaja. Banyak dari bug ini hanya muncul saat dimuat, jadi tes integrasi dasar saja mungkin tidak dapat mengungkapnya.

Penutup

Transaksi mengubah beberapa langkah berisiko menjadi satu operasi yang aman, dan ACID menjelaskan jaminan yang menjadikannya dapat dipercaya. Setiap kali suatu tindakan bisnis menyentuh lebih dari satu baris atau tabel, tanyakan pada diri Anda apa yang akan terjadi jika tindakan tersebut berhenti di tengah jalan. Jika jawabannya membuat Anda khawatir, bungkuslah dalam sebuah transaksi. Untuk berlatih, buka dua sesi di database lokal dan coba buat sendiri anomalinya.

Pertanyaan yang sering diajukan

Teruslah belajar