Optimizando SuperServer: poniendo toda la Base de Datos en la memoria caché

5 comentarios

Hay un truco que podemos utilizar para optimizar a SuperServer, funciona también en Classic pero solamente con bases de datos mucho más pequeñas.

Como recordarás, si las filas que devolverá un SELECT ya se encuentran en la memoria caché entonces la velocidad de respuesta será muy alta porque la memoria RAM es muchísimo más rápida que el disco duro. Entonces, la idea es tener a toda la Base de Datos (o al menos a las tablas más utilizadas cuando tener a toda la Base de Datos no es posible) en la memoria caché.

Para poner a toda la Base de Datos en la memoria caché podríamos ejecutar el siguiente stored procedure:

Listado 1.

CREATE PROCEDURE LLENAR_CACHE
AS
   DECLARE VARIABLE lcNombreTabla   VARCHAR(1024);
   DECLARE VARIABLE lcComando       VARCHAR(1024);
   DECLARE VARIABLE lnCantidadFilas INTEGER;
BEGIN
   
   FOR SELECT
      RDB$RELATION_NAME
   FROM
      RDB$RELATIONS
   INTO
      :lcNombreTabla
   DO BEGIN
      lcComando = 'SELECT COUNT(*) FROM ' || lcNombreTabla;
      EXECUTE STATEMENT lcComando INTO :lnCantidadFilas;
   END

END;

Luego de ejecutar el comando EXECUTE PROCEDURE LLENAR_CACHE y el comando COMMIT, toda la Base de Datos se encontrará en la memoria caché (si hay suficiente espacio en la memoria caché, por supuesto). ¿Por qué? Porque cada vez que se lee una página de una tabla esa página es puesta en la memoria caché. La función COUNT(*) lee cada página de una tabla y por consiguiente coloca a cada página de esa tabla en la memoria caché. El stored procedure LLENAR_CACHE realiza esa operación para todas las tablas de la Base de Datos. Como resultado final, todas las páginas de todas las tablas se encontrarán en la memoria caché, si es que hay allí suficiente espacio libre.

¿Y si no hay suficiente espacio libre?

Entonces podríamos poner en la memoria caché a las tablas más utilizadas en los informes.

Listado 2.

CREATE PROCEDURE LLENAR_CACHE
AS
BEGIN
   SELECT COUNT(*) FROM BANCOS;
   SELECT COUNT(*) FROM PRODUCTOS;
   SELECT COUNT(*) FROM CLIENTES;
   SELECT COUNT(*) FROM PROVEEDORES;
   SELECT COUNT(*) FROM STOCK;
   SELECT COUNT(*) FROM MONEDAS;
END; 

Eso hará que todas las operaciones de lectura se realicen súper rápido. Pero durante el trabajo normal diario muchas operaciones de INSERT, UPDATE, y DELETE se irán ejecutando y en la memoria caché ya no se encontrarán las últimas filas insertadas, actualizadas, y borradas.

¿Y entonces?

Entonces lo que debemos hacer es volver a ejecutar el stored procedure LLENAR_CACHE para que nuevamente en la memoria caché se encuentre toda la Base de Datos (como en el caso del Listado 1.) o las tablas más utilizadas (como en el caso del Listado 2.).

Ese proceso podríamos hacerlo manualmente (por ejemplo, cada hora) pero es mucho más inteligente pedirle al Sistema Operativo que realice esa tarea.

Para ello crearemos un archivo de script y un archivo batch.

Listado 3.

CONNECT MiBaseDatos.FDB USER SYSDBA PASSWORD masterkey;

-- Primera vez
EXECUTE PROCEDURE LLENAR_CACHE;
COMMIT;
SHELL PING 192.0.2.2 -n 1 -w 36000 > NUL;

-- Segunda vez
EXECUTE PROCEDURE LLENAR_CACHE;
COMMIT;
SHELL PING 192.0.2.2 -n 1 -w 5000 > NUL;

-- Finalizar
EXIT;

Escribimos el contenido del Listado 3. en el bloc de notas y lo grabamos con el nombre LLENAR_CACHE.SQL

¿Qué hace este archivo de script?

Primero, se conecta a nuestra Base de Datos. Segundo, ejecuta el stored procedure llamado LLENAR_CACHE. Tercero, para finalizar la transacción hace un COMMIT. Cuarto, ejecuta el comando PING del Sistema Operativo donde la dirección IP mostrada es una IP que nunca existe y se usa normalmente para pruebas, la opción -n indica la cantidad de repeticiones, la opción -w indica la cantidad de milisegundos (36.000 milisegundos = 1 hora), y al redirigir a NUL no se ve salida en la pantalla.

En el Listado 3. el stored procedure LLENAR_CACHE se ejecutó 2 veces, con una hora de diferencia entre esas ejecuciones. Si la Base de Datos se usará durante 10 horas seguidas cada día, entonces deberíamos escribir 10 veces el EXECUTE PROCEDURE, el COMMIT, y el SHELL. Para que el Listado 3. no sea muy largo solamente escribí 2 veces esos comandos, en tu caso podrías necesitar escribirlos 8 veces, 10 veces, 14 veces, etc.

Listado 4.

C:
CD "\ARCHIVOS DE PROGRAMA\FIREBIRD\FIREBIRD_2_5\BIN"
ISQL -INPUT C:\USERS\WALTER\DESKTOP\LLENAR_CACHE.SQL

¿Qué hace este archivo batch?

Primero, se ubica en la unidad C:. Segundo, se ubica en la carpeta donde se encuentra el programa ISQL.EXE. Tercero, ejecuta el programa ISQL.EXE con la opción -INPUT y el nombre completo del archivo de script.

En síntesis, ejecuta al programa ISQL.EXE pidiéndole que ejecute los comandos que se encuentran en el archivo de script.

¿Cómo ejecutamos ese archivo batch?

Tenemos dos formas:

  • Manualmente
  • Automáticamente

Manualmente sería haciendo clic en él cada día antes de que el primer usuario empiece a trabajar, por ejemplo a las 7:30 de cada día. El problema es que podríamos olvidarnos de hacer ese clic o llegamos tarde al trabajo o estamos de vacaciones, etc.

Automáticamente sería mediante el Programador de Tareas del Windows. Podríamos programar una tarea que se ejecute cada vez que se enciende el Servidor o cada día a las 7:30. También podríamos hacerlo en nuestra aplicación: si detectamos que nadie está usando la Base de Datos entonces ejecutamos el archivo batch.

Conclusión:

Si la arquitectura que usamos es SuperServer entonces podemos conseguir que los resultados de las consultas sean rapidísimos poniendo a toda la Base de Datos en la memoria caché. Si no tenemos suficiente memoria para poner a toda la Base de Datos en la memoria caché, entonces pondremos allí a las tablas más utilizadas.

Dependiendo de nuestro caso crearíamos un stored procedure como el mostrado en el Listado 1. o como el mostrado en el Listado 2.

Luego creamos un archivo de script como el mostrado en el Listado 3., el cual se encargará de llenar la memoria caché. Como los usuarios estarán insertando, actualizando, y borrando filas de las tablas el contenido de la memoria caché se irá quedando desactualizado. Por eso cada hora (o el tiempo que te parezca conveniente) se vuelve a llenar la memoria caché con el contenido actual de cada tabla.

Finalmente, creamos un archivo batch que se encargará de ejecutar al programa ISQL.EXE teniendo como entrada al archivo de script. Ese archivo batch debería ser ejecutado cada día antes de que el primer usuario se conecte a la Base de Datos. Esto podríamos hacerlo manualmente o mucho mejor, automáticamente.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Anuncios

Evitando actualizaciones concurrentes del mismo registro

2 comentarios

Como ya hemos visto, hay técnicas para evitar conflictos entre transacciones, esos conflictos ocurren cuando dos o más de ellas quieren actualizar o borrar el mismo registro al mismo tiempo.

Evitando conflictos en las transacciones

Pero a veces no podemos usar esas técnicas porque debemos estar actualizando a los mismos registros concurrentemente, sí o sí. ¿Cómo resolvemos el problema en ese caso?

Bien, primero veamos de manera simplificada cuales son las tareas que realiza el motor del Firebird cuando una transacción READ WRITE READ COMMITTED quiere actualizar un registro:

  1. Lee el registro
  2. Evalúa los datos que se guardarán en el nuevo registro
  3. Escribe el nuevo registro en una página de datos

Recuerda que en Firebird cada vez que se usa el comando UPDATE o el comando DELETE se le está agregando un nuevo registro a la Base de Datos. Si la transacción finaliza con un COMMIT entonces el nuevo registro será el que vale, el que sirve, el que se usará de ahí en adelante y el registro viejo será basura. En cambio, si la transacción finaliza con un ROLLBACK el registro anterior seguirá siendo usado y el nuevo pasará a engrosar la basura. Como ves, en ambos casos se genera basura, y ese es el motivo por el cual periódicamente hay que eliminarla.

¿Y cuál es el problema con esta forma de proceder del Firebird?

Una transacción que se inició con los parámetros anteriores, en el paso 1. lee el último registro confirmado (o sea, que finalizó con un COMMIT) y al llegar al paso 3. vuelve a leerlo para asegurarse que se trata del mismo registro. Si no lo hiciera entonces podría estar silenciosamente sobreescribiendo el trabajo de otra persona.

Si el registro que leyó en el paso 1. es distinto del registro que leyó en el paso 3. entonces lanzará una excepción que nos informará que hay un error en la actualización. Un deadlock. Un bloqueo mortal. La actualización falló.

El tiempo que el Firebird demora en completar los pasos del 1. al 3. es pequeñísimo, se mide en milisegundos, así que usualmente no tendremos problemas con eso … salvo cuando varios usuarios estén actualizando a muchísimos registros al mismo tiempo, pues allí sí tales circunstancias podrían darse.

El Firebird solamente “bloquea” a un registro cuando lo está actualizando, mientras no lo esté actualizando el registro está desbloqueado. Esto significa que si queremos bloquear a un registro, debemos actualizarlo, aunque sea escribiendo en él lo mismo que ya tenía escrito.

Y eso es lo que hace el comando SELECT … WITH LOCK.

Y es ese comando el que deberíamos usar cuando queremos ejecutar un proceso de actualización y queremos tener la seguridad de que no tendremos conflictos con otras transacciones. Esta es una forma de transacción pesimista, la cual aunque no es recomendada en Firebird a veces puede ser necesario usar y por ese motivo es que existe.

El algoritmo entonces sería el siguiente:

SET TRANSACTION 
READ WRITE 
WAIT 
READ COMMITTED 
NO RECORD_VERSION 
LOCK TIMEOUT 5 ;
SELECT MisColumnas FROM MiTabla WHERE MiCondición WITH LOCK ;
UPDATE MiTabla SET MisColumnas = MisNuevosValores WHERE MiCondición ;

¿Qué se hizo aquí?

Primero: se establecieron los parámetros de la transacción:

READ WRITE: La transacción puede leer y también puede escribir en los registros

WAIT: Si otra transacción está actualizando el registro, esta transacción esperará

READ COMMITTED: Leerá la última versión confirmada del registro, sin importar cuando ese registro fue confirmado. Si antes de que esta transacción empezara, o después.

NO RECORD_VERSION: No podrá leer un registro que otra transacción está actualizando

LOCK TIMEOUT 5: Esperará un máximo de 5 segundos hasta que la otra transacción que está actualizando al registro finalice con un COMMIT o con un ROLLBACK. Desde luego que podrías poner más o menos segundos, como te parezca.

Segundo: se consultaron y se bloquearon los registros. Al usar la cláusula WITH LOCK le estamos diciendo que también haga un UPDATE silencioso, para que ninguna otra transacción pueda actualizar a este registro antes de que nosotros lo actualicemos. Fíjate que este segundo paso puede fallar, fallará si ya otra transacción tiene bloqueado a cualquiera de los registros de MiTabla que cumplen con MiCondición.

Tercero: se actualizaron los registros. Si el segundo paso finalizó con éxito entonces este tercer paso también finalizará con éxito. Para eso justamente sirve el SELECT … WITH LOCK, para asegurarnos de que el UPDATE finalizará con éxito.

Observaciones:

Este método es muy efectivo, aunque raramente necesario. En Firebird lo normal y lo correcto es que las transacciones sean optimistas y este es un ejemplo de transacción pesimista. Por ello lo recomendable es que los registros que son bloqueados por el SELECT … WITH LOCK sean muy pocos, idealmente solamente un registro y no más de uno.

La ventaja de este método es que si el SELECT … WITH LOCK finalizó con éxito entonces tendremos una seguridad del 100% de que el UPDATE también finalizará con éxito. Desde luego que para que eso sea verdad, MiCondición debe ser la misma tanto en el SELECT como en el UPDATE. Si ponemos condiciones distintas, nada podremos asegurar.

Conclusión:

En general, y como regla, deberíamos evitar usar los comandos UPDATE y DELETE ya que los conflictos entre transacciones solamente pueden ocurrir cuando se usan esos comandos.

Sin embargo, a veces es necesario usar esos comandos, no podemos evitarlo. En esos casos casi siempre es preferible que nuestras transacciones sean optimistas, porque el Firebird está optimizado para usar transacciones optimistas. Solamente cuando no tenemos alternativa deberíamos usar transacciones pesimistas, y en este artículo se vio una técnica de ello.

Artículos relacionados:

Entendiendo a las transacciones

Modos de bloqueo de las transacciones

Transacciones optimistas y transacciones pesimistas

Algo más sobre transacciones optimistas y transacciones pesimistas

¿Por qué en Firebird es preferible que las transacciones sean optimistas?

Evitando conflictos en las transacciones

Entendiendo las páginas de la Base de Datos

El índice del blog Firebird21

El foro del blog Firebird21

 

Evitando conflictos en las transacciones

2 comentarios

A las bases de datos de Firebird normalmente se conectan muchos usuarios y en ocasiones quieren actualizar o borrar la misma fila y al mismo tiempo y eso puede causar conflictos. ¿Cómo los evitamos?

Primero, debemos tener presente que es normal que existan conflictos y que debemos prepararnos para lidiar con ellos.

Segundo, en muchos casos es posible evitar esos conflictos si diseñamos inteligentemente la Base de Datos.

Evitando los conflictos

Los conflictos solamente pueden ocurrir cuando dos o más usuarios quieren actualizar o borrar la misma fila y al mismo tiempo. O sea, cuando se usa el comando UPDATE o el comando DELETE.

Por lo tanto, lo mejor es evitar usar esos comandos y solamente usar el comando INSERT.

¿Y cómo evitamos usar el comando UPDATE o el comando DELETE?

Hay dos técnicas:

  1. Guardar los movimientos. Sería el caso si por ejemplo tenemos una tabla de PRODUCTOS y se hacen compras y ventas de esos productos. Mientras se están haciendo las compras y las ventas jamás actualizamos la tabla de PRODUCTOS, solamente insertamos filas a nuestra tabla de movimientos.
  2. Usando una tabla auxiliar. Sería el caso cuando no guardamos los datos en una tabla de movimientos. Por ejemplo, cuando queremos cambiar el precio de venta de un producto.

En ambos casos, esperamos que nadie esté conectado a la Base de Datos (o al menos, que nadie esté usando la tabla de PRODUCTOS) y mediante un stored procedure hacemos las actualizaciones correspondientes.

En otras palabras, sí usaremos el comando UPDATE o el comando DELETE pero los usaremos cuando estamos 100% seguros de que no pueden ocurrir conflictos.

Ventaja de evitar los conflictos:

  • El trabajo de los usuarios que registran movimientos o actualizaciones es más rápido, porque nunca se encontrarán con una situación de tener que esperar o de deadlock (o sea, una actualización o borrado que falló).

Desventaja de evitar los conflictos:

  • El usuario que debe consultar los datos (mediante el comando SELECT) obtendrá más lentamente los resultados porque los datos se encontrarán en dos o más tablas, no solamente en una tabla.

Conclusión:

Lo normal es que sea preferible evitar los conflictos, ya que los usuarios que registran movimientos o actualizaciones constantemente están trabajando y los conflictos los afectarán negativamente. Además generalmente son muchos más que los usuarios que solamente consultan datos. Quienes necesitan consultar datos generalmente son muy pocas personas y además lo hacen esporádicamente, y con la gran velocidad de las computadoras actuales solamente podrían tardar unos segundos más en obtener un informe, así que no vale la pena preocuparse demasiado por ellos.

Por lo tanto, en el trabajo del día a día es conveniente usar solamente el comando INSERT, y usar el comando UPDATE o el comando DELETE en procesos de actualización que se realizan cuando nadie está usando la Base de Datos (o cuando nadie está usando la tabla que será actualizada).

Si un usuario no puede grabar una fila porque otro usuario tiene bloqueada la fila, eso es muy molesto y debemos hacer lo posible por evitar ese tipo de situaciones.

Artículos relacionados:

Bloqueos mortales

Modos de bloqueo de las transacciones

Transacciones optimistas y transacciones pesimistas

Usando transacciones con acceso exclusivo a tablas

Algo más sobre transacciones optimistas y transacciones pesimistas

¿Por qué en Firebird es preferible que las transacciones sean optimistas?

El índice del blog Firebird21

El foro del blog Firebird21

 

 

Usando Firebird durante 24/7/365

5 comentarios

Algunas aplicaciones requieren acceso a las bases de datos durante 24/7/365, o sea durante las 24 horas del día, los 7 días de la semana y los 365 días del año. En otras palabras: sin interrupción, por ningún motivo.

Algunos ejemplos típicos son:

  • Sanatorios, hospitales, centros de salud
  • Farmacias que trabajan las 24 horas
  • Estaciones de servicio (expendio de combustibles)
  • Policía
  • Fuerzas Armadas
  • etc.

Una aplicación desarrollada para ser utilizada por ellos tiene la obligación de poder funcionar sin interrupción en cualquier momento del día. No le puedes decir al Director de un hospital que entre las 22:00 y las 23:00 no pueden registrarse los datos de los pacientes porque hay que hacerle un mantenimiento a la Base de Datos. Ni decirle a un General que si mañana el país es atacado por el enemigo entre las 02:00 y las 03:00 entonces no podrá usar la Base de Datos porque ya tiene mucha basura y que hiciste un proceso para que elimine esa basura pero requieres que nadie esté conectado a la Base de Datos.

¿Y cuál es el problema?

El problema radica en que a las bases de datos de Firebird debe hacérsele una tarea de mantenimiento llamada sweep de vez en cuando. El sweep (barrido, en castellano) tiene como finalidad eliminar a todas las versiones inútiles de las filas que fueron modificadas o borradas. Cada vez que una fila es modificada o borrada se crea una versión de esa fila llamada delta. La parte positiva de esto es que puede realizarse un ROLLBACK y dejar a la fila como se encontraba anteriormente si el usuario cambió de idea. La parte negativa es que se va acumulando basura y todas las operaciones se van volviendo más lentas y por lo tanto esa basura debe ser eliminada alguna vez.

 Y el sweep puede demorar mucho tiempo.

¿Cuándo se inicia el sweep?

El sweep puede iniciarse automáticamente (que es su valor por defecto) cuando la diferencia entre la OST (Oldest Snapshot Transaction) y la OAT (Oldest Active Transaction) es mayor que el intervalo predefinido del sweep. Ese intervalo es de 20.000 por defecto pero puede modificarse.

Si el intervalo del sweep es de 0, entonces el sweep nunca se iniciará automáticamente. Eso implica que hay que iniciarlo manualmente. Para ello se usa el programa GFIX con la opción –sweep. En este caso lo recomendable es que nadie esté usando la Base de Datos mientras se realiza el sweep.

 ¿Qué ocurre cuándo se inicia el sweep?

Que la transacción que hizo iniciar al sweep no empezará hasta que el sweep finalice. Supongamos que el intervalo del sweep es de 20.000, entonces el desafortunado usuario que quiso iniciar una transacción cuando OST – OAT fue de 20.001 tendrá que esperar hasta que finalice el sweep antes de que su transacción pueda empezar. A quienes iniciaron sus transacciones antes que él no les afectará, pero a él sí. Y si justo es un General de muy malas pulgas, entonces…

 ¿Se puede evitar que se inicie el sweep sin perjudicar el rendimiento de la Base de Datos?

Sí.

La buena noticia es que si las aplicaciones que usan la Base de Datos están correctamente diseñadas entonces nunca será necesario realizar el sweep.

La basura que se encuentra dentro de una Base de Datos puede deberse a los siguientes motivos:

  • Una operación de UPDATE que finalizó con un COMMIT
  • Una operación de DELETE que finalizó con un COMMIT
  • Una operación de UPDATE que finalizó con un ROLLBACK
  • Una operación de DELETE que finalizó con un ROLLBACK

La basura dejada por las transacciones que finalizaron con un COMMIT es recolectada (garbage collection) automáticamente cuando se hace un SELECT a esas filas. Por lo tanto, para eliminar toda la basura de una tabla que fue dejada por los COMMIT se puede escribir un simple comando: SELECT * FROM MiTabla y listo: esa tabla ya no tendrá basura dejada por los COMMIT.

La basura dejada por el ROLLBACK sin embargo, solamente puede ser eliminada con un sweep.

Por lo tanto la solución es muy simple y muy sencilla: No usar ROLLBACK.

¿Y cómo se puede evitar usar ROLLBACK?

Hay varias alternativas posibles, una posible solución sería esta:

  • Haces un SELECT a la fila que te interesa, por supuesto finalizando ese SELECT con un COMMIT. El resultado de ese SELECT lo guardas en una tabla temporal que no es de Firebird, por ejemplo en una tabla .DBF
  • Le muestras la fila de la tabla .DBF al usuario
  • Le permites que cambie los campos o que borre la fila
  • Si decide Guardar los cambios, entonces haces un UPDATE a la tabla de Firebird con los valores que se ven en la pantalla y luego el COMMIT correspondiente
  • Si decide Cancelar los cambios, entonces allí termina todo, no tocas la tabla de Firebird para nada

Como ves, en estos casos se trabaja un poco más pero te aseguras que todas las transacciones terminen con un COMMIT.

Recolectando la basura

Adicionalmente, en tu aplicación deberías tener un proceso que se encargue de hacer un SELECT * a todas las tablas de tu Base de Datos. Cuando el usuario ejecuta ese proceso entonces se hace un SELECT * FROM MiTabla1, SELECT * FROM MiTabla2, SELECT * FROM MiTabla3, etc.

Recordatorio:

La técnica mostrada en este artículo es necesaria solamente para las bases de datos que deben estar activas durante 24/7/365. En los demás casos se puede seguir el procedimiento normal de hacer el sweep automáticamente o manualmente.

Conclusión:

Las bases de datos de Firebird pueden perfectamente ser usadas en entornos que las requieren abiertas durante las 24 horas, los 7 días de la semana y los 365 días del año. Lo que debemos tener en cuenta en esos casos es que deberíamos evitar el sweep porque el sweep puede demorar mucho tiempo en bases de datos muy grandes. La forma de evitar el sweep es nunca terminar las transacciones con ROLLBACK porque las transacciones que terminan con ROLLBACK dejan basura que solamente puede ser eliminada con un sweep, en cambio la basura dejada por las transacciones que terminan con un COMMIT puede ser eliminada con un comando SELECT * FROM MiTabla.

 Entonces, el problema es causado por la forma en que Firebird actúa cuando se hace un UPDATE o un DELETE que finalizaron con un ROLLBACK pero puede ser solucionado si la aplicación finaliza todas las transacciones con un COMMIT.

Artículos relacionados:

La arquitectura MGA

Entendiendo a las transacciones

Entendiendo a los identificadores de las transacciones

Entendiendo sweep y garbage collection

El índice del blog Firebird21

El foro del blog Firebird21

 

¿Por qué en Firebird es preferible que las transacciones sean optimistas?

4 comentarios

Para entenderlo, debes tener bien en claro cuando se puede actualizar (UPDATE) o borrar (DELETE) una fila en Firebird.

En Firebird, todo lo que hagamos estará siempre dentro de una transacción, siempre, sin excepción. Todas las operaciones (INSERT, UPDATE, DELETE, SELECT, FETCH) siempre se encuentran dentro de una transacción.

Y una transacción puede actualizar (UPDATE) o borrar (DELETE) una fila solamente si la tiene bloqueada. Si una transacción no ha bloqueado a una fila entonces no podrá actualizarla ni borrarla, le será imposible hacerlo.

En un determinado momento solamente puede haber una transacción bloqueando a una fila. Es imposible que dos o más transacciones tengan a una fila bloqueada al mismo tiempo. No se puede. Solamente una transacción la puede estar bloqueando.

Y solamente la transacción que la tiene bloqueada la puede actualizar (UPDATE) o borrar (DELETE), las demás transacciones no podrán hacerlo hasta que la transacción que la tiene bloqueada finalice (con un COMMIT o con un ROLLBACK, pero debe finalizar antes de que esa fila pueda ser actualizada o borrada por otra transacción).

Por lo tanto, lo que ocurre es lo siguiente:

  • Se bloquea la fila
  • Se la actualiza (con el comando UPDATE) o se la borra (con el comando DELETE)
  • Finaliza la transacción

Entonces, la pregunta es ¿en qué momento se bloquea la fila?

La transacción optimista piensa: “Cuando necesite actualizar la fila la encontraré desbloqueada y entonces yo podré bloquearla”

La transacción pesimista piensa: “No sé si cuando necesite actualizar la fila la encontraré desbloqueada por eso lo mejor es que la bloquee ahora mismo para que cuando necesite actualizarla ya la tenga bloqueada”.

Ya ves por que a una se le llama “optimista” y a la otra se le llama “pesimista”.

¿Cómo actúa una transacción optimista?

  1. Inicia la transacción
  2. Realiza ochopotecientas tareas
  3. Bloquea la fila
  4. Actualiza la fila
  5. Realiza ochopotecientas tareas más
  6. Finaliza la transacción

¿Cómo actúa una transacción pesimista?

  1. Inicia la transacción
  2. Bloquea la fila
  3. Realiza ochoptecientas tareas
  4. Actualiza la fila
  5. Realiza ochopotecientas tareas más
  6. Finaliza la transacción

¿Cuál es la diferencia?

Que la transacción optimista bloquea a la fila inmediatamente antes de actualizarla, un microsegundo antes; en cambio la transacción pesimista la bloquea mucho antes de utilizarla. Como puedes ver, los pasos 2. y 3. están invertidos, los demás son iguales.

¿Y esto qué implica?

Que las transacciones pesimistas mantienen a la fila bloqueada durante mucho más tiempo, porque realizar esas “ochopotecientas tareas” puede demorar bastante.

Pero además hay otro problema con las transacciones pesimistas y es que pueden estar bloqueando a muchas filas que jamás usarán. Si una transacción pesimista bloquea a una tabla está bloqueando a todas las filas de esa tabla, aunque solamente necesite actualizar algunas. Si una transacción pesimista bloquea a una tabla que tiene 2500 filas aunque solamente necesite actualizar 200 de esas filas, ninguna de las 2500 filas podrán ser actualizadas por otras transacciones, no solamente las 200 filas que necesita estarán bloqueadas, las 2500 filas estarán bloqueadas.

¿Qué ocurre si una transacción quiere bloquear una fila que ya está bloqueada por otra transacción?

Que no podrá bloquearla. Eso porque en un determinado momento una fila puede estar bloqueada por una sola transacción, no pueden dos transacciones bloquear a la misma fila en el mismo momento. El intento de bloquear a una fila que está bloqueada por otra transacción se conoce como conflicto o colisión. La transacción que intentó bloquear a la fila y falló tiene dos alternativas:

a) Espera hasta que la transacción que tiene a la fila bloqueada finalice con un COMMIT o con un ROLLBACK, para después de eso volver a intentar bloquear dicha fila. Eso ocurre cuando el modo de bloqueo de la transacción es WAIT (esperar)

b) Sale inmediatamente, con un mensaje de error. Eso ocurre cuando el modo de bloqueo de la transacción es NO WAIT (no esperar)

¿Cuáles son las ventajas de usar transacciones optimistas?

  1. Las filas quedan bloqueadas durante muy poco tiempo
  2. La probabilidad de que haya conflictos (colisiones) con otras transacciones es muy pequeña

¿Cuáles son las ventajas de usar una transacción pesimista?

En Firebird es extremadamente raro necesitar usar transacciones pesimistas, en general casi todo puede hacerse con transacciones optimistas. Si requieres de muchas transacciones pesimistas entonces es muy probable que aún no entiendas como funciona Firebird. Pero también usarlas tiene algunas ventajas, ellas son:

  1. Rápidamente, se puede numerar secuencialmente y sin que haya números faltantes y sin provocar deadlocks
  2. Se pueden ejecutar procesos que requieren que otras transacciones no estén insertando, actualizando, o borrando filas

Resumiendo:

En Firebird todo se hace dentro de una transacción, ninguna operación (INSERT, UPDATE, DELETE, SELECT, FETCH) puede hacerse afuera de una transacción. Las transacciones pueden ser optimistas o pesimistas. Las operaciones de UPDATE y de DELETE requieren que la fila sea bloqueada antes de ser actualizada o borrada, no se puede actualizar ni borrar una fila que previamente no fue bloqueada. Las transacciones optimistas bloquean a la fila durante muy poco tiempo, las transacciones pesimistas bloquean a la fila durante más tiempo. Cuanto menos tiempo una fila esté bloqueada mucho mejor, porque eso reduce el riesgo de colisiones. Ocurre una “colisión” cuando una transacción quiere actualizar o borrar una fila que otra transacción tiene bloqueada. Cuando ocurre una colisión la transacción que no pudo bloquear a la fila solamente tiene dos posibilidades: a) espera hasta que la transacción que tiene a la fila bloqueada termine, o b) sale inmediatamente con un mensaje de error.

En aplicaciones multi-usuario es común y es normal que existan colisiones porque dos o más transacciones a veces querrán actualizar la misma fila al mismo tiempo. Eso a veces es inevitable. Pero un buen profesional siempre tratará de que esas colisiones ocurran la menor cantidad de veces posibles y por eso en Firebird hay que tratar de usar siempre transacciones optimistas, porque son las que durante menor tiempo tienen a las filas bloqueadas.

Artículos relacionados:

Entendiendo a las transacciones

Transacciones optimistas y transacciones pesimistas

Algo más sobre transacciones optimistas y transacciones pesimistas

El índice del blog Firebird21

El foro del blog Firebird21

 

Usando vistas para insertar, actualizar, y borrar filas

7 comentarios

Probablemente ya has usado vistas para consultar datos y si no lo has hecho seguro que pronto lo harás, ya que eso es lo correcto. Tener las consultas precompiladas aumenta la velocidad de respuesta.

Sin embargo, ¿sabías que también puedes usar las vistas para insertar, actualizar, o borrar filas?

En un entorno multiusuario, donde muchas personas pueden estar al mismo tiempo conectadas a una Base de Datos es importantísimo limitar lo que cada una de esas personas puede hacer. De lo contrario, alguien podría estar haciendo algo que debería tener prohibido. Por ejemplo, un vendedor no debería tener la posibilidad de cambiar el precio de venta de un producto porque eso se presta a corrupción. Ni tampoco poder cambiar el porcentaje de comisión que cobra por cada venta.

Si necesitas que un usuario pueda insertar, actualizar, o borrar filas de una tabla puedes hacerlo de dos formas:

  1. Otorgándole derechos sobre esa tabla
  2. Otorgándole derechos sobre una vista, la cual se encargará de realizar las operaciones sobre dicha tabla

El problema de hacerlo con la primera forma es que el usuario podrá también ver y cambiar columnas que no debería poder ver o cambiar. Si le das el derecho de consulta sobre la tabla PRODUCTOS hace un SELECT * FROM PRODUCTOS y ve todas las filas y todas las columnas de esa tabla.

Mediante la segunda forma, en cambio, puedes controlar muchísimo mejor lo que puede y lo que no puede hacer. Si le das el derecho de consulta sobre la vista V_PRODUCTOS  la cual contiene un SELECT PRD_CODIGO, PRD_NOMBRE FROM PRODUCTOS, entonces solamente podrá ver el código y el nombre de cada producto y nada más.

Desde el punto de vista del Firebird puedes tratar a una vista como si fuera un usuario. Es decir que a una vista puedes otorgarle derechos (privilegios, permisos) sobre una tabla.

Y a los usuarios humanos les otorgas derechos sobre la vista, no sobre la tabla.

Por lo tanto, en lugar de escribir esto:

GRANT ALL ON PRODUCTOS TO ALICIA

GRANT ALL ON PRODUCTOS TO CLAUDIA

GRANT ALL ON PRODUCTOS TO MIRTHA

Sería mucho mejor que escribas esto:

GRANT ALL ON PRODUCTOS TO V_PRODUCTOS

GRANT ALL ON V_PRODUCTOS TO ALICIA

GRANT ALL ON V_PRODUCTOS TO CLAUDIA

GRANT ALL ON V_PRODUCTOS TO MIRTHA

Y fíjate en la primera línea, allí se trata a la vista V_PRODUCTOS como si fuera una persona, ¡¡¡se le están otorgando derechos sobre una tabla!!!. Los derechos se le otorgan a la vista, no a una persona.

¿Qué significa todo esto?

Que ni ALICIA, ni CLAUDIA, ni MIRTHA podrán hacer algo con la tabla PRODUCTOS, no tienen derechos ahí, ni siquiera para consultarla. Pero sí tienen derechos sobre la vista V_PRODUCTOS y por lo tanto estarán limitadas a lo que la vista haga.

Hasta ahí todo bien pero ¿cómo hago para insertar, actualizar, o borrar filas usando vistas?

Muy simple, las vistas también tienen triggers. Entonces es en esos triggers donde escribirás el código que insertará, actualizará, o borrará filas de la/s tabla/s subyacentes. Aunque en realidad no es obligatorio que uses triggers pero sí es conveniente porque te da más control.

VISTAS1

Captura 1. Si haces clic en la imagen la verás más grande

VISTAS2

Captura 2. Si haces clic en la imagen la verás más grande

VISTAS3

Captura 3. Si haces clic en la imagen la verás más grande

Como puedes ver en la Captura 3, esta vista solamente nos permite insertar o actualizar las columnas PRD_IDENTI, PRD_CODIGO, y PRD_NOMBRE, pero no podemos hacer lo mismo con las columnas PRD_PRECTO y PRD_PREVTA. ¿Por qué no? porque en el trigger solamente podemos usar las columnas que pertenecen a la vista. Ni la columna PRD_PRECTO ni la columna PRD_PREVTA pertenecen a la vista, por lo tanto no las podemos usar en el trigger. Si lo intentas, obtendrás un mensaje de error.

En otras palabras, si a un usuario le otorgamos derechos sobre la vista V_PRODUCTOS él solamente podrá ver, insertar, y actualizar las columnas PRD_IDENTI, PRD_CODIGO, y PRD_NOMBRE, y ninguna columna más, porque ninguna otra columna pertenece a la vista.

Por lo tanto, si necesitamos que un usuario pueda acceder a las columnas PRD_PRECTO y PRD_PREVTA entonces deberemos crear otra vista, que le otorgue acceso a esas columnas.

¿Pero hacerlo así no nos obliga a trabajar más?

Pues sí, pero no puedes tenerlo todo fácil en la vida. Se trabaja más pero en contrapartida tu aplicación será mucho más segura y mucho más confiable. Si cualquiera puede meter las pezuñas en tus tablas eso tarde o temprano te provocará grandes problemas. Así que mejor evitarlos desde el principio.

UPDATE OR INSERT INTO V_PRODUCTOS
         (PRD_IDENTI, PRD_CODIGO, PRD_NOMBRE)
  VALUES (0         , '1234'    , 'Producto xxx');

Como puedes ver en el código fuente de arriba, la inserción, actualización, o borrado se hace de la forma normal, las diferencias son:

  1. Se escribe el nombre de una vista, no el nombre de una tabla
  2. Solamente se pueden usar las columnas que pertenecen a la vista

Conclusión:

Usar vistas para realizar todas las operaciones de manipulación de tablas (inserción, actualización, borrado, consulta) es lo mejor, es lo correcto, es lo ideal. ¿Por qué? porque eso nos da un control total, 100%, sobre lo que cada usuario puede hacer o no hacer.

De esta manera los usuarios nunca, y por ningún motivo, acceden directamente a las tablas. Todos los accesos lo hacen a través de las vistas. Y en nuestras vistas podemos usar la cláusula WHERE para establecer condiciones. Por lo tanto, solamente podrán hacer lo que específicamente queremos que puedan hacer. Y nada más.

Artículo relacionado:

El índice del blog Firebird21

 

Conociendo la aplicación que insertó filas a una tabla

2 comentarios

Cuando una Base de Datos puede ser accedida desde varias aplicaciones es muy importante saber cual aplicación insertó las filas que tiene cada tabla.

¿Por qué?

Porque solamente la aplicación que insertó la fila debería tener el derecho de modificar o de borrar esa fila. Si otra aplicación pudiera hacerlo, eso solamente acarreará problemas. Imagínate que la aplicación de VENTAS registró una venta y cuando se la quiere consultar no se la encuentra por ningún lado porque CONTABILIDAD borró esa venta. Eso es inadmisible.

¿Qué necesitaremos?

  1. Una tabla APLICACIONES, para guardar los nombres de las aplicaciones que pueden usarse
  2. Una tabla GTT, para guardar los datos de la conexión actual, incluyendo el Identificador de la aplicación usada para el acceso
  3. Un trigger de Base de Datos que le inserte una fila a la tabla GTT
  4. Desde nuestra aplicación actualizar la tabla GTT para que tenga el Identificador de aplicación correcto
  5. Agregarle una columna a todas las tablas que pueden usarse por más de una aplicación
  6. Dos excepciones, para mostrar los mensajes de error
  7. Agregarle un trigger a cada tabla que puede usarse por más de una aplicación, para decidir si la fila puede ser modificada o eliminada

Tabla APLICACIONES

APLICACIÓN1

Captura 1. Si haces clic en la imagen la verás más grande

CREATE TABLE APLICACIONES (
   APL_IDENTI D_IDENTIFICADOR NOT NULL,
   APL_NOMBRE D_NOMBRE40);

   ALTER TABLE APLICACIONES ADD CONSTRAINT PK_APLICACIONES PRIMARY KEY (APL_IDENTI);

   ALTER TABLE APLICACIONES ADD CONSTRAINT UQ_APLICACIONES UNIQUE (APL_NOMBRE);

SET TERM ^ ;

CREATE TRIGGER BI_APLICACIONES_APL_IDENTI FOR APLICACIONES
   ACTIVE BEFORE INSERT
   POSITION 0
AS
BEGIN
   IF (NEW.APL_IDENTI IS NULL OR NEW.APL_IDENTI = 0) THEN
      NEW.APL_IDENTI = GEN_ID(APLICACIONES_APL_IDENTI_GEN, 1);
END^

SET TERM ; ^

El contenido de esta tabla podría ser algo así:

APLICACIÓN2

Captura 2. Si haces clic en la imagen la verás más grande

Tabla GTT: CONEXION_ACTUAL

En una tabla GTT guardamos los datos que necesitamos conocer de la conexión que actualmente está usando el usuario.

APLICACIÓN5

Captura 3. Si haces clic en la imagen la verás más grande

Trigger de Base de Datos para insertarle una fila a la tabla GTT

CREATE TRIGGER NEW_DBTRIGGER_C
   ACTIVE ON CONNECT
      POSITION 0
AS
BEGIN

   INSERT INTO
      CONEXION_ACTUAL
      (CON_IDENTI, CON_USUARI, CON_TIMEST)
   VALUES
      (CURRENT_CONNECTION, CURRENT_USER, CURRENT_TIMESTAMP) ;

END;

El valor de la columna faltante (CON_APLICA) debería ser puesto por la aplicación correspondiente.

Contenido de la tabla CONEXION_ACTUAL

APLICACIÓN6Captura 4. Si haces clic en la imagen la verás más grande

Ejemplo:

A la tabla CLIENTES pueden insertarle filas las aplicaciones de Contabilidad, Facturación, y Ventas. Si la fila fue insertada por Contabilidad entonces Facturación y Ventas no deberían tener el derecho de modificar esa fila ni de borrarla. De la misma manera, si la fila fue insertada por Facturación o por Ventas las otras aplicaciones no deberían poder cambiarla.

Cualquier aplicación puede consultar una fila, pero solamente la aplicación que la insertó puede modificarla o borrarla.

APLICACIÓN3

Captura 5. Si haces clic en la imagen la verás más grande

Y para ver los nombres de las aplicaciones que insertaron las filas podríamos escribir algo como:

SELECT
   C.CLI_IDENTI,
   C.CLI_NOMBRE,
   C.CLI_IDEAPL,
   A.APL_NOMBRE AS CLI_NOMAPL
FROM
   CLIENTES C
JOIN
   APLICACIONES A
      ON C.CLI_IDEAPL = A.APL_IDENTI

que nos dará como resultado algo similar a:

APLICACIÓN4

Captura 6. Si haces clic en la imagen la verás más grande

Aquí como puedes ver las filas de MARCELA, DIANA y MIRTA fueron insertadas por CONTABILIDAD, en cambio la fila de SILVIA fue insertada por FACTURACIÓN. Eso implica que si debemos modificar o borrar las filas de MARCELA, DIANA, o MIRTA deberemos hacerlo desde la CONTABILIDAD y si queremos modificar o borrar la fila de SILVIA deberemos hacerlo desde FACTURACIÓN.

Esto nos asegura que nunca una aplicación modifique o borre las filas que no le corresponden.

Las excepciones para mostrar los mensajes de error

CREATE EXCEPTION E_UPDATE_NO_ADMITIDO 'No puedes modificar el contenido de esta fila';

CREATE EXCEPTION E_DELETE_NO_ADMITIDO 'No tienes permiso para borrar filas de esta tabla';

El trigger para modificar o borrar filas de la tabla CLIENTES

CREATE TRIGGER BUD_CLIENTES1 FOR CLIENTES
   ACTIVE BEFORE UPDATE OR DELETE
      POSITION 0
AS
   DECLARE VARIABLE lnAplicacion SMALLINT;
BEGIN

   lnAplicacion = (SELECT CON_APLICA FROM CONEXION_ACTUAL);

   IF (UPDATING AND NEW.CLI_IDEAPL <> lnAplicacion) THEN
      EXCEPTION E_UPDATE_NO_ADMITIDO;

   IF (DELETING AND OLD.CLI_IDEAPL <> lnAplicacion) THEN
      EXCEPTION E_DELETE_NO_ADMITIDO;

END;

Terminando:

Y listo, eso es todo, ahora solamente la aplicación que insertó una fila podrá modificar o borrar esa fila. Los intentos realizados por las demás aplicaciones serán  rechazados, como tiene que ser.

Conclusión:

Siempre es muy importante delimitar exactamente que puede hacer cada aplicación que se conecta a nuestra Base de Datos, no podemos permitir que una aplicación modifique o borre filas que fueron insertadas por otra aplicación porque eso solamente nos traerá problemas y ningún beneficio.

Artículos relacionados:

Los triggers de la Base de Datos

El índice del blog Firebird21

Older Entries