Cómo utilizar transacciones en procedimientos almacenados de MySQL

ACTUALIZAR tabla2 SET resumen=@A DONDE tipo=1;

COMMITIR;

START TRANSACTION se agregó en MySQL 4.0.11; es un modo de confirmación automática. Esta es la forma recomendada de iniciar una transacción especial (temporal) porque se trata de sintaxis ANSI SQL.

Tenga en cuenta que si utiliza una tabla que no es segura para transacciones, los cambios se almacenan inmediatamente, independientemente del estado del modo de confirmación automática.

Al actualizar una tabla no transaccional, si se realiza una operación ROLLBACK, se producirá un error (ER_WARNING_NOT_COMPLETE_ROLLBACK) a modo de advertencia. Se restaurarán todas las tablas seguras para transacciones, pero las tablas que no son seguras para transacciones permanecerán sin cambios.

Si usa START TRANSACTION o SET AUTOCOMMIT=0, debe usar el registro binario de MySQL

como copia de seguridad para reemplazar el registro de actualización anterior. Las transacciones se almacenan en el registro binario como un fragmento grande encima de COMMIT

para proteger las transacciones de reversión no almacenadas. Consulte la Sección 4.9.4 Registros binarios. Si utiliza una transacción de inicio o establece AUTOCOMMIT=0

, debe utilizar el registro binario de MySQL como copia de seguridad en lugar de utilizar el registro de actualización anterior. Las transacciones se almacenan en el registro binario en fragmentos y se garantiza que las transacciones continuas no se almacenarán. Consulte la Sección 4

. 9.4 Registros binarios.

El siguiente comando finaliza automáticamente la transacción (como si se hubiera ejecutado un COMMIT antes de ejecutar este comando):

comando comando comando comando

ALTER TABLE BEGIN CREATE ÍNDICE

DROP DATABASE DROP TABLE RENAME TABLE

TRUNCATE

Puede utilizar SET TRANSACTION ISOLATION LEVEL... para cambiar el nivel de aislamiento de la transacción. Consulte la Sección 6.7.3 Sintaxis de SET TRANSACTION.

6.7.2 Sintaxis de la tabla LOCK/UNLOCK

LOCK table tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

[, tbl_name [alias AS] {LEER [LOCAL] | [LOW_PRIORITY] WRITE}....]

...

DESBLOQUEAR TABLAS

BLOQUEAR TABLAS bloquea el tabla para el hilo actual, y UNLOCK TABLES libera todos los bloqueos mantenidos por el hilo actual. Todas las tablas bloqueadas por el hilo actual se desbloquean automáticamente cuando el hilo emite otro LOCK TABLES o cierra la conexión al servidor.

Para usar LOCK TABLES en MySQL 4.0.2, debe tener el permiso global LOCK TABLES y

el permiso SELECT en la tabla relacionada. En MySQL 3.23, necesita permisos SELECCIONAR, INSERTAR, ELIMINAR y ACTUALIZAR en la tabla.

La razón principal para utilizar una tabla LOCK es simular el procesamiento de transacciones u obtener velocidades más rápidas al actualizar la tabla. Esto se explicará con más detalle más adelante.

Si un hilo adquiere un bloqueo READ en una tabla, ese hilo (y todos los demás hilos) solo pueden leer datos de la tabla. Si un subproceso adquiere un bloqueo de ESCRITURA en una tabla, solo el subproceso propietario del bloqueo puede leer y escribir en la tabla. Todos los demás hilos serán bloqueados.

La diferencia entre READ LOCAL y READ es que READ LOCAL permite ejecutar declaraciones INSERT no conflictivas cuando se carga el bloqueo. Esto aún no funcionará si está manipulando el archivo de la base de datos desde fuera de MySQL mientras se carga el bloqueo.

Cuando se utiliza LOCK TABLES, todas las tablas que se utilizarán deben estar bloqueadas y se deben utilizar los mismos alias utilizados en la consulta. Si una tabla se usa varias veces en una consulta (usando alias), se debe bloquear un bloqueo para cada alias.

El bloqueo de ESCRITURA tiene permisos más altos que el bloqueo de LECTURA y garantiza que las actualizaciones se procesen lo más rápido posible. Esto significa que si un subproceso adquiere un bloqueo de LECTURA

y otro subproceso solicita un bloqueo de ESCRITURA, las solicitudes de bloqueo de READ simultáneas esperarán a que el subproceso de ESCRITURA adquiera el bloqueo y lo libere. Puede utilizar el bloqueo

LOW_PRIORITY WRITE, que permitirá que otros subprocesos obtengan el bloqueo READ mientras este hilo espera el bloqueo WRITE. El bloqueo LOW_PRIORITY WRITE se puede usar solo si está seguro de que esta es la última vez que ningún hilo mantendrá el bloqueo READ.

Las tablas LOCK funcionan de la siguiente manera:

Todas las tablas bloqueadas se ordenan en un orden definido internamente (que no es explícito desde la perspectiva del usuario).

Si una tabla está bloqueada tanto por un bloqueo de lectura como por un bloqueo de escritura, coloque el bloqueo de escritura antes del bloqueo de lectura.

Solo se bloquea una tabla a la vez hasta que el hilo adquiere todos los bloqueos.

Esta solución es para garantizar que el punto muerto se libere después de bloquear la tabla. Hay algunas otras cosas que necesitas saber sobre este patrón:

Si usas un bloqueo LOW_PRIORITY WRITE en una tabla, esto significa que MySQL esperará el bloqueo hasta que ningún subproceso solicite READ

Bloquear. Cuando un subproceso adquiere un bloqueo de ESCRITURA y espera adquirir un bloqueo en la siguiente tabla en la lista de tablas bloqueadas, todos los demás subprocesos esperan a que se libere el bloqueo de ESCRITURA

.

Si esto causa problemas graves para su aplicación, debería considerar convertir algunas tablas en tablas seguras para transacciones.

Puedes utilizar KILL para eliminar de forma segura subprocesos que utilizan bloqueos de tabla. Consulte la Sección 4.5.5 Sintaxis KILL.

Tenga en cuenta que no debe bloquear una tabla que esté usando INSERT DELAYED. Esto se debe a que en este caso el INSERT se realiza en un hilo aparte.

Normalmente, no es necesario bloquear ninguna tabla porque todas las declaraciones UPDATE individuales son atómicas; otros subprocesos no pueden interferir con la declaración SQL que se está ejecutando actualmente. Si desea bloquear tablas de todos modos, aquí tiene algunos escenarios:

Si está ejecutando muchas operaciones en varias tablas, será más rápido bloquear la tabla que está utilizando. Por supuesto, esto también tiene desventajas: otros subprocesos no podrán actualizar las tablas bloqueadas por "lectura" y otros subprocesos no podrán leer las tablas bloqueadas por "escritura". La razón por la que se ejecuta más rápido bajo LOCK TABLES es que MySQL no volca ni borra el caché de claves de la tabla bloqueada antes de llamar a UNLOCK TABLES (normalmente las claves se volcan y borran después de cada caché de declaraciones SQL). Esto acelerará las inserciones, actualizaciones y eliminaciones en

tablas MyISAM.

Si el motor de almacenamiento que está utilizando en MySQL no admite transacciones, debe usar LOCK TABLES si desea asegurarse de que ningún otro subproceso se interponga entre SELECT y UPDATE. El siguiente ejemplo muestra que para una ejecución segura, se requiere LOCK TABLES aquí:

mysqlgt; LOCK TABLES trans READ, customer WRITE;

mysqlgt SELECT SUM(value) FROM trans WHERE customer_id; =some_id;

mysqlgt; ACTUALIZAR cliente SET total_value=sum_from_previous_statement

-gt; DONDE customer_id=some_id;

DESBLOQUEAR TABLAS;

Si no se utiliza LOCK TABLES, otro hilo puede insertar nuevas filas en la tabla trans durante la ejecución de las declaraciones SELECT y UPDATE.

Puedes usar actualizaciones incrementales (ACTUALIZAR valor SET del cliente=valor nuevo_valor) o la función LAST_INSERT_ID() para evitar el uso de LOCK TABLES en muchas situaciones.

También puede utilizar las funciones de bloqueo a nivel de usuario GET_LOCK() y RELEASE_LOCK() para resolver la situación en la que el bloqueo se almacena en una tabla hash en el servidor y utilizar

pthread_mutex_lock() y El método de pthread_mutex_unlock() se ejecuta a alta velocidad. Consulte la Sección 6.3.6.2

Funciones auxiliares.

Para obtener más información sobre los esquemas de bloqueo, consulte la Sección 5.3.1 Cómo MySQL bloquea tablas.

Puede utilizar el comando FLUSH TABLES WITH READ LOCK para bloquear todas las tablas en todas las bases de datos con bloqueos de lectura. Consulte la Sección 4.5.3 Sintaxis de FLUSH. Si su sistema de archivos (como Veritas) puede crear instantáneas de archivos a tiempo, esta puede ser una forma muy conveniente de realizar copias de seguridad.

Nota: LOCK TABLES no es seguro para las transacciones y confirmará automáticamente todas las transacciones activas antes de intentar bloquear la tabla.

6.7.3 SET TRANSACTION sintaxis

ESTABLECER [GLOBAL | SESIÓN] NIVEL DE AISLAMIENTO DE TRANSACCIÓN

{ LEER NO COMPROMETIDO | LEER COMPROMETIDO | p>

Establezca el nivel de aislamiento de la transacción globalmente, para toda la sesión o para la siguiente transacción.

El comportamiento predeterminado es establecer el nivel de aislamiento para la siguiente transacción (no iniciada). Si utiliza la palabra clave GLOBAL

, la declaración establece el nivel de aislamiento de transacción global predeterminado para todas las conexiones nuevas establecidas en ese punto. Para hacer esto, se requiere permiso SUPER. Usando la palabra clave SESSION

, la declaración establecerá el nivel de aislamiento de transacción predeterminado para todas las transacciones futuras ejecutadas por la conexión actual.

Puedes usar --transaction-isolation=... para establecer el nivel de aislamiento global predeterminado para mysqld. Consulte la Sección 4.1.1 Opciones de línea de comandos de mysqld

Puede establecer el nivel de aislamiento global predeterminado para mysqld usando --transaction-isolation=... .