7. Gestión del acceso concurrente a los datos

Hasta ahora hemos utilizado tablas de las que éramos los únicos usuarios. En la práctica, en una máquina multiusuario, los datos suelen compartirse entre diferentes usuarios. Entonces surge la pregunta: ¿Quién puede utilizar tal o cual tabla y de qué forma (consulta, inserción, eliminación, adición, ...)?

7.1. Creación de usuarios de Firebird

Cuando hemos trabajado con IB-Expert, nos hemos conectado como usuario SYSDBA. Esta información se puede encontrar en las propiedades de la conexión abierta a SGBD:

A la derecha, vemos que el usuario conectado es [SYSDBA]. Lo que no se ve es su contraseña [masterkey]. [SYSDBA] es un usuario especial de Firebird: tiene todos los derechos sobre todos los objetos gestionados por SGBD. Se pueden crear nuevos usuarios con IBExpert utilizando option, [Tools / User Manager] o el siguiente icono:

Aparece la ventana de gestión de usuarios:

El botón [Add] permite crear nuevos usuarios:

Creemos, pues, los siguientes usuarios:

nombre	contraseña
ADMIN1	admin1
ADMIN2	admin2
SELECT1	select1
SELECT2	select2
UPDATE1	actualizar1
UPDATE2	actualizar2

7.2. Conceder derechos de acceso a los usuarios

Una base de datos pertenece a quien la ha creado. Las bases de datos que hemos creado hasta ahora pertenecían al usuario [SYSDBA]. Para ilustrar el concepto de derechos, creemos (Database / Create Database) una nueva base de datos con la identidad [ADMIN1, admin1]:

y la registramos con el alias DBACCES (ADMIN1). El uso de alias permite abrir conexiones en una misma base asignándoles identificadores diferentes, lo que facilita su localización en el explorador de bases de datos de IBExpert. :

Creemos ahora las dos tablas siguientes: TA y TB:

Tabla TA

Tabla TB

Estas tablas no tienen relación entre sí.

Con IB-Expert, creemos una segunda conexión a la base de datos [DBACCES], esta vez con el nombre [ADMIN2 / admin2]. Para ello, utilizamos option [Database / Register Database]:

Seleccionemos DBACCES (ADMIN2) y abramos un editor SQL (Shift + F12):

Tendremos la oportunidad de utilizar varias conexiones en la misma base [DBACCES]. Para cada una de ellas, tendremos un editor SQL. En [1], el editor SQL indica el alias de la base de datos conectada. Utilice esta indicación para saber en qué editor SQL se encuentra. Esto será importante, ya que vamos a crear conexiones que no tendrán los mismos derechos de acceso a los objetos de la base de datos.

Solicitemos el contenido de la tabla TA:

Obtenemos el siguiente mensaje de error:

¿Qué significa? La base [DBACCESS] fue creada por el usuario [ADMIN1] y, por lo tanto, es de su propiedad. Solo él tiene acceso a los distintos objetos de esta base. Puede conceder derechos de acceso a otros usuarios con el comando SQL GRANT. Este comando tiene varias sintaxis. Una de ellas es la siguiente:

`syntaxe`	GRANT privilegio1, privilegio2, ...\| ALL PRIVILEGES ON table/vue TO usuario1, usuario2, ...\| PUBLIC [ WITH GRANT OPTION ]
`action`	concede privilegios de acceso privilègei o todos los privilegios (ALL PRIVILEGES) sobre table o vue a los usuarios utilisateuri o a todos los usuarios ( PUBLIC ). La cláusula WITH GRANT OPTION permite a los usuarios que han recibido los privilegios transmitirlos a su vez a otros usuarios.

Entre los privilegios privilègei que se pueden conceder se encuentran los siguientes:

`DELETE`	derecho a utilizar el comando DELETE en la tabla o vista.
`INSERT`	derecho a utilizar el comando INSERT en la tabla o vista
`SELECT`	derecho a utilizar el comando SELECT en la tabla o vista
`UPDATE`	derecho a utilizar el comando UPDATE en la tabla o vista. Este derecho puede restringirse a determinadas columnas mediante la sintaxis: GRANT update ( col1, col2, ...) ON tabla/vista TO usuario1, usuario2, ...\| PUBLIC [ WITH GRANT OPTION ]

Otorguemos al usuario [ADMIN2] el derecho SELECT sobre la tabla TA. Solo el propietario de la tabla puede conceder este derecho, c.a.d. Aquí [ADMIN1]. Vayamos a la conexión DBACCES (ADMIN1) y abramos un nuevo editor SQL (Shift+F12):

A continuación, pasaremos de un editor SQL a otro. Para orientarnos, podemos utilizar el option [Windows] del menú:

Arriba vemos los dos editores SQL, cada uno asociado a un usuario concreto. Volvamos al editor SQL (ADMIN1) y ejecutemos el siguiente comando:

A continuación, validémoslo con un COMMIT:

Una vez hecho esto, pasamos al editor del usuario ADMIN2 para volver a ejecutar el SELECT que había fallado:

Aparece el siguiente mensaje de error:

El usuario [ADMIN2] sigue sin tener permiso para consultar la tabla [TA]. De hecho, parece que los derechos de un usuario se cargan en el momento en que se conecta. [ADMIN2] seguiría teniendo entonces los mismos derechos que al inicio de su conexión, es decir, ninguno. Comprobémoslo. Desconectemos al usuario [ADMIN2]:

seleccionar su conexión
solicitar la desconexión haciendo clic con el botón derecho del ratón sobre la conexión y seleccionando option [Deconnect from database] o (Shift + Ctrl + D)

Si un panel solicita un [COMMIT], introduzca el [COMMIT]. A continuación, volvamos a conectar al usuario [ADMIN2] utilizando el option [Reconnect] anterior. Una vez hecho esto, volvamos al editor SQL (ADMIN2) y volvamos a ejecutar la consulta SELECT que falló:

Se obtiene entonces el siguiente resultado:

Esta vez, ADMIN2 puede consultar la tabla TA gracias al derecho SELECT que le ha concedido su propietario, ADMIN1. Normalmente, este es el único derecho que tiene. Comprobémoslo. Siguiendo en el editor SQL (ADMIN2):

La pantalla de la derecha muestra que ADMIN2 no tiene el derecho DELETE sobre la tabla TA.

Volvamos al editor SQL (ADMIN1) para otorgar más derechos al usuario ADMIN2. Ejecutamos sucesivamente los dos comandos siguientes:

El primer comando otorga al usuario ADMIN2 todos los derechos de acceso a la tabla [TA], además de la posibilidad de conceder también derechos (WITH GRANT OPTION)
el segundo comando valida el anterior

Una vez hecho esto, al igual que antes, renovemos la conexión del usuario [ADMIN2] (Desconectar / Reconectar) y, a continuación, en el editor SQL (ADMIN2), escribamos los siguientes comandos:

ADMIN2 ha podido eliminar todas las líneas de la tabla TA. Anulemos esta eliminación con un ROLLBACK:

Comprobemos que ADMIN2 puede, a su vez, otorgar derechos sobre la tabla TA.

Ahora abramos una conexión en la base de datos [DBACCES] (Base de datos / Registrar base de datos) con el nombre [SELECT1 / select1], uno de los usuarios creados anteriormente, y luego hagamos doble clic en el enlace así creado en [Database Explorer]:

Seleccionemos esta nueva conexión y abramos un nuevo editor SQL (Shift + F12) para introducir los siguientes comandos:

El usuario SELECT1 tiene el derecho SELECT en la tabla TA. ¿Puede transferir este derecho al usuario SELECT2?

La operación ha fallado porque el usuario SELECT1 no ha recibido el derecho a transferir el derecho SELECT que recibió del usuario ADMIN2. Para ello, habría sido necesario que elusuario ADMIN2 utilizara la cláusula WITH GRANT OPTION en su orden SQL GRANT. Las reglas de transmisión son sencillas:

un usuario solo puede transmitir los derechos que ha recibido y nada más
y solo puede transmitirlos si los ha recibido con el privilegio [WITH GRANT OPTION]

Un derecho concedido puede retirarse con el comando REVOKE:

`syntaxe`	REVOKE privilegio1, privilegio2, ...\| ALL PRIVILEGES ON table/vue FROM usuario1, usuario2, ...\| PUBLIC
`action`	elimina los privilegios de acceso privilègei o todos los privilegios (ALL PRIVILEGES) en table o vue a los usuarios utilisateuri o a todos los usuarios ( PUBLIC ).

Probemos. Volvamos al editor SQL de ADMIN2 para eliminar el derecho SELECT que le hemos concedido al usuario SELECT1:

Desconectemos y volvamos a conectar la sesión del usuario SELECT1. A continuación, en el editor SQL (SELECT1), solicitemos el contenido de la tabla TA:

El usuario SELECT1 ha perdido efectivamente su derecho de lectura de la tabla TA. Cabe señalar que fue ADMIN2 quien le concedió este derecho y ADMIN2 quien se lo retiró. Si ADMIN1 intenta retirárselo, no se señala ningún error, pero se puede comprobar después que SELECT1 ha conservado su derecho SELECT.

Se puede conceder un derecho a todos con la sintaxis: GRANT derecho(s) ON tabla / vista TO PUBLIC. Concedamos así el derecho SELECT sobre la tabla TA a todos. Para ello, podemos utilizar ADMIN1 o ADMIN2. Utilizamos ADMIN2:

Creemos una conexión en la base de datos con el usuario USER1 / user1:

Con la sesión DBACCES (USER1), abramos un nuevo editor SQL (Shift + F12) y escribamos los siguientes comandos:

El usuario USER1 tiene efectivamente el derecho SELECT sobre la tabla TA.

7.3. Las transacciones

7.3.1. Niveles de aislamiento

Dejamos ahora el tema de los derechos de acceso a los objetos de una base de datos para abordar el de los accesos concurrentes a dichos objetos. Dos usuarios con derechos de acceso suficientes a un objeto de la base, una tabla por ejemplo, quieren utilizarlo al mismo tiempo. ¿Qué ocurre?

Cada usuario trabaja dentro de una transacción. Una transacción es una secuencia de órdenes SQL que se ejecuta de forma «atómica»:

o bien todas las operaciones se realizan con éxito
o bien falla una de ellas y, en ese caso, se anulan todas las anteriores

Al final, las operaciones de una transacción o bien se han aplicado todas con éxito o bien ninguna se ha aplicado. Cuando el propio usuario tiene el control de la transacción (como ocurre en todo este documento), valida una transacción mediante una orden COMMIT o la cancela mediante una orden ROLLBACK.

Cada usuario trabaja en una transacción que le pertenece. Normalmente se distinguen cuatro niveles de aislamiento entre los diferentes usuarios:

Lectura no confirmada
Lectura confirmada
Lectura repetible
Serializable

Lectura no confirmada

Este modo de aislamiento también se denomina «lectura sucia». A continuación se muestra un ejemplo de lo que puede ocurrir en este modo:

un usuario U1 inicia una transacción en una tabla T
un usuario U2 inicia una transacción en esa misma tabla T
el usuario U1 modifica las filas de la tabla T, pero aún no las valida
el usuario U2 «ve» estas modificaciones y toma decisiones basándose en lo que ve
el usuario cancela su transacción mediante un ROLLBACK

Se observa que, en el punto 4, el usuario U2 ha tomado una decisión basándose en datos que posteriormente resultarán ser falsos.

Lectura confirmada

Este modo de aislamiento evita el problema anterior. En este modo, el usuario U2 en el paso 4 no «verá» los cambios realizados por el usuario U1 en la tabla T. Solo las verá después de que U1 haya completado su transacción.

En este modo, también denominado «Unrepeatable Read», pueden darse las siguientes situaciones:

un usuario U1 inicia una transacción en una tabla T
un usuario U2 inicia una transacción en esa misma tabla T
El usuario U2 ejecuta un SELECT para obtener la media de la columna C de las filas de T que cumplen una determinada condición
El usuario U1 modifica (UPDATE) ciertos valores de la columna C de T y los valida (COMMIT)
el usuario U2 vuelve a ejecutar el mismo SELECT que en el punto 3. Descubrirá que la media de la columna C ha cambiado debido a las modificaciones realizadas por U1.

Ahora el usuario U2 solo ve las modificaciones «validadas» por U1. Pero, aunque permanece en la misma transacción, dos operaciones idénticas (3 y 5) dan resultados diferentes. El término «lectura no repetible» (Unrepeatable Read) designa esta situación. Se trata de una situación molesta para alguien que desea tener una imagen estable de la tabla T.

Lectura repetible

En este modo de aislamiento, un usuario tiene la garantía de obtener los mismos resultados en sus lecturas de la base de datos siempre que permanezca en la misma transacción. Trabaja con una instantánea en la que nunca se reflejan los cambios realizados por otras transacciones, ni siquiera los validados. Solo las verá cuando él mismo finalice su transacción con un COMMIT o un ROLLBACK.

Sin embargo, este modo de aislamiento aún no es perfecto. Tras la operación 3 anterior, las líneas consultadas por el usuario U2 quedan bloqueadas. Durante la operación 4, el usuario U1 no podrá modificar (UPDATE) los valores de la columna C de estas líneas. Sin embargo, puede añadir líneas (INSERT). Si algunas de las filas añadidas cumplen la condición comprobada en el paso 3, la operación 5 dará una media diferente a la obtenida en el paso 3 debido a las filas añadidas.

Para resolver este nuevo problema, hay que pasar al nivel de aislamiento «Serializable».

Serializable

En este modo de aislamiento, las transacciones son completamente independientes entre sí. Garantiza que el resultado de dos transacciones realizadas simultáneamente será el mismo que si se hubieran realizado una tras otra. Para lograr este resultado, en la operación 4, en la que el usuario U1 quiere añadir líneas que cambiarían el resultado de SELECT del usuario U1, se le impedirá hacerlo. Un mensaje de error le indicará que la inserción no es posible. Lo será cuando el usuario U2 haya validado su transacción.

Los cuatro niveles de aislamiento de transacciones no están disponibles en todos los entornos. Firebird proporciona los siguientes niveles de aislamiento:

snapshot: modo de aislamiento por defecto. Corresponde al modo «Repeatable Read» del estándar SQL.
lectura confirmada: corresponde al modo «lectura confirmada» del estándar SQL

Este nivel de aislamiento se establece mediante el comando SET TRANSACTION:

syntaxe

SET TRANSACTION

[READ WRITE | READ ONLY]

[WAIT|NOWAIT]

ISOLATION LEVEL [SNAPSHOT | READ COMMITTED]

fonctionnement

Las palabras clave subrayadas son los valores por defecto

READ WRITE: la transacción puede leer y escribir

READ ONLY: la transacción solo puede leer

WAIT: en caso de conflicto entre dos transacciones, la que no ha podido realizar su operación espera a que se valide la otra transacción. Ya no puede emitir órdenes SQL.

NOWAIT: la transacción que no ha podido realizar su operación no queda bloqueada. Recibe un mensaje de error y puede seguir trabajando.

ISOLATION LEVEL [SNAPSHOT | READ COMMITTED]: nivel de aislamiento

Probemos. En el editor SQL (ADMIN1) escribimos el siguiente comando SQL:

Vemos que no se ha autorizado. No sabemos por qué...

IB-Expert permite configurar el modo de aislamiento de otra manera. Hacemos clic con el botón derecho en la conexión DBACCES(ADMIN1) para seleccionar option [Database Registration Info]:

La pantalla de la derecha muestra la presencia de un option [Transactions]. Esto nos permitirá establecer el nivel de aislamiento de las transacciones. Lo fijamos aquí en [snapshot]. Hacemos lo mismo con la conexión DBACCES(ADMIN2).

7.3.2. El modo snapshot

Examinemos el nivel de aislamiento snapshot, que es el modo de aislamiento predeterminado de Firebird. Cuando el usuario inicia una transacción, se toma una instantánea de la base de datos. El usuario trabajará entonces sobre esta instantánea. De este modo, cada usuario trabaja con una instantánea de la base de datos que le es propia. Si realiza modificaciones en ella, los demás usuarios no las ven. Solo las verán cuando el usuario que las haya realizado las haya validado mediante un COMMIT.

Se pueden considerar dos casos:

un usuario lee la tabla (select) mientras otro la está modificando (insert, update, delete)
los dos usuarios quieren modificar la tabla al mismo tiempo

7.3.2.1. Principio de lectura coherente

Supongamos que dos usuarios, U1 y U2, están trabajando en la misma tabla TAB:

      --------+----------+--------+-------+----------------------
              T1a       T2a      T1b     T2b

La transacción del usuario U1 comienza en el momento T1a y termina en el momento T1b.

La transacción del usuario U2 comienza en el momento T2a y termina en el momento T2b.

U1 está trabajando en una foto de TAB tomada en el momento T1a. Entre T1a y T1b, modifica TAB. Los demás usuarios solo tendrán acceso a estas modificaciones en el momento T1b, cuando U1 realice un COMMIT.

U2 trabaja en una foto de TAB tomada en el momento T2a, por lo tanto, la misma foto que utilizó U1 (si otros usuarios no han modificado el original entretanto). No «ve» los cambios que el usuario U1 haya podido realizar en TAB. Solo podrá verlos en el momento T1b.

Ilustremos este punto en nuestra base [DBACCES]. Vamos a hacer que los dos usuarios [ADMIN1] y [ADMIN2] trabajen simultáneamente. Situémonos en la conexión DBACCES (ADMIN1) y, en el editor SQL de ADMIN1, realicemos las siguientes operaciones:

ADMIN1 ha modificado la línea n.º 2 de la tabla TA, pero aún no ha validado (COMMIT) su operación. El usuario ADMIN2 realiza entonces un SELECT en la tabla TA (se pasa al editor SQL de ADMIN2). Nos encontramos antes del momento T2a del ejemplo.

Volvemos al editor SQL de ADMIN1, que valida su adición:

Vuelta al editor SQL de ADMIN2 para rehacer el SELECT:

ADMIN2 ve las modificaciones realizadas por ADMIN1. En el modo instantánea, una transacción no ve las modificaciones realizadas por otras transacciones hasta que estas últimas no hayan finalizado.

7.3.2.2. Modificación simultánea de un mismo objeto de la base de datos por parte de dos transacciones

Tomemos un ejemplo de contabilidad: U1 y U2 trabajan en cuentas. U1 carga a comptex una suma S y abona a comptey la misma suma. Lo hará en varios pasos:

      --------+----------+--------+-------+----------------------
              T1a       T1b     T1c      T1d

U1 inicia una transacción en el momento T1a, carga comptex en el momento T1b, abona comptey en el momento T1c y valida ambas operaciones en el momento T1d. Supongamos, además, que U2 quiere hacer lo mismo, inicia su transacción en el momento T2a y la termina en el momento T2d según el siguiente esquema:

      --------+----------+----+----+-------+------+-----+-------+---------
              T1a       T1b  T2a   T1c     T2b   T1d   T2c    T2d

En el momento T2, se toma una instantánea de la tabla de cuentas para U2. Esta es coherente según el principio de snapshot. U2 ve el estado inicial de las cuentas comptex y comptey, ya que U1 aún no ha validado sus operaciones.

Supongamos que comptex tiene un saldo inicial de 1000 € y que cada uno de los usuarios U1 y U2 quiere cargarle 100 €.

En el momento T1b, U1 reduce el saldo de comptex en 100 € y, por lo tanto, lo deja en 90 €. Esta operación no se validará hasta el momento T1d.
En el momento T2b, U2 ve que comptex tiene 1000 € (principio de lectura coherente) y lo decrementa en 100 €, con lo que pasa a tener 90 €.
Al final, en el momento T2d, cuando todo se haya validado, comptex tendrá un saldo de 90 € en lugar de los 80 € previstos.

La solución a este problema es impedir que U2 modifique comptex mientras U1 no haya finalizado su transacción. De este modo, U2 quedará bloqueado hasta el momento T1d. El modo snapshot proporciona este mecanismo.

Ilustremos esto con la base DBACCES. ADMIN1 inicia una transacción en su editor SQL (ADMIN1):

Empezamos por crear un COMMIT para asegurarnos de iniciar una nueva transacción. A continuación, eliminamos la línea n.º 4. La transacción aún no se ha validado.

ADMIN2 inicia a su vez una transacción en su editor SQL (ADMIN2):

La pantalla de la derecha muestra que ADMIN2 ha intentado modificar la línea n.º 4. Se le ha respondido que no era posible porque otra persona ya la había modificado, pero aún no había validado dicha modificación.

Volvamos al editor SQL (ADMIN1) para crear el COMMIT:

Volvamos al editor SQL(ADMIN2) para volver a ejecutar el comando UPDATE:

La operación UPDATE se ejecuta correctamente, aunque la línea n.º 4 ya no existe, tal y como muestra el SELECT que le sigue. Es en ese momento cuando ADMIN2 detecta que la línea ya no existe.

7.3.2.3. El modo Repeatable Read

Veamos ahora el modo «Repeatable Read». Este nivel de aislamiento lo proporciona el modo «snapshot». Garantiza que una transacción obtenga siempre el mismo resultado al leer la base de datos.

Comencemos trabajando con el editor SQL de ADMIN2:

Pasemos ahora al editor SQL de ADMIN1:

El usuario ADMIN1 ha añadido dos líneas y ha validado su transacción. Volvamos ahora al editor SQL (ADMIN2) para reproducir el SELECT SUM:

Se observa que ADMIN2 no detecta las líneas añadidas de ADMIN1, aunque hayan sido validadas por un COMMIT. El SELECT SUM da el mismo resultado que antes de las adiciones. Este es el principio de la lectura repetible.

Ahora, siempre en el editor SQL (ADMIN2), validemos la transacción con un COMMIT y luego volvamos a ejecutar el SELECT SUM:

Las líneas añadidas por ADMIN1 ya se tienen en cuenta.

7.3.3. El modo Committed Read

Veamos ahora el modo «Committed Read». Este nivel de aislamiento es similar al de snapshot, salvo en lo que respecta a la «Repeatable Read».

Comenzamos cambiando el nivel de aislamiento de las transacciones de ambas conexiones.

Desconectamos a los dos usuarios ADMIN1 y ADMIN2
Cambiamos el nivel de aislamiento de sus transacciones

Volvemos a conectar a los usuarios ADMIN1 y ADMIN2

Ahora retomamos el ejemplo anterior que ilustraba la «lectura repetible» para mostrar que ya no tenemos el mismo comportamiento. Comencemos trabajando con el editor SQL de ADMIN2:

Pasemos ahora al editor SQL de ADMIN1:

El usuario ADMIN1 ha añadido dos líneas y ha validado su transacción. Volvamos ahora al editor SQL (ADMIN2) para reproducir el SELECT SUM:

El SELECT SUM no ofrece el mismo resultado que antes de las modificaciones realizadas por ADMIN1. Esta es la diferencia entre los modos «snapshot» y «read committed».