Latihan Lock

Transaksi

- BEGIN transaksi COMMIT;

- ROLLBACK; (utk membatalkan)

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

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;

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)

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;