Pt6. Pràctica de transaccions i gestionant la concurrència
Salta a la navegació
Salta a la cerca
Contingut
Guillem Solà i Boeck (host A10BPC05)
Transacció
Esborrar un registre de la taula city i fer un roll-back
| Sessió 1 | Sessió 2 |
|---|---|
MariaDB [world]> select * from City; |(----------------------------------------------------------------------------------------------) | 4077 | Jabaliya | PSE | North Gaza | 113901 | | 4078 | Nablus | PSE | Nablus | 100231 | | 4079 | Rafah | PSE | Rafah | 92020 | | 4081 | Guillemlandia | AND | Boeck | 1996 | | 4082 | Guillemlandia | AND | Boeck | 1996 | | 4083 | No man's Land | AND | COD | 25000 | +------+------------------------------------+-------------+------------------------+------------+ 4082 rows in set (0.00 sec) | |
MariaDB [world]> begin; Query OK, 0 rows affected (0.00 sec) |
|
MariaDB [world]> delete from world.City where City.ID=4083; Query OK, 1 row affected (0.00 sec) |
MariaDB [world]> select * from City; |(----------------------------------------------------------------------------------------------) | 4077 | Jabaliya | PSE | North Gaza | 113901 | | 4078 | Nablus | PSE | Nablus | 100231 | | 4079 | Rafah | PSE | Rafah | 92020 | | 4081 | Guillemlandia | AND | Boeck | 1996 | | 4082 | Guillemlandia | AND | Boeck | 1996 | | 4083 | No man's Land | AND | COD | 25000 | +------+------------------------------------+-------------+------------------------+------------+ 4082 rows in set (0.00 sec) |
MariaDB [world]> rollback; Query OK, 0 rows affected (0.04 sec) |
MariaDB [world]> select * from City; |(----------------------------------------------------------------------------------------------) | 4077 | Jabaliya | PSE | North Gaza | 113901 | | 4078 | Nablus | PSE | Nablus | 100231 | | 4079 | Rafah | PSE | Rafah | 92020 | | 4081 | Guillemlandia | AND | Boeck | 1996 | | 4082 | Guillemlandia | AND | Boeck | 1996 | | 4083 | No man's Land | AND | COD | 25000 | +------+------------------------------------+-------------+------------------------+------------+ 4082 rows in set (0.00 sec) |
Bloquejos
Bloquejar només la modificació de la ciutat "Barcelona" de la taula City
| Sessió 1 | Sessió 2 |
|---|---|
MariaDB [world]> select * from City where Name = "Barcelona"; +------+-----------+-------------+-------------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------+-------------+-------------+------------+ | 654 | Barcelona | ESP | Katalonia | 1503451 | | 3546 | Barcelona | VEN | Anzoátegui | 322267 | +------+-----------+-------------+-------------+------------+ 2 rows in set (0.00 sec) | |
MariaDB [world]> begin; Query OK, 0 rows affected (0.00 sec) |
|
MariaDB [world]> SELECT * FROM City WHERE Name = "Barcelona" LOCK IN SHARE MODE; +------+-----------+-------------+-------------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------+-------------+-------------+------------+ | 654 | Barcelona | ESP | Katalonia | 1503451 | | 3546 | Barcelona | VEN | Anzoátegui | 322267 | +------+-----------+-------------+-------------+------------+ 2 rows in set (0.01 sec) |
|
MariaDB [world]> delete from City where Name = "Barcelona"; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction | |
MariaDB [world]> commit; Query OK, 0 rows affected (0.00 sec) |
MariaDB [world]> delete from City where Name = "Barcelona"; Query OK, 2 rows affected (0.00 sec) |
