Komit dan kembalikan dalam contoh prosedur tersimpan mysql

Memahami Pemrosesan Transaksi dalam Prosedur Tersimpan

Secara default, setiap pernyataan SQL di dalam SP dijalankan secara berurutan dan independen dari pernyataan sebelumnya atau berikutnya. Jika SP melontarkan pengecualian yang tidak tertangani selama pelaksanaannya, pernyataan SQL apa pun yang dieksekusi sebelum kesalahan akan diproses, dan pernyataan SQL apa pun yang mengikuti kesalahan tidak akan diproses

Perilaku ini juga berlaku untuk pernyataan transaksional dengan cara berikut. Jika SP melontarkan pengecualian yang tidak tertangani setelah memulai transaksi dan sebelum melakukannya, transaksi akan tetap terbuka. Transaksi yang tidak terikat tidak secara otomatis dibatalkan jika SP gagal dijalankan sepenuhnya

Pertimbangkan contoh berikut, yang memulai transaksi, memasukkan bilangan bulat ke dalam tabel, dan mencoba melakukan transaksi

DELIMITER //
CREATE PROCEDURE insert_transaction_error() AS
  BEGIN
    START TRANSACTION;
    INSERT INTO t VALUES (1); -- Insert is successful
    CALL my_func(); -- Throws unhandled exception
    COMMIT; -- Commit is unreachable, and the transaction remains open
  END //
DELIMITER ;

Dalam contoh di atas, integer berhasil dimasukkan, tetapi pengecualian yang tidak tertangani mencegah transaksi dilakukan. Pada titik ini, transaksi tetap terbuka. Untuk memastikan bahwa transaksi tidak tetap terbuka tanpa henti, tambahkan rollback untuk transaksi tersebut di bagian penanganan pengecualian dari prosedur tersimpan

Transaksi terbuka sering kali menghasilkan perilaku yang tidak terduga dan dapat menimbulkan konsekuensi yang serius. Misalnya, jika ada transaksi terbuka saat SP dipanggil menggunakan CALL() atau ECHO(), pernyataan SQL apa pun di dalam SP akan dieksekusi sebagai bagian dari transaksi tanpa komitmen sebelumnya. Perilaku ini akan berlanjut hingga pernyataan dieksekusi yang mengakhiri transaksi, seperti COMMIT, atau ROLLBACK

Variabel Sesi transcount

Untuk mencegah perilaku tak terduga dan untuk menentukan apakah transaksi sedang dibuka, Anda dapat menggunakan variabel sesi trancount. Variabel ini bernilai 1_ jika ada transaksi terbuka, dan bernilai 0 jika tidak ada transaksi terbuka. Perhatikan bahwa transaksi bersarang tidak didukung di SingleStoreDB Cloud , jadi 1 dan 0 adalah satu-satunya nilai yang mungkin dari trancount.

Contoh berikut menunjukkan cara menggunakan trancount di SP

DELIMITER //
CREATE PROCEDURE check_trancount() AS
  DECLARE
    trancount_query QUERY(i INT) = SELECT @@trancount;
  BEGIN
    IF SCALAR(trancount_query) = 0 THEN
      START TRANSACTION;
    END IF;
  END //
DELIMITER ;

Pada contoh di atas, variabel sesi trancount dibaca, dan jika transaksi saat ini tidak terbuka, transaksi baru akan dimulai

Di blog, kami mempelajari bagaimana transaksi adalah cara yang luar biasa untuk melindungi dari kehilangan data dan ketidakkonsistenan dengan menjamin bahwa semua operasi yang dilakukan dengan transaksi berhasil atau gagal secara bersamaan. Dalam tindak lanjut hari ini, kita akan mempelajari cara menggunakan transaksi dalam prosedur tersimpan untuk memastikan bahwa semua tabel yang terlibat tetap dalam keadaan konsisten.

Jika Anda pernah membaca artikel blog saya sebelumnya, Anda mungkin tahu bahwa saya sering mengilustrasikan konsep baru menggunakan Database Sampel Sakila. Dan kenapa tidak? . Jika Anda belum mengetahuinya, Basis Data Sampel Sakila berisi data yang berkaitan dengan rantai toko persewaan film fiktif. Selain tabel dan tampilan, Anda juga akan menemukan fungsi pengguna, pemicu, kueri, dan prosedur tersimpan yang mengilustrasikan objek dan tugas database yang paling umum digunakan

Salah satu prosedur tersimpan yang sangat relevan dengan blog ini adalah sp_delete_from_table. Itu menerima tiga parameter input sebagai berikut

• @meja. nama tabel dari mana untuk menghapus baris
• @dimanaklausa. kriteria untuk mengidentifikasi baris mana yang akan dihapus
• @delcnt. berapa banyak baris yang kami harapkan akan dihapus

Prosedur mengembalikan parameter keluaran @actcnt (bigint) yang berisi jumlah baris yang benar-benar dihapus

Berikut definisi lengkap seperti yang ditunjukkan pada

Database relasional memberi kita beberapa pernyataan penting untuk mengontrol transaksi

• Untuk memulai transaksi, gunakan pernyataan BEGIN TRANSACTION. Baik MULAI atau BEGIN WORK adalah alias dari MULAI TRANSAKSI. Anda akan menemukannya di baris 17 dari prosedur sp_delete_from_table
• Untuk melakukan transaksi saat ini dan membuat perubahannya permanen, gunakan pernyataan COMMIT. Itu terjadi pada baris 32 dari prosedur
• Untuk mengembalikan transaksi saat ini dan membatalkan perubahannya, gunakan pernyataan ROLLBACK. Ada beberapa situasi di mana hal itu muncul dalam kode
  1. Jika pernyataan akan menghapus semua baris dalam tabel, sebuah pesan ditampilkan dan transaksi dibatalkan pada baris 26
  2. Jika jumlah baris yang dihapus tidak sesuai dengan jumlah yang Anda harapkan, sekali lagi, sebuah pesan ditampilkan dan transaksi dibatalkan. Itu terjadi di baris 38
• Untuk menonaktifkan atau mengaktifkan mode komitmen otomatis untuk transaksi saat ini, gunakan pernyataan SET komitmen otomatis. Secara default, beberapa database, seperti MySQL, dijalankan dengan mode autocommit yang diaktifkan secara default. Ini berarti bahwa, jika tidak sebaliknya di dalam transaksi, setiap pernyataan bersifat atomik, seolah-olah dikelilingi oleh MULAI TRANSAKSI dan KOMIT. Anda tidak dapat menggunakan ROLLBACK untuk membatalkan efek. Namun, jika terjadi kesalahan selama eksekusi pernyataan, pernyataan tersebut dibatalkan. Karena sebagian besar pekerjaan terjadi dalam transaksi dalam prosedur sp_delete_from_table, pernyataan SET autocommit tidak diperlukan

Karena kita tahu bahwa prosedur sp_delete_from_table akan dibatalkan jika jumlah yang diharapkan tidak cocok dengan jumlah sebenarnya dari baris yang dihapus, kita dapat menguji rollback dengan memastikan bahwa kriteria @whereclause kita akan menghapus setiap baris dalam tabel atau dengan hanya memberikan @ . Mari kita coba yang terakhir

Di Navicat, kita bisa menjalankan stored procedure dari editor melalui tombol Execute. Mengkliknya menyebabkan dialog muncul yang menerima parameter input (params output dapat diabaikan)

Setelah prosedur berakhir, kita dapat melihat pesan keluaran di tab Pesan. Kita dapat melihat bahwa itu telah diputar kembali seperti yang diharapkan

Di blog hari ini, kita belajar cara menggunakan transaksi dalam stored procedure untuk memastikan bahwa semua tabel yang terlibat tetap dalam keadaan konsisten, apa pun hasilnya.

Berminat ?

Rob Gravelle tinggal di Ottawa, Kanada, dan telah menjadi Guru TI selama lebih dari 20 tahun. Saat itu, Rob telah membangun sistem untuk organisasi terkait intelijen seperti Layanan Perbatasan Kanada dan berbagai organisasi komersial. Di waktu luangnya, Rob telah menjadi artis musik yang berprestasi dengan beberapa CD dan penghargaannya

Bagaimana cara menggunakan COMMIT dan ROLLBACK di MySQL?

Bagaimana cara ROLLBACK prosedur di MySQL?

Bagaimana cara ROLLBACK setelah COMMIT di MySQL?

Bisakah kita menggunakan ROLLBACK dalam prosedur tersimpan?