Tahap Pengasingan MySQL dalam CFML: Apa yang Transaksi Anda Nampak

Nilai lalai MySQL bukan read_committed. MySQL 8.4 memulakan setiap sambungan pada REPEATABLE-READ, jadi dua cfquery yang sama di dalam satu cftransaction boleh memulangkan nilai lama walaupun permintaan lain sudah commit. Artikel ini mengukur apa yang dilihat oleh setiap tahap, bila bacaan yang stabil menyakitkan, dan perangkap yang tidak disebut dalam dokumen asas.

Semua kod di bawah dijalankan pada Lucee 7.1.1.17-RC dengan MySQL 8.4.11 dalam rig ujian, bukan pada laman hidup, dan jadual yang digunakan ialah produk(id, nama, harga, stok). Asas cftransaction sendiri, iaitu bentuk tag, commit dan rollback, sudah ditulis dalam cftransaction: transaksi, commit dan rollback, jadi artikel ini tidak mengulanginya.

Apa yang tahap pengasingan tentukan

Tahap pengasingan menjawab satu soalan: apabila dua transaksi menyentuh baris yang sama pada masa yang sama, apa yang dibenarkan dilihat oleh setiap satu, dan siapa yang perlu menunggu. Ia bukan tetapan prestasi, tetapi kontrak tentang kebenaran data, dan setiap tahap membeli jaminan lebih kuat dengan harga kunci yang lebih lama.

Tiga kejadian yang biasa disebut. Bacaan kotor ialah membaca nilai yang ditulis transaksi lain yang kemudiannya dirollback. Bacaan tidak berulang ialah membaca baris yang sama dua kali dan mendapat nilai berbeza kerana orang lain commit di antaranya. Phantom ialah baris baharu muncul dalam julat yang sama antara dua bacaan.

Yang penting untuk kerja harian bukan nama ketiga-tiganya, tetapi sama ada aplikasi anda bergantung pada bacaan yang stabil: laporan dan pengiraan stok biasanya bergantung, senarai yang hanya dipaparkan semula tidak.

Ringkasan ujian dalam artikel ini, semuanya diukur pada rig yang sama:

Nilai lalai MySQL diukur, bukan diandaikan

Sebelum menukar apa-apa, lihat nilai sebenar sambungan yang diberikan oleh pool.

<cfquery name="info" datasource="kedai">
    SELECT @@version AS versi, @@transaction_isolation AS iso
</cfquery>
<cfoutput>#info.versi# / #info.iso#</cfoutput>

8.4.11 / REPEATABLE-READ

Kalau anda menulis kod yang menganggap read_committed kerana itu nilai lalai PostgreSQL atau H2, anda menulis untuk pangkalan data yang berbeza daripada yang berjalan. Atribut isolation pada cftransaction memang menukar tahap itu: satu blok isolation="serializable" melaporkan SERIALIZABLE di dalam blok dan REPEATABLE-READ selepasnya, pada CONNECTION_ID() yang sama.

Bukti: bacaan kedua dalam transaksi yang sama

Untuk membuktikan perbezaan antara dua tahap, kita perlukan dua sambungan. Menambah datasource kedua yang menunjuk ke pangkalan data yang sama tidak berkesan: dalam ujian, Lucee mendaftarkan cfquery daripada datasource lain ke dalam transaksi yang sama, dan rollback membatalkan kedua-duanya. Jadi sambungan kedua dibina terus melalui pemacu JDBC, menggunakan butiran datasource yang sudah didaftarkan oleh aplikasi.

<cfscript>
ds = getApplicationMetaData().datasources["kedai"];
props = createObject("java", "java.util.Properties").init();
props.setProperty("user", ds.username);
props.setProperty("password", ds.password);
conn = createObject("java", "com.mysql.cj.jdbc.Driver").init().connect(ds.connectionString, props);
conn.setAutoCommit(true);
stmt = conn.createStatement();
stmt.executeUpdate("UPDATE produk SET harga = 12.50 WHERE id = 1");

function uji(iso) {
    var pertama = "";
    var kedua = "";
    transaction isolation=iso {
        q1 = queryExecute("SELECT harga FROM produk WHERE id = 1", {}, {datasource="kedai"});
        pertama = q1.harga;
        stmt.executeUpdate("UPDATE produk SET harga = 99.00 WHERE id = 1");
        q2 = queryExecute("SELECT harga FROM produk WHERE id = 1", {}, {datasource="kedai"});
        kedua = q2.harga;
    }
    stmt.executeUpdate("UPDATE produk SET harga = 12.50 WHERE id = 1");
    return iso & ": pertama=" & pertama & " kedua=" & kedua;
}

writeOutput(uji("repeatable_read") & "<br>");
writeOutput(uji("read_committed"));
stmt.close();
conn.close();
</cfscript>

repeatable_read: pertama=12.5 kedua=12.5
read_committed: pertama=12.5 kedua=99

Pada repeatable_read, bacaan kedua masih memulangkan 12.5 walaupun sambungan lain sudah commit 99.00. Itu bukan cache dan bukan pepijat: MySQL membekukan snapshot pada bacaan pertama, dan seluruh transaksi melihat snapshot itu. Pada read_committed, setiap pernyataan mengambil snapshot baharu, jadi bacaan kedua nampak 99. Semua contoh di sini menggunakan queryExecute; bentuk tag cfquery berkelakuan sama, dan perbezaan antara keduanya ada dalam queryExecute lawan cfquery.

Kesan praktikalnya besar untuk corak baca, ubah, tulis. Kalau kod anda membaca stok, mengira nilai baharu dalam CFML, kemudian menulis semula, snapshot tidak menghalang permintaan lain daripada menulis nilai yang sama; ia hanya menghalang anda daripada nampak perubahan itu.

Bacaan kotor hanya berlaku bila anda memintanya

read_uncommitted ialah satu-satunya tahap yang membenarkan bacaan kotor, dan ia berbahaya justeru kerana nilainya kelihatan sah. Blok ini menulis melalui sambungan kedua tanpa commit, membacanya dari dalam transaksi, kemudian membatalkan tulisan itu.

<cfscript>
ds = getApplicationMetaData().datasources["kedai"];
props = createObject("java", "java.util.Properties").init();
props.setProperty("user", ds.username);
props.setProperty("password", ds.password);
conn = createObject("java", "com.mysql.cj.jdbc.Driver").init().connect(ds.connectionString, props);
stmt = conn.createStatement();

conn.setAutoCommit(false);
stmt.executeUpdate("UPDATE produk SET harga = 55.00 WHERE id = 3");
transaction isolation="read_uncommitted" {
    q = queryExecute("SELECT harga FROM produk WHERE id = 3", {}, {datasource="kedai"});
    writeOutput("dalam read_uncommitted: " & q.harga);
}
conn.rollback();
conn.setAutoCommit(true);
q2 = queryExecute("SELECT harga FROM produk WHERE id = 3", {}, {datasource="kedai"});
writeOutput(" | selepas rollback: " & q2.harga);
stmt.close();
conn.close();
</cfscript>

dalam read_uncommitted: 55 | selepas rollback: 11.9

Nilai 55 itu tidak pernah wujud sebagai data yang sah. Kalau transaksi anda mengira sesuatu daripada nilai itu dan kemudian commit, anda menyimpan keputusan berdasarkan data yang dibatalkan. Dalam amalan, read_uncommitted hampir tidak pernah menjadi jawapan yang betul.

Serializable menyekat penulis lain

serializable ialah tahap paling kuat, dan harganya mudah dilihat: bacaan biasa pun mengunci. Di sini satu SELECT tanpa FOR UPDATE di dalam transaksi serializable menyebabkan UPDATE daripada sambungan kedua menunggu sehingga had masa kunci tamat.

<cfscript>
ds = getApplicationMetaData().datasources["kedai"];
props = createObject("java", "java.util.Properties").init();
props.setProperty("user", ds.username);
props.setProperty("password", ds.password);
conn = createObject("java", "com.mysql.cj.jdbc.Driver").init().connect(ds.connectionString, props);
conn.setAutoCommit(true);
stmt = conn.createStatement();
stmt.execute("SET SESSION innodb_lock_wait_timeout = 2");
out = "";

transaction isolation="serializable" {
    q = queryExecute("SELECT stok FROM produk WHERE id = 2", {}, {datasource="kedai"});
    out &= "bacaan dalam serializable: " & q.stok;
    try {
        stmt.executeUpdate("UPDATE produk SET stok = 99 WHERE id = 2");
        out &= " | update kedua berjaya";
    } catch (any e) {
        out &= " | update kedua gagal: " & e.message;
    }
    transactionRollback();
}
stmt.close();
conn.close();
writeOutput(out);
</cfscript>

bacaan dalam serializable: 16 | update kedua gagal: Lock wait timeout exceeded; try restarting transaction

Had masa dua saat itu ditetapkan sendiri supaya ujian tidak menunggu lima puluh saat. Dalam pengeluaran, had masa lalai itulah yang akan ditunggu oleh pelawat anda, jadi serializable hanya sesuai untuk blok kecil yang memang perlu bersiri.

Apabila anda hanya perlu melindungi satu baris, FOR UPDATE lebih murah daripada menaikkan tahap untuk semua bacaan. Untuk pengurangan stok, UPDATE produk SET stok = stok - 1 WHERE id = ? AND stok >= 1 selalunya lebih baik daripada kedua-duanya, kerana ia satu pernyataan tanpa bacaan dahulu.

Perangkap: ralat yang ditangkap di dalam transaksi

Ini kesilapan yang senang berlaku semasa menulis kod pertahanan. Kalau try/catch berada di dalam blok transaksi, ralat itu ditangkap sebelum ia keluar, jadi transaksi tidak dianggap gagal dan perubahan sebelum serta selepas catch tetap dicommit.

<cfscript>
queryExecute("DELETE FROM produk WHERE id IN (9970, 9971)", {}, {datasource="kedai"});
out = "";
transaction {
    queryExecute("INSERT INTO produk (id, nama, harga, stok) VALUES (9970, 'satu', 1, 1)", {}, {datasource="kedai"});
    try {
        throw(message="ralat dalaman");
    } catch (any e) {
        out &= "ralat ditangkap di dalam transaksi;";
    }
    queryExecute("INSERT INTO produk (id, nama, harga, stok) VALUES (9971, 'dua', 1, 1)", {}, {datasource="kedai"});
}
q = queryExecute("SELECT id FROM produk WHERE id IN (9970, 9971)", {}, {datasource="kedai"});
out &= " baris selepas commit=" & q.recordCount;
queryExecute("DELETE FROM produk WHERE id IN (9970, 9971)", {}, {datasource="kedai"});
writeOutput(out);
</cfscript>

ralat ditangkap di dalam transaksi; baris selepas commit=2

Dua baris kekal, termasuk baris yang ditulis sebelum ralat. Corak yang betul ialah menangkap di luar blok transaksi, atau memanggil transactionRollback() secara eksplisit di dalam catch. cftransaction membatalkan kerja apabila ralat keluar dari blok, bukan apabila ralat berlaku di dalamnya.

Memilih tahap untuk kerja biasa

Tahap lebih kuat memegang kunci lebih lama, dan kunci lebih lama menambah sambungan yang menunggu di dalam pool; saiz pool yang kecil menjadikan kesannya lebih cepat kelihatan, seperti dalam tala connection pool Lucee. Prosedur tersimpan yang melakukan COMMIT sendiri pula mengeluarkan kawalan daripada cftransaction, seperti dalam prosedur tersimpan MySQL.

Senarai semak sebelum menukar tahap

1. Ukur nilai lalai sambungan anda, jangan warisi andaian daripada pangkalan data lain.
2. Tentukan sama ada unit kerja itu memerlukan bacaan stabil, atau hanya bacaan terkini.
3. Untuk baca, ubah, tulis, pilih antara satu UPDATE bersyarat dan FOR UPDATE, bukan tahap pengasingan lebih tinggi.
4. Tangkap ralat di luar blok transaksi, atau rollback secara eksplisit.
5. Tetapkan had masa kunci yang munasabah, dan uji mesej Lock wait timeout exceeded itu sendiri.
6. Jangan campurkan DDL dengan DML dalam satu transaksi, kerana MySQL melakukan commit tersirat sebelum DDL.

Soalan Lazim

Apakah tahap pengasingan lalai MySQL? REPEATABLE-READ. Diukur pada MySQL 8.4.11 dalam rig: SELECT @@transaction_isolation memulangkan REPEATABLE-READ untuk sambungan baharu, dan nilai itu kembali selepas blok cftransaction dengan isolation tamat.

Adakah cftransaction isolation benar-benar berkesan dalam Lucee? Ya. Satu blok dengan isolation="serializable" melaporkan SERIALIZABLE di dalam blok dan menyekat UPDATE daripada sambungan lain, manakala read_committed membenarkan bacaan kedua melihat nilai yang baru dicommit.

Kenapa bacaan kedua saya masih nilai lama walaupun orang lain sudah commit? Kerana transaksi itu berjalan pada REPEATABLE-READ, dan snapshotnya diambil pada bacaan pertama. Tukar kepada read_committed kalau anda perlukan data terbaharu, atau gunakan FOR UPDATE kalau masalahnya ialah tulisan yang bertindih.

Adakah saya perlu serializable untuk mengelakkan stok lebih terjual? Tidak semestinya. UPDATE ... SET stok = stok - 1 WHERE id = ? AND stok >= 1 sudah cukup untuk satu baris, dan FOR UPDATE cukup untuk beberapa baris. serializable menyekat bacaan biasa juga, jadi ia hanya sesuai untuk blok kecil yang perlu bersiri.

Bolehkah satu cftransaction meliputi dua datasource? Boleh, dan rollback membatalkan kedua-duanya dalam ujian ini. Tetapi dua sambungan MySQL tidak commit secara atomik tanpa XA, jadi jangan anggap ia jaminan merentas dua pangkalan data.

Sumber

• Dokumentasi tag cftransaction, Lucee
• Fungsi getTransactionIsolation, Lucee
• Fungsi isWithinTransaction, Lucee
• Fungsi transactionSetSavepoint, Lucee
• Tahap pengasingan transaksi InnoDB, manual MySQL 8.4
• Commit tersirat dalam MySQL