Transacciones

UNIVERSIDAD TECNOLÓGICA DE QUERÉTARO

Carrera: Tecnología de la Información y Comunicación

1-.Crear una tabla InnoDB, procedemos con el código SQL estándar CREATE TABLE, pero debemos especificar que se trata de una tabla del tipo InnoDB
(TYPE= InnoDB).
mysql> CREATE TABLE innotest (campo INT NOT NULL PRIMARY KEY) TYPE = InnoDB;

Enter password: ******
Welcome tothe MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.33-community-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database practica9;
Query OK, 1 row affected (0.00 sec)
mysql> use practica9;
Database changed
mysql> CREATE TABLE innotest (campo INT NOT NULL PRIMARY KEY) TYPE =InnoDB;
Query OK, 0 rows affected, 1 warning (0.11 sec)

2. Insertar valores a la tabla innotest, de tal manera que al realizar una consulta sobre la misma nos muestre el siguiente resultado:
mysql> SELECT * FROM innotest;
+-------+
| campo |
+-------+
| 1 |
| 2 |
|3 |
+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO innotest VALUES('1');
Query OK, 1 row affected (0.12 sec)

mysql> INSERT INTO innotest VALUES('2');
Query OK, 1 row affected (0.10 sec)

mysql> INSERT INTO innotest VALUES('3');
Query OK, 1 row affected (0.08 sec)

mysql> Select * From innotest;}
+-------+
| campo |
+-------+| 1 |
| 2 |
| 3 |
+-------+
3 rows in set (0.00 sec)

3. Realizar una transacción donde se inserte un nuevo registro a la tabla innotest.
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO innotest VALUES(4);
Query OK, 1 row affected (0.00 sec)mysql> SELECT * FROM innotest;
+-------+
| campo |
+-------+
| 1 |
| 2 |
| 3 |
| 4 |
+-------+
4 rows in set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO innotest VALUES('4');
QueryOK, 1 row affected (0.00 sec)

mysql> Select * From innotest;
+-------+
| campo |
+-------+
| 1 |
| 2 |
| 3 |
| 4 |
+-------+
4 rows in set (0.00 sec)
4. Se asume que ocurre algún error y ejecutamos un ROLLBACK, por lo que la transacción no será completada, y los cambios realizados sobre la tabla no tendrán efecto.
mysql> Select * From innotest;
+-------+
|campo |
+-------+
| 1 |
| 2 |
| 3 |
| 4 |
+-------+
4 rows in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM innotest;
+-------+
| campo |
+-------+
| 1 |
| 2 || 3 |
+-------+
3 rows in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.07 sec)

mysql> Select * From innotest;
+-------+
| campo |
+-------+
| 1 |
| 2 |
| 3 |
+-------+
3 rows in set (0.00 sec)
5. Ahora, se asume que perdemos la conexión al servidor antes de que la transacción sea completada.mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO innotest VALUES(4);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM innotest;
+-------+
| campo |
+-------+
| 1 |
| 2 |...