Bloqueo de registros en SQL Server 2008

En caso de que los cambios en los datos invalide el proceso, tenemos dos
opciones:

1) Detectar ese cambio a la hora de realizar la transacción.

Me refiero al estilo de detecciones de conflictos de concurrencia usando por
ejemplo técnicas basadas en columnas timestamp. Si se detecta que ha
ocurrido un cambio, se lanza un error y hay que empezar de nuevo. Si la
posibilidad o frecuencia de que se produzca un cambio que invalide el
proceso es lo suficientemente pequeña como para considerarla despreciable, o
al menos lo suficientemente pequeña como para "poder vivir con ella", Esta
opción es adecuada.


2) Implementar un mecanismo de check-out y check-in.

Antes comenzar la operación, el usuario hace check-out sobre el objeto
(entidad, conjunto de entidades, filas o lo que sea). La aplicación
implementa la lógica que impide a otros usuarios modificar el objeto sin
haber hecho primero un check-out, pero no impide ver el objeto. Además la
aplicación impide que dos usuarios puedan hacer check-out sobre el mismo
objeto.

Una vez hecho el check out, la aplicación recopila toda la información
necesaria para realizar el proceso completo, como dije antes la información
intermedia se puede guardar donde sea.

Hasta el momento no se ha hecho ninguna modificación a la base de datos
excepto lo que supone hacer el checkout.

Cuando tiene toda la información necesaria, el usuario hace check-in. La
aplicación empieza una transacción, hace todas las modificaciones de la base
de datos y confirma la transacción. Entonces otro usuario ya puede hacer
check-out sobre el mismo objeto.


La implementación de check-out y check-in no es muy complicada. Por ejemplo:

/*
En esta tabla tenemos todos los objetos reservables
*/
CREATE TABLE Objects
(
ObjectID int PRIMARY KEY, -- identifica el objeto
UserID int,-- el id del usuario que tiene reservado el objeto
Expiration datetime -- las reservas expiran, no se mantienen para siempre
)
GO

/*
Un objeto puede estar en tres estados:
1) No reservado. UserID y Expiration son nulos. En este estado cualquier
usuario puede hacer checkout, y ninguno puede hacer checkin
2) Reservado. UserID contiene un valor no nulo y Expiration es mayor que
GETDATE(). En este estado sólo el usuario que tiene el objeto reservado
puede hacer check-out (renovar la reserva) o check-in (liberar la reserva).
3) Reserva expirada. UserID contiene un valor no nulo y Expiration es mayor
o igual que GETDATE(). En este estado cualquier usuario puede hacer
check-out incluido el usuario cuya reserva ha expirado. Pero sólo el usuario
que tiene la reserva expirada puede hacer check-in.
*/


/*
Procedimiento que reserva (check-out) o renueva
la reserva de un objeto para un usuario
*/
CREATE PROCEDURE CheckOut
@ObjectID int,
@UserID int
AS
UPDATE Objects
SET
UserID = @UserID,
Expiration = DATEADD(mi, 5, GETDATE()) -- expira en cinco minutos
WHERE
ObjectID = @ObjectID
AND (Expiration < GETDATE() OR Expiration IS NULL OR UserID = @UserID)
IF @@ROWCOUNT = 0
RAISERROR('El objeto ya está reservado por otro usuario', 16, -1)


GO

/*
Procedimiento que libera la reserva de un objeto por parte de un
usuario que lo tenía reservado.
*/
CREATE PROCEDURE CheckIn
@ObjectID int,
@UserID int
AS
UPDATE Objects
SET
UserID = NULL, Expiration = NULL
WHERE
ObjectID = @ObjectID
AND UserID = @UserID
IF @@ROWCOUNT = 0
RAISERROR('
Error al liberar la reserva del objeto por alguna de las siguientes razones:
* Vd. no había reservado el objeto.
* Vd. había reservado el objeto, pero la reserva expiró,
Vd. no la renovó y otro usuario lo reservó', 16, -1)

GO












"Carlos M. Calvelo" escribió en el mensaje de
noticias
news:
Hola Jesús, José Antonio,

On 2 feb, 21:32, Jesús wrote:

Realmente es una mala práctica mantener bloqueados recursos dejando al
usuario el control de cuando se liberan dichos recursos.

Cierto. Muy mala práctica.
No debe haber la más mínima interacción con el usuario dentro
de una transacción.

"José Antonio Muñoz" escribió en el mensaje de
noticiasnews:%

> El proceso es bastante complejo como para explicarlo aquí en el foro, se
> trata de una aplicación donde a través de una tabla principal que se
> compone de muchos campos (más de 100 campos) y muchos de estos campos se
> actualizan a partir de muchas tablas maestras (en total pueden existir
> más
> de 50 tablas). El usuario necesita tener bloqueado un grupo determinado
> de
> registros que a su vez está modificando otras tablas en un orden
> determinado, claro si hay que actualizar muchos datos con fórmulas,
> procesos, validaciones, etc., y, además, se requieren varios formularios
> de entrada de datos es por lo que es necesario el bloqueo, para que otro
> usuario no pueda tocar ni ver esos datos mientras están en el proceso
> que
> he explicado anteriormente. Simplemente es así, no hay otra forma, ya
> que
> durante la actualización de estos registros se generan datos intermedios
> que reflejarían un estado que no se correspondería con la realidad
> cuando
> el otro usuario intenta consultar esos mismos datos en otro terminal.

Sí existe otra forma. Hasta que no se haya finalizado el proceso no haces
ninguna modificación en la base de datos. Toda la información intermedia
la
vas almacenado en otro sitio como por ejemplo en memoria, en tablas
intermedias, o donde quieras. Cuando tengas toda la información necesaria
para realizar el proceso completo empiezas una transacción, haces todas
las
modificaciones a la base de datos que tengas que hacer y confirmas la
transacción. De esta manera la duración de los bloqueos no está bajo el
control del usuario, durará lo que tarde en ejecutarse las intrucciones
SQL..

Yo presiento que el problema de José Antonio es más complejo que
eso. Los datos de entrada (input) de ese proceso no vienen solo
de la aplicación sino también de la base de datos misma. Cualquier
cambio en esos datos, por ejemplo a causa de acciones de otros
usuarios, puede invalidar el proceso. Y ese proceso no equivale
a una transación, sino a una serie de transaciones.
Me explico:

A mi me dá la impresión que el problema que tiene José Antonio es
de análisis de ese proceso y de (re)diseño de la base de datos para
poder acomodarlo. Estoy pensando en que los registros implicados
(entidades o como quiera llamarsele) pueden tener que pasar por una
serie de estados donde cada paso de un estado a otro de los
siguientes posibles sí puede considerarse como una transacción en
el sentido que se está hablando aquí. Todas esas entidades pueden
estar en cualquier estado en cualquier momento y la base de datos
estar en un estado consistente en cuanto a integridad de datos. Lo
que está o no permitido hacer con esas entidades dependería
naturalmente del estado en que se encuentren (restricciones de
transición). También es posible que sea necesario que ese proceso
'lleve consigo' todos los datos de entrada necesarios y que
pertenecen al proceso (una copia de los originales al inicio del
proceso).

Solo unas ideas generales por lo que presiento 'entre líneas' en
lo que describe José Antonio. Pero puede que mi intuición me engañe.

Saludos,
Carlos

Jose:

Por que no incluyes banderas o flags en los registros para evitar que lo
accedan en ese momento en lugar de querer bloquear los registros.
Creo que deberias resolver tus problemas desde el punto de vista del diseño
y no forzar a la base de datos a hacer cosas para las cuales el modelo
relacional no esta preparado.
Creo que tarde o temprano vas a encontrar un problema que no vas a poder
resolver si sigues por ese camino, independientemente de la base de datos
relacional que uses.


Saludos


Ing. Jose Mariano Alvarez
SQLTotal Consulting

(Cambia los ceros por O y saca lo que sobra)

Este mensaje se proporciona tal como es, SIN GARANTIAS de ninguna clase. Por
favor tratar de indicar la versión de SQL y Service Pack. La inclusión de
(CREATE, INSERTS, etc.) para poder reproducir el problema también ayuda.










"José Antonio Muñoz" wrote in message
news:#

El proceso es bastante complejo como para explicarlo aquí en el foro, se
trata de una aplicación donde a través de una tabla principal que se
compone de muchos campos (más de 100 campos) y muchos de estos campos se
actualizan a partir de muchas tablas maestras (en total pueden existir más
de 50 tablas). El usuario necesita tener bloqueado un grupo determinado de
registros que a su vez está modificando otras tablas en un orden
determinado, claro si hay que actualizar muchos datos con fórmulas,
procesos, validaciones, etc., y, además, se requieren varios formularios
de entrada de datos es por lo que es necesario el bloqueo, para que otro
usuario no pueda tocar ni ver esos datos mientras están en el proceso que
he explicado anteriormente. Simplemente es así, no hay otra forma, ya que
durante la actualización de estos registros se generan datos intermedios
que reflejarían un estado que no se correspondería con la realidad cuando
el otro usuario intenta consultar esos mismos datos en otro terminal.

No sé si me he explicado bien. De todas formas hoy mismo, probando, he
generado un índice "noclustered" y no "único" a partir del campo en
cuestión y "vualá" el problema se ha solucionado de golpe. Parece ser que
en el bloqueo de registros de SQL server intervienen los índices.

Muchas gracias por vuestra colaboración.

Saludos,
José Antonio Muñoz.

"Gustavo Larriera (MVP)"
escribió en el mensaje de noticias
news:

Hola José Antonio Muñoz,

"José Antonio Muñoz" wrote:

Yo tengo una tabla que tiene un campo que se repite según unas
determinadas
condiciones. Si un usuario necesita modificar los registros cuyo valor
de
ese campo sea 1 (puede haber varios registros) primero hago un SELECT
para
ver que registros que son y uno por uno voy modificando con la
instrucción
UPDATE.

Cuando usted dice "uno por uno voy modificando" se refiere a que usa un
cursor? O cómo es que usted hace esa modificación fila por fila?

NOTA: Solo cuando el campo correspondiente sea clave y no se repita el
bloqueo si funciona correctamente, es decir, que puedo hacer un SELECT
cuyo
valor sea 1 y solo me recupera un registro. Otro usuario intenta hacer
SELECT a ese registro y no puede ya que da un error de bloqueo. Pero si
hace
un SELECT de otro registro, por ejemplo, el 2 y si puede.

Puede usted mostrar la estructura de la tabla y los índices que hay
creados
para ella?

Saludos
~gux

Hola Jesús,

Parece que no me he explicado bien. No me refería a técnicas para
bloqueo, sino a un análisis del proceso del que habla José Antonio.

Una entidad puede recorrer una serie de estados (piensa en diagramas
de estados) donde el paso de un estado a otro corresponde a una
transacción completa.
Cualquier estado intermedio en el proceso no es un bloqueo sino
que solo expresa donde se encuentra esa entidad en el proceso y,
por medio de restricciones de transición, a que otros estatos
puede pasar. El que una entidad esté en un determinado estado
intermedio en un proceso no bloquea a nadie y puede estar en ese
estado todo el tiempo que sea necesario. Solo dice qué es lo que
se puede hacer con esa entidad, independientemente de quien o
qué haya inicializado el proceso.

Me dió la impresión que quizás José Antonio pueda reconsiderar
ese proceso en esos términos.

Por cierto, buena idea la del check-in / check-out. Como en los
sistemas de control de versiones de código fuente.
Con su 'expiration date' y todo. :) Aunque en control de
concurrencia no debería estar en manos de la(s) aplicación(es).

En todo caso, espero haberme explicado mejor ahora.

Saludos,
Carlos

Hola Carlos,

Mi respuesta venía sobre todo a cuento del párrafo:

"Los datos de entrada (input) de ese proceso no vienen solo
de la aplicación sino también de la base de datos misma. Cualquier
cambio en esos datos, por ejemplo a causa de acciones de otros
usuarios, puede invalidar el proceso"

Sobre todo de la frase "Cualquier cambio en esos datos,...,puede invalidar
el proceso". Creo sinceramente que esa frase describe perfectamente el
problema de José Antonio Muñoz. ¿Por qué si no José Antonio iba a bloquear
las filas sino es para impedir que se modificaran? y ¿Por qué no quiere que
los demás usuarios las vean? Pues creo que porque han sido modificadas, aún
no están confirmadas y respresentan un estado de la base de datos no
consistente desde el pundo de vista del negocio. Creo que la intención de
José Antonio es la de impedir lecturas sucias a los demás usuarios.

Y ante eso pues propongo dos soluciones:

1) Detectar que ha habido cambios e invalidar el proceso
2) Implentar un mecanismo de check-in y check-out, que impida que se
produzca ese cambio en los datos. Como has mencionado, se trata básicamente
de una implementación de bloqueos a nivel de aplicación, lo cual lo
considero mejor alternativa que bloquear recursos en el servidor con control
de su duración por parte del usuario.


Lo de los estados, transiciones de estados y restricciones en las
transiciones de estados, aunque entiendo más o menos lo que quieres decir,
no termino de encajarlo en el contexto. También creo que eso puede ser un
análisis válido que explique por qué "Cualquier cambio en esos
datos,...,puede invalidar el proceso".

Pero pienso que sea lo que sea lo que explique por qué "Cualquier cambio en
esos datos,...,puede invalidar el proceso" las soluciones que he propuesto
funcionarían..

Espero que al final podamos entendernos.

Un saludo:

Jesús López


"Carlos M. Calvelo" escribió en el mensaje de
noticias
news:
Hola Jesús,

Parece que no me he explicado bien. No me refería a técnicas para
bloqueo, sino a un análisis del proceso del que habla José Antonio.

Una entidad puede recorrer una serie de estados (piensa en diagramas
de estados) donde el paso de un estado a otro corresponde a una
transacción completa.
Cualquier estado intermedio en el proceso no es un bloqueo sino
que solo expresa donde se encuentra esa entidad en el proceso y,
por medio de restricciones de transición, a que otros estatos
puede pasar. El que una entidad esté en un determinado estado
intermedio en un proceso no bloquea a nadie y puede estar en ese
estado todo el tiempo que sea necesario. Solo dice qué es lo que
se puede hacer con esa entidad, independientemente de quien o
qué haya inicializado el proceso.

Me dió la impresión que quizás José Antonio pueda reconsiderar
ese proceso en esos términos.

Por cierto, buena idea la del check-in / check-out. Como en los
sistemas de control de versiones de código fuente.
Con su 'expiration date' y todo. :) Aunque en control de
concurrencia no debería estar en manos de la(s) aplicación(es).

En todo caso, espero haberme explicado mejor ahora.

Saludos,
Carlos

Correcto Jesús, ¡impedir que se efectuen lecturas sucias!. De todas formas
bloquear registros por una aplicación no creo que sea una mala práctica, yo
lo estoy haciendo desde los tiempos del "clipper" y el "basic" y era
entonces una herramienta básica a la hora de programas aplicaciones en red.
Cada herramienta de programación tenía sus métodos para el bloqueo de
registros y de tablas.

En cuanto al proceso del análisis, es posible que volviendo a resiseñar la
aplicación para resolver los problemas de control check-in y check-out sería
una solución pero llevo la aplicación ya muy avanzada para ello y el bloqueo
(ya resuelto) me resuelve el problema.

Gracías por vuestras sugerencias y un saludo,
José Antonio Muñoz.

"Jesús" escribió en el mensaje de
noticias news:%

Hola Carlos,

Mi respuesta venía sobre todo a cuento del párrafo:

"Los datos de entrada (input) de ese proceso no vienen solo
de la aplicación sino también de la base de datos misma. Cualquier
cambio en esos datos, por ejemplo a causa de acciones de otros
usuarios, puede invalidar el proceso"

Sobre todo de la frase "Cualquier cambio en esos datos,...,puede invalidar
el proceso". Creo sinceramente que esa frase describe perfectamente el
problema de José Antonio Muñoz. ¿Por qué si no José Antonio iba a bloquear
las filas sino es para impedir que se modificaran? y ¿Por qué no quiere
que los demás usuarios las vean? Pues creo que porque han sido
modificadas, aún no están confirmadas y respresentan un estado de la base
de datos no consistente desde el pundo de vista del negocio. Creo que la
intención de José Antonio es la de impedir lecturas sucias a los demás
usuarios.

Y ante eso pues propongo dos soluciones:

1) Detectar que ha habido cambios e invalidar el proceso
2) Implentar un mecanismo de check-in y check-out, que impida que se
produzca ese cambio en los datos. Como has mencionado, se trata
básicamente de una implementación de bloqueos a nivel de aplicación, lo
cual lo considero mejor alternativa que bloquear recursos en el servidor
con control de su duración por parte del usuario.


Lo de los estados, transiciones de estados y restricciones en las
transiciones de estados, aunque entiendo más o menos lo que quieres decir,
no termino de encajarlo en el contexto. También creo que eso puede ser un
análisis válido que explique por qué "Cualquier cambio en esos
datos,...,puede invalidar el proceso".

Pero pienso que sea lo que sea lo que explique por qué "Cualquier cambio
en esos datos,...,puede invalidar el proceso" las soluciones que he
propuesto funcionarían..

Espero que al final podamos entendernos.

Un saludo:

Jesús López


"Carlos M. Calvelo" escribió en el mensaje de
noticias
news:
Hola Jesús,

Parece que no me he explicado bien. No me refería a técnicas para
bloqueo, sino a un análisis del proceso del que habla José Antonio.

Una entidad puede recorrer una serie de estados (piensa en diagramas
de estados) donde el paso de un estado a otro corresponde a una
transacción completa.
Cualquier estado intermedio en el proceso no es un bloqueo sino
que solo expresa donde se encuentra esa entidad en el proceso y,
por medio de restricciones de transición, a que otros estatos
puede pasar. El que una entidad esté en un determinado estado
intermedio en un proceso no bloquea a nadie y puede estar en ese
estado todo el tiempo que sea necesario. Solo dice qué es lo que
se puede hacer con esa entidad, independientemente de quien o
qué haya inicializado el proceso.

Me dió la impresión que quizás José Antonio pueda reconsiderar
ese proceso en esos términos.

Por cierto, buena idea la del check-in / check-out. Como en los
sistemas de control de versiones de código fuente.
Con su 'expiration date' y todo. :) Aunque en control de
concurrencia no debería estar en manos de la(s) aplicación(es).

En todo caso, espero haberme explicado mejor ahora.

Saludos,
Carlos