MySQL 5.1 admite bloqueo a nivel de tabla para tablas MyISAM y MEMORY, bloqueo a nivel de página para tablas BDB e InnoDB. Bloqueo a nivel de fila. En muchos casos, es posible adivinar, basándose en el entrenamiento, qué tipo de bloqueo es mejor para una aplicación, pero generalmente es difícil saber si un tipo de bloqueo determinado es mejor que otro. Todo depende de la aplicación, diferentes partes de la aplicación pueden requerir diferentes tipos de bloqueo. Para determinar si desea utilizar un motor de almacenamiento de bloqueo a nivel de fila, debe observar qué hace su aplicación y qué combinación de declaraciones de selección y actualización utiliza. Por ejemplo, la mayoría de las aplicaciones web realizan muchas selecciones y pocas eliminaciones, solo actualizaciones de valores clave y solo algunas inserciones de tablas específicas. La configuración básica de MySQL MyISAM está bien ajustada.
En MySQL, para los motores de almacenamiento que utilizan bloqueo a nivel de tabla, no habrá punto muerto cuando la tabla esté bloqueada. Esto se gestiona solicitando siempre todos los bloqueos necesarios inmediatamente al comienzo de una consulta y bloqueando siempre la tabla en el mismo orden.
Para ESCRITURA, el método de bloqueo de tabla utilizado por MySQL funciona de la siguiente manera:
◆ Si no hay ningún bloqueo en la tabla, colóquele un bloqueo de escritura.
◆ De lo contrario, coloque la solicitud de bloqueo en la cola de bloqueo de escritura.
Para READ, el método de bloqueo utilizado por MySQL funciona de la siguiente manera:
◆ Si no hay un bloqueo de escritura en la tabla, colóquele un bloqueo de lectura.
◆ De lo contrario, coloque la solicitud de bloqueo en la cola de bloqueo de lectura.
Cuando se libera un bloqueo, el bloqueo puede ser adquirido por subprocesos en la cola de bloqueo de escritura y luego por subprocesos en la cola de bloqueo de lectura.
Esto significa que si tiene muchas actualizaciones en una tabla, la instrucción SELECT esperará hasta que no haya más actualizaciones.
Si las declaraciones INSERT no entran en conflicto, puede mezclar libremente declaraciones INSERT y SELECT paralelas para una tabla MyISAM sin bloquear.
InnoDB usa bloqueo de filas y BDB usa bloqueo de páginas. Con ambos motores de almacenamiento, es posible que se produzcan puntos muertos. Esto se debe a que InnoDB obtiene automáticamente bloqueos de fila y BDB obtiene bloqueos de página durante el procesamiento de la declaración SQL, en lugar de cuando se inicia la transacción.
Ventajas del bloqueo a nivel de fila:
· Sólo hay unos pocos conflictos de bloqueo cuando se accede a diferentes filas en muchos subprocesos.
· Revertir con sólo una pequeña cantidad de cambios.
· Una sola fila puede permanecer bloqueada durante mucho tiempo.
Desventajas del bloqueo a nivel de fila:
· Ocupa más memoria que el bloqueo a nivel de página o de tabla.
· Cuando se utiliza en grandes porciones de una tabla, es más lento que el bloqueo a nivel de página o de tabla porque debe adquirir más bloqueos.
· Si realiza con frecuencia operaciones GROUP BY en la mayoría de sus datos o debe escanear con frecuencia toda la tabla, será significativamente más lento que otros bloqueos.
· Con el bloqueo de alto nivel, también puedes escalar fácilmente tu aplicación al admitir diferentes tipos de bloqueo porque el costo del bloqueo es menor que el bloqueo a nivel de fila.
Los bloqueos de tabla tienen prioridad sobre los bloqueos de nivel de página o de fila cuando:
· La mayoría de las instrucciones de la tabla se utilizan para lecturas.
· Leer y actualizar con claves estrictas, puede actualizar o eliminar una fila que se puede extraer con una sola clave de lectura:
• ACTUALIZAR columna_nombre_tbl = valor DONDE clave_col_única = valor_clave
• ELIMINAR DE nombre_tbl DONDE col_clave_única = valor_clave;
SELECCIONAR combinado con
;declaraciones INSERT paralelas y muy pocas declaraciones UPDATE o DELETE.
· Hay muchos escaneos u operaciones GROUP BY en toda la tabla sin ninguna operación de escritura.
Opciones que difieren del bloqueo a nivel de fila o a nivel de página:
· Control de versiones (por ejemplo, la técnica utilizada en MySQL para inserciones paralelas), donde puede haber una operación de escritura y muchas operaciones de lectura al mismo tiempo. Esto significa que la base de datos o tabla admite diferentes vistas de los datos, dependiendo de cuándo comienza el acceso. Otros términos comunes son "seguimiento del tiempo", "copia sobre escritura" o "copia bajo demanda".
· La replicación bajo demanda tiene prioridad sobre el bloqueo a nivel de página o de fila en muchos casos. Sin embargo, en el peor de los casos, puede utilizar más memoria que el bloqueo normal.
· Además del bloqueo a nivel de fila, puedes utilizar el bloqueo a nivel de aplicación, como GET_LOCK() y RELEASE_LOCK() en MySQL. Estos son bloqueos de aviso y sólo funcionarán en aplicaciones que funcionen bien.
Para lograr la máxima velocidad de bloqueo, MySQL utiliza el bloqueo de tablas (en lugar de bloqueo de páginas, filas o columnas) para todos los motores de almacenamiento excepto InnoDB y BDB. Para las tablas InnoDB y BDB, MySQL solo usa el bloqueo de tabla si la bloquea explícitamente con LOCK TABLES si no usa LOCK TABLES, porque InnoDB usa bloqueo automático a nivel de fila y BDB usa bloqueo a nivel de página para garantizar el aislamiento de transacciones.
Pero para tablas grandes, el bloqueo de tablas es mejor que el bloqueo de filas para la mayoría de las aplicaciones, pero tiene algunos inconvenientes. El bloqueo de tabla permite que muchos subprocesos lean de una tabla simultáneamente, pero si un subproceso quiere escribir en la tabla, primero debe obtener acceso exclusivo. Durante la actualización, todos los demás subprocesos que quieran acceder a la tabla deben esperar hasta que se complete la actualización.
Las actualizaciones de tablas generalmente se consideran más importantes que las recuperaciones de tablas, por lo que se les da mayor prioridad. Esto debería garantizar que la actividad que actualiza una tabla no pueda morir de hambre, incluso si hay una gran actividad SELECT en la tabla.
El bloqueo de la tabla puede causar problemas en situaciones como cuando un subproceso está esperando porque el disco duro está lleno y debe haber espacio libre antes de que el subproceso pueda procesarse. En este caso, todos los subprocesos que desean acceder a la tabla en cuestión también se ponen en estado de espera hasta que haya más espacio disponible en el disco duro.
El bloqueo de tablas también es problemático en las siguientes situaciones:
· Un cliente emite una consulta de larga duración.
· Luego, otro cliente actualiza la misma tabla. El cliente debe esperar hasta que se complete SELECT.
· Otro cliente emite otra declaración SELECT en la misma tabla. Debido a que ACTUALIZAR tiene una prioridad más alta que SELECCIONAR, la instrucción SELECT espera a que se complete la ACTUALIZACIÓN y espera a que se complete el primer SELECT.
Algunos métodos se describen a continuación para evitar o reducir la contención causada por bloqueos de tabla:
· Intente hacer que la instrucción SELECT se ejecute más rápido. Es posible que tengas que crear algunas tablas de resumen para hacer esto.
· Inicie mysqld con --low-priority-updates. Esto dará a todas las declaraciones que actualizan (modifican) una tabla una prioridad menor que las declaraciones SELECT. En este caso, la segunda instrucción SELECT de la situación anterior se ejecutará antes de la instrucción UPDATE sin esperar a que se complete la primera SELECT.
· Puedes usar la instrucción SET LOW_PRIORITY_UPDATES=1 para especificar que todas las actualizaciones en una conexión específica deben usar baja prioridad.
· Puedes utilizar el atributo LOW_PRIORITY para dar una prioridad más baja a una instrucción INSERT, UPDATE o DELETE específica.
· Puedes utilizar el atributo HIGH_PRIORITY para dar una prioridad más alta a una declaración SELECT específica.
· Inicie mysqld especificando un valor bajo para la variable de sistema max_write_lock_count para forzar a MySQL a aumentar temporalmente la prioridad de todas las declaraciones SELECT que esperan una tabla después de que se haya completado un número específico de inserciones. Esto permite que se otorgue un bloqueo de LECTURA después de un cierto número de bloqueos de ESCRITURA.
· Si tiene problemas con INSERT combinado con SELECT, cambie a usar las nuevas tablas MyISAM ya que admiten SELECT e INSERT simultáneos.
· Si combinas inserciones y eliminaciones en la misma tabla, INSERT DELAYED será de gran ayuda.
· Si tienes problemas al mezclar declaraciones SELECT y DELETE en la misma tabla, la opción LIMIT de DELETE puede ayudarte.
· El uso de SQL_BUFFER_RESULT para declaraciones SELECT puede ayudar a acortar los tiempos de bloqueo de la tabla.
· El código de bloqueo en mysys/thr_lock.c se puede cambiar para usar una sola cola. En este caso, los bloqueos de escritura y de lectura tendrán la misma prioridad, lo que puede resultar útil para algunas aplicaciones.
Aquí hay algunos consejos relacionados con el bloqueo de tablas en MySQL:
· Si no mezclas actualizaciones con selecciones que requieren verificar muchas filas en la misma tabla, puedes realizar operaciones paralelas.
· Puedes usar LOCK TABLES para mejorar la velocidad, porque muchas actualizaciones en un bloqueo son mucho más rápidas que las actualizaciones sin bloqueos. También puede resultar útil dividir el contenido de la tabla en varias tablas.
· Si encuentra problemas de velocidad al bloquear tablas en MySQL, puede convertir la tabla a una tabla InnoDB o BDB para mejorar el rendimiento.