Comandos transaccionales y de bloqueo de MySQL









13.4.1. Sintaxis de
START TRANSACTION,
COMMIT y
ROLLBACK









Por defecto, MySQL se ejecuta con el modo autocommit
activado. Esto significa que en cuanto ejecute un comando que
actualice (modifique) una tabla, MySQL almacena la actualización
en disco.


Si usa tablas transaccionales (como
InnoDB
o BDB), puede
desactivar el modo autocommit con el siguiente comando:


SET AUTOCOMMIT=0;


Tras deshabilitar el modo autocommit poniendo la variable
AUTOCOMMIT a cero, debe usar
COMMIT para almacenar los cambios
en disco o ROLLBACK si quiere
ignorar los cambios hechos desde el comienzo de la transacción.


Si quiere deshabilitar el modo autocommit para una serie
única de comandos, puede usar el comando
START TRANSACTION
:


START TRANSACTION;

SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

Con START TRANSACTION,
autocommit permanece deshabilitado hasta el final de la
transacción con COMMIT o
ROLLBACK. El modo autocommit vuelve
a su estado prévio.


BEGIN y
BEGIN WORK
se soportan como alias para
START TRANSACTION para iniciar una
transacción. START TRANSACTION es
sintaxis SQL estándar y es la forma recomendada para iniciar una
transacción ad-hoc . El comando BEGIN
difiere del uso de la palabra clave BEGIN
que comienza un comando compuesto BEGIN
... END
. El último no comienza una transacción. Consulte

Sección 19.2.7, ?Sentencia compuesta BEGIN
... END
?
.


Puede comenzar una transacción así:


START TRANSACTION WITH CONSISTENT SNAPSHOT;


La cláusula WITH CONSISTENT SNAPSHOT
comienza una lectura consistente para motores de almacenamiento
capaces de ello. Actualmente, esto se aplica sólo a
InnoDB. El efecto es el mismo que
realizar un START TRANSACTION
seguido por un SELECT desde
cualquier tabla InnoDB . Consulte

Sección 15.10.4, ?Lecturas consistentes que no bloquean?
.


Comenzar una transacción provoca que se realice un
UNLOCK TABLES implícito.


Tenga en cuenta que si no usa tablas transaccionales,
cualquier cambio se almacena de golpe, a pesar del estado del
modo autocommit .


Si realiza un comando ROLLBACK
tras actualizar una tabla no transaccional dentro de una
transacción, ocurre una advertencia
ER_WARNING_NOT_COMPLETE_ROLLBACK
. Los cambios en tablas
transaccionales se deshacen, pero no los cambios en tablas no
transaccionales.


Cada transacción se almacena en el log binario en un trozo,
hasta COMMIT. Las transacciones que
se deshacen no se loguean. (Exceción:
Las modificaciones a tablas no transaccionales no pueden
deshacerse. Si una transacción que se deshace incluye
modificaciones a tablas no transaccionales, la transacción
entera se loguea con un comando ROLLBACK
al final para asegurar que las modificaciones a estas tablas se
replican.) Consulte

Sección 5.10.3, ?El registro binario (Binary Log)?
.


Puede cambiar el nivel de aislamiento para transacciones con
SET TRANSACTION ISOLATION LEVEL.
Consulte

Sección 13.4.6, ?Sintaxis de SET
TRANSACTION
?
.


Deshacer puede ser una operación lenta que puede ocurrir sin
que el usuario lo haya pedido explícitamente (por ejemplo,
cuando ocurre un error). Debido a ello,
SHOW PROCESSLIST
en MySQL 5.0 muestra
Rolling back en la columna
Statepara la conexión durante
rollbacks implícitos y explícitos (comando SQL
ROLLBACK).






13.4.2. Sentencias que no se
pueden deshacer





Algunos comandos no pueden deshacerse. En general, esto
incluye comandos del lenguaje de definición de datos (DDL),
tales como los que crean y borran bases de datos, los que crean,
borran o alteran tablas o rutinas almacenadas.


Debe designar que sus transacciones no incluyan tales
comandos. Si realiza un comando pronto en una transacción que no
puede deshacerse, y luego un comando posterior falla, el efecto
global de la transacción no puede deshacerse mediante un comando
ROLLBACK .






13.4.3. Sentencias que causan una
ejecución (commit) implícita





Cada uno de los comandos siguientes (y cualquier sinónimo de
los mismos) terminan una transacción implícitamente, como si
hubiera realizado un COMMIT antes
de ejecutar el comando:
































ALTER TABLE BEGIN CREATE INDEX
CREATE TABLE   CREATE DATABASE
DROP DATABASE DROP INDEX DROP TABLE
LOAD MASTER DATA LOCK TABLES RENAME TABLE
SET AUTOCOMMIT=1 START TRANSACTION TRUNCATE TABLE


UNLOCK TABLES también realiza un
commit de una transacción si hay cualquier tabla bloqueada.


Las transacciones no pueden anidarse. Esto es una
consecuencia del COMMIT implícito
realizado por cualquier transacción actual cuando realiza un
comando START TRANSACTION o uno de
sus sinónimos.






13.4.4. Sintaxis de
SAVEPOINT y
ROLLBACK TO SAVEPOINT







SAVEPOINT identifier

ROLLBACK TO SAVEPOINT identifier

En MySQL 5.0, InnoDB soporta los
comandos SQL SAVEPOINT y
ROLLBACK TO SAVEPOINT.


El comando SAVEPOINT crea un
punto dentro de una transacción con un nombre
identifier. Si la transacción
actual tiene un punto con el mismo nombre, el antiguo se borra y
se crea el nuevo.


El comando ROLLBACK TO SAVEPOINT
deshace una transacción hasta el punto nombrado. Las
modificaciones que la transacción actual hace al registro tras
el punto se deshacen en el rollback, pero
InnoDB
no libera
los bloqueos de registro que se almacenaron en memoria tras el
punto . (Tenga en cuenta que para un nuevo registro insertado,
la información de bloqueo se realiza a partir del ID de
transacción almacenado en el registro; el bloqueo no se almacena
separadamente en memoria. En este caso, el bloqueo de registro
se libera al deshacerse todo.) Los puntos creados tras el punto
nombrado se borran.


Si un comando retorna el siguiente error, significa que no
existe ningún punto con el nombre especificado:


ERROR 1181: Got error 153 during ROLLBACK


Todos los puntos de la transacción actual se borran si
ejecuta un COMMIT, o un
ROLLBACK que no nombre ningún
punto.






13.4.5. Sintaxis de
LOCK TABLES y
UNLOCK TABLES







LOCK TABLES

tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES

LOCK TABLES bloquea tablas para
el flujo actual. Si alguna de las tablas la bloquea otro flujo,
bloquea hasta que pueden adquirirse todos los bloqueos.
UNLOCK TABLES libera cualquier
bloqueo realizado por el flujo actual. Todas las tablas
bloqueadas por el flujo actual se liberan implícitamente cuando
el flujo reliza otro LOCK TABLES, o
cuando la conexión con el servidor se cierra.


Un bloqueo de tabla protege sólo contra lecturtas
inapropoadas o escrituras de otros clientes. El cliente que
tenga el bloqueo, incluso un bloqueo de lectura, puede realizar
operaciones a nivel de tabla tales como
DROP TABLE
.


Tenga en cuenta lo siguiente a pesar del uso de
LOCK TABLES con tablas
transaccionales:




  • LOCK TABLES no es una
    operación transaccional y hace un commit implícito de
    cualquier transacción activa antes de tratar de bloquear
    las tablas. También, comenzar una transacción (por
    ejemplo, con START TRANSACTION)
    realiza un UNLOCK TABLES
    implícito. (Consulte

    Sección 13.4.3, ?Sentencias que causan una ejecución
    (commit) implícita?
    .)

  • La forma correcta de usar LOCK
    TABLES
    con tablas transaccionales,como
    InnoDB, es poner
    AUTOCOMMIT = 0 y no llamar
    a UNLOCK TABLES hasta que
    hace un commit de la transacción explícitamente. Cuando
    llama a LOCK TABLES,
    InnoDB internamente realiza
    su propio bloqueo de tabla, y MySQL realiza su propio
    bloqueo de tabla. InnoDB
    libera su bloqueo de tabla en el siguiente commit, pero
    para que MySQL libere su bloqueo de tabla, debe llamar a
    UNLOCK TABLES. No debe
    tener AUTOCOMMIT = 1,
    porque entonces InnoDB
    libera su bloqueo de tabla inmediatamente tras la
    llamada de LOCK TABLES, y
    los deadlocks pueden ocurrir fácilmente. (Tenga en
    cuenta que en MySQL 5.0, no adquirimos el bloqueo de
    tabla InnoDB en absoluto si
    AUTOCOMMIT=1, para ayudar a
    aplicaciones antiguas a envitar deadlocks.)

  • ROLLBACK no libera
    bloqueos de tablas no transaccionales de MySQL.



Para usar LOCK TABLES en MySQL
5.0, debe tener el permiso LOCK TABLES
y el permiso SELECT para las tablas
involucradas.


La razón principal para usar LOCK
TABLES
es para emular transacciones o para obtener más
velocidad al actualizar tablas. Esto se explica con más detalle
posteriormente.


Si un flujo obtiene un bloqueo READ
en una tabla, ese flujo (y todos los otros) sólo pueden leer de
la tabla. Si un flujo obtiene un bloqueo
WRITE
en una tabla, sólo el flujo con el bloqueo puede
escribir a la tabla. El resto de flujos se bloquean hasta que se
libera el bloqueo.


La diferencia entre READ LOCAL y
READ es que
READ LOCAL
permite comandos INSERT
no conflictivos (inserciones concurrentes) se ejecuten mientras
se mantiene el bloqueo. Sin embargo, esto no puede usarse si va
a manipular los ficheros de base de datos fuera de MySQL
mientras mantiene el bloqueo. Para tablas
InnoDB
, READ LOCAL
esencialmente no hace nada: No bloquea la tabla. Para tablas
InnoDB , el uso de
READ LOCAL está obsoleto ya que una
SELECT consistente hace lo mismo, y
no se necesitan bloqueos.


Cuando usa LOCK TABLES, debe
bloquear todas las tablas que va a usar en sus consultas.
Mientras los bloqueos obtenidos con un comando
LOCK TABLES están en efecto, no
puede acceder a ninguna tabla que no estuviera bloqueada por el
comando. Además, no puede usar una tabla bloqueada varias veces
en una consulta --- use alias para ello. Tenga en cuenta que en
este caso, debe tener un bloqueo separado para cada alias.


mysql> LOCK TABLE t WRITE, t AS t1 WRITE;

mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

Si sus consultas se refieren a una tabla que use un alias,
debe bloquear la tabla que usa el mismo alias. No funciona
bloquear la tabla sin especificar el alias:


mysql> LOCK TABLE t READ;

mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

Si bloquea una tabla usando un alias, debe referirse a ella
en sus consultas usando este alias:


mysql> LOCK TABLE t AS myalias READ;

mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

WRITE bloquea normalmente
teniendo una prioridad superior que READ
al bloquear para asegurar que las actualizaciones se procesan en
cuanto se puede. Esto significa que si un flujo obtiene un
bloqueo READ y luego otro flujo
pide un bloqueo WRITE , las
peticiones de bloqueo READ
posteriores esperan hasta que el flujo
WRITE
quita el bloqueo. Puede usar bloqueos
LOW_PRIORITY WRITE para permitir a
otros flujos que obtengan bloqueos READ
mientras el flujo está en espera para el bloqueo
WRITE. Debe usar bloqueos
LOW_PRIORITY WRITE sólo si está
seguro que habrá un momento sin flujos con bloqueos
READ .


LOCK TABLES funciona como sigue:




  1. Ordena todas las tablas a ser bloqueadas en un orden
    definido internamente. Desde el punto de vista del
    usuario, este orden es indefinido.

  2. Si una tabla se bloquea con bloqueo de escritura y
    lectura, pone el bloqueo de lectura antes del de
    escritura.

  3. Bloquea una tabla cada vez hasta que el flujo
    obtiene todos los bloqueos.



Esta política asegura un bloqueo de tablas libre de
deadlocks. Sin embargo hay otros puntos que debe tener en cuenta
respecto a esta política:


Si está usando un bloqueo LOW_PRIORITY
WRITE
para una tabla, sólo significa que MySQL espera
para este bloqueo particular hasta que no hay flujos que quieren
un bloqueo READ . Cuando el flujo
ha obtenido el bloqueo WRITE y está
esperando para obtener un bloqueo para la siguiente tabla en la
lista, todos los otros flujos esperan hasta que el bloqueo
WRITE se libera. Si esto es un
problema con su aplicación, debe considerar convertir algunas de
sus tablas a transaccionales.


Puede usar KILL para terminar un
flujo que está esperando para un bloqueo de tabla. Consulte

Sección 13.5.5.3, ?Sintaxis de KILL?
.


Tenga en cuenta que no
debe bloquear ninguna tabla que esté usando con
INSERT DELAYED ya que en tal caso
el INSERT lo realiza un flujo
separado.


Normalmente, no tiene que bloquear tablas, ya que todos los
comandos UPDATE son atómicos,
ningún otro flujo puede interferir con ningún otro que está
ejecutando comandos SQL . Hay algunos casos en que no debe
bloquear tablas de ningún modo:




  • Si va a ejecutar varias operaciones en un conjunto
    de tablas MyISAM , es mucho
    más rápido bloquear las tablas que va a usar. Bloquear
    tablas MyISAM acelera la
    inserción, las actualizaciones, y los borrados. Por
    contra, ningún flujo puede actualizar una tabla con un
    bloqueo READ (incluyendo el
    que tiene el bloqueo) y ningún flujo puede acceder a una
    tabla con un bloqueo WRITE
    distinto al que tiene el bloqueo.

    La razón que algunas operaciones
    MyISAM sean más rápidas
    bajo LOCK TABLES es que
    MySQL no vuelca la caché de claves para la tabla
    bloqueada hasta que se llama a
    UNLOCK TABLES
    . Normalmente, la caché de claves se
    vuelca tras cada comando SQL.


  • Si usa un motor de almacenamiento en MySQL que no
    soporta transacciones, debe usar
    LOCK TABLES
    si quiere asegurarse que ningún otro
    flujo se ejecute entre un SELECT
    y un UPDATE. El ejemplo
    mostrado necesita LOCK TABLES
    para ejecutarse sin problemas:
    mysql> LOCK TABLES trans READ, customer WRITE;
    
    mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id;
    mysql> UPDATE customer
    -> SET total_value=sum_from_previous_statement
    -> WHERE customer_id=some_id;
    mysql> UNLOCK TABLES;

    Sin LOCK TABLES, es
    posible que otro flujo pueda insertar un nuevo registro
    en la tabla trans entre la
    ejecución del comando SELECT
    y UPDATE.




Puede evitar usar LOCK TABLES en
varios casos usando actualizaciones relativas (UPDATE
customer SET value=value+new_value
)
o la función LAST_INSERT_ID() ,
Consulte

Sección 1.7.5.3, ?Transacciones y operaciones atómicas?
.


Puede evitar bloquear tablas en algunos casos usando las
funciones de bloqueo de nivel de usuario
GET_LOCK()
y RELEASE_LOCK().
Estos bloqueos se guardan en una tabla hash en el servidor e
implementa pthread_mutex_lock() y
pthread_mutex_unlock() para alta
velocidad. Consulte

Sección 12.9.4, ?Funciones varias?
.


Consulte

Sección 7.3.1, ?Métodos de bloqueo?
, para más información
acerca de la política de bloqueo.


Puede bloquear todas las tablas en todas las bases de datos
con bloqueos de lectura con el comando
FLUSH TABLES WITH READ LOCK
. Consulte

Sección 13.5.5.2, ?Sintaxis de FLUSH?
.
Esta es una forma muy conveniente para obtener copias de
seguridad si tiene un sistema de ficheros como Veritas que puede
obtener el estado en un punto temporal.


Nota: Si usa
ALTER TABLE en una tabla bloqueada,
puede desbloquearse. Consulte

Sección A.7.1, ?Problemas con ALTER TABLE?
.






13.4.6. Sintaxis de
SET TRANSACTION







SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL

{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

Este comando prepara el nivel de aislamiento de transacción
para la siguiente transacción, globalmente, o para la sesión
actual.


El comportamiento por defecto de SET
TRANSACTION
es poner el nivel de aislammiento para la
siguiente transacción (que no ha empezado todavía). Si usa lka
palabra clave GLOBAL el comando
pone el nivel de aislamiento de transacción por defecto
globalmente para todas las transacciones creadas desde ese
momento. Las conexiones existentes no se ven afectadas. Necesita
el permiso SUPER para hacerlo. Usar
la palabra clave SESSION determina
el nivel de transacción para todas las transacciones futuras
realizadas en la conexión actual.


Para descripciones del nivel de aislamiento de cada
transacción InnoDB, consulte

Sección 15.10.3, ?InnoDB y
TRANSACTION ISOLATION LEVEL?
.
InnoDB soporta cada uno de estos
niveles en MySQL 5.0. El nivel por defecto es
REPEATABLE READ.


Puede inicializar el nivel de aislamiento global por defecto
para mysqld con la opción
--transaction-isolation . Consulte

Sección 5.3.1, ?Opciones del comando
mysqld
?
.


 




Creditos: Ésta es una traducción del manual de referencia de MySQL,
que puede encontrarse en
dev.mysql.com.
El manual de referencia original de MySQL está escrito en inglés, y esta
traducción no necesariamente está tan actualizada como la versión
original. Para cualquier sugerencia sobre la traducción y para señalar
errores de cualquier tipo, no dude en dirigirse a
mysql-es@vespito.com.


 


¿Te ha parecido útil este artículo? Haz click aquí para saber más sobre los expertos de ProfesionalHosting en alojamiento web y ¡todo lo que podemos hacer por ti!

Etiquetas: