Instituto Tecnológico Superior de la Montaña
INGENIERíA EN SISTEMA COMPUTACIONALES
Nombre de la materia:
Taller de base de datos
TRABAJO:
UNIDAD 4 y 5
docente:
Ing. Francisco Castro Hurtado
alumno:
Alberto Rodríguez Casimiro
Grado grupo
5º Semestre “A”
Tlapa de Comonfort, Gro; a 14 de diciembre del 2009
Unidad 4 CONTROL DE TRANSACCIONES
Los sistemas que tratan el problema de control de concurrencia permiten que sus usuarios asuman que cada una de sus aplicaciones se ejecutan atómicamente, como si no existieran otras aplicaciones ejecutándose concurrentemente.
Esta abstracción de una ejecución atómica y confiable de una aplicación se conoce como una transacción.
Un algoritmo de control de concurrencia asegura que las transacciones se ejecuten atómicamente controlando la intercalación de transacciones concurrentes, para dar la ilusión de que las transacciones se ejecutan serialmente, una después de la otra, sin ninguna intercalación. Las ejecuciones intercaladas cuyos efectos son los mismos que las ejecuciones seriales son denominadas serializables y son correctos ya que soportan la ilusión de la atomicidad de las transacciones.
El concepto principal es el de transacción. Informalmente, una transacción es la ejecución de ciertas instrucciones que accesan a una base de datos compartida. El objetivo del control de concurrencia y recuperación es asegurar que dichas transacciones se ejecuten atómicamente, es decir:
Cada transacción accede a información compartida sin interferir con otras transacciones, y si una transacción termina normalmente, todos sus efectos son permanentes, en caso contrario no tiene afecto alguno.
Una base de datos está en un estado consistente si obedece todas las restricciones de integridad (significa que cuando un registro en una tabla haga referencia a un registro en otra tabla, el registro correspondientes debe existir) definidas sobre ella.
Los cambios de estado ocurren debido a actualizaciones, inserciones y supresiones de información. Por supuesto, se quiere asegurar que la base de datos nunca entre en un estado de inconsistencia.
Sin embargo, durante la ejecución de una transacción, la base de datos puede estar temporalmente en un estado inconsistente.
El punto importante aquí es asegurar que la base de datos regresa a un estado consistente al fin de la ejecución de una transacción.
4.1PROPIEDADES DE UNA TRANSACIÓN
Una transacción en un Sistema de Gestión de Bases de Datos (SGBD), es un conjunto de órdenes que se ejecutan formando una unidad de trabajo, es decir, en forma indivisible o atómica.
Un SGBD se dice transaccional, si es capaz de mantener la integridad de los datos, haciendo que estas transacciones no puedan finalizar en un estado intermedio. Cuando por alguna causa el sistema debe cancelar la transacción, empieza a deshacer las órdenes ejecutadas hasta dejar la base de datos en su estado inicial (llamado punto de integridad), como si la orden de la transacción nunca se hubiese realizado.
Para esto, el lenguaje de consulta de datos SQL (Structured Query Language), provee los mecanismos para especificar que un conjunto de acciones deben constituir una transacción.
Ø BEGIN TRAN: Especifica que va a empezar una transacción.
Ø COMMIT TRAN: Le indica al motor que puede considerar la transacción completada con éxito.
Ø ROLLBACK TRAN: Indica que se ha alcanzado un fallo y que debe restablecer la base al punto de integridad.
En un sistema ideal, las transacciones deberían garantizar todas las propiedades ACID; en la práctica, a veces alguna de estas propiedades se simplifica o debilita con vistas a obtener un mejor rendimiento.
El control de transacciones concurrentes en una base de datos brinda un eficiente desempeño del Sistema de Base de Datos, puesto que permite controlar la ejecución de transacciones que operan en paralelo, accesando a información compartida y, por lo tanto, interfiriendo potencialmente unas con otras.
El hecho de reservar un asiento en una avión mediante un sistema basado en aplicaciones web, cuando decenas de personas en el mundo pueden reservarlo también, nos da una idea de lo importante y crucial que es el control de concurrencia en un sistema de base de datos a mediana o gran escala.
Ø Ejemplo en el que podemos observar la incidencia del control de concurrencia en el siguiente: en una Base de Datos bancaria podría ocurrir que se paguen dos cheques en forma simultánea sobre una cuenta que no tiene saldo suficiente para cubrirlos en su totalidad, esto es posible evitarlo si se tiene un control de concurrencia.
TRANSACCIONES
Los sistemas que tratan el problema de control de concurrencia permiten que sus usuarios asuman que cada una de sus aplicaciones se ejecutan atómicamente, como si no existieran otras aplicaciones ejecutándose concurrentemente.
Esta abstracción de una ejecución atómica y confiable de una aplicación se conoce como una transacción.
Un algoritmo de control de concurrencia asegura que las transacciones se ejecuten atómicamente controlando la intercalación de transacciones concurrentes, para dar la ilusión de que las transacciones se ejecutan serialmente, una después de la otra, sin ninguna intercalación. Las ejecuciones intercaladas cuyos efectos son los mismos que las ejecuciones seriales son denominadas serializables y son correctos ya que soportan la ilusión de la atomicidad de las transacciones.
El concepto principal es el de transacción. Informalmente, una transacción es la ejecución de ciertas instrucciones que accesan a una base de datos compartida. El objetivo del control de concurrencia y recuperación es asegurar que dichas transacciones se ejecuten atómicamente, es decir:
Cada transacción accede a información compartida sin interferir con otras transacciones, y si una transacción termina normalmente, todos sus efectos son permanentes, en caso contrario no tiene afecto alguno.
Una base de datos está en un estado consistente si obedece todas las restricciones de integridad (significa que cuando un registro en una tabla haga referencia a un registro en otra tabla, el registro correspondientes debe existir) definidas sobre ella.
Los cambios de estado ocurren debido a actualizaciones, inserciones y supresiones de información. Por supuesto, se quiere asegurar que la base de datos nunca entre en un estado de inconsistencia.
Sin embargo, durante la ejecución de una transacción, la base de datos puede estar temporalmente en un estado inconsistente.
El punto importante aquí es asegurar que la base de datos regresa a un estado consistente al fin de la ejecución de una transacción.
Los sistemas que tratan el problema de control de concurrencia permiten que sus usuarios asuman que cada una de sus aplicaciones se ejecutan atómicamente, como si no existieran otras aplicaciones ejecutándose concurrentemente.
Esta abstracción de una ejecución atómica y confiable de una aplicación se conoce como una transacción.
Un algoritmo de control de concurrencia asegura que las transacciones se ejecuten atómicamente controlando la intercalación de transacciones concurrentes, para dar la ilusión de que las transacciones se ejecutan serialmente, una después de la otra, sin ninguna intercalación. Las ejecuciones intercaladas cuyos efectos son los mismos que las ejecuciones seriales son denominadas serializables y son correctos ya que soportan la ilusión de la atomicidad de las transacciones.
El concepto principal es el de transacción. Informalmente, una transacción es la ejecución de ciertas instrucciones que accesan a una base de datos compartida. El objetivo del control de concurrencia y recuperación es asegurar que dichas transacciones se ejecuten atómicamente, es decir:
Cada transacción accede a información compartida sin interferir con otras transacciones, y si una transacción termina normalmente, todos sus efectos son permanentes, en caso contrario no tiene afecto alguno.
Una base de datos está en un estado consistente si obedece todas las restricciones de integridad (significa que cuando un registro en una tabla haga referencia a un registro en otra tabla, el registro correspondientes debe existir) definidas sobre ella.
Los cambios de estado ocurren debido a actualizaciones, inserciones y supresiones de información. Por supuesto, se quiere asegurar que la base de datos nunca entre en un estado de inconsistencia.
Sin embargo, durante la ejecución de una transacción, la base de datos puede estar temporalmente en un estado inconsistente.
El punto importante aquí es asegurar que la base de datos regresa a un estado consistente al fin de la ejecución de una transacción.
PROPIEDADES FUNDAMENTALES DE UNA TRANSACCIÓN:
1. Atomicidad Se refiere al hecho de que una transacción se trata como una unidad de operación.
2. Por lo tanto, o todas las acciones de la transacción se realizan o ninguna de ellas se lleva a cabo. La atomicidad requiere que si una transacción se interrumpe por una falla, sus resultados parciales sean anulados.
3. Consistencia La consistencia de una transacción es simplemente su correctitud. En otras palabras, una transacción es un programa correcto que lleva a la base de datos de un estado consistente a otro con la misma característica. Debido a esto, las transacciones no violan las restricciones de integridad de una base de datos.
4. Aislamiento Una transacción en ejecución no puede revelar sus resultados a otras transacciones concurrentes antes de finalizar.
5. Más aún, si varias transacciones se ejecutan concurrentemente, los resultados deben ser los mismos que si ellas se hubieran ejecutado de manera secuencial.
6. Permanencia Es la propiedad de las transacciones que asegura que una vez que una transacción finaliza exitosamente, sus resultados son permanentes y no pueden ser borrados de la base de datos por alguna falla posterior.
7. Por lo tanto, los sistemas manejadores de base de datos aseguran que los resultados de una transacción sobrevivirán a fallas del sistema. Esta propiedad motiva el aspecto de recuperación de base de datos, el cual trata sobre cómo recuperar la base de datos a un estado consistente donde todas las acciones que han finalizado con éxito queden reflejadas en la base.
8. En esencia, lo que se persigue con el procesamiento de transacciones es, por una parte obtener una transparencia adecuada de las acciones concurrentes a una base de datos y por otra, manejar adecuadamente las fallas que se puedan presentar en una base de datos.
9. La mayoría de medianas y grandes compañías modernas utilizan el procesamiento de transacciones para sus sistemas de producción, y es tan imprescindible que las organizaciones no pueden funcionar en ausencia de él.
El procesamiento de transacciones representa una enorme y significativa porción del mercado de los sistemas informáticos (más de cincuenta billones de dólares al año) y es, probablemente, la aplicación simple más amplia de las computadoras.
Además, se ha convertido en el elemento que facilita el comercio electrónico.
Como puede percibirse, el procesamiento de transacciones es una de las tareas más importantes dentro de un sistema de base de datos, pero a la vez, es una de las más difíciles de manejar debido a diversos aspectos, tales como:
10. Confiabilidad Puesto que los sistemas de base de datos en línea no pueden fallar.
11. Disponibilidad Debido a que los sistemas de base de datos en línea deben estar actualizados correctamente todo el tiempo.
12. Tiempos de Respuesta En sistemas de este tipo, el tiempo de respuesta de las transacciones no debe ser mayor a doce segundos.
13. Throughput Los sistemas de base de datos en línea requieren procesar miles de transacciones por segundo.
14. Atomicidad En el procesamiento de transacciones no se aceptan resultados parciales.
15. Permanencia No se permite la eliminación en la base de datos de los efectos de una transacción que ha culminado con éxito.
Para ejecutar altas, Bajas y Eliminaciones en MYSQL, este es el siguiente código
1. Atomicidad Se refiere al hecho de que una transacción se trata como una unidad de operación.
2. Por lo tanto, o todas las acciones de la transacción se realizan o ninguna de ellas se lleva a cabo. La atomicidad requiere que si una transacción se interrumpe por una falla, sus resultados parciales sean anulados.
3. Consistencia La consistencia de una transacción es simplemente su correctitud. En otras palabras, una transacción es un programa correcto que lleva a la base de datos de un estado consistente a otro con la misma característica. Debido a esto, las transacciones no violan las restricciones de integridad de una base de datos.
4. Aislamiento Una transacción en ejecución no puede revelar sus resultados a otras transacciones concurrentes antes de finalizar.
5. Más aún, si varias transacciones se ejecutan concurrentemente, los resultados deben ser los mismos que si ellas se hubieran ejecutado de manera secuencial.
6. Permanencia Es la propiedad de las transacciones que asegura que una vez que una transacción finaliza exitosamente, sus resultados son permanentes y no pueden ser borrados de la base de datos por alguna falla posterior.
7. Por lo tanto, los sistemas manejadores de base de datos aseguran que los resultados de una transacción sobrevivirán a fallas del sistema. Esta propiedad motiva el aspecto de recuperación de base de datos, el cual trata sobre cómo recuperar la base de datos a un estado consistente donde todas las acciones que han finalizado con éxito queden reflejadas en la base.
8. En esencia, lo que se persigue con el procesamiento de transacciones es, por una parte obtener una transparencia adecuada de las acciones concurrentes a una base de datos y por otra, manejar adecuadamente las fallas que se puedan presentar en una base de datos.
9. La mayoría de medianas y grandes compañías modernas utilizan el procesamiento de transacciones para sus sistemas de producción, y es tan imprescindible que las organizaciones no pueden funcionar en ausencia de él.
El procesamiento de transacciones representa una enorme y significativa porción del mercado de los sistemas informáticos (más de cincuenta billones de dólares al año) y es, probablemente, la aplicación simple más amplia de las computadoras.
Además, se ha convertido en el elemento que facilita el comercio electrónico.
Como puede percibirse, el procesamiento de transacciones es una de las tareas más importantes dentro de un sistema de base de datos, pero a la vez, es una de las más difíciles de manejar debido a diversos aspectos, tales como:
10. Confiabilidad Puesto que los sistemas de base de datos en línea no pueden fallar.
11. Disponibilidad Debido a que los sistemas de base de datos en línea deben estar actualizados correctamente todo el tiempo.
12. Tiempos de Respuesta En sistemas de este tipo, el tiempo de respuesta de las transacciones no debe ser mayor a doce segundos.
13. Throughput Los sistemas de base de datos en línea requieren procesar miles de transacciones por segundo.
14. Atomicidad En el procesamiento de transacciones no se aceptan resultados parciales.
15. Permanencia No se permite la eliminación en la base de datos de los efectos de una transacción que ha culminado con éxito.
Para ejecutar altas, Bajas y Eliminaciones en MYSQL, este es el siguiente código
MySQLTransaction TransMySql;Boolean logico;MySQLConnection cnMy = new MySQLConnection(new MySQLConnectionString(this.strServidor, this.strDB, this.strUser, this.strPass).AsString);MySQLCommand cmdCMD = new MySQLCommand();MySQLCommand cmdCMDAux;cmdCMD = cmdCMDAux;cmdCMDAux.Connection = cnMy;cnMy.Open();cmdCMDAux.CommandTimeout = TiempoConexion;if (boolTrans == true){TransMySql = cnMy.BeginTransaction();cmdCMDAux.Transaction = TransMySql;}try{cmdCMDAux.CommandType = TipoComando;cmdCMDAux.CommandText = strSQL;cmdCMDAux.ExecuteNonQuery();if (boolTrans == true){TransMySql.Commit();}logico = true;}......pero me sale error en :...if (boolTrans == true){TransMySql = cnMy.BeginTransaction();cmdCMDAux.Transaction = TransMySql;}
Ejemplo de transacción
Lecturas consistentes que no bloquean
Lectura consistente significa que InnoDB utiliza su característica de multiversión para presentar a una consulta una captura de la base de datos en un momento determinado. La consulta ve los cambios realizados exactamente por aquellas transacciones confirmadas antes de ese momento, y no los cambios hechos con posterioridad o por transacciones no confirmadas. La excepción a esto es que la consulta ve los cambios efectuados por la transacción a donde pertenece.
Si se está ejecutando con el nivel de aislamiento predeterminado REPEATABLE READ, entonces todas las lecturas consistentes dentro de la misma transacción leen la captura creada por la primer lectura en esa transacción. Se puede refrescar esta captura confirmando la transacción actual y emitiendo nuevas consultas.
Lectura consistente es el modo por defecto en el cual InnoDB procesa las sentencias SELECT en los niveles de aislamiento READ COMMITTED y REPEATABLE READ. Una lectura consistente no establece ningún bloqueo en las tablas a las que accede, y, por lo tanto, otros usuarios están libres para modificar las tablas sobre las que se está haciendo la lectura consistente.
Cómo utilizar transacciones en InnoDB con distintas APIs
En forma predeterminada, cada cliente se que conecta al servidor MySQL comienza con el modo de autocommit habilitado, lo cual automáticamente confirma (commit) cada sentencia SQL ejecutada. Para utilizar transacciones de múltiples sentencias se puede deshabilitar el modo autocommit con la sentencia SQL SET AUTOCOMMIT = 0 y emplear COMMIT y ROLLBACK para confirmar o cancelar la transacción. Si se desea dejar activado autocommit, se pueden encerrar las transacciones entre las sentencias START TRANSACTION y COMMIT o ROLLBACK. El siguiente ejemplo muestra dos transacciones. La primera se confirma, la segunda se cancela.
shell> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.50-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM CUSTOMER;
+------+--------+
A B
+------+--------+
10 Heikki
+------+--------+
1 row in set (0.00 sec)
Mysql>
En APIs como PHP, Perl DBI/DBD, JDBC, ODBC, o la interface de llamadas C estándar de MySQL, se pueden enviar sentencias de control de transacciones como COMMIT al servidor MySQL en forma de cadenas, igual que otras sentencias SQL como SELECT o INSERT. Algunas APIs también ofrecen funciones o métodos especiales para confirmación y cancelación de transacciones.
Lecturas consistentes que no bloquean
Lectura consistente significa que InnoDB utiliza su característica de multiversión para presentar a una consulta una captura de la base de datos en un momento determinado. La consulta ve los cambios realizados exactamente por aquellas transacciones confirmadas antes de ese momento, y no los cambios hechos con posterioridad o por transacciones no confirmadas. La excepción a esto es que la consulta ve los cambios efectuados por la transacción a donde pertenece.
Si se está ejecutando con el nivel de aislamiento predeterminado REPEATABLE READ, entonces todas las lecturas consistentes dentro de la misma transacción leen la captura creada por la primer lectura en esa transacción. Se puede refrescar esta captura confirmando la transacción actual y emitiendo nuevas consultas.
Lectura consistente es el modo por defecto en el cual InnoDB procesa las sentencias SELECT en los niveles de aislamiento READ COMMITTED y REPEATABLE READ. Una lectura consistente no establece ningún bloqueo en las tablas a las que accede, y, por lo tanto, otros usuarios están libres para modificar las tablas sobre las que se está haciendo la lectura consistente.
Cómo utilizar transacciones en InnoDB con distintas APIs
En forma predeterminada, cada cliente se que conecta al servidor MySQL comienza con el modo de autocommit habilitado, lo cual automáticamente confirma (commit) cada sentencia SQL ejecutada. Para utilizar transacciones de múltiples sentencias se puede deshabilitar el modo autocommit con la sentencia SQL SET AUTOCOMMIT = 0 y emplear COMMIT y ROLLBACK para confirmar o cancelar la transacción. Si se desea dejar activado autocommit, se pueden encerrar las transacciones entre las sentencias START TRANSACTION y COMMIT o ROLLBACK. El siguiente ejemplo muestra dos transacciones. La primera se confirma, la segunda se cancela.
shell> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.50-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM CUSTOMER;
+------+--------+
A B
+------+--------+
10 Heikki
+------+--------+
1 row in set (0.00 sec)
Mysql>
En APIs como PHP, Perl DBI/DBD, JDBC, ODBC, o la interface de llamadas C estándar de MySQL, se pueden enviar sentencias de control de transacciones como COMMIT al servidor MySQL en forma de cadenas, igual que otras sentencias SQL como SELECT o INSERT. Algunas APIs también ofrecen funciones o métodos especiales para confirmación y cancelación de transacciones.
4.2 GRADOS DE CONSISTENCIA
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 realiza otro LOCK TABLES, o cuando la conexión con el servidor se cierra.
Un bloqueo de tabla protege sólo contra lecturas inapropiadas 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
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:
Ordena todas las tablas a ser bloqueadas en un orden definido internamente. Desde el punto de vista del usuario, este orden es indefinido.
Si una tabla se bloquea con bloqueo de escritura y lectura, pone el bloqueo de lectura antes del de escritura.
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.
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() , 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.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 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.
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 realiza otro LOCK TABLES, o cuando la conexión con el servidor se cierra.
Un bloqueo de tabla protege sólo contra lecturas inapropiadas 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
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:
Ordena todas las tablas a ser bloqueadas en un orden definido internamente. Desde el punto de vista del usuario, este orden es indefinido.
Si una tabla se bloquea con bloqueo de escritura y lectura, pone el bloqueo de lectura antes del de escritura.
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.
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() , 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.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 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.
4.3 NIVELES DE AISLAMIENTO
En los términos de los niveles de aislamiento de transacciones SQL:1992, el nivel predeterminado en InnoDB es REPEATABLE READ. En MySQL 5.0, InnoDB ofrece los cuatro niveles de aislamiento de transacciones descriptos por el estándar SQL. Se puede establecer el nivel predeterminado de aislamiento por todas las conexiones mediante el uso de la opción --transaction-isolation en la línea de comandos o en ficheros de opciones. Por ejemplo, se puede establecer la opción en la sección [mysqld] de my.cnf de este modo:
[mysqld]
transaction-isolation = {READ-UNCOMMITTED READ-COMMITTED
REPEATABLE-READ SERIALIZABLE}
Un usuario puede cambiar el nivel de aislamiento de una sesión individual o de todas las nuevas conexiones con la sentencia SET TRANSACTION. Su sintaxis es la siguiente:
SET [SESSION GLOBAL] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED READ COMMITTED
REPEATABLE READ SERIALIZABLE}
Nótese que se usan guiones en los nombres de niveles de la opción --transaction-isolation, pero no en la sentencia SET TRANSACTION.
El comportamiento predeterminado es establecer el nivel de aislamiento a partir de la próxima transacción que se inicie. Si se emplea la palabra clave GLOBAL, la sentencia establece el nivel predeterminado de la transacción globalmente para todas las nuevas conexiones creadas a partir de ese punto (pero no en las existentes). Se necesita el privilegio SUPER para hacer esto. Utilizando la palabra clave SESSION se establece el nivel de transacción para todas las futuras transacciones ejecutadas en la actual conexión.
Cualquier cliente es libre de cambiar el nivel de aislamiento de la sesión (incluso en medio de una transacción), o el nivel de aislamiento para la próxima transacción.
Los niveles de aislamiento de transacciones globales y de sesión pueden consultarse con estas sentencias:
SELECT @@global.tx_isolation;
SELECT @@tx_isolation;
En el bloqueo a nivel de fila, InnoDB emplea bloqueo de clave siguiente (next-key). Esto significa que, además de registros de índice, InnoDB también puede bloquear el “vacío” que precede a un registro de índice para bloquear inserciones de otros usuarios inmediatamente antes del registro de índice. Un bloqueo de clave siguiente hace referencia a bloquear un registro de índice y la posición vacía antes de él. Bloquear una posición vacía es establecer un bloqueo que actúa solamente sobre el vacío anterior a un registro de índice.
A continuación una descripción detallada de cada nivel de aislamiento en InnoDB:
Ø READ UNCOMMITTED
Las sentencias SELECT son ejecutadas sin realizar bloqueos, pero podría usarse una versión anterior de un registro. Por lo tanto, las lecturas no son consistentes al usar este nivel de aislamiento. Esto también se denomina “lectura sucia” (dirty read). En otro caso, este nivel de aislamiento funciona igual que READ COMMITTED.
Ø READ COMMITTED
Similar en parte al mismo nivel de aislamiento de Oracle. Todas las sentencias SELECT ... FOR UPDATE y SELECT ... LOCK IN SHARE MODE bloquean solamente los registros de índice, no los espacios vacíos que los preceden, por lo tanto se permite la libre inserción de nuevos registros junto a los bloqueados. Las sentencias UPDATE and DELETE que empleen un índice único con una condición de búsqueda única bloquean solamente el registro de índice hallado, no el espacio que lo precede. En las sentencias UPDATE y DELETE que actúan sobre rangos de registros, InnoDB debe bloquear los espacios vacíos y bloquear las inserciones de otros usuarios en los espacios vacíos que hay dentro del rango. Esto es necesario debido a que las “filas fantasma” deben ser bloqueadas para que funcionen la replicación y recuperación en MySQL.
Las lecturas consistentes se comportan como en Oracle: Cada lectura consistente, incluso dentro de la misma transacción, establece y lee su propia captura tomada de la base de datos. REPEATABLE READ
Este es el nivel de aislamiento predeterminado de InnoDB. Las sentencias SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, y DELETE que utilicen un índice único con una condición de búsqueda única, bloquean solamente el registro de índice hallado, no el espacio vacío que lo precede. Con otras condiciones de búsqueda, estas operaciones emplean bloqueo de clave siguiente (next-key), bloqueando el rango de índice cubierto por la operación incluyendo los espacios vacíos, y bloqueando las nuevas inserciones por parte de otros usuarios.
En lecturas consistentes (consistent reads), hay una importante diferencia con respecto al nivel de aislamiento anterior: En este nivel, todas las lecturas consistentes dentro de la misma transacción leen de la captura de la base de datos tomada por la primera lectura. Esta práctica significa que si se emiten varias sentencias SELECT dentro de la misma transacción, éstas serán consistentes unas con otras. SERIALIZABLE
Este nivel es similar a REPEATABLE READ, pero todas las sentencias SELECT son convertidas implícitamente a SELECT ... LOCK IN SHARE MODE.
4.4 INSTRUCCIONES COMMIT Y ROLLBACK
[mysqld]
transaction-isolation = {READ-UNCOMMITTED READ-COMMITTED
REPEATABLE-READ SERIALIZABLE}
Un usuario puede cambiar el nivel de aislamiento de una sesión individual o de todas las nuevas conexiones con la sentencia SET TRANSACTION. Su sintaxis es la siguiente:
SET [SESSION GLOBAL] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED READ COMMITTED
REPEATABLE READ SERIALIZABLE}
Nótese que se usan guiones en los nombres de niveles de la opción --transaction-isolation, pero no en la sentencia SET TRANSACTION.
El comportamiento predeterminado es establecer el nivel de aislamiento a partir de la próxima transacción que se inicie. Si se emplea la palabra clave GLOBAL, la sentencia establece el nivel predeterminado de la transacción globalmente para todas las nuevas conexiones creadas a partir de ese punto (pero no en las existentes). Se necesita el privilegio SUPER para hacer esto. Utilizando la palabra clave SESSION se establece el nivel de transacción para todas las futuras transacciones ejecutadas en la actual conexión.
Cualquier cliente es libre de cambiar el nivel de aislamiento de la sesión (incluso en medio de una transacción), o el nivel de aislamiento para la próxima transacción.
Los niveles de aislamiento de transacciones globales y de sesión pueden consultarse con estas sentencias:
SELECT @@global.tx_isolation;
SELECT @@tx_isolation;
En el bloqueo a nivel de fila, InnoDB emplea bloqueo de clave siguiente (next-key). Esto significa que, además de registros de índice, InnoDB también puede bloquear el “vacío” que precede a un registro de índice para bloquear inserciones de otros usuarios inmediatamente antes del registro de índice. Un bloqueo de clave siguiente hace referencia a bloquear un registro de índice y la posición vacía antes de él. Bloquear una posición vacía es establecer un bloqueo que actúa solamente sobre el vacío anterior a un registro de índice.
A continuación una descripción detallada de cada nivel de aislamiento en InnoDB:
Ø READ UNCOMMITTED
Las sentencias SELECT son ejecutadas sin realizar bloqueos, pero podría usarse una versión anterior de un registro. Por lo tanto, las lecturas no son consistentes al usar este nivel de aislamiento. Esto también se denomina “lectura sucia” (dirty read). En otro caso, este nivel de aislamiento funciona igual que READ COMMITTED.
Ø READ COMMITTED
Similar en parte al mismo nivel de aislamiento de Oracle. Todas las sentencias SELECT ... FOR UPDATE y SELECT ... LOCK IN SHARE MODE bloquean solamente los registros de índice, no los espacios vacíos que los preceden, por lo tanto se permite la libre inserción de nuevos registros junto a los bloqueados. Las sentencias UPDATE and DELETE que empleen un índice único con una condición de búsqueda única bloquean solamente el registro de índice hallado, no el espacio que lo precede. En las sentencias UPDATE y DELETE que actúan sobre rangos de registros, InnoDB debe bloquear los espacios vacíos y bloquear las inserciones de otros usuarios en los espacios vacíos que hay dentro del rango. Esto es necesario debido a que las “filas fantasma” deben ser bloqueadas para que funcionen la replicación y recuperación en MySQL.
Las lecturas consistentes se comportan como en Oracle: Cada lectura consistente, incluso dentro de la misma transacción, establece y lee su propia captura tomada de la base de datos. REPEATABLE READ
Este es el nivel de aislamiento predeterminado de InnoDB. Las sentencias SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, y DELETE que utilicen un índice único con una condición de búsqueda única, bloquean solamente el registro de índice hallado, no el espacio vacío que lo precede. Con otras condiciones de búsqueda, estas operaciones emplean bloqueo de clave siguiente (next-key), bloqueando el rango de índice cubierto por la operación incluyendo los espacios vacíos, y bloqueando las nuevas inserciones por parte de otros usuarios.
En lecturas consistentes (consistent reads), hay una importante diferencia con respecto al nivel de aislamiento anterior: En este nivel, todas las lecturas consistentes dentro de la misma transacción leen de la captura de la base de datos tomada por la primera lectura. Esta práctica significa que si se emiten varias sentencias SELECT dentro de la misma transacción, éstas serán consistentes unas con otras. SERIALIZABLE
Este nivel es similar a REPEATABLE READ, pero todas las sentencias SELECT son convertidas implícitamente a SELECT ... LOCK IN SHARE MODE.
4.4 INSTRUCCIONES COMMIT Y ROLLBACK
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.
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. 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 . 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.).
Puede cambiar el nivel de aislamiento para transacciones con SET TRANSACTION ISOLATION LEVEL. 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).
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. 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. 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.) Puede cambiar el nivel de aislamiento para transacciones con SET TRANSACTION ISOLATION LEVEL. 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).
UNIDAD 5 VISTAS
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.
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. 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 . 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.).
Puede cambiar el nivel de aislamiento para transacciones con SET TRANSACTION ISOLATION LEVEL. 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).
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. 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. 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.) Puede cambiar el nivel de aislamiento para transacciones con SET TRANSACTION ISOLATION LEVEL. 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).
UNIDAD 5 VISTAS
Las vistas son útiles para permitir acceder a los usuarios a un conjunto de relaciones (tablas) como si fueran una sola, y limitar su acceso a las mismas. También se pueden usar las vistas para restringir el acceso a registros (un subconjunto de una tabla particular). Para control de acceso a columnas, puede usar el sofisticado sistema de privilegios de MySQL Server
Las vistas (incluyendo vistas actualizables) fueron introducidas en la versión 5.0 del servidor de base de datos MySQL
En este capítulo se tratan los siguientes temas:
Creación o modificación de vistas con CREATE VIEW o ALTER VIEW
Eliminación de vistas con DROP VIEW
Obtención de información de definición de una vista (metadatos) con SHOW CREATE VIEW
* Toda vista pertenece a una base de datos. Por defecto, las vistas se crean en la base de datos actual.
Para crear una vista en una base de datos específica: base_de_datos.nombre_vista al momento de crearla.
mysql> CREATE VIEW test.v AS SELECT * FROM t;
* Las tablas y las vistas comparten el mismo espacio de nombres en la base de datos, por eso, una base de datos no puede contener una tabla y una vista con el mismo nombre.
*las vistas no pueden tener nombres de columnas duplicados
* Para dar explícitamente un nombre a las columnas de la vista se utiliza la cláusula columnas para indicar una lista de nombres separados con comas.
Las columnas devueltas por la sentencia SELECT pueden ser simples referencias a columnas de la tabla, pero también pueden ser expresiones conteniendo funciones, constantes, operadores, etc.
Pueden usar combinaciones, UNION, y subconsultas.
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED MERGE TEMPTABLE}]
VIEW nombre_vista [(columnas)] AS sentencia_select [WITH [CASCADED LOCAL] CHECK OPTION]
En el siguiente ejemplo se define una vista que selecciona dos columnas de otra tabla, así como una expresión calculada a partir de ellas:
mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v; + +------+------- +-------+
qty price value
+------+-------+--------+
3 50 150
+------+-------+-------+
La definición de una vista está sujeta a las siguientes limitaciones:
• La sentencia SELECT no puede contener una subconsulta en su cláusula FROM.
• La sentencia SELECT no puede hacer referencia a variables del sistema o del usuario.
• La sentencia SELECT no puede hacer referencia a parámetros de sentencia preparados.
• Dentro de una rutina almacenada, la definición no puede hacer referencia a parámetros de la rutina o a variables locales.
• Las tablas mencionadas en la definición de la vista deben existir siempre.
• No se puede asociar un disparador con una vista.
Restricciones en vistas
El procesamiento de vistas no está optimizado:
No es posible crear un ínidice en una vista.
Los índices pueden utilizarse para procesar vistas usando un algoritmo de combinación (MERGE). Sin embargo, una vista que se procesa con el algoritmo de tablas temporales (temptable) no es capaz de tomar ventaja de los índices que hacen referencia a las tablas que contiene (aunque los índices pueden ser usados durante la generación de las tablas temporales).
Las subconsultas no pueden utilizarse en la cláusula FROM de una vista.
El mismo principio se aplica también si se hace una selección de una vista que hace una selección de una tabla, si la vista selecciona de la tabla dentro de una subconsulta, y la vista es evaluada usando el algoritmo de combinación (merge). Ejemplo:
CREATE VIEW v1 AS
SELECT * FROM t2 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.a = t2.a);
UPDATE t1, v2 SET t1.a = 1 WHERE t1.b = v2.b;
Si la vista se evalúa usando una tabla temporal, se puede seleccionar de la tabla en la subconsulta de la vista y modificarla en la consulta exterior. En este caso la vista se almacenará en una tabla temporal y por ello no se está realmente seleccionando de una tabla en una subconsulta y modificándola “al mismo tiempo”. (Esta es otra razón por la que tal vez sea deseable forzar a MySQL para que use el algoritmo de tablas (temptable) temporales especificando las palabras ALGORITHM = TEMPTABLE en la definición de la vista.)
Se puede usar DROP TABLE o ALTER TABLE para eliminar o modificar una tabla utilizada en la definición de una vista (lo cual invalida la vista) y no se obtendrá ninguna alerta de las operaciones de eliminar o modificar. Sin embargo, se obtiene un error más tarde, cuando se utiliza la vista.
Algunas sentencias “congelan” una definición de vista:
Si una sentencia preparada por PREPARE se refiere a una vista, los contenidos de la vista que se ven cada vez que se ejecuta la sentencia, serán los contenidos de la vista en el momento en el que la sentencia fue preparada. Esto es cierto incluso si la definición de la vista se cambia después de preparar la sentencia y antes de que ésta se ejecute. Ejemplo:
· CREATE VIEW v AS SELECT 1;
· PREPARE s FROM 'SELECT * FROM v';
· ALTER VIEW v AS SELECT 2;
· EXECUTE s;
El resultado devuelto por la sentencia EXECUTE es 1, y no 2.
Si una sentencia en una rutina almacenada se refiere a una vista, los contenidos de la vista que ve la sentencia son sus contenidos de la primera ejecución de la sentencia. Esto significa por ejemplo que si se ejecuta una sentencia en un bucle, en todas las iteraciones de la sentencia se verá el mismo contenido de la vista, aunque la definición de la vista cambie durante el bucle. Ejemplo:
· CREATE VIEW v AS SELECT 1;
· delimiter //
· CREATE PROCEDURE p ()
· BEGIN
· DECLARE i INT DEFAULT 0;
· WHILE i < 5 DO
· SELECT * FROM v;
· SET i = i + 1;
· ALTER VIEW v AS SELECT 2;
· END WHILE;
· END;
· //
· delimiter ;
· CALL p();
Cuando se llama al procedimiento p(), el SELECT devuelve siempre 1 dentro del bucle, aunque dentro del mismo cambie la definición de la vista.
Con respecto a las actualizaciones en vistas, el objetivo es que cualquier vista que sea teóricamente actualizable, tiene que serlo en la práctica. Esto incluye vistas que tienen UNION en su definición. Actualmente, no todas las vistas teóricamente actualizables se pueden actualizar. La implementación inicial de vistas fue deliberadamente escrita de esta forma para obtener en MySQL vistas utilizables y actualizables lo antes posible. Muchas vistas teóricamente actualizables pueden ser actualizadas actualmente, pero algunas limitaciones siguen existiendo:
Ø Las vistas actualizables con subconsultas en cualquier lugar que no sea en la cláusula WHERE. Algunas vistas que tienen subconsultas en la lista SELECT podrían ser actualizables.
Ø No se puede utilizar UPDATE para actualizar más de una tabla incluida en una vista que sea definida como un join.
Ø No se puede usar una sentencia DELETE para actualizar una vista que está definida como un JOIN.
5.1 DEFINICIÓN OBJETIVO DE LAS VISTAS
Las vistas (incluyendo vistas actualizables) fueron introducidas en la versión 5.0 del servidor de base de datos MySQL
En este capítulo se tratan los siguientes temas:
Creación o modificación de vistas con CREATE VIEW o ALTER VIEW
Eliminación de vistas con DROP VIEW
Obtención de información de definición de una vista (metadatos) con SHOW CREATE VIEW
* Toda vista pertenece a una base de datos. Por defecto, las vistas se crean en la base de datos actual.
Para crear una vista en una base de datos específica: base_de_datos.nombre_vista al momento de crearla.
mysql> CREATE VIEW test.v AS SELECT * FROM t;
* Las tablas y las vistas comparten el mismo espacio de nombres en la base de datos, por eso, una base de datos no puede contener una tabla y una vista con el mismo nombre.
*las vistas no pueden tener nombres de columnas duplicados
* Para dar explícitamente un nombre a las columnas de la vista se utiliza la cláusula columnas para indicar una lista de nombres separados con comas.
Las columnas devueltas por la sentencia SELECT pueden ser simples referencias a columnas de la tabla, pero también pueden ser expresiones conteniendo funciones, constantes, operadores, etc.
Pueden usar combinaciones, UNION, y subconsultas.
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED MERGE TEMPTABLE}]
VIEW nombre_vista [(columnas)] AS sentencia_select [WITH [CASCADED LOCAL] CHECK OPTION]
En el siguiente ejemplo se define una vista que selecciona dos columnas de otra tabla, así como una expresión calculada a partir de ellas:
mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v; + +------+------- +-------+
qty price value
+------+-------+--------+
3 50 150
+------+-------+-------+
La definición de una vista está sujeta a las siguientes limitaciones:
• La sentencia SELECT no puede contener una subconsulta en su cláusula FROM.
• La sentencia SELECT no puede hacer referencia a variables del sistema o del usuario.
• La sentencia SELECT no puede hacer referencia a parámetros de sentencia preparados.
• Dentro de una rutina almacenada, la definición no puede hacer referencia a parámetros de la rutina o a variables locales.
• Las tablas mencionadas en la definición de la vista deben existir siempre.
• No se puede asociar un disparador con una vista.
Restricciones en vistas
El procesamiento de vistas no está optimizado:
No es posible crear un ínidice en una vista.
Los índices pueden utilizarse para procesar vistas usando un algoritmo de combinación (MERGE). Sin embargo, una vista que se procesa con el algoritmo de tablas temporales (temptable) no es capaz de tomar ventaja de los índices que hacen referencia a las tablas que contiene (aunque los índices pueden ser usados durante la generación de las tablas temporales).
Las subconsultas no pueden utilizarse en la cláusula FROM de una vista.
El mismo principio se aplica también si se hace una selección de una vista que hace una selección de una tabla, si la vista selecciona de la tabla dentro de una subconsulta, y la vista es evaluada usando el algoritmo de combinación (merge). Ejemplo:
CREATE VIEW v1 AS
SELECT * FROM t2 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.a = t2.a);
UPDATE t1, v2 SET t1.a = 1 WHERE t1.b = v2.b;
Si la vista se evalúa usando una tabla temporal, se puede seleccionar de la tabla en la subconsulta de la vista y modificarla en la consulta exterior. En este caso la vista se almacenará en una tabla temporal y por ello no se está realmente seleccionando de una tabla en una subconsulta y modificándola “al mismo tiempo”. (Esta es otra razón por la que tal vez sea deseable forzar a MySQL para que use el algoritmo de tablas (temptable) temporales especificando las palabras ALGORITHM = TEMPTABLE en la definición de la vista.)
Se puede usar DROP TABLE o ALTER TABLE para eliminar o modificar una tabla utilizada en la definición de una vista (lo cual invalida la vista) y no se obtendrá ninguna alerta de las operaciones de eliminar o modificar. Sin embargo, se obtiene un error más tarde, cuando se utiliza la vista.
Algunas sentencias “congelan” una definición de vista:
Si una sentencia preparada por PREPARE se refiere a una vista, los contenidos de la vista que se ven cada vez que se ejecuta la sentencia, serán los contenidos de la vista en el momento en el que la sentencia fue preparada. Esto es cierto incluso si la definición de la vista se cambia después de preparar la sentencia y antes de que ésta se ejecute. Ejemplo:
· CREATE VIEW v AS SELECT 1;
· PREPARE s FROM 'SELECT * FROM v';
· ALTER VIEW v AS SELECT 2;
· EXECUTE s;
El resultado devuelto por la sentencia EXECUTE es 1, y no 2.
Si una sentencia en una rutina almacenada se refiere a una vista, los contenidos de la vista que ve la sentencia son sus contenidos de la primera ejecución de la sentencia. Esto significa por ejemplo que si se ejecuta una sentencia en un bucle, en todas las iteraciones de la sentencia se verá el mismo contenido de la vista, aunque la definición de la vista cambie durante el bucle. Ejemplo:
· CREATE VIEW v AS SELECT 1;
· delimiter //
· CREATE PROCEDURE p ()
· BEGIN
· DECLARE i INT DEFAULT 0;
· WHILE i < 5 DO
· SELECT * FROM v;
· SET i = i + 1;
· ALTER VIEW v AS SELECT 2;
· END WHILE;
· END;
· //
· delimiter ;
· CALL p();
Cuando se llama al procedimiento p(), el SELECT devuelve siempre 1 dentro del bucle, aunque dentro del mismo cambie la definición de la vista.
Con respecto a las actualizaciones en vistas, el objetivo es que cualquier vista que sea teóricamente actualizable, tiene que serlo en la práctica. Esto incluye vistas que tienen UNION en su definición. Actualmente, no todas las vistas teóricamente actualizables se pueden actualizar. La implementación inicial de vistas fue deliberadamente escrita de esta forma para obtener en MySQL vistas utilizables y actualizables lo antes posible. Muchas vistas teóricamente actualizables pueden ser actualizadas actualmente, pero algunas limitaciones siguen existiendo:
Ø Las vistas actualizables con subconsultas en cualquier lugar que no sea en la cláusula WHERE. Algunas vistas que tienen subconsultas en la lista SELECT podrían ser actualizables.
Ø No se puede utilizar UPDATE para actualizar más de una tabla incluida en una vista que sea definida como un join.
Ø No se puede usar una sentencia DELETE para actualizar una vista que está definida como un JOIN.
5.1 DEFINICIÓN OBJETIVO DE LAS VISTAS
Una vista en SQL es el resultado de una consulta de varias tablas que te aparece como una sola tabla.
Si se suprime una tabla la vista asociada se invalida.
Formato: CREATE [OR REPLACE] VIEW NOMBREVISTA
[(COLUMNA [,COLUMNA])]
AS CONSULTA;
AS CONSULTA= Determina las columnas y las tablas que aparecerán en la vista.
[OR REPLACE]= Crea de nuevo la vista si ya existía.
Para consultar la vista creada, USER_VIEWS:
SELECT VIEW_NAME FROM…
Objetivo de la vista
Sintaxis de SHOW CREATE VIEW
SHOW CREATE VIEW nombre_vista
Muestra la sentencia CREATE VIEW que se utilizó para crear la vista.
mysql> SHOW CREATE VIEW v;
+-------+----------------------------------------------------+
Table Create Table
+-------+----------------------------------------------------+
v CREATE VIEW `test`.`v` AS select 1 AS `a`,2 AS `b`
+-------+----------------------------------------------------+
Esta sentencia fue introducida en MySQL 5.0.1.
Vistas
Vistas (incluyendo vistas actualizables) se implementan en la versión 5.0 de MySQL Server. Las vistas están disponibles en las versiones binarias a partir de la 5.0.1. Las vistas son útiles para permitir acceder a los usuarios a un conjunto de relaciones (tablas) como si fueran una sola, y limitar su acceso a las mismas. También se pueden usar las vistas para restringir el acceso a registros (un subconjunto de una tabla particular). Para control de acceso a columnas, puede usar el sofisticado sistema de privilegios de MySQL Server. Al diseñar la implementación de las vistas, nuestro ambicioso objetivo, dentro de los límites de SQL, ha sido la plena compatibilidad con la regla 6 de Codd para sistemas relacionales de bases de datos: "Todas las vistas que son actualizables en teoría, deben serlo en la práctica".
5.2 INSTRUCCIONES ADMINISTRACIÓN DE VISTA
Si se suprime una tabla la vista asociada se invalida.
Formato: CREATE [OR REPLACE] VIEW NOMBREVISTA
[(COLUMNA [,COLUMNA])]
AS CONSULTA;
AS CONSULTA= Determina las columnas y las tablas que aparecerán en la vista.
[OR REPLACE]= Crea de nuevo la vista si ya existía.
Para consultar la vista creada, USER_VIEWS:
SELECT VIEW_NAME FROM…
Objetivo de la vista
Sintaxis de SHOW CREATE VIEW
SHOW CREATE VIEW nombre_vista
Muestra la sentencia CREATE VIEW que se utilizó para crear la vista.
mysql> SHOW CREATE VIEW v;
+-------+----------------------------------------------------+
Table Create Table
+-------+----------------------------------------------------+
v CREATE VIEW `test`.`v` AS select 1 AS `a`,2 AS `b`
+-------+----------------------------------------------------+
Esta sentencia fue introducida en MySQL 5.0.1.
Vistas
Vistas (incluyendo vistas actualizables) se implementan en la versión 5.0 de MySQL Server. Las vistas están disponibles en las versiones binarias a partir de la 5.0.1. Las vistas son útiles para permitir acceder a los usuarios a un conjunto de relaciones (tablas) como si fueran una sola, y limitar su acceso a las mismas. También se pueden usar las vistas para restringir el acceso a registros (un subconjunto de una tabla particular). Para control de acceso a columnas, puede usar el sofisticado sistema de privilegios de MySQL Server. Al diseñar la implementación de las vistas, nuestro ambicioso objetivo, dentro de los límites de SQL, ha sido la plena compatibilidad con la regla 6 de Codd para sistemas relacionales de bases de datos: "Todas las vistas que son actualizables en teoría, deben serlo en la práctica".
5.2 INSTRUCCIONES ADMINISTRACIÓN DE VISTA
INSTRUCCIONES PARA LA ADMINISTRACION
DE VISTAS
Creación o modificación de vistas con CREATE VIEW o ALTER VIEW.
Eliminación de vistas con DROP VIEW.
Obtención de información de definición de una vista (metadatos) con SHOW CREATE VIEW
Las vistas (incluyendo vistas actualizables) fueron introducidas en la versión 5.0 del servidor de base de datos MySQL
En este capítulo se tratan los siguientes temas:
Ø Creación o modificación de vistas con CREATE VIEW o ALTER VIEW
Ø Eliminación de vistas con DROP VIEW
Ø Obtención de información de definición de una vista (metadatos) con SHOW CREATE VIEW
Si ha actualizado a MySQL 5.0.1 desde una versión anterior, debería actualizar las tablas de permisos para que contengan los privilegios relacionados con vistas.
Las vistas pueden considerarse como tablas virtuales. Generalmente hablando, una tabla tiene un conjunto de definiciones, y almacena datos físicamente. Una vista también tiene un conjunto de definiciones, que se construye en la parte superior de la(s) tabla(s) u otra(s) vista(s), y no almacena datos físicamente.
La sintaxis para la creación de una vista es la siguiente:
CREATE VIEW "NOMBRE_VISTA" AS "Instrucción SQL"
La “Instrucción SQL” puede ser cualquiera de las instrucciones SQL que hemos descripto en esta guía de referencia.
Utilicemos un ejemplo simple para ilustrar. Supongamos que tenemos la siguiente tabla:
Tabla Customer(First_Name char(50),Last_Name char(50),Address char(50),City char(50),Country char(25),Birth_Date date)
y deseamos crear una vista denominada V_Customer que contiene sólo las columnas First_Name, Last_Name y País de esta tabla, ingresaríamos
CREATE VIEW V_CustomerAS SELECT First_Name, Last_Name, CountryFROM Customer
Ahora tenemos una vista llamada V_Customer con la siguiente estructura:
View V_Customer(First_Name char(50),Last_Name char(50),Country char(25))
Podemos utilizar también una vista para aplicar uniones a dos tablas. En este caso, los usuarios sólo ven una vista en vez de dos tablas, y la instrucción SQL que los usuarios necesitan emitir se vuelve mucho más simple. Digamos que tenemos las siguientes dos tablas:
Tabla Store_Information
store_name
Sales
Date
Los Angeles
1500 €
05-Jan-1999
San Diego
250 €
07-Jan-1999
Los Angeles
300 €
08-Jan-1999
Boston
700 €
08-Jan-1999
Tabla Geography
region_name
store_name
East
Boston
East
New York
West
Los Angeles
West
San Diego
y deseamos construir una vista que tenga ventas organizadas según la región. Colocaríamos la siguiente instrucción SQL:
CREATE VIEW V_REGION_SALESAS SELECT A1.region_name REGION, SUM(A2.Sales) SALESFROM Geography A1, Store_Information A2WHERE A1.store_name = A2.store_nameGROUP BY A1.region_name
Esto nos brinda una vista, V_REGION_SALES, que se ha definido para las ventas de los negocios según los registros de la región. Si deseamos saber el contenido de esta vista, ingresamos,
SELECT * FROM V_REGION_SALES
Resultado:
REGION
SALES
East
700 €
West
2050 €
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED MERGE TEMPTABLE}]
VIEW nombre_vista [(columnas)]
AS sentencia_select
[WITH [CASCADED LOCAL] CHECK OPTION]
Esta sentencia crea una vista nueva o reemplaza una existente si se incluye la cláusula OR REPLACE. La sentencia_select es una sentencia SELECT que proporciona la definición de la vista. Puede estar dirigida a tablas de la base o a otras vistas.
Se requiere que posea el permiso CREATE VIEW para la vista, y algún privilegio en cada columna seleccionada por la sentencia SELECT. Para columnas incluidas en otra parte de la sentencia SELECT debe poseer el privilegio SELECT. Si está presente la cláusula OR REPLACE, también deberá tenerse el privilegio DELETE para la vista.
Toda vista pertenece a una base de datos. Por defecto, las vistas se crean en la base de datos actual. Pera crear una vista en una base de datos específica, indíquela con base_de_datos.nombre_vista al momento de crearla.
mysql> CREATE VIEW test.v AS SELECT * FROM t;
Las tablas y las vistas comparten el mismo espacio de nombres en la base de datos, por eso, una base de datos no puede contener una tabla y una vista con el mismo nombre.
Al igual que las tablas, las vistas no pueden tener nombres de columnas duplicados. Por defecto, los nombres de las columnas devueltos por la sentencia SELECT se usan para las columnas de la vista. Para dar explícitamente un nombre a las columnas de la vista utilice la clásula columnas para indicar una lista de nombres separados con comas. La cantidad de nombres indicados en columnas debe ser igual a la cantidad de columnas devueltas por la sentencia SELECT.
Las columnas devueltas por la sentencia SELECT pueden ser simples referencias a columnas de la tabla, pero tambien pueden ser expresiones conteniendo funciones, constantes, operadores, etc.
Los nombres de tablas o vistas sin calificar en la sentencia SELECT se interpretan como pertenecientes a la base de datos actual. Una vista puede hacer referencia a tablas o vistas en otras bases de datos precediendo el nombre de la tabla o vista con el nombre de la base de datos apropiada.
Las vistas pueden crearse a partir de varios tipos de sentencias SELECT. Pueden hacer referencia a tablas o a otras vistas. Pueden usar combinaciones, UNION, y subconsultas. El SELECT inclusive no necesita hacer referencia a otras tablas. En el siguiente ejemplo se define una vista que selecciona dos columnas de otra tabla, así como una expresión calculada a partir de ellas:
mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
qty price value
+------+-------+-------+
3 50 150
+------+-------+-------+
La definición de una vista está sujeta a las siguientes limitaciones:
La sentencia SELECT no puede contener una subconsulta en su cláusula FROM.
La sentencia SELECT no puede hacer referencia a variables del sistema o del usuario.
La sentencia SELECT no puede hacer referencia a parámetros de sentencia preparados.
Dentro de una rutina almacenada, la definición no puede hacer referencia a parámetros de la rutina o a variables locales.
Cualquier tabla o vista referenciada por la definición debe existir. Sin embargo, es posible que después de crear una vista, se elimine alguna tabla o vista a la que se hace referencia. Para comprobar la definición de una vista en busca de problemas de este tipo, utilice la sentencia CHECK TABLE.
La definición no puede hacer referencia a una tabla TEMPORARY, y tampoco se puede crear una vista TEMPORARY.
Las tablas mencionadas en la definición de la vista deben existir siempre.
No se puede asociar un disparador con una vista.
En la definición de una vista está permitido ORDER BY, pero es ignorado si se seleccionan columnas de una vista que tiene su propio ORDER BY.
Con respecto a otras opciones o cláusulas incluidas en la definición, las mismas se agregan a las opciones o cláusulas de cualquier sentencia que haga referencia a la vista creada, pero el efecto es indefinido. Por ejemplo, si la definición de una vista incluye una cláusula LIMIT, y se hace una selección desde la vista utilizando una sentencia que tiene su propia cláusula LIMIT, no está definido cuál se aplicará. El mismo principio se extiende a otras opciones como ALL, DISTINCT, o SQL_SMALL_RESULT que se ubican a continuación de la palabra reservada SELECT, y a cláusulas como INTO, FOR UPDATE, LOCK IN SHARE MODE, y PROCEDURE.
Si se crea una vista y luego se modifica el entorno de proceso de la consulta a traves de la modificación de variables del sistema, puede afectar los resultados devueltos por la vista:
mysql> CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65));
Query OK, 0 rows affected (0.00 sec)
mysql> SET NAMES 'latin1';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM v;
+-------------------+---------------------+
CHARSET(CHAR(65)) COLLATION(CHAR(65))
+-------------------+---------------------+
latin1 latin1_swedish_ci
+-------------------+---------------------+
1 row in set (0.00 sec)
mysql> SET NAMES 'utf8';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM v;
+-------------------+---------------------+
CHARSET(CHAR(65)) COLLATION(CHAR(65))
+-------------------+---------------------+
utf8 utf8_general_ci
+-------------------+---------------------+
1 row in set (0.00 sec)
La cláusula opcional ALGORITHM es una extensión de MySQL al SQL estándar. ALGORITHM puede tomar tres valores: MERGE, TEMPTABLE, o UNDEFINED. El algoritmo por defecto es UNDEFINED si no se encuentra presente la cláusula ALGORITHM. El algoritmo afecta la manera en que MySQL procesa la vista.
Para MERGE, el texto de una sentencia que haga referencia a la vista y la definición de la vista son mezclados de forma que parte de la definición de la vista reemplaza las partes correspondientes de la consulta.
Para TEMPTABLE, los resultados devueltos por la vista son colocados en una tabla temporal, la cual es luego utilizada para ejecutar la sentencia.
Para UNDEFINED, MySQL determina el algoritmo que utilizará. En ese caso se prefiere MERGE por sobre TEMPTABLE si es posible, ya que MERGE por lo general es más eficiente y porque la vista no puede ser actualizable si se emplea una tabla temporal.
Una razón para elegir explícitamente TEMPTABLE es que los bloqueos en tablas subyacentes pueden ser liberados despues que la tabla temporal fue creada, y antes de que sea usada para terminar el procesamiento de la sentencia. Esto podría resultar en una liberación del bloqueo más rápida que en el algoritmo MERGE, de modo que otros clientes que utilicen la vista no estarán bloqueados mucho tiempo.
El algoritmo de una vista puede ser UNDEFINED en tres situaciones:
No se encuentra presente una cláusula ALGORITHMen la sentencia CREATE VIEW.
La sentencia CREATE VIEW tiene explícitamente una cláusula ALGORITHM = UNDEFINED.
Se especificó ALGORITHM = MERGE para una vista que solamente puede ser procesada usando una tabla temporal. En este caso, MySQL emite una advertencia y establece el algoritmo en UNDEFINED.
Como se dijo anteriormente, MERGE provoca que las partes correspondientes de la definición de la vista se combinen dentro de la sentencia que hace referencia a la vista. El siguiente ejemplo muestra brevemente cómo funciona el algoritmo MERGE. El ejemplo asume que hay una vista v_merge con esta definición:
CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 > 100;
Ejemplo 1: Suponiendo que se utilice esta sentencia:
SELECT * FROM v_merge;
MySQL la gestiona del siguiente modo:
v_merge se convierte en t
* se convierte en vc1, vc2, que corresponden a c1, c2
Se agrega la cláusula WHERE de la vista
La sentencia ejecutada resulta ser:
SELECT c1, c2 FROM t WHERE c3 > 100;
Ejemplo 2: Suponiendo que se utilice esta sentencia:
SELECT * FROM v_merge WHERE vc1 < 100;
Esta sentencia se gestiona en forma similar a la anterior, a excepción de que vc1 < 100 se convierte en c1 < 100 y la cláusula WHERE de la vista se agrega a la cláusula WHERE de la sentencia empleando un conector AND (y se agregan paréntesis para asegurarse que las partes de la cláusula se ejecutarán en el orden de precedencia correcto). La sentencia ejecutada resulta ser:
SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);
Necesariamente, la sentencia a ejecutar tiene una cláusula WHERE con esta forma:
WHERE (WHERE de la sentencia) AND (WHERE de la vista)
El algoritmo MERGE necesita una relación uno-a-uno entre los registros de la vista y los registros de la tabla subyacente. Si esta relación no se sostiene, debe emplear una tabla temporal en su lugar. No se tendrá una relación uno-a-uno si la vista contiene cualquiera de estos elementos:
Funciones agregadas (SUM(), MIN(), MAX(), COUNT(), etcétera)
DISTINCT
GROUP BY
HAVING
UNION o UNION ALL
Hace referencia solamente a valores literales (en tal caso, no hay una tabla subyacente)
Algunas vistas son actualizables. Esto significa que se las puede emplear en sentencias como UPDATE, DELETE, o INSERT para actualizar el contenido de la tabla subyacente. Para que una vista sea actualizable, debe haber una relación uno-a-uno entre los registros de la vista y los registros de la tabla subyacente. Hay otros elementos que impiden que una vista sea actualizable. Más específicamente, una vista no será actualizable si contiene:
Funciones agregadas (SUM(), MIN(), MAX(), COUNT(), etcétera)
DISTINCT
GROUP BY
HAVING
UNION o UNION ALL
Una subconsulta en la lista de columnas del SELECT
Join
Una vista no actualizable en la cláusula FROM
Una subconsulta en la cláusula WHERE que hace referencia a una tabla en la cláusula FROM
Hace referencia solamente a valores literales (en tal caso no hay una) tabla subyacenta para actualizar.
ALGORITHM = TEMPTABLE (utilizar una tabla temporal siempre resulta en una vista no actualizable)
Con respecto a la posibilidad de agregar registros mediante sentencias INSERT, es necesario que las columnas de la vista actualizable también cumplan los siguientes requisitos adicionales:
No debe haber nombres duplicados entre las columnas de la vista.
La vista debe contemplar todas las columnas de la tabla en la base de datos que no tengan indicado un valor por defecto.
Las columnas de la vista deben ser referencias a columnas simples y no columnas derivadas. Una columna derivada es una que deriva de una expresión. Estos son algunos ejemplos de columnas derivadas:
· 3.14159
· col1 + 3
· UPPER(col2)
· col3 / col4
· (subquery)
No puede insertar registros en una vista conteniendo una combinación de columnas simples y derivadas, pero puede actualizarla si actualiza únicamente las columnas no derivadas. Considere esta vista:
CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;
En esta vista no pueden agregarse registros porque col2 es derivada de una expresión. Pero será actualizable si no intenta actualizar col2. Esta actualización es posible:
UPDATE v SET col1 = 0;
Esta actualización no es posible porque se intenta realizar sobre una columna derivada:
UPDATE v SET col2 = 0;
A veces, es posible que una vista compuesta por múltiples tablas sea actualizable, asumiendo que es procesada con el algoritmo MERGE. Para que esto funcione, la vista debe usar inner join (no outer join o UNION). Además, solamente puede actualizarse una tabla de la definición de la vista, de forma que la cláusula SET debe contener columnas de sólo una tabla de la vista. Las vistas que utilizan UNION ALL no se pueden actualizar aunque teóricamente fuese posible hacerlo, debido a que en la implementación se emplean tablas temporales para procesarlas.
En vistas compuestas por múltiples tablas, INSERT funcionará si se aplica sobre una única tabla. DELETE no está soportado.
La cláusula WITH CHECK OPTION puede utilizarse en una vista actualizable para evitar inserciones o actualizaciones excepto en los registros en que la cláusula WHERE de la sentencia_select se evalúe como true.
En la cláusula WITH CHECK OPTION de una vista actualizable, las palabras reservadas LOCAL y CASCADED determinan el alcance de la verificación cuando la vista está definida en términos de otras vistas. LOCAL restringe el CHECK OPTION sólo a la vista que está siendo definida. CASCADED provoca que las vistas subyacentes también sean verificadas. Si no se indica, el valor por defecto es CASCADED. Considere las siguientes definiciones de tabla y vistas:
mysql> CREATE TABLE t1 (a INT);
mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
-> WITH CHECK OPTION;
mysql> CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0
-> WITH LOCAL CHECK OPTION;
mysql> CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0
-> WITH CASCADED CHECK OPTION;
Las vistas v2 y v3 están definidas en términos de otra vista, v1. v2 emplea check option LOCAL, por lo que las inserciones sólo atraviesan la verificación de v2. v3 emplea check option CASCADED de modo que las inserciones no solamente atraviesan su propia verificación sino tambien las de las vistas subyacentes. Las siguientes sentencias demuestran las diferencias:
ql> INSERT INTO v2 VALUES (2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'
La posibilidad de actualización de las vistas puede verse afectada por el valor de la variable del sistema updatable_views_with_limit. La sentencia CREATE VIEW fue introducida en MySQL 5.0.1. La cláusula WITH CHECK OPTION fue implementada en MySQL 5.0.2.
INFORMATION_SCHEMA contiene una tabla VIEWS de la cual puede obtenerse información sobre los objetos de las vistas.
DE VISTAS
Creación o modificación de vistas con CREATE VIEW o ALTER VIEW.
Eliminación de vistas con DROP VIEW.
Obtención de información de definición de una vista (metadatos) con SHOW CREATE VIEW
Las vistas (incluyendo vistas actualizables) fueron introducidas en la versión 5.0 del servidor de base de datos MySQL
En este capítulo se tratan los siguientes temas:
Ø Creación o modificación de vistas con CREATE VIEW o ALTER VIEW
Ø Eliminación de vistas con DROP VIEW
Ø Obtención de información de definición de una vista (metadatos) con SHOW CREATE VIEW
Si ha actualizado a MySQL 5.0.1 desde una versión anterior, debería actualizar las tablas de permisos para que contengan los privilegios relacionados con vistas.
Las vistas pueden considerarse como tablas virtuales. Generalmente hablando, una tabla tiene un conjunto de definiciones, y almacena datos físicamente. Una vista también tiene un conjunto de definiciones, que se construye en la parte superior de la(s) tabla(s) u otra(s) vista(s), y no almacena datos físicamente.
La sintaxis para la creación de una vista es la siguiente:
CREATE VIEW "NOMBRE_VISTA" AS "Instrucción SQL"
La “Instrucción SQL” puede ser cualquiera de las instrucciones SQL que hemos descripto en esta guía de referencia.
Utilicemos un ejemplo simple para ilustrar. Supongamos que tenemos la siguiente tabla:
Tabla Customer(First_Name char(50),Last_Name char(50),Address char(50),City char(50),Country char(25),Birth_Date date)
y deseamos crear una vista denominada V_Customer que contiene sólo las columnas First_Name, Last_Name y País de esta tabla, ingresaríamos
CREATE VIEW V_CustomerAS SELECT First_Name, Last_Name, CountryFROM Customer
Ahora tenemos una vista llamada V_Customer con la siguiente estructura:
View V_Customer(First_Name char(50),Last_Name char(50),Country char(25))
Podemos utilizar también una vista para aplicar uniones a dos tablas. En este caso, los usuarios sólo ven una vista en vez de dos tablas, y la instrucción SQL que los usuarios necesitan emitir se vuelve mucho más simple. Digamos que tenemos las siguientes dos tablas:
Tabla Store_Information
store_name
Sales
Date
Los Angeles
1500 €
05-Jan-1999
San Diego
250 €
07-Jan-1999
Los Angeles
300 €
08-Jan-1999
Boston
700 €
08-Jan-1999
Tabla Geography
region_name
store_name
East
Boston
East
New York
West
Los Angeles
West
San Diego
y deseamos construir una vista que tenga ventas organizadas según la región. Colocaríamos la siguiente instrucción SQL:
CREATE VIEW V_REGION_SALESAS SELECT A1.region_name REGION, SUM(A2.Sales) SALESFROM Geography A1, Store_Information A2WHERE A1.store_name = A2.store_nameGROUP BY A1.region_name
Esto nos brinda una vista, V_REGION_SALES, que se ha definido para las ventas de los negocios según los registros de la región. Si deseamos saber el contenido de esta vista, ingresamos,
SELECT * FROM V_REGION_SALES
Resultado:
REGION
SALES
East
700 €
West
2050 €
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED MERGE TEMPTABLE}]
VIEW nombre_vista [(columnas)]
AS sentencia_select
[WITH [CASCADED LOCAL] CHECK OPTION]
Esta sentencia crea una vista nueva o reemplaza una existente si se incluye la cláusula OR REPLACE. La sentencia_select es una sentencia SELECT que proporciona la definición de la vista. Puede estar dirigida a tablas de la base o a otras vistas.
Se requiere que posea el permiso CREATE VIEW para la vista, y algún privilegio en cada columna seleccionada por la sentencia SELECT. Para columnas incluidas en otra parte de la sentencia SELECT debe poseer el privilegio SELECT. Si está presente la cláusula OR REPLACE, también deberá tenerse el privilegio DELETE para la vista.
Toda vista pertenece a una base de datos. Por defecto, las vistas se crean en la base de datos actual. Pera crear una vista en una base de datos específica, indíquela con base_de_datos.nombre_vista al momento de crearla.
mysql> CREATE VIEW test.v AS SELECT * FROM t;
Las tablas y las vistas comparten el mismo espacio de nombres en la base de datos, por eso, una base de datos no puede contener una tabla y una vista con el mismo nombre.
Al igual que las tablas, las vistas no pueden tener nombres de columnas duplicados. Por defecto, los nombres de las columnas devueltos por la sentencia SELECT se usan para las columnas de la vista. Para dar explícitamente un nombre a las columnas de la vista utilice la clásula columnas para indicar una lista de nombres separados con comas. La cantidad de nombres indicados en columnas debe ser igual a la cantidad de columnas devueltas por la sentencia SELECT.
Las columnas devueltas por la sentencia SELECT pueden ser simples referencias a columnas de la tabla, pero tambien pueden ser expresiones conteniendo funciones, constantes, operadores, etc.
Los nombres de tablas o vistas sin calificar en la sentencia SELECT se interpretan como pertenecientes a la base de datos actual. Una vista puede hacer referencia a tablas o vistas en otras bases de datos precediendo el nombre de la tabla o vista con el nombre de la base de datos apropiada.
Las vistas pueden crearse a partir de varios tipos de sentencias SELECT. Pueden hacer referencia a tablas o a otras vistas. Pueden usar combinaciones, UNION, y subconsultas. El SELECT inclusive no necesita hacer referencia a otras tablas. En el siguiente ejemplo se define una vista que selecciona dos columnas de otra tabla, así como una expresión calculada a partir de ellas:
mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
qty price value
+------+-------+-------+
3 50 150
+------+-------+-------+
La definición de una vista está sujeta a las siguientes limitaciones:
La sentencia SELECT no puede contener una subconsulta en su cláusula FROM.
La sentencia SELECT no puede hacer referencia a variables del sistema o del usuario.
La sentencia SELECT no puede hacer referencia a parámetros de sentencia preparados.
Dentro de una rutina almacenada, la definición no puede hacer referencia a parámetros de la rutina o a variables locales.
Cualquier tabla o vista referenciada por la definición debe existir. Sin embargo, es posible que después de crear una vista, se elimine alguna tabla o vista a la que se hace referencia. Para comprobar la definición de una vista en busca de problemas de este tipo, utilice la sentencia CHECK TABLE.
La definición no puede hacer referencia a una tabla TEMPORARY, y tampoco se puede crear una vista TEMPORARY.
Las tablas mencionadas en la definición de la vista deben existir siempre.
No se puede asociar un disparador con una vista.
En la definición de una vista está permitido ORDER BY, pero es ignorado si se seleccionan columnas de una vista que tiene su propio ORDER BY.
Con respecto a otras opciones o cláusulas incluidas en la definición, las mismas se agregan a las opciones o cláusulas de cualquier sentencia que haga referencia a la vista creada, pero el efecto es indefinido. Por ejemplo, si la definición de una vista incluye una cláusula LIMIT, y se hace una selección desde la vista utilizando una sentencia que tiene su propia cláusula LIMIT, no está definido cuál se aplicará. El mismo principio se extiende a otras opciones como ALL, DISTINCT, o SQL_SMALL_RESULT que se ubican a continuación de la palabra reservada SELECT, y a cláusulas como INTO, FOR UPDATE, LOCK IN SHARE MODE, y PROCEDURE.
Si se crea una vista y luego se modifica el entorno de proceso de la consulta a traves de la modificación de variables del sistema, puede afectar los resultados devueltos por la vista:
mysql> CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65));
Query OK, 0 rows affected (0.00 sec)
mysql> SET NAMES 'latin1';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM v;
+-------------------+---------------------+
CHARSET(CHAR(65)) COLLATION(CHAR(65))
+-------------------+---------------------+
latin1 latin1_swedish_ci
+-------------------+---------------------+
1 row in set (0.00 sec)
mysql> SET NAMES 'utf8';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM v;
+-------------------+---------------------+
CHARSET(CHAR(65)) COLLATION(CHAR(65))
+-------------------+---------------------+
utf8 utf8_general_ci
+-------------------+---------------------+
1 row in set (0.00 sec)
La cláusula opcional ALGORITHM es una extensión de MySQL al SQL estándar. ALGORITHM puede tomar tres valores: MERGE, TEMPTABLE, o UNDEFINED. El algoritmo por defecto es UNDEFINED si no se encuentra presente la cláusula ALGORITHM. El algoritmo afecta la manera en que MySQL procesa la vista.
Para MERGE, el texto de una sentencia que haga referencia a la vista y la definición de la vista son mezclados de forma que parte de la definición de la vista reemplaza las partes correspondientes de la consulta.
Para TEMPTABLE, los resultados devueltos por la vista son colocados en una tabla temporal, la cual es luego utilizada para ejecutar la sentencia.
Para UNDEFINED, MySQL determina el algoritmo que utilizará. En ese caso se prefiere MERGE por sobre TEMPTABLE si es posible, ya que MERGE por lo general es más eficiente y porque la vista no puede ser actualizable si se emplea una tabla temporal.
Una razón para elegir explícitamente TEMPTABLE es que los bloqueos en tablas subyacentes pueden ser liberados despues que la tabla temporal fue creada, y antes de que sea usada para terminar el procesamiento de la sentencia. Esto podría resultar en una liberación del bloqueo más rápida que en el algoritmo MERGE, de modo que otros clientes que utilicen la vista no estarán bloqueados mucho tiempo.
El algoritmo de una vista puede ser UNDEFINED en tres situaciones:
No se encuentra presente una cláusula ALGORITHMen la sentencia CREATE VIEW.
La sentencia CREATE VIEW tiene explícitamente una cláusula ALGORITHM = UNDEFINED.
Se especificó ALGORITHM = MERGE para una vista que solamente puede ser procesada usando una tabla temporal. En este caso, MySQL emite una advertencia y establece el algoritmo en UNDEFINED.
Como se dijo anteriormente, MERGE provoca que las partes correspondientes de la definición de la vista se combinen dentro de la sentencia que hace referencia a la vista. El siguiente ejemplo muestra brevemente cómo funciona el algoritmo MERGE. El ejemplo asume que hay una vista v_merge con esta definición:
CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 > 100;
Ejemplo 1: Suponiendo que se utilice esta sentencia:
SELECT * FROM v_merge;
MySQL la gestiona del siguiente modo:
v_merge se convierte en t
* se convierte en vc1, vc2, que corresponden a c1, c2
Se agrega la cláusula WHERE de la vista
La sentencia ejecutada resulta ser:
SELECT c1, c2 FROM t WHERE c3 > 100;
Ejemplo 2: Suponiendo que se utilice esta sentencia:
SELECT * FROM v_merge WHERE vc1 < 100;
Esta sentencia se gestiona en forma similar a la anterior, a excepción de que vc1 < 100 se convierte en c1 < 100 y la cláusula WHERE de la vista se agrega a la cláusula WHERE de la sentencia empleando un conector AND (y se agregan paréntesis para asegurarse que las partes de la cláusula se ejecutarán en el orden de precedencia correcto). La sentencia ejecutada resulta ser:
SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);
Necesariamente, la sentencia a ejecutar tiene una cláusula WHERE con esta forma:
WHERE (WHERE de la sentencia) AND (WHERE de la vista)
El algoritmo MERGE necesita una relación uno-a-uno entre los registros de la vista y los registros de la tabla subyacente. Si esta relación no se sostiene, debe emplear una tabla temporal en su lugar. No se tendrá una relación uno-a-uno si la vista contiene cualquiera de estos elementos:
Funciones agregadas (SUM(), MIN(), MAX(), COUNT(), etcétera)
DISTINCT
GROUP BY
HAVING
UNION o UNION ALL
Hace referencia solamente a valores literales (en tal caso, no hay una tabla subyacente)
Algunas vistas son actualizables. Esto significa que se las puede emplear en sentencias como UPDATE, DELETE, o INSERT para actualizar el contenido de la tabla subyacente. Para que una vista sea actualizable, debe haber una relación uno-a-uno entre los registros de la vista y los registros de la tabla subyacente. Hay otros elementos que impiden que una vista sea actualizable. Más específicamente, una vista no será actualizable si contiene:
Funciones agregadas (SUM(), MIN(), MAX(), COUNT(), etcétera)
DISTINCT
GROUP BY
HAVING
UNION o UNION ALL
Una subconsulta en la lista de columnas del SELECT
Join
Una vista no actualizable en la cláusula FROM
Una subconsulta en la cláusula WHERE que hace referencia a una tabla en la cláusula FROM
Hace referencia solamente a valores literales (en tal caso no hay una) tabla subyacenta para actualizar.
ALGORITHM = TEMPTABLE (utilizar una tabla temporal siempre resulta en una vista no actualizable)
Con respecto a la posibilidad de agregar registros mediante sentencias INSERT, es necesario que las columnas de la vista actualizable también cumplan los siguientes requisitos adicionales:
No debe haber nombres duplicados entre las columnas de la vista.
La vista debe contemplar todas las columnas de la tabla en la base de datos que no tengan indicado un valor por defecto.
Las columnas de la vista deben ser referencias a columnas simples y no columnas derivadas. Una columna derivada es una que deriva de una expresión. Estos son algunos ejemplos de columnas derivadas:
· 3.14159
· col1 + 3
· UPPER(col2)
· col3 / col4
· (subquery)
No puede insertar registros en una vista conteniendo una combinación de columnas simples y derivadas, pero puede actualizarla si actualiza únicamente las columnas no derivadas. Considere esta vista:
CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;
En esta vista no pueden agregarse registros porque col2 es derivada de una expresión. Pero será actualizable si no intenta actualizar col2. Esta actualización es posible:
UPDATE v SET col1 = 0;
Esta actualización no es posible porque se intenta realizar sobre una columna derivada:
UPDATE v SET col2 = 0;
A veces, es posible que una vista compuesta por múltiples tablas sea actualizable, asumiendo que es procesada con el algoritmo MERGE. Para que esto funcione, la vista debe usar inner join (no outer join o UNION). Además, solamente puede actualizarse una tabla de la definición de la vista, de forma que la cláusula SET debe contener columnas de sólo una tabla de la vista. Las vistas que utilizan UNION ALL no se pueden actualizar aunque teóricamente fuese posible hacerlo, debido a que en la implementación se emplean tablas temporales para procesarlas.
En vistas compuestas por múltiples tablas, INSERT funcionará si se aplica sobre una única tabla. DELETE no está soportado.
La cláusula WITH CHECK OPTION puede utilizarse en una vista actualizable para evitar inserciones o actualizaciones excepto en los registros en que la cláusula WHERE de la sentencia_select se evalúe como true.
En la cláusula WITH CHECK OPTION de una vista actualizable, las palabras reservadas LOCAL y CASCADED determinan el alcance de la verificación cuando la vista está definida en términos de otras vistas. LOCAL restringe el CHECK OPTION sólo a la vista que está siendo definida. CASCADED provoca que las vistas subyacentes también sean verificadas. Si no se indica, el valor por defecto es CASCADED. Considere las siguientes definiciones de tabla y vistas:
mysql> CREATE TABLE t1 (a INT);
mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
-> WITH CHECK OPTION;
mysql> CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0
-> WITH LOCAL CHECK OPTION;
mysql> CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0
-> WITH CASCADED CHECK OPTION;
Las vistas v2 y v3 están definidas en términos de otra vista, v1. v2 emplea check option LOCAL, por lo que las inserciones sólo atraviesan la verificación de v2. v3 emplea check option CASCADED de modo que las inserciones no solamente atraviesan su propia verificación sino tambien las de las vistas subyacentes. Las siguientes sentencias demuestran las diferencias:
ql> INSERT INTO v2 VALUES (2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'
La posibilidad de actualización de las vistas puede verse afectada por el valor de la variable del sistema updatable_views_with_limit. La sentencia CREATE VIEW fue introducida en MySQL 5.0.1. La cláusula WITH CHECK OPTION fue implementada en MySQL 5.0.2.
INFORMATION_SCHEMA contiene una tabla VIEWS de la cual puede obtenerse información sobre los objetos de las vistas.
