Hastinapura

Oracle Database Resources

Interaksi PL/SQL dengan Oracle

6.8.5. Bagaimana Oracle Melakukan Implicit Rollbacks

Sebelum mengeksekusi perintah INSERT, UPDATE, atau DELETE, Oracle menandai savepoint implisit (tidak tersedia bagi kita). Jika perintah tersebut gagal, Oracle melakukan roll back ke savepoint tersebut. Secara normal, hanya perintah SQL yang gagal saja yang di-rollback, bukan keseluruhan transaksi. Namun, jika suatu perintah menyebabkan munculnya unhandled exception, host environment akan menentukan apa yang akan di-rollback.

6.8.6. Mengakhiri Transaksi

Praktek pemrograman yang baik adalah dengan melakukan commit atau rollback terhadap setiap transaksi secara eksplisit. Apakah kita melakukan commit atau rollback di dalam program  PL/SQL atau dalam host enviroment bergantung kepada aliran logika aplikasi kita. Jika kita mengabaikan commit atau rollback sebuah transaksi secara eksplisit, host environment menentukan keadaan akhirnya.

Sebagai contoh, dalam lingkungan SQL*Plus, jika blok PL/SQL tidak menyertakan perintah COMMIT atau ROLLBACK, keadaan akhir dari transaksi kita bergantung pada apa yang kita kerjakan setelah menjalankan suatu blok. Jika kita mengeksekusi perintah data definition, data control, atau COMMIT atau jika kita mengetikkan perintah EXIT, DISCONNECT, atau QUIT, Oracle meng-commit transaksi. Jika kita mengeksekusi perintah ROLLBACK atau menggagalkan session SQL*Plus, Oracle me-rollback transaksi.

Dalam lingkungan Oracle Precompiler, jika program kita tidak berakhir secara normal, Oracle me-rollback transaksi. Program berakhir secara normal ketika ia secara eksplisit melakukan commit atau rollback pekerjaan dan keluar dari Oracle dengan menggunakan parameter RELEASE, seperti berikut:

EXEC SQL COMMIT WORK RELEASE;

6.8.7. Mengatur Properti Transaksi dengan SET TRANSACTION

Kita menggunakan perintah SET TRANSACTION untuk memulai transaksi read-only atau read-write, menetapkan level isolasi, atau menempatkan transaksi terkini ke rollback segment tertentu. Transaksi-transaksi read-only berguna untuk menjalankan banyak query terhadap satu atau lebih table-table ketika pengguna-pengguna lain meng-update table-table yang sama.

Selama transaksi read-only, seluruh query mengacu kepada snapshot yang sama dari database, menyediakan view multi-table, multi-query, dan read-consistent. Pengguna-pengguna lain dapat melanjutkan query atau melakukan update data seperti biasa.

Commit atau rollback mengakhiri transaksi. Dalam contoh di bawah ini, sebagai manajer toko, kita menggunakan transaksi read-only untuk mendapatkan gambaran penjualan untuk suatu hari, minggu sebelumnya, dan bulan sebelumnya. Gambaran-gambaran tersebut tidak terpengaruh oleh pengguna-pengguna lain yang sedang melakukan update database selama transaksi.

DECLARE
  daily_sales REAL;
  weekly_sales REAL;
  monthly_sales REAL;
BEGIN
  ...
  COMMIT; -- mengakhiri transaksi sebelumnya
  SET TRANSACTION READ ONLY NAME 'Calculate sales figures';
  SELECT SUM(amt)
  INTO daily_sales
  FROM sales
  WHERE dte = SYSDATE;
  SELECT SUM(amt)
  INTO weekly_sales
  FROM sales
  WHERE dte > SYSDATE - 7;
  SELECT SUM(amt)
  INTO monthly_sales
  FROM sales
  WHERE dte > SYSDATE - 30;
  COMMIT; -- mengakhiri transaksi read-only
  ...
END;

Perintah SET TRANSACTION harus menjadi perintah SQL pertama dalam transaksi read-only dan hanya dapat muncul sekali dalam transaksi. Jika kita mengatur transaksi untuk menjadi READ ONLY, query-query selanjutnya hanya melihat perubahan-perubahan yang di-commit sebelum transaksi dimulai. Penggunaan READ ONLY tidak mempengaruhi pengguna-pengguna atau transaksi-transaksi lain.

6.8.7.1. Batasan-batasan pada SET TRANSACTION

Hanya perintah-perintah SELECT INTO, OPEN, FETCH, CLOSE, LOCK TABLE, COMMIT, and ROLLBACK yang diperbolehkan di dalam transaksi read-only. Juga, query-query tidak dapat menjadi FOR UPDATE.

6.8.8. Mengesampingkan Default Locking

Secara default, Oracle mengunci struktur-struktur data untuk kita secara otomatis. Namun, kita dapat meminta penguncian-penguncian tertentu terhadap data atau table-table ketika menguntungkan bagi kita untuk mengesampingkan penguncian default. Penguncian eksplisit mengijinkan kita untuk berbagi atau meniadakan akses ke table untuk satu waktu tertentu saat transaksi.

Dengan perintah LOCK TABLE, kita dapat mengunci seluruh table-table secara eksplisit. Dengan perintah SELECT FOR UPDATE, kita dapat secara eksplisit mengunci baris-baris data tertentu pada table untuk meyakinkan bahwa mereka tidak berubah sebelum pengubahan atau penghapusan dieksekusi. Namun, Oracle secara otomatis menghasilkan penguncian row-level pada saat pengubahan atau penghapusan. Jadi, gunakan klausa FOR UPDATE hanya jika kita intin mengunci baris-baris data sebelum pengubahan atau penghapusan.

6.8.8.1. Menggunakan FOR UPDATE

Ketika kita mendeklarasikan cursor yang akan direferensi dalam klausa CURRENT OF dari perintah UPDATE atau DELETE, kita harus menggunakan klausa FOR UPDATE untuk memperoleh penguncian-penguncian baris data secara eksklusif. Contohnya adalah sebagai berikut:

DECLARE
  CURSOR c1 IS
    SELECT empno, sal
    FROM emp
    WHERE job = 'SALESMAN' AND comm > sal
    FOR UPDATE NOWAIT;

Perintah SELECT …FOR UPDATE mengidentifikasi baris-baris data yang akan diubah atau dihapus, kemudian mengunci baris data dalam result set. Hal ini berguna ketika kita ingin mendasarkan pengubahan pada nilai-nilai yang telah ada pada baris data. Dalam kasus tersebut, kita harus meyakinkan bahwa baris data tidak diubah oleh pengguna lain sebelum proses update.

Keyword opsional NOWAIT memberitahukan Oracle untuk tidak menunggu jika baris-baris data yang diminta telah dikunci oleh pengguna lain. Kontrol segera dikembalikan ke program kita sehingga ia dapat melakukan perkerjaan lain sebelum mencoba lagi untuk mendapatkan penguncian. Jika kita mengabaikan keyword NOWAIT, Oracle menunggu sampai baris-baris data tersebut tersedia.

Seluruh baris-baris data dikunci ketika kita membuka cursor, tidak ketika mereka di-fetch. Baris-baris data dibuka pengunciannya ketika kita melakukan commit atau rollback terhadap transaksi. Jadi, kita tidak dapat melakukan fetch dari cursor FOR UPDATE setelah commit.

Ketika melakukan query terhadap banyak table, kita dapat menggunakan klausa FOR UPDATE untuk membatasi penguncian baris data terhadap table-table tertentu. Baris-baris data dalam table dikunci hanya jika klausa FOR UPDATE OF mengacu kepada kolom dalam table tersebut. Sebagai contoh, query berikut mengunci baris-baris data pada table emp namun tidak pada table dept:

DECLARE
  CURSOR c1 IS
    SELECT ename, dname
    FROM emp, dept
    WHERE emp.deptno = dept.deptno AND job = 'MANAGER'
    FOR UPDATE OF sal;

Seperti ditunjukkan oleh contoh selanjutnya, kita menggunakan klausa CURRENT OF pada perintah UPDATE atau DELETE untuk mengacu kepada baris data terakhir yang di-fetch dari sebuah cursor:

DECLARE
  CURSOR c1 IS
    SELECT empno, job, sal
    FROM emp
    FOR UPDATE;
  ...
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO ...
    ...
    UPDATE emp
    SET sal = new_sal
    WHERE CURRENT OF c1;
  END LOOP;

6.8.8.2. Menggunakan LOCK TABLE

Kita menggunakan perintah LOCK TABLE untuk mengunci seluruh database tables dalam mode penguncian tertentu sehingga kita dapat membagi atau meniadakan akses kepada mereka. Sebagai contoh, perintah berikut mengunci table emp dalam mode row share. Penguncian-penguncian row share mengijinkan akses secara bersamaan ke table; penguncian-penguncian ini mencegah pengguna-pengguna lain mengunci seluruh table untuk penggunaan eksklusif. Penguncian table dibebaskan ketika transaksi kita memunculkan commit atau rollback.

LOCK TABLE emp IN ROW SHARE MODE NOWAIT;

Mode penguncian menentukan penguncian lain apa yang dapat ditempatkan pada table tersebut. Sebagai contoh, banyak pengguna dapat meminta penguncian-penguncian row share pada table pada waktu yang sama, namun hanya satu pengguna pada satu waktu yang dapat meminta penguncian eksklusif. Disaat satu pengguna memiliki penguncian eksklusif pada table, tidak ada pengguna lain yang dapat menambahkan, menghapus, atau mengubah baris-baris data pada table tersebut.

Penguncian table tidak pernah tetap memegang pengguna-pengguna lain dari melakukan query terhadap table, dan query tidak pernah meminta penguncian table. Hanya jika dua transaksi berbeda berusaha mengubah baris data yang sama, satu transaksi menunggu transaksi lain selesai.

6.8.8.3. Melakukan Fetch Melewati Commits

Klausa FOR UPDATE meminta penguncian-penguncian eksklusif terhadap baris data. Seluruh baris-baris data dikunci ketika kita membuka cursor, dan mereka dibuka pengunciannya, ketika kita melakukan commit terhadap transaksi. Jadi, kita tidak dapat melakukan fetch dari cursor FOR UPDATE setelah commit. Jika kita melakukannya, PL/SQL memunculkan exception. Dalam contoh berikut ini, cursor FOR loop gagal setelah penambahan data kesepuluh:

DECLARE
  CURSOR c1 IS
    SELECT ename
    FROM emp
    FOR UPDATE OF sal;
  ctr NUMBER := 0;
BEGIN
    FOR emp_rec IN c1 LOOP -- FETCH secara implisit
    ...
    ctr := ctr + 1;
    INSERT INTO temp VALUES (ctr, 'still going');
    IF ctr>= 10 THEN
      COMMIT; -- membebaskan penguncian-penguncian
    END IF;
  END LOOP;
END;

Jika kita ingin melakukan fetch melewati commit-commit, jangan menggunakan klausa FOR UPDATE atau CURRENT OF. Namun, gunakan pseudocolumn ROWID untuk menirukan klausa CURRENT OF. Secara sederhana kita menampilkan rowid dari setiap baris data ke dalam variable UROWID. Lalu, gunakan rowid tersebut untuk mengidentifikasi baris data terkini selama pengubahan-pengubahan dan penghapusan-penghapusan berikutnya. Contohnya sebagai berikut:

DECLARE
  CURSOR c1 IS
    SELECT ename, job, rowid
    FROM emp;
  my_ename emp.ename%TYPE;
  my_job emp.job%TYPE;
  my_rowid UROWID;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO my_ename, my_job, my_rowid;
    EXIT WHEN c1%NOTFOUND;
    UPDATE emp
    SET sal = sal * 1.05
    WHERE rowid = my_rowid;
    -- ini menyerupai WHERE CURRENT OF c1
    COMMIT;
  END LOOP;
  CLOSE c1;
END;

Kita perlu berhati-hati. Dalam contoh terakhir, baris-baris data yang dihasilkan tidak terkunci karena tidak terdapat klausa FOR UPDATE yang digunakan. Jadi, pengguna-pengguna lain dapat tanpa sengaja menimpa pengubahan-pengubahan yang kita lakukan.

Cursor juga harus memiliki view read-consistent dari data, sehingga rollback segments yang digunakan dalam penghapusan tidak dibebaskan sampai cursor ditutup. Hal ini dapat memperlambat pemrosesan ketika banyak baris-baris data yang diubah.

Contoh selanjutnya menunjukkan bahwa kita dapat menggunakan atribut %ROWTYPE dengan cursor-cursor yang mereferensi pseudocolumn ROWID:

DECLARE
  CURSOR c1 IS
    SELECT ename, sal, rowid
    FROM emp;
  emp_rec c1%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO emp_rec;
    EXIT WHEN c1%NOTFOUND;
    ...
    IF ... THEN
       DELETE FROM emp
       WHERE rowid = emp_rec.rowid;
    END IF;
  END LOOP;
  CLOSE c1;
END;

 

 

Buku ini membahas berbagai macam tip dan trik yang sangat berguna bagi Anda pembaca pengguna database Oracle. Pemula, mahasiswa, programmer, maupun database administrator yang mengharapkan solusi cepat dalam menangani permasalahan-permasalahan dalam penggunaan database Oracle dapat membaca buku ini. Anda tidak harus membaca buku ini secara urut karena buku ini di susun berdasarkan topik permasalahan, dan Anda dapat membaca topik-topik yang sesuai dengan permasalahan yang Anda hadapi. Dapat di beli di toko buku Gramedia, Gunung Agung, Uranus, Karisma dsb. Harga Rp. 24.800,- (157 halaman)

 

• Native Dynamic SQL
• PL/SQL Object Types
• PL/SQL Packages
• PL/SQL Subprograms
• Menangani PL/SQL Errors

 

Copyright © 2012 Hastinapura. Beranda | Berbagi Cerita | Hastinapura | Kelasik | Langgam | Nunung | Obsesi | Propolis | Serasi | Spektakuler

Switch to our mobile site