1 / 19

Latihan Lock

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 ;

echeryl
Download Presentation

Latihan Lock

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Latihan Lock

  2. Transaksi - BEGIN transaksiCOMMIT; - ROLLBACK; (utk membatalkan)

  3. Transaksi • buatlah tabel rekening dan isikan datanya CREATE TABLErekening (norekchar(10) PRIMARY KEY, namavarchar(30),saldonumeric(10.2)); DESCRIBEmhs; INSERT INTOrekeningVALUES (‘111’,’Bapak’,5000000); INSERT INTOrekeningVALUES (‘222’,’anak’,1000000); SELECT* FROMrekening; • buatlah transaksi transfer uang 1 juta dari Bapak ke anak

  4. Transaksi Jawaban: BEGIN; BEGIN; UPDATErekening SET saldo=saldo-1000000 WHERE nama=‘Bapak’;UPDATErekening SET saldo=saldo+1000000 WHERE nama=‘anak’; COMMIT; Latihan: BEGIN; UPDATErekening SET saldo=saldo-1000000 WHERE nama=‘Bapak’; ROLLBACK;

  5. 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)

  6. B • mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) • mysql> DELETE FROM t WHERE i = 1;

  7. 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

  8. Consistent Read

  9. 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)

  10. 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)

  11. A • mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) • mysql> SELECT * FROM t;

  12. A • mysql> DELETE FROM t WHERE i=4; Query OK, 2 rows affected (0.00 sec) • mysql> SELECT * FROM t;

  13. 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)

  14. B • mysql> SELECT MAX(i) FROM t FOR UPDATE; • mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)

  15. 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;

  16. READ LOCK A • mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) • mysql> SELECT MAX(i) FROM t LOCK IN SHARE MODE;

  17. B • mysql> UPDATE t SET i = 55 WHERE i=5;

  18. A • mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)

  19. 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;

More Related