Scribd
Upload
28 views9 pages

MariaDB Transaction and Rollback Guide

Uploaded by

yajujmajuj38
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
28 views9 pages

MariaDB Transaction and Rollback Guide

Uploaded by

yajujmajuj38
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 9

C:\xampp\mysql\bin>mysql -u root -p dbtoko_senin

Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.32-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

- TRANSACTION (COMMIT)

MariaDB [dbtoko_senin]> start transaction;


Query OK, 0 rows affected (0.000 sec)

MariaDB [dbtoko_senin]> update produk set stok=7 where id=1;


Query OK, 1 row affected (0.003 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [dbtoko_senin]> delete from produk where id=5;


Query OK, 1 row affected (0.003 sec)

MariaDB [dbtoko_senin]> commit;


Query OK, 0 rows affected (0.001 sec)

MariaDB [dbtoko_senin]> select * from produk;


+----+-------+--------------+---------+---------+------+------------------
+----------+
| id | kode | nama | kondisi | harga | stok | foto |
jenis_id |
+----+-------+--------------+---------+---------+------+------------------
+----------+
| 1 | TV001 | TV 32 inchi | Baru | 5000000 | 7 | produk_tv001.jpg |
1 |
| 2 | MJ001 | Meja Belajar | Baru | 2000000 | 6 | produk_mj001.jpg |
2 |
| 3 | MJ002 | Meja Makan | Second | 4000000 | 3 | produk_mj002.jpg |
2 |
| 4 | SF111 | Sofa | Baru | 7000000 | 2 | produk_SF111.jpg |
2 |
| 6 | MX111 | Mixer | Second | 500000 | 5 | produk_MX111.jpg |
1 |
+----+-------+--------------+---------+---------+------+------------------
+----------+
5 rows in set (0.000 sec)

- TRANSACTION (ROLLBACK)
MariaDB [dbtoko_senin]> start transaction;
Query OK, 0 rows affected (0.000 sec)

MariaDB [dbtoko_senin]> update produk set stok=7 where id=1;


Query OK, 0 rows affected (0.000 sec)
Rows matched: 1 Changed: 0 Warnings: 0

MariaDB [dbtoko_senin]> delete from produk where id=5;


Query OK, 0 rows affected (0.000 sec)
MariaDB [dbtoko_senin]> rollback;
Query OK, 0 rows affected (0.000 sec)

MariaDB [dbtoko_senin]> select * from produk;


+----+-------+--------------+---------+---------+------+------------------
+----------+
| id | kode | nama | kondisi | harga | stok | foto |
jenis_id |
+----+-------+--------------+---------+---------+------+------------------
+----------+
| 1 | TV001 | TV 32 inchi | Baru | 5000000 | 7 | produk_tv001.jpg |
1 |
| 2 | MJ001 | Meja Belajar | Baru | 2000000 | 6 | produk_mj001.jpg |
2 |
| 3 | MJ002 | Meja Makan | Second | 4000000 | 3 | produk_mj002.jpg |
2 |
| 4 | SF111 | Sofa | Baru | 7000000 | 2 | produk_SF111.jpg |
2 |
| 6 | MX111 | Mixer | Second | 500000 | 5 | produk_MX111.jpg |
1 |
+----+-------+--------------+---------+---------+------+------------------
+----------+
5 rows in set (0.000 sec)

MariaDB [dbtoko_senin]> start transaction;


Query OK, 0 rows affected (0.000 sec)

MariaDB [dbtoko_senin]> update produk set stok=5 where id=1;


Query OK, 1 row affected (0.003 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [dbtoko_senin]> select * from produk;


+----+-------+--------------+---------+---------+------+------------------
+----------+
| id | kode | nama | kondisi | harga | stok | foto |
jenis_id |
+----+-------+--------------+---------+---------+------+------------------
+----------+
| 1 | TV001 | TV 32 inchi | Baru | 5000000 | 5 | produk_tv001.jpg |
1 |
| 2 | MJ001 | Meja Belajar | Baru | 2000000 | 6 | produk_mj001.jpg |
2 |
| 3 | MJ002 | Meja Makan | Second | 4000000 | 3 | produk_mj002.jpg |
2 |
| 4 | SF111 | Sofa | Baru | 7000000 | 2 | produk_SF111.jpg |
2 |
| 6 | MX111 | Mixer | Second | 500000 | 5 | produk_MX111.jpg |
1 |
+----+-------+--------------+---------+---------+------+------------------
+----------+
5 rows in set (0.000 sec)

MariaDB [dbtoko_senin]> rollback;


Query OK, 0 rows affected (0.004 sec)

MariaDB [dbtoko_senin]> select * from produk;


+----+-------+--------------+---------+---------+------+------------------
+----------+
| id | kode | nama | kondisi | harga | stok | foto |
jenis_id |
+----+-------+--------------+---------+---------+------+------------------
+----------+
| 1 | TV001 | TV 32 inchi | Baru | 5000000 | 7 | produk_tv001.jpg |
1 |
| 2 | MJ001 | Meja Belajar | Baru | 2000000 | 6 | produk_mj001.jpg |
2 |
| 3 | MJ002 | Meja Makan | Second | 4000000 | 3 | produk_mj002.jpg |
2 |
| 4 | SF111 | Sofa | Baru | 7000000 | 2 | produk_SF111.jpg |
2 |
| 6 | MX111 | Mixer | Second | 500000 | 5 | produk_MX111.jpg |
1 |
+----+-------+--------------+---------+---------+------+------------------
+----------+
5 rows in set (0.000 sec)

- TRANSACTION (SAVEPOINT)
MariaDB [dbtoko_senin]> start transaction;
Query OK, 0 rows affected (0.000 sec)

MariaDB [dbtoko_senin]> savepoint updateProduk;


Query OK, 0 rows affected (0.000 sec)

MariaDB [dbtoko_senin]> update produk set nama="Laptop" where id=6;


Query OK, 1 row affected (0.005 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [dbtoko_senin]> rollback to updateProduk;


Query OK, 0 rows affected (0.003 sec)

MariaDB [dbtoko_senin]> select * from produk;


+----+-------+--------------+---------+---------+------+------------------
+----------+
| id | kode | nama | kondisi | harga | stok | foto |
jenis_id |
+----+-------+--------------+---------+---------+------+------------------
+----------+
| 1 | TV001 | TV 32 inchi | Baru | 5000000 | 7 | produk_tv001.jpg |
1 |
| 2 | MJ001 | Meja Belajar | Baru | 2000000 | 6 | produk_mj001.jpg |
2 |
| 3 | MJ002 | Meja Makan | Second | 4000000 | 3 | produk_mj002.jpg |
2 |
| 4 | SF111 | Sofa | Baru | 7000000 | 2 | produk_SF111.jpg |
2 |
| 6 | MX111 | Mixer | Second | 500000 | 5 | produk_MX111.jpg |
1 |
+----+-------+--------------+---------+---------+------+------------------
+----------+
5 rows in set (0.000 sec)

MariaDB [dbtoko_senin]> savepoint updateProduk;


Query OK, 0 rows affected (0.000 sec)

MariaDB [dbtoko_senin]> start transaction;


Query OK, 0 rows affected (0.000 sec)

MariaDB [dbtoko_senin]> savepoint update_produk;


Query OK, 0 rows affected (0.000 sec)

MariaDB [dbtoko_senin]> update produk set nama="Laptop" where id=1;


Query OK, 1 row affected (0.003 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [dbtoko_senin]> rollback to update_produk;


Query OK, 0 rows affected (0.003 sec)

MariaDB [dbtoko_senin]> select * from produk;


+----+-------+--------------+---------+---------+------+------------------
+----------+
| id | kode | nama | kondisi | harga | stok | foto |
jenis_id |
+----+-------+--------------+---------+---------+------+------------------
+----------+
| 1 | TV001 | TV 32 inchi | Baru | 5000000 | 7 | produk_tv001.jpg |
1 |
| 2 | MJ001 | Meja Belajar | Baru | 2000000 | 6 | produk_mj001.jpg |
2 |
| 3 | MJ002 | Meja Makan | Second | 4000000 | 3 | produk_mj002.jpg |
2 |
| 4 | SF111 | Sofa | Baru | 7000000 | 2 | produk_SF111.jpg |
2 |
| 6 | MX111 | Mixer | Second | 500000 | 5 | produk_MX111.jpg |
1 |
+----+-------+--------------+---------+---------+------+------------------
+----------+
5 rows in set (0.000 sec)

MariaDB [dbtoko_senin]> start transaction;


Query OK, 0 rows affected (0.000 sec)

MariaDB [dbtoko_senin]> savepoint updateProduk;


Query OK, 0 rows affected (0.000 sec)

MariaDB [dbtoko_senin]> update produk set nama="Laptop" where id=6;


Query OK, 1 row affected (0.003 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [dbtoko_senin]> select * from produk;


+----+-------+--------------+---------+---------+------+------------------
+----------+
| id | kode | nama | kondisi | harga | stok | foto |
jenis_id |
+----+-------+--------------+---------+---------+------+------------------
+----------+
| 1 | TV001 | TV 32 inchi | Baru | 5000000 | 7 | produk_tv001.jpg |
1 |
| 2 | MJ001 | Meja Belajar | Baru | 2000000 | 6 | produk_mj001.jpg |
2 |
| 3 | MJ002 | Meja Makan | Second | 4000000 | 3 | produk_mj002.jpg |
2 |
| 4 | SF111 | Sofa | Baru | 7000000 | 2 | produk_SF111.jpg |
2 |
| 6 | MX111 | Laptop | Second | 500000 | 5 | produk_MX111.jpg |
1 |
+----+-------+--------------+---------+---------+------+------------------
+----------+
5 rows in set (0.000 sec)

MariaDB [dbtoko_senin]> update produk set nama="Laptoppp" where id=6;


Query OK, 1 row affected (0.000 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [dbtoko_senin]> rollback to updateProduk;


Query OK, 0 rows affected (0.003 sec)

MariaDB [dbtoko_senin]> select * from produk;


+----+-------+--------------+---------+---------+------+------------------
+----------+
| id | kode | nama | kondisi | harga | stok | foto |
jenis_id |
+----+-------+--------------+---------+---------+------+------------------
+----------+
| 1 | TV001 | TV 32 inchi | Baru | 5000000 | 7 | produk_tv001.jpg |
1 |
| 2 | MJ001 | Meja Belajar | Baru | 2000000 | 6 | produk_mj001.jpg |
2 |
| 3 | MJ002 | Meja Makan | Second | 4000000 | 3 | produk_mj002.jpg |
2 |
| 4 | SF111 | Sofa | Baru | 7000000 | 2 | produk_SF111.jpg |
2 |
| 6 | MX111 | Mixer | Second | 500000 | 5 | produk_MX111.jpg |
1 |
+----+-------+--------------+---------+---------+------+------------------
+----------+
5 rows in set (0.000 sec)

MariaDB [dbtoko_senin]> savepoint updateProduk;


Query OK, 0 rows affected (0.000 sec)

MariaDB [dbtoko_senin]> select * from produk;


+----+-------+--------------+---------+---------+------+------------------
+----------+
| id | kode | nama | kondisi | harga | stok | foto |
jenis_id |
+----+-------+--------------+---------+---------+------+------------------
+----------+
| 1 | TV001 | TV 32 inchi | Baru | 5000000 | 7 | produk_tv001.jpg |
1 |
| 2 | MJ001 | Meja Belajar | Baru | 2000000 | 6 | produk_mj001.jpg |
2 |
| 3 | MJ002 | Meja Makan | Second | 4000000 | 3 | produk_mj002.jpg |
2 |
| 4 | SF111 | Sofa | Baru | 7000000 | 2 | produk_SF111.jpg |
2 |
| 6 | MX111 | Mixer | Second | 500000 | 5 | produk_MX111.jpg |
1 |
+----+-------+--------------+---------+---------+------+------------------
+----------+
5 rows in set (0.000 sec)

MariaDB [dbtoko_senin]> update produk set nama="Laptop" where id=6;


Query OK, 1 row affected (0.003 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [dbtoko_senin]> select * from produk;


+----+-------+--------------+---------+---------+------+------------------
+----------+
| id | kode | nama | kondisi | harga | stok | foto |
jenis_id |
+----+-------+--------------+---------+---------+------+------------------
+----------+
| 1 | TV001 | TV 32 inchi | Baru | 5000000 | 7 | produk_tv001.jpg |
1 |
| 2 | MJ001 | Meja Belajar | Baru | 2000000 | 6 | produk_mj001.jpg |
2 |
| 3 | MJ002 | Meja Makan | Second | 4000000 | 3 | produk_mj002.jpg |
2 |
| 4 | SF111 | Sofa | Baru | 7000000 | 2 | produk_SF111.jpg |
2 |
| 6 | MX111 | Laptop | Second | 500000 | 5 | produk_MX111.jpg |
1 |
+----+-------+--------------+---------+---------+------+------------------
+----------+
5 rows in set (0.000 sec)

------------------------ PROCEDURE ----------------------------

- MEMBUAT PROCEDURE
MariaDB [dbtoko_senin]> delimiter $$
MariaDB [dbtoko_senin]> create procedure showProduk()
-> begin
-> select kode, nama, harga, stok
-> from produk;
-> end $$
Query OK, 0 rows affected (0.006 sec)

MariaDB [dbtoko_senin]> call showProduk() $$


+-------+--------------+---------+------+
| kode | nama | harga | stok |
+-------+--------------+---------+------+
| TV001 | TV 32 inchi | 5000000 | 7 |
| MJ001 | Meja Belajar | 2000000 | 6 |
| MJ002 | Meja Makan | 4000000 | 3 |
| SF111 | Sofa | 7000000 | 2 |
| MX111 | Laptop | 500000 | 5 |
+-------+--------------+---------+------+
5 rows in set (0.000 sec)

Query OK, 0 rows affected (0.004 sec)

- PROCEDURE DENGAN PARAMETER


MariaDB [dbtoko_senin]> delimiter //
MariaDB [dbtoko_senin]> create procedure produkElektronik(idprod int)
-> begin
-> select * from produk where jenis_id = idprod;
-> end //
Query OK, 0 rows affected (0.006 sec)
MariaDB [dbtoko_senin]> call produkElektronik(1) //
+----+-------+-------------+---------+---------+------+------------------
+----------+
| id | kode | nama | kondisi | harga | stok | foto | jenis_id
|
+----+-------+-------------+---------+---------+------+------------------
+----------+
| 1 | TV001 | TV 32 inchi | Baru | 5000000 | 7 | produk_tv001.jpg | 1
|
| 6 | MX111 | Laptop | Second | 500000 | 5 | produk_MX111.jpg | 1
|
+----+-------+-------------+---------+---------+------+------------------
+----------+
2 rows in set (0.000 sec)

Query OK, 0 rows affected (0.004 sec)

MariaDB [dbtoko_senin]> call produkElektronik(2) //


+----+-------+--------------+---------+---------+------+------------------
+----------+
| id | kode | nama | kondisi | harga | stok | foto |
jenis_id |
+----+-------+--------------+---------+---------+------+------------------
+----------+
| 2 | MJ001 | Meja Belajar | Baru | 2000000 | 6 | produk_mj001.jpg |
2 |
| 3 | MJ002 | Meja Makan | Second | 4000000 | 3 | produk_mj002.jpg |
2 |
| 4 | SF111 | Sofa | Baru | 7000000 | 2 | produk_SF111.jpg |
2 |
+----+-------+--------------+---------+---------+------+------------------
+----------+
3 rows in set (0.000 sec)

Query OK, 0 rows affected (0.006 sec)

- PROCEDURE BANYAK PARAMETER


MariaDB [dbtoko_senin]> delimiter //
MariaDB [dbtoko_senin]> create procedure inputProduk(
-> kode char(4), nama varchar(45), hrg double, stok int, foto varchar(50), idj
int
-> ) begin
-> insert into produk (kode, nama, harga, stok, foto, jenis_id)
-> values (kode, nama, hrg, stok, foto, idj);
-> end //
Query OK, 0 rows affected (0.006 sec)

MariaDB [dbtoko_senin]> call inputProduk(


-> 'HP09', 'HP', 4000000, 5, 'hp09.jpg', 1);
-> //
Query OK, 1 row affected (0.002 sec)

MariaDB [dbtoko_senin]> show procedure status//


+--------------+------------------+-----------+----------------
+---------------------+---------------------+---------------+---------
+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified
| Created | Security_type | Comment | character_set_client |
collation_connection | Database Collation |
+--------------+------------------+-----------+----------------
+---------------------+---------------------+---------------+---------
+----------------------+----------------------+--------------------+
| dbtoko_senin | inputProduk | PROCEDURE | root@localhost | 2024-05-27
16:06:04 | 2024-05-27 16:06:04 | DEFINER | | cp850 |
cp850_general_ci | utf8_general_ci |
| dbtoko_senin | produkElektronik | PROCEDURE | root@localhost | 2024-05-27
15:49:52 | 2024-05-27 15:49:52 | DEFINER | | cp850 |
cp850_general_ci | utf8_general_ci |
| dbtoko_senin | showProduk | PROCEDURE | root@localhost | 2024-05-27
15:43:30 | 2024-05-27 15:43:30 | DEFINER | | cp850 |
cp850_general_ci | utf8_general_ci |
+--------------+------------------+-----------+----------------
+---------------------+---------------------+---------------+---------
+----------------------+----------------------+--------------------+
3 rows in set (0.007 sec)

MariaDB [dbtoko_senin]> select * from produk//


+----+-------+--------------+---------+---------+------+------------------
+----------+
| id | kode | nama | kondisi | harga | stok | foto |
jenis_id |
+----+-------+--------------+---------+---------+------+------------------
+----------+
| 1 | TV001 | TV 32 inchi | Baru | 5000000 | 7 | produk_tv001.jpg |
1 |
| 2 | MJ001 | Meja Belajar | Baru | 2000000 | 6 | produk_mj001.jpg |
2 |
| 3 | MJ002 | Meja Makan | Second | 4000000 | 3 | produk_mj002.jpg |
2 |
| 4 | SF111 | Sofa | Baru | 7000000 | 2 | produk_SF111.jpg |
2 |
| 6 | MX111 | Laptop | Second | 500000 | 5 | produk_MX111.jpg |
1 |
| 13 | HP09 | HP | NULL | 4000000 | 5 | hp09.jpg |
1 |
+----+-------+--------------+---------+---------+------+------------------
+----------+
6 rows in set (0.000 sec)

- PROCEDURE RETURN
MariaDB [dbtoko_senin]> delimiter //
MariaDB [dbtoko_senin]> create procedure totalHarga (in hrg double, in jml int, out
total double)
-> begin
-> set total = hrg * jml;
-> select @total;
-> end //
Query OK, 0 rows affected (0.006 sec)

MariaDB [dbtoko_senin]> select @totalHarga as total_harga //


+-------------+
| total_harga |
+-------------+
| 100000 |
+-------------+
1 row in set (0.000 sec)
MariaDB [dbtoko_senin]>

You might also like