Latihan Lock Transaksi BEGIN transaksi COMMIT ROLLBACK utk
Latihan Lock
Transaksi - BEGIN transaksi COMMIT; - ROLLBACK; (utk membatalkan)
Transaksi • buatlah tabel rekening dan isikan datanya CREATE TABLE rekening (norek char(10) PRIMARY KEY, nama varchar(30), saldo numeric(10. 2)); DESCRIBE mhs; INSERT INTO rekening VALUES (‘ 111’, ’Bapak’, 5000000); INSERT INTO rekening VALUES (‘ 222’, ’anak’, 1000000); SELECT * FROM rekening; • buatlah transaksi transfer uang 1 juta dari Bapak ke anak
Transaksi Jawaban: BEGIN; UPDATE rekening SET saldo=saldo-1000000 WHERE nama=‘Bapak’; UPDATE rekening SET saldo=saldo+1000000 WHERE nama=‘anak’; COMMIT; Latihan: BEGIN; UPDATE rekening SET saldo=saldo-1000000 WHERE nama=‘Bapak’; ROLLBACK;
A • mysql> CREATE TABLE t (i INT) ENGINE = Inno. DB; Query OK, 0 rows affected (1. 07 sec) • mysql> INSERT INTO t (i) VALUES(1); Query OK, 1 row affected (0. 09 sec) • mysql> START TRANSACTION; Query OK, 0 rows affected (0. 00 sec) • mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE; 1 row in set (0. 10 sec)
B • mysql> START TRANSACTION; Query OK, 0 rows affected (0. 00 sec) • mysql> DELETE FROM t WHERE i = 1;
A • mysql> DELETE FROM t WHERE i = 1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction Sama-sama minta exclusive lock jadi deadlock
Consistent Read
A • mysql> BEGIN; Query OK, 0 rows affected (0. 00 sec) • mysql> SELECT MAX(i) FROM t; • mysql> INSERT INTO t(i) VALUES (4); Query OK, 1 row affected (0. 00 sec)
B • mysql> BEGIN; Query OK, 0 rows affected (0. 00 sec) • mysql> SELECT MAX(i) FROM t; • mysql> INSERT INTO t(i) VALUES(4); Query OK, 1 row affected (0. 00 sec) • mysql> COMMIT; Query OK, 0 rows affected (0. 00 sec)
A • mysql> COMMIT; Query OK, 0 rows affected (0. 00 sec) • mysql> SELECT * FROM t;
A • mysql> DELETE FROM t WHERE i=4; Query OK, 2 rows affected (0. 00 sec) • mysql> SELECT * FROM t;
A • mysql> SELECT * FROM t; • mysql> BEGIN; Query OK, 0 rows affected (0. 00 sec) • mysql> SELECT MAX(i) FROM t FOR UPDATE; • mysql> INSERT INTO t(i) VALUES (4); Query OK, 1 row affected (0. 00 sec)
B • mysql> SELECT MAX(i) FROM t FOR UPDATE; • mysql> COMMIT; Query OK, 0 rows affected (0. 00 sec)
B • mysql> SELECT MAX(i) FROM t FOR UPDATE; • mysql> INSERT INTO t(i) VALUES(5); Query OK, 1 row affected (0. 00 sec) • mysql> COMMIT; Query OK, 0 rows affected (0. 01 sec) • mysql> SELECT * FROM t;
READ LOCK A • mysql> BEGIN; Query OK, 0 rows affected (0. 00 sec) • mysql> SELECT MAX(i) FROM t LOCK IN SHARE MODE;
B • mysql> UPDATE t SET i = 55 WHERE i=5;
A • mysql> COMMIT; Query OK, 0 rows affected (0. 00 sec)
B • mysql> UPDATE t SET i = 55 WHERE i=5; Query OK, 0 rows affected (6. 95 sec) Rows matched: 0 Changed: 0 Warnings: 0 • mysql> UPDATE t SET i = 55 WHERE i=5; Query OK, 1 row affected (43. 30 sec) Rows matched: 1 Changed: 1 Warnings: 0 • mysql> SELECT * FROM t;
- Slides: 19