Administrasi Basis Data Transaksi dan Lock Yoannita
Mengenal Transaksi dan Lock • Transaksi dan lock dipakai untuk menjamin konsistensi dan integritas data. • Transaksi adalah sebuah unit kerja logis yang berisi sekumpulan perintah. • Lock mencegah konflik antarpemakai sehingga seorang user tidak dapat membaca atau mengubah data yang sedang diubah oleh user lain. • Transaksi memakai lock untuk mencegah user mengubah data yang sedang dipakai oleh transaksi.
Mengenal Transaksi dan Lock • Setiap database SQL Server mempunyai sebuah log transaksi yang mencatat semua transaksi dan modifikasi database yang dibuat oleh transaksi. • Log tersebut mendukung 3 operasi : – Proses Recovery dari transaksi-transaksi tunggal – Proses Recovery semua transaksi yang belum selesai pada saat SQL Server dimulai – Mengembalikan database ke keadaan semula setelah terjadi kegagalan
• Proses Recovery dari transaksi-transaksi tunggal
– Jika sebuah aplikasi menjalankan perintah ROLLBACK atua SQL Server mendeteksi sebuah kesalahan, record-record dalam log dipakai untuk kembali ke data sebelum transaksi dimulai
• Proses Recovery semua transaksi yang belum selesai pada saat SQL Server dimulai
• Jika SQL Server berhenti tiba-tiba karena sesuatu hal, database mungkin dalam keadaan sedang mengalami perubahan yang belum ditulis ke file data. Jika SQL Server dimulai, proses recovery akan dijalankan pada setiap database. Setiap modifikasi yang dicatat di dalam log dan belum ditulis ke dalam file data akan dikembalikan ke keadaan sebelum transaksi dimulai. Hal ini untuk menjaga integritas database.
• Mengembalikan database ke keadaan semula setelah terjadi kegagalan
• Setelah terjadi kegagalan pada harddisk, Anda dapat mengembalikan data pada keadaan sebelum terjadi kerusakan.
Log Transaksi • Log transaksi bukanlah sebuah tabel, tetapi merupakan sebuah file atau beberapa file terpisah dalam database. • Cache dari log adalah tempat terpisah dari cache buffer untuk page data. • Bentuk data dalam log tidaklah sama dengan format dari page data. • Anda dapat membuat log transaksi menjadi beberapa file. Anda juga dapat mengatur pertumbuhan file log sehingga menghindari ukuran log yang tidak terkontrol.
Log Transaksi • Log transaksi dapat dianggap sebagai sebuah string serial dari record-record log. Log Sequence Number (LSN) mengidentifikasi setiap record log. Record log baru ditulis pada akhir log dengan LSN lebih tinggi dari LSN record sebelumnya. • Record log untuk modifikasi data dapat berupa operasi yang dilakukan atau data sebelum dan sesudah terjadi perubahan.
Log Transaksi • Beberapa operasi yang dicatat di dalam log transaksi : • Awal dan akhir transaksi • Setiap perubahan data (INSERT, UPDATE, dan DELETE) • Setiap perluasan alokasi atau dealokasi. • Pembuatan dan penghapusan tabel atau index
Log Transaksi • Record-record log disimpan secara serial sesuai dengan pembuatannya. • Untuk sebuah transaksi, semua record log dari transaksi tersebut dihubungkan dengan menggunakan pointer agar proses rollback dapat dilakukan.
Log Transaksi • Pernyataan ROLLBACK tidak dimasukkan ke dalam log transaksi. • Setiap transaksi menyediakan sejumlah tempat di log transaksi agar proses rollback dapat dilakukan. Tempat tersebut akan dibebaskan pada saat transaksi selesai. • Jumlah tempat yang disediakan tergantung pada operasi yang dilakukan transaksi, tetapi pada umumnya sama dengan jumlah tempat yang dipakai untuk setiap operasi.
Properti ACID Sebuah transaksi harus memenuhi empat kriteria yang dikenal dengan properti ACID. SQL Server menyediakan mekanisme agar sebuah transaksi memenuhi keempat kriteria tersebut : • Atomicity • Consistency • Isolation • Durability
Properti ACID (i) : Automicity • Semua modifikasi dalma sebuah transaksi dianggap sebagai satu kesatuan, semua modifikasi akan berhasil atau semua akan gagal. • SQL Server memberikan mekanisme yang secara otomatis menentukan apakah transaksi berhasil atau tidak.
Properti ACID (i) : Automicity • Misalnya adalah sebuah transaksi untuk mentransfer uang melalui bank. Ada dua perubahan yang harus dilakukan. Rekening pengirim harus dikurangi dan rekening penerima harus ditambah. Kedua modifikasi tersebut merupakai sebuah kesatuan. • Jika rekening pengirim berhasil dikurangi tetapi rekening penerima tidak berhasil ditambah, maka transaksi dibatalkan dan data dikembalikan ke keadaan semula.
Properti ACID(ii) : Consistency • Semua data dalam keadaan konsisten. • Integritas data dijaga setelah transaksi selesai, baik berhasil atau gagal. Sebelum transaksi dimulai, database berada dalam keadaan konsisten artinya index pohon B+ dan list gandanya dalam keadaan benar. Setelah transaksi selesai, database juga dalam keadaan konsisten.
Properti ACID(iii) : Isolation • Efek dari setiap transaksi dama dengan jika hanya ada transaksi tersebut di dalam sistem, modifikasi oleh sebuah transaksi diisolasi (tidak dipengaruhi) oleh modifikasi yang dilakukan oleh transaksi lain. • Dengan cara ini sebuah transaksi tidak akan dipengaruhi oleh nilai yang diubah oleh transaksi lain sampai perubahan selesai (transaksi di-commit). • Jika sebuah transaksi gagal, modifikasimodifikasi yang dilakukan dibatalkan dengan proses rollback.
Properti ACID(iv) : Durability • Sekali transaksi dicommit (selesai) efek dari transaksi akan bersifat permanen di dalam database. • Jika SQL Server, sistem operasi, atau sebuah komponen server gagal, secra otomatis database akan dikembalikan ke keadaan pada saat SQL Server dimulai. • SQL Server memakai log transaksi untuk mengembalikan ke keadaan semula
Level-Level Isolasi • Setting yang menentukan level sebuah transaksi menerima data yang tidak konsisten, yaitu tingkatan sebuah transaksi diisolasi dari transaksi lain. • Semakin tinggi tingkat isolasi semakin tinggi keakuratan data. Level-level isolasi dari terendah sampai tertinggi adalah : read uncommitted, read committed, repeatable read, serializable.
Level Isolasi : Read Uncommitted • Level isolasi terendah. • Pada level ini, transaksi diisolasi hanya untuk menjadi data yang rusak secara fisik tidak dapat dibaca
Level Isolasi : Read Committed • Level default dari SQL Server • Pada level ini, pembacaan data diperbolehkan hanya pada data yang telah di-commit. • Data yang telah dicommit adalah data yang merupakan bagian permanen dari database. • Pada level ini data yang masih belum dicommit (masih dipakai oleh transaksi) tidak dapat dibaca.
Level Isolasi : Repeatable Read • Pembacaan berulang pada data yang dipakai oleh sebuah transaksi akan menghasilkan data yang sama
Level Isolasi : Serializable • Level tertinggi isolasi • Transaksi terisolasi dari transaksi lainnya • Hasil yang diperoleh dari transaksi konkuren pada sebuah database sama dengan jika transaksi dijalankan secara serial (satu per satu)
Level Isolasi • Level isolasi yang anda pakai menentukan perilaku LOCK untuk semua perintah SELECT yang dilakukan. • Anda dapat mengubah level isolasi dengan perintah SET TRANSACTION ISOLATION LEVEL • Misalnya mengubah level isolasi menjadi SERIALIZABLE : SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Level Isolasi • Sekali anda mengubah level isolasi untuk sebuah session, transaksitransaksi selanjutnya akan melakukan locking sesuai dengan level isolasinya. Untuk mengetahui level isolasi yang sedang dipakai, gunakan perintah : DBCC USEROPTIONS
Perilaku Transaksi Konkuren • Dirty read • Sebuah pembacaan data dapat membaca data yang belum di-commit. Dirty read terjadi pada saat sebuah transaksi mengubah data dan transaksi berikutnya membaca data yang diubah sebelum transaksi selesai (commit). Jika transaksi pertama membatalkan perubahan, transaksi kedua membaca data yang tidak ada di dalam database, karena perubahan dibatalkan oleh transaksi yang mengubah data.
Perilaku Transaksi Konkuren • Nonrepeatable read • Hal ini terjadi pada saat sebuah baris dibaca lebih dari sekali dalam sebuah transaksi, di antara beberapa pembacaan, transaksi lain mengubah baris tersebut. Oleh karena transaksi pertama membaca ulang dengan data yang berbeda, hasilnya tidak berulang dalam transaksi tersebut.
Perilaku Transaksi Konkuren • Phantom Read • Pembacaan yang terjadi pada saat sebuah transaksi mencoba membaca sebuah data yang tidak ada pada saat transaksi dimulai, tetapi data tersebut ditambahkan oleh transaksi kedua sebelum transaksi pertama selesai. Jika kemudian transaksi pertama mencari data tersebut, data tiba-tiba ditemukan padahal sebelumnya tidak ada. Data tersebut disebut data phantom.
Dirty Read
Nonrepeatable
Phantom Read
Read Read
Ya
Ya
Ya
Read committed Tidak
Ya
Ya
Repeatable
Tidak
Ya
Tidak
Tidak
Tidak
Tidak
uncommitted
read Serializable
