Download - Latihan Lock
![Page 1: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/1.jpg)
Latihan Lock
![Page 2: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/2.jpg)
Transaksi
- BEGIN transaksi COMMIT;
- ROLLBACK; (utk membatalkan)
![Page 3: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/3.jpg)
Transaksi
• buatlah tabel rekening dan isikan datanyaCREATE 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
![Page 4: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/4.jpg)
Transaksi
Jawaban:BEGIN;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;
![Page 5: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/5.jpg)
A
• mysql> CREATE TABLE t (i INT) ENGINE = InnoDB; 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)
![Page 6: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/6.jpg)
B
• mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec)
• mysql> DELETE FROM t WHERE i = 1;
![Page 7: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/7.jpg)
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
![Page 8: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/8.jpg)
Consistent Read
![Page 9: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/9.jpg)
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)
![Page 10: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/10.jpg)
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)
![Page 11: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/11.jpg)
A
• mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)
• mysql> SELECT * FROM t;
![Page 12: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/12.jpg)
A
• mysql> DELETE FROM t WHERE i=4; Query OK, 2 rows affected (0.00 sec)
• mysql> SELECT * FROM t;
![Page 13: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/13.jpg)
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)
![Page 14: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/14.jpg)
B
• mysql> SELECT MAX(i) FROM t FOR UPDATE;
• mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)
![Page 15: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/15.jpg)
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;
![Page 16: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/16.jpg)
READ LOCK A
• mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)
• mysql> SELECT MAX(i) FROM t LOCK IN SHARE MODE;
![Page 17: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/17.jpg)
B
• mysql> UPDATE t SET i = 55 WHERE i=5;
![Page 18: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/18.jpg)
A
• mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)
![Page 19: Latihan Lock](https://reader036.vdokumen.com/reader036/viewer/2022082517/56812de8550346895d934260/html5/thumbnails/19.jpg)
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;