TASK-003-Transaction-SQL

[Dimasrsp98]

SOURCE : MODUL TRANSACTION

• DATABASE SAMPLE :

  • https://www.mysqltutorial.org/mysql-sample-database.aspx

• TASK :

  • Melakukan Transaction SQL

  a. Insert untuk 5 record & commited.

  b. Insert/update/delete di 1 sesi dan read table di sesi yg berbeda.

  c. Rollback ke savepoint.

[Dimasrsp98]

A. Insert 5 record & commited

• PROGRES 100%

• OBSTACLE

  • sempat salah baca soal jadi sebelumnya tidak dibuat ISSUE

• EVIDENCE

MYSQL Query

``` sql START TRANSACTION; INSERT INTO offices (officeCode,city,phone,addressLine1,addressLine2,state,country,postalCode,territory) VALUES ("8", "Soerabaya", "+62854673222", "Jl.Bahagia", "Kav. Boongan", "Jawa Timur", "Indonesia", "40322", "IDN"), ("9", "Bandoeng", "+623563244257", "Jl.Banceuy", "Gang Amir", "Jawa Barat", "Indonesia", "40311", "IDN"), ("10", "Batavia", "+623567324543", "Jl.Bondol", "Gg Abang", "DKI JAKARTA", "Indonesia", "402221", "IDN"), ("11", "Djogjakarta", "+62346433566", "Jl.Gudeg", "Gg Pathok", "JOGJAKARTA", "Indonesia", "40821", "IDN"), ("12", "Malang", "+62358743478", "Jl.Asin", "Kav. Lumpia", "Jawa Timur", "Indonesia", "40531", "IDN"); COMMIT; ```

SEBELUM INSERT & COMMIT

MYSQL 

PROSES INSERT & COMMIT


SESUDAH INSERT & COMMIT


[Dimasrsp98]

B. Insert/update/delete di 1 sesi dan read table di sesi yg berbeda

• PROGRES 100%

• OBSTACLE

  • sempat salah baca soal jadi sebelumnya tidak dibuat ISSUE

• EVIDENCE

MYSQL Query

``` sql START TRANSACTION; INSERT INTO offices (officeCode, city, phone, addressLine1, addressLine2, state, country, postalCode, territory) VALUES ("13", "Balikpapan", "+62813245786", "Jl.AjaDulu", "KAV.LiatNanti", "KALTIM", "Indonesia", "78872", "IDN"); SAVEPOINT SimpanDulu; UPDATE offices SET city = "JAKARTA" WHERE officeCode = "12"; DELETE FROM offices WHERE officeCode="11"; ```

SESI PERTAMA (INSERT, UPDATE, DELETE DAN SAVE POINT)

MYSQL 

SESI KEDUA (SELECT)

DATA TIDAK ADA PERUBAHAN SEDANGKAN DI SESI 1 BANYAK PERUBAHAN (INSERT, UPDATE, DELETE) KARENA BELUM DI KOMIT 

[Dimasrsp98]

C. ROLLBACK KE SAVEPOINT

• PROGRES 100%

• OBSTACLE

  • sempat salah baca soal jadi sebelumnya tidak dibuat ISSUE

• EVIDENCE

MYSQL Query

``` sql ROLLBACK TO simpanDulu; COMMIT; ```

ROLLBACK KE SAVEPOINT

MYSQL 

[Dimasrsp98]

DATA SETELAH DI ROLLBACK DAN DI COMMIT

RESPONSE

DATA MEMILIKI PERUBAHAN YAITU PENAMBAHAN DATA (INSERT) DENGAN OFFICECODE 13 KARENA SAVEPOINT DILETAKAN SETELAH INTRUKSI INSERT SEDANGKAN PERINTAH UPDATE DAN DELETE TIDAK MERUBAH DATA KARENA TIDAK MASUK KE SAVEPOINT YANG DIBUAT (PERINTAH UPDATE DAN DELETE ADA SETELAH PERINTAH SAVEPOINT DIBUAT SEHINGGA KETIKA DI ROLLBACK KE SAVEPOINT DAN BARU DI COMMIT, PERINTAH UPDATE DAN DELETE DIANGGAP TIDAK TERJADI)