Hay un aspecto sobre bases de datos, que Ana estudió en el ciclo formativo, y que no había tenido ocasión de ver en un caso real, y es el de las transacciones en una base de datos. Es un tema que le apasiona y le pide a María que le muestre alguna que haya realizado ella, para estudiarla a fondo y aprender con sus consejos.
Cuando tenemos una serie de consultas SQL que deben ejecutarse en conjunto, con el uso de transacciones podemos asegurarnos de que nunca nos quedaremos a medio camino de su ejecución.
Las transacciones tienen la característica de poder “deshacer” los cambios efectuados en las tablas, de una transacción dada, si no se han podido realizar todas las operaciones que forman parte de dicha transacción.
Por eso, las bases de datos que soportan transacciones son mucho más seguras y fáciles de recuperar si se produce algún fallo en el servidor que almacena la base de datos, ya que las consultas se ejecutan o no en su totalidad.
Al ejecutar una transacción, el motor de base de datos garantiza: atomicidad, consistencia, aislamiento y durabilidad (ACID) de la transacción (o conjunto de comandos) que se utilice.
El ejemplo típico que se pone para hacer más clara la necesidad de transacciones en algunos casos es el de una transacción bancaria. Por ejemplo, si una cantidad de dinero es transferida de la cuenta de Antonio a la cuenta de Pedro, se necesitarían dos consultas:
UPDATE cuentas SET saldo = saldo - cantidad WHERE cliente = “Antonio”;UPDATE cuentas SET saldo = saldo + cantidad WHERE cliente = “Pedro”;Pero, ¿qué ocurre si por algún imprevisto (un apagón de luz, etc.), el sistema “cae” después de que se ejecute la primera consulta, y antes de que se ejecute la segunda? Antonio tendrá una cantidad de dinero menos en su cuenta y creerá que ha realizado la transferencia. Pedro, sin embargo, creerá que todavía no le han realizado la transferencia.
De manera más formal el control de la transacción es realizado por el objeto de la conexión. Cunado se crea una conexión, por defecto esta en el modo activado. Esto significa que cada operación DML (INSERT, UPDATE, DELETE ) es tratada como transacción por sí misma que se valida automáticamente en cuanto se ejecute.
A veces necesitamos agrupar varias sentencias SQL en una única transacción, para ello:
conexion.setAutoCommit(false) conexion.Commit.Ejemplo:
El siguiente programa actualiza la tabla CAFFEE con los datos de las ventas que se envían al programa como un objeto de tipo HashMap (colección que almacena datos asociando una clave a un valor ), las actualizaciones se tratan como una única transacción de forma que si se ha podido completar todas las actualizaciones, se validan y en caso de que falle alguna, el gestor de errores deshace los cambios ( con.rollback())
public void updateCoffeeSales(HashMap<String, Integer> salesForWeek)
throws SQLException {
PreparedStatement updateSales = null;
PreparedStatement updateTotal = null;
String updateString =
"update " + dbName + ".COFFEES " +
"set SALES = ? where COF_NAME = ?";
String updateStatement =
"update " + dbName + ".COFFEES " +
"set TOTAL = TOTAL + ? " +
"where COF_NAME = ?";
try {
con.setAutoCommit(false);
updateSales = con.prepareStatement(updateString);
updateTotal = con.prepareStatement(updateStatement);
for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
updateSales.setInt(1, e.getValue().intValue());
updateSales.setString(2, e.getKey());
updateSales.executeUpdate();
updateTotal.setInt(1, e.getValue().intValue());
updateTotal.setString(2, e.getKey());
updateTotal.executeUpdate();
con.commit();
}
} catch (SQLException e ) {
JDBCTutorialUtilities.printSQLException(e);
if (con != null) {
try {
System.err.print("Transaction is being rolled back");
con.rollback();
} catch(SQLException excep) {
JDBCTutorialUtilities.printSQLException(excep);
}
}
} finally {
if (updateSales != null) {
updateSales.close();
}
if (updateTotal != null) {
updateTotal.close();
}
con.setAutoCommit(true);
}
}
Una transacción tiene dos finales posibles, COMMIT o ROLLBACK. Si se finaliza correctamente y sin problemas se hará con COMMIT, con lo que los cambios se realizan en la base de datos, y si por alguna razón hay un fallo, se deshacen los cambios efectuados hasta ese momento, con la ejecución de ROLLBACK.
Por defecto, al menos en MySQL o con Oracle, en una conexión trabajamos en modo autocommit con valor true. Eso significa que cada consulta es una transacción en la base de datos.
Por tanto, si queremos definir una transacción de varias operaciones, estableceremos el modo autocommit a false con el método setAutoCommit de la clase Connection.
En modo no autocommit las transacciones quedan definidas por las ejecuciones de los métodos commit y rollback. Una transacción abarca desde el último commit o rollback hasta el siguiente commit. Los métodos commit o rollback forman parte de la clase Connection.
En la siguiente porción de código de un procedimiento almacenado, puedes ver un ejemplo sencillo de cómo se puede utilizar commit y rollback: tras las operaciones se realiza el commit, y si ocurre una excepción, al capturarla realizaríamos el rollback.
BEGIN
…
SET AUTOCOMMIT OFF
update cuenta set saldo=saldo + 250 where dni=”12345678-L”;
update cuenta set saldo=saldo - 250 where dni=”89009999-L”;
COMMIT;
…
EXCEPTION
WHEN OTHERS THEN
ROLLBACK ;
END;Es conveniente planificar bien la aplicación para minimizar el tiempo en el que se tengan transacciones abiertas ejecutándose, ya que consumen recursos y suponen bloqueos en la base de datos que puede parar otras transacciones. En muchos casos, un diseño cuidadoso puede evitar usos innecesarios que se salgan fuera del modo estándar AutoCommit.
Hay una documentación muy extensa para programar con PL-SQL: procedimientos, funciones, triggers, etc., en el siguiente enlace:
Interesante tutorial sobre transacciones y otras cuestiones con MySQL.