Sistema de Soporte » Manuales y Preguntas Frecuentes ( FAQ ) » Comandos transaccionales y de bloqueo de MySQL
 Buscar
Realice consulta a hostineitor
 Opciones de Articulo
 Login [Recuperar contraseña] 
Email:
Contraseña:
Recordarme:
 
 Comandos transaccionales y de bloqueo de MySQL
Respuesta

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.

 



Detalles del Articulo
Código del Articulo: 208
Fecha de Creación: 30 Aug 2007 09:02 AM

 Esta respuesta me fue util  Esta respuesta no me fue util

 Volver