Enfoque mixto de gestión de transacciones en SQL Server
Sergio Alcalde | 13 de febrero de 2009 | Añadir comentario
Más sobre: Documentación, SQL Server | Tags: bloqueos, concurrencia, SQL, T-SQL, transacciones
Cuando los requisitos de eficiencia de una aplicación en la que se produce acceso concurrente a datos sobre SQL Server son muy estrictos, no podemos basar el diseño de nuestra aplicación ni en un enfoque optimista de gestión de transacciones ni en un enfoque pesimista de gestión de transacciones ya que corremos el riesgo de no satisfacer dichos requisitos.
Además, aunque en apariencia estos dos enfoques de control de transacciones expuestos con anterioridad cubren las necesidades de cualquier aplicación, esto no es así dado que las técnicas empleadas para implementar uno y otro enfoque presentan ciertas carencias que obligan a diseñar otras formas de trabajo.
En cuanto al enfoque optimista, esas carencias se resumen en el siguiente punto:
- La carencia principal reside en el hecho de que puede darse que dos usuarios seleccionen los mismos datos para su posterior modificación. Ambos usuarios podrán realizar todas las modificaciones que quieran, pero sólo uno de ellos conseguirá completar la operación con éxito, dado que el mecanismo implementado por estos enfoques optimistas impedirá que se pierdan modificaciones. Si el mantenimiento afectaba a un elevado volumen da datos, el usuario que no consiga completar la actualización de los datos habrá perdido mucho tiempo de trabajo.
- Existe la necesidad de implementar la gestión de tratamiento de carreras de actualización de datos.
Las técnicas de enfoque optimista funcionan bien si el número de tablas a manejar es pequeño. Sin embargo, esta técnica complica el desarrollo de aquellos mantenimientos que manejen un elevado número de tablas. En estos casos debe estudiarse la posibilidad de utilizar un enfoque pesimista del control de transacciones, siempre y cuando esta decisión no genere nuevos problemas.
En cuanto al enfoque pesimista, las carencias se resumen en los siguientes puntos:
- La elección en cada caso del nivel de aislamiento de una transacción o una conexión y de la granularidad del bloqueo es una tarea extremadamente complicada dado el elevado número de factores y combinaciones que pueden darse. Una elección no adecuada puede hacer que la eficiencia del servidor, y por tanto de la aplicación, se vea seriamente afectada.
- Existe la necesidad de implementar el tratamiento de interbloqueos.
- Por otro lado, existe el mismo problema que había con el enfoque optimista referente a la actualización simultánea de los mismos datos por procesos distintos, con la particularidad de que ahora el tratamiento de interbloqueos será diferente.
- Finalmente, existe un problema relacionado con el carácter indefinido que tienen los bloqueos sobre los datos. Con un enfoque pesimista podría darse la situación de que un usuario bloqueara los datos para su posterior modificación y que, en el caso extremo, nunca liberara los recursos bloqueados. Esta situación que parece imposible es realmente usual.
Los siguientes esquemas de trabajo propuestos, basados en que cada una de las copias de nuestra aplicación respete una serie de reglas a la hora de acceder a los datos, tratan de cubrir estas carencias. Cabe resaltar que no son excluyentes con respecto a los métodos expuestos anteriormente, necesitándose en algunos casos dichos métodos para asegurar el correcto funcionamiento y mejorar la eficiencia de estos últimos.
Los dos métodos están basados en añadir una columna a las tablas que indique si la fila en cuestión está bloqueada o no. La elección de qué tablas son las que deben incluir esta columna depende de cada modelo de datos y de las tablas que se vean involucradas en cada mantenimiento.
Bloqueo pesimista utilizando “flag + plan de contingencia”
En las tablas en las que se quiera controlar el acceso concurrente se introducirá una columna “IT_EN_USO”. En una fila que haya sido seleccionada por un proceso de modificación, el valor de dicha columna será 1, y valdrá 0 en cualquier otro caso.
Un proceso que quiera modificar los datos de una fila, sólo podrá hacerlo si “IT_EN_USO” vale 0. Para ocupar el recurso, pondrá el valor 1 en dicha columna.
Si un mismo proceso necesita modificar varias filas, de la misma tabla o de varias, deberá obtener todos los recursos a modificar o no ocupar ninguno. En caso de no poder ocupar todos los recursos deberá liberar aquellos que ya haya ocupado y se mostrará un mensaje al usuario que le invite a intentarlo más tarde.
Tras finalizar la modificación de los datos, el proceso que los ha modificado será el encargado de liberar los recursos poniendo el valor 0 en la columna “IT_EN_USO” en aquellas filas que corresponda.
Dentro de la utilización de este enfoque, pueden darse mantenimientos que necesiten la interacción con el usuario (para la recogida de los nuevos datos) y mantenimientos que no la necesiten (actualizaciones automáticas, por ejemplo).
Para mantenimientos en los que es necesaria la intervención del usuario, el esquema de trabajo es el siguiente:
(*
* ----------------------------------------------------------------
* function OcuparTodosRecursos (Cod) : boolean;
* Cometido..: Ocupa todos los recursos que se requieren para
* realizar las posteriores modificaciones.
* Enfoque optimista.
* ----------------------------------------------------------------
*)
function OcuparTodosRecursos (Cod) : boolean;
begin
TodosLibres:=true;
strSQL:='select CD, IT_EN_USO '+
'from TABLA '+
'where CD = '+Cod;
EjecutarSQL(strSQL);
while not eof and TodosLibres do begin
if FieldByName('IT_EN_USO').AsString = '1' then
TodosLibres:=false
else begin
strSQL:='update TABLA '+
'set IT_EN_USO = 1 '+
'where CD = '+Cod+' and '+
'IT_EN_USO = 0';
NumRegsAfectados:=EjecutarSQL(strSQL);
if NumRegsAfectados < 1 then
TodosLibres:=false;
end;
Next;
end;
OcuparTodosRecursos:=TodosLibres;
end; // OcuparTodosRecursos
(*
* ----------------------------------------------------------------
* procedure LiberarTodosRecursos (Cod);
* Cometido..: Libera todos los recursos que se ocuparon para la
* modificación.
* ----------------------------------------------------------------
*)
procedure LiberarTodosRecursos (Cod);
begin
strSQL:='select CD '+
'from TABLA '+
'where CD = '+Cod;
EjecutarSQL(strSQL);
while not eof do begin
strSQL:='update TABLA '+
'set IT_EN_USO = 0 '+
'where CD = '+Cod;
EjecutarSQL(strSQL);
Next;
end;
end; // LiberarTodosRecursos
(*
* ----------------------------------------------------------------
* procedure VolcarDatosABD (Cod, DatosModif);
* Cometido..: Vuelca los datos modificados por el usuario a la BD.
* ----------------------------------------------------------------
*)
procedure VolcarDatosABD (Cod, DatosModif);
begin
strSQL:='select CD '+
'from TABLA '+
'where CD = '+Cod;
EjecutarSQL(strSQL);
while not eof do begin
strSQL:='update TABLA '+
'set CAMPOS_MODIF = '+DatosModif+' '+
'where CD = '+Cod;
EjecutarSQL(strSQL);
Next;
end;
end; // VolcarDatosABD
(*
* ----------------------------------------------------------------
* rutina Principal;
* Cometido..: Esqueleto de la aplicación que maneje esta forma de
* trabajo.
* ----------------------------------------------------------------
*)
rutina Principal;
begin
StartTransaction;
if OcuparTodosRecursos(Cod) then begin
CommitTransaction;
CargarDatosModificables(Cod);
DatosModificados:=RecogerDatosAModificar(DatosModif);
if DatosModificados = OK then begin
StartTransaction;
VolcarDatosABD(Cod,DatosModif);
LiberarTodosRecursos(Cod);
CommitTransaction;
end;
if DatosModificados = CANCEL then begin
StartTransaction;
LiberarTodosRecursos(Cod);
CommitTransaction;
end;
end
else begin
RollBackTransaction;
MostrarError('Los datos están siendo modificados'+
' por otro usuario.'+#13+
'Por favor, inténtelo más tarde.');
end;
end; // Principal
Como puede apreciarse, en el proceso existen dos transacciones. En la primera de ellas se ocupan todos los recursos que sean objeto de la modificación. Una vez ocupados todos los recursos objeto de modificación, se cierra la primera transacción. De esta forma se liberan los bloqueos establecidos por SQL Server y se potencia así la concurrencia en el acceso a los datos.
Cuando se han recogido todos los datos que el usuario quiere modificar, si éste acepta los cambios, se inicia una nueva transacción en la que, tras volcar la nueva información a la base de datos, se liberan todos los recursos que se habían ocupado. Si el usuario ha cancelado los cambios, se inicia una nueva transacción con el fin de liberar todos los recursos ocupados. Después se cierra la transacción.
Si entre la primera y la segunda transacción se produce un corte en el suministro eléctrico, el ordenador se bloquea, etc., los recursos ocupados permanecerán constantemente en este estado, ya que se cerró la transacción en la que se ocuparon. En estos casos, poco habituales por otro lado, será necesario aplicar el plan de contingencia.
El esquema del plan de contingencia sería:
(*
* ----------------------------------------------------------------
* procedure PlanContingencia;
* Cometido..: Liberar todos los recursos de la BD.
* ----------------------------------------------------------------
*)
procedure PlanContingencia;
begin
StartTransaction;
for TodosLosRecursosControlados do
LiberarTodosRecursos(Cod);
CommitTransaction;
end; // PlanContingencia
Por otro lado, en aquellos mantenimientos en los que no se requiere interactuación con el usuario, el esquema de trabajo varía en algunos aspectos. El pseudocódigo en este caso sería:
(*
* ----------------------------------------------------------------
* function ModificarDatos (Cod,DatosModif) : boolean;
* Cometido..: Modificación de datos de la BD.
* ----------------------------------------------------------------
*)
function ModificarDatos (Cod,DatosModif) : boolean;
begin
StartTransaction;
if OcuparTodosRecursos(Cod) then begin
VolcarDatosABD(Cod,DatosModif);
LiberarTodosRecursos(Cod);
CommitTransaction;
ModificarDatos:=true;
end
else begin
RollBackTransaction;
ModificarDatos:=false;
end;
end; // ModificarDatos
(*
* ----------------------------------------------------------------
* rutina Principal;
* Cometido..: Esqueleto de la aplicación que maneje esta forma de
* trabajo.
* ----------------------------------------------------------------
*)
rutina Principal;
begin
if ModificarDatos(Cod,DatosModif) then
Mensaje('Datos modificados')
else
MostrarError('Imposible modificar datos');
end; // Principal
Como puede apreciarse, en este caso, la ocupación y liberación de los recursos se produce dentro de la misma rutina y en la misma transacción, dado que ya se tienen los nuevos datos a introducir en la base de datos. Este tipo de mantenimientos no provoca problemas que hagan necesaria la ejecución del plan de contingencia, ya que todas las modificaciones sobre la base de datos se realizan dentro de la misma transacción.
Los procesos que sólo visualicen datos sin llegar a modificarlos, no tendrán en cuenta el valor de la columna IT_EN_USO, no estando para estos procesos bloqueada la fila en cuestión.
Esta forma de trabajo evita que dos procesos seleccionen a la vez los mismos datos para su posterior modificación, por lo que ningún usuario perderá tiempo modificando datos que finalmente no pueda salvar. Sin embargo, no impide que un usuario bloquee indefinidamente datos.
La pega de este esquema reside en el plan de contingencia. Para poder aplicar el proceso que liberará todas las filas bloqueadas permanentemente por un fallo en el suministro eléctrico, etc., será necesario que no haya ninguna aplicación activa excepto la que está ejecutando el administrador. Es, por tanto, aplicable en aplicaciones con pocos usuarios y en oficinas en las que se pueda controlar la actividad de todos los operarios a la vez para evitar situaciones de pérdida de datos e inconsistencias que podrían llegar a derivarse de la aplicación del plan de contingencia estando alguna aplicación activa.
Una forma de evitar el plan de contingencia manteniendo este esquema de trabajo es bajar el nivel de aislamiento de las transacciones para poder leer datos de los que todavía no se ha hecho el commit (permitiendo lecturas sucias). En este caso, no se cerraría la transacción tras ocupar los recursos, sólo se cerraría tras liberarlos, por lo que si algo falla entre el proceso de ocupar los recursos y después liberarlos, sería el propio SQL Server el encargado de deshacer los cambios, dejando de nuevo los recursos libres.
El flag “IT_EN_USO” asegura que dos usuarios no modifiquen a la vez los mismos datos. Bajar el nivel de aislamiento permite una mayor concurrencia en el acceso a los datos.
El esquema de trabajo para mantenimientos que necesiten la intervención del usuario será en este caso:
(*
* ----------------------------------------------------------------
* rutina Principal;
* Cometido..: Esqueleto de la aplicación que maneje esta forma de
* trabajo.
* ----------------------------------------------------------------
*)
rutina Principal;
begin
EjecutarSQL('set transaction isolation level readuncommitted');
StartTransaction;
if OcuparTodosRecursos(Cod) then begin
CargarDatosModificables(Cod);
DatosModificados:=RecogerDatosAModificar(DatosModif);
if DatosModificados = OK then begin
VolcarDatosABD(Cod,DatosModif);
LiberarTodosRecursos(Cod);
CommitTransaction;
end;
if DatosModificados = CANCEL then begin
RollBackTransaction;
end;
end
else
RollBackTransaction;
MostrarError('Los datos están siendo modificados'+
' por otro usuario.'+#13+
'Por favor, inténtelo más tarde.');
EjecutarSQL('set transaction isolation level readcommitted');
end; // Principal
El esquema de trabajo para mantenimientos que no necesiten la intervención del usuario no variará con respecto al esquema que no baja el nivel de aislamiento excepto en el hecho de que no necesita el plan de contingencia.
En las consultas que trabajen sobre las tablas manejadas con estos esquemas de bloqueo también se debe bajar el nivel de aislamiento ya que, al no cerrar la transacción hasta el final, SQL Server mantendrá bloqueos sobre dichas tablas.
Esta opción puede emplearse en contados casos ya que bajar el nivel de aislamiento de las transacciones puede ocasionar graves problemas. Un caso en el que sería medianamente fácil usar esta optimización sería en aquellos mantenimientos que sólo afectan a datos de una tabla. Si el mantenimiento afecta a tablas de tipo maestro – detalle, la programación se complica ya que las consultas que se realicen podrán leer datos sucios, en los que todavía no existan todos los detalles de un maestro, etc., pudiendo ocasionar resultados no deseados.
Supongamos ahora que mezclamos este esquema de trabajo con un enfoque pesimista. La función “OcuparTodosRecursos” podría haber sido escrita:
(*
* ----------------------------------------------------------------
* function OcuparTodosRecursosPesimista (Cod) : boolean;
* Cometido..: Ocupa todos los recursos que se requieren para
* realizar las posteriores modificaciones.
* Enfoque pesimista.
* ----------------------------------------------------------------
*)
function OcuparTodosRecursosPesimista (Cod) : boolean;
begin
TodosLibres:=true;
strSQL:='select CD, IT_EN_USO '+
'from TABLA with (repeatableread) '+
'where CD = '+Cod;
EjecutarSQL(strSQL);
while not eof and TodosLibres do begin
if FieldByName('IT_EN_USO').AsString = '1' then
TodosLibres:=false
else begin
strSQL:='update TABLA '+
'set IT_EN_USO = 1 '+
'where CD = '+Cod;
EjecutarSQL(strSQL);
end;
next;
end;
OcuparTodosRecursosPesimista:=TodosLibres;
end; // OcuparTodosRecursosPesimista
Podría ocurrir que dos usuarios intentaran modificar a la vez los mismos datos. En este caso, podría producirse un interbloqueo. SQL Server mataría a la transacción que ha provocado la cadena circular de bloqueo y sería necesario un tratamiento de errores especial. Con la primera de las versiones para ocupar recursos, no se producirá nunca un interbloqueo, descargando así al servidor de la tarea de tener que resolver este tipo de conflictos y evitando tener que programar un tratamiento de errores adicional en la aplicación.
Existe aún otra manera de codificar el procedimiento “OcuparTodosRecursos”.
Esta forma evita el interbloqueo, ya que lo primero que se hace es el update, con lo que se obtienen directamente los bloqueos de escritura. Además, como consecuencia de hacer un único update no se sobrecarga la red, aunque sí el servidor, por lo que es adecuada para arquitecturas en la que la red es lenta y existe un servidor potente, frente a la solución presentada en la primera solución que sería más adecuada si el servidor se encuentra ya sobrecargado.
(*
* ----------------------------------------------------------------
* function OcuparTodosRecursosEficiente (Cod) : boolean;
* Cometido..: Ocupa todos los recursos que se requieren para
* realizar las posteriores modificaciones.
* Enfoque optimista.
* ----------------------------------------------------------------
*)
function OcuparTodosRecursosEficiente (Cod) : boolean;
begin
strSQL:='update TABLA '+
'set IT_EN_USO = 1 '+
'where IT_EN_USO = 0 and '+
'CD = '+Cod;
NumRegsAfectadosUpdate:=EjecutarSQL(strSQL);
strSQL:='select count(*) as NumRegsSe '+
'from TABLA '+
'where CD = '+Cod;
EjecutarSQL(strSQL);
NumRegsAfectadosSelect:=FieldByName('NumRegsSe').AsInteger;
if NumRegsAfectadosUpdate = NumRegsAfectadosSelect then
OcuparTodosRecursosEficiente:=true
else
OcuparTodosRecursosEficiente:=false;
end; // OcuparTodosRecursosEficiente
Esta forma de ocupar los recursos, con un servidor poco cargado, es mucho más eficiente siendo este aumento de eficiencia más patente cuanto más recursos sea necesario ocupar.
Esta forma de codificación es válida también para la operación de liberar recursos.
Aunque esta codificación es más eficiente, en los ejemplos de código se seguirá manteniendo la codificación más natural del mismo para que resulte más clara su comprensión. Sin embargo, hay que tener en cuenta siempre en cuenta que esta forma de trabajo es más eficiente y ocasiona una menor carga en el servidor por resolución de contiendas de bloqueo.
El hecho de que la ocupación y liberación de recursos no sea transaccional y se necesite un plan de contingencia para los casos de bloqueo permanente hace de esta solución una primera aproximación de la solución que se expone a continuación.
Bloqueo pesimista utilizando “caducidad” de bloqueo
Partiendo del bloqueo optimista utilizando un flag para bloquear los recursos y basándose en la filosofía de trabajo de los enfoques optimistas simples, se puede plantear otra solución que tiene ciertas características que la hacen interesante.
En este caso, en aquellas tablas en las que se quiera controlar el acceso concurrente se introducirá una columna “FC_OCUPACION” de tipo datetime que, en el caso de SQL Server, ofrece una precisión de 1 milisegundo.
Se establece un tiempo de caducidad, que indicará el tiempo máximo que un proceso de modificación puede tener ocupado un recurso.
Un proceso que quiera ocupar recursos, sólo podrá hacerlo si el campo “FC_OCUPACION” del recurso a ocupar contiene una fecha caducada, es decir, si la fecha actual del servidor menos el valor de “FC_OCUPACION” supera el tiempo de caducidad. Para ocupar un recurso, el proceso introducirá la fecha actual del servidor en el campo “FC_OCUPACION” del recurso a ocupar. Esta será su marca de ocupación.
Si un mismo proceso quiere modificar varias filas, de la misma tabla o de varias, deberá obtener todos los recursos a modificar o no ocupar ninguno. En caso de no poder ocupar todos los recursos deberá liberar aquellos que ya haya ocupado y se mostrará un mensaje al usuario que le invite a intentarlo más tarde. Todos los recursos que tenga que ocupar el proceso se han de ocupar con la misma marca de ocupación.
Un proceso que haya conseguido completar con éxito todo el proceso de actualización de los datos será el encargado de liberar los mismos para que otros procesos puedan ocuparlos. Para liberar un recurso, ha de introducirse una fecha caducada en el campo “FC_OCUPACION” de cada recurso que se ha de liberar.
Llegado este punto, hay que hacer una puntualización: Una fecha no caducada indica que el recurso en cuestión está en uso. Una fecha caducada indica que el recurso no está en uso, o que el proceso que lo estaba usando ha excedido el tiempo que se le ha dado para modificar el recurso que ocupó y que, por tanto, se ha liberado el recurso (pero no lo ha liberado el proceso que lo ocupó) o, mejor dicho, que el recurso se puede ocupar.
De esta forma, un proceso que haya ocupado todos los recursos que necesite, a la hora de volcar la actualización a la base de datos, debe comprobar que la marca de ocupación sigue siendo la misma con la que ocupó los recursos. Si la marca sigue siendo la suya, podrá volcar la información, incluso aunque la marca haya caducado ya que si sigue siendo la suya significa que no hay otro proceso que haya ocupado los recursos. Por el contrario, si la marca es distinta significa que el tiempo de modificación fue excedido y que otro proceso a ocupado el recurso. En este caso, el proceso al que le ha caducado la marca no puede realizar la modificación. Este proceso debe liberar los recursos que sigan teniendo su marca de ocupación y mostrar un mensaje al usuario que le indique que no se puede realizar la actualización de los datos porque se excedió el tiempo de modificación.
Para mantenimientos en los que es necesaria la intervención del usuario, el esquema de trabajo es el siguiente:
const
MARCA_NO_OCUPADO = '01/01/1900 00:00:00.000';
T_CADUCIDAD = XX; // Tiempo de caducidad
(*
* ----------------------------------------------------------------
* function ObtenerMarcaTemp : DateTime;
* Cometido..: Obtiene la fecha del servidor para establecer la
* marca de ocupación.
* ----------------------------------------------------------------
*)
function ObtenerMarcaTemp : DateTime;
begin
strSQL:='select getdate() as MarcaTemp';
EjecutarSQL(strSQL);
ObtenerMarcaTemp:=FieldByName('MarcaTemp').AsDateTime;
end; // ObtenerMarcaTemp
(*
* ----------------------------------------------------------------
* function MarcaCaducada (MarcaOld, MarcaTemp): boolean;
* Cometido..: Comprueba si la marca de modificación ha caducado
* o no.
* ----------------------------------------------------------------
*)
function MarcaCaducada : boolean;
begin
if (MarcaTemp – MarcaOld) > T_CADUCIDAD then
MarcaCaducada:=true
else
MarcaCaducada:=false;
end; // MarcaCaducada
(*
* ----------------------------------------------------------------
* function OcuparTodosRecursos (Cod, MarcaTemp) : boolean;
* Cometido..: Ocupa todos los recursos que se requieren para
* realizar las posteriores modificaciones.
* ----------------------------------------------------------------
*)
function OcuparTodosRecursos (Cod, MarcaTemp) : boolean;
begin
TodosLibres:=true;
strSQL:='select CD, FC_OCUPACION '+
'from TABLA '+
'where CD = '+Cod;
EjecutarSQL(strSQL);
while not eof and TodosLibres do begin
MarcaOld:= FieldByName('FC_OCUPACION').AsString;
if not MarcaCaducada(MarcaOld,MarcaTemp) then
TodosLibres:=false
else begin
strSQL:='update TABLA '+
'set FC_OCUPACION = '+MarcaTemp+' '+
'where CD = '+Cod+' and '+
'FC_OCUPACION = 'MarcaOld+;
NumRegsAfectados:=EjecutarSQL(strSQL);
if NumRegsAfectados < 1 then
TodosLibres:=false;
end;
Next;
end;
OcuparTodosRecursos:=TodosLibres;
end; // OcuparTodosRecursos
(*
* ----------------------------------------------------------------
* procedure LiberarTodosRecursos (Cod, MarcaTemp);
* Cometido..: Libera todos los recursos que se ocuparon para la
* modificación.
* ----------------------------------------------------------------
*)
procedure LiberarTodosRecursos (Cod, MarcaTemp);
begin
TodosOcupados:=true;
strSQL:='select CD '+
'from TABLA '+
'where CD = '+Cod;
EjecutarSQL(strSQL);
while not eof and TodosOcupados do begin
strSQL:='update TABLA '+
'set FC_OCUPACION = '+MARCA_NO_OCUPADO+' '+
'where CD = '+Cod+' and '
'FC_OCUPACION = '+MarcaTemp;
NumRegsAfectados:=EjecutarSQL(strSQL);
if NumRegsAfectados < 1 then
TodosOcupados:=false;
Next;
end;
end; // LiberarTodosRecursos
(*
* ----------------------------------------------------------------
* function VolcarDatosABD (Cod, MarcaTemp, DatosModif) : boolean;
* Cometido..: Vuelca los datos modificados por el usuario a la BD.
* ----------------------------------------------------------------
*)
function VolcarDatosABD (Cod, MarcaTemp, DatosModif) : boolean;
begin
TodosOcupados:=true;
strSQL:='select CD, FC_OCUPACION '+
'from TABLA '+
'where CD = '+Cod;
EjecutarSQL(strSQL);
while not eof and TodosOcupados do begin
if MarcaCaducada(
FieldByName('FC_OCUPACION').AsDateTime,MarcaTemp) then
TodosOcupados:=false;
if TodosOcupados then begin
strSQL:='update TABLA '+
'set CAMPOS_MODIF = '+DatosModif+' '+
'where CD = '+Cod+' and '
'FC_OCUPACION = 'MarcaTemp;
NumRegsAfectados:=EjecutarSQL(strSQL);
if NumRegsAfectados < 1 then
TodosOcupados:=false;
end;
Next;
end;
VolcarDatosABD:=TodosOcupados;
end; // VolcarDatosABD
(*
* ----------------------------------------------------------------
* rutina Principal;
* Cometido..: Esqueleto de la aplicación que maneje esta forma de
* trabajo.
* ----------------------------------------------------------------
*)
rutina Principal;
begin
StartTransaction;
MarcaTempOc:=ObtenerMarcaTemp;
if OcuparTodosRecursos(Cod,MarcaTempOc) then begin
CommitTransaction;
CargarDatosModificables(Cod);
DatosModificados:=RecogerDatosAModificar(DatosModif);
if DatosModificados = OK then begin
StartTransaction;
if VolcarDatosABD(Cod,MarcaTempOc,DatosModif) then begin
LiberarTodosRecursos(Cod,MarcaTempOc);
CommitTransaction;
else begin
RollBackTransaction;
MostrarError('Los datos en memoria han caducado.'+
'Para modificar estos datos vuelva'+
' a filtrar');
end;
end;
if DatosModificados = CANCEL then begin
MarcaTempLib:=ObtenerMarcaTemp;
if not MarcaCaducada(MarcaTempOc,MarcaTempLib) then begin
StartTransaction;
LiberarTodosRecursos(Cod,MarcaTempOc);
CommitTransaction;
end;
end;
end
else begin
RollBackTransaction;
MostrarError('Los datos están siendo modificados'+
' por otro usuario.'+#13+
'Por favor, inténtelo más tarde.');
end;
end; // Principal
En este caso, por poner un ejemplo, la ocupación eficiente de los recursos tendría la forma:
(*
* ----------------------------------------------------------------
* function OcuparTodosRecursosEficiente (Cod,MarcaTemp) : boolean;
* Cometido..: Ocupa todos los recursos que se requieren para
* realizar las posteriores modificaciones con el
* esquema de trabajo más eficiente en servidores poco
* cargados.
* ----------------------------------------------------------------
*)
function OcuparTodosRecursosEficiente (Cod, MarcaTemp) : boolean;
begin
strSQL:='update TABLA '+
'set FC_OCUPACION = '+MarcaTemp+' '+
'where '+MarcaTemp+'-FC_OCUPACION > '+T_CADUCIDAD+
' and '+
'CD = '+Cod;
NumRegsAfectadosUpdate:=Ejecutar(strSQL);
strSQL:='select count(*) as NumRegsSe '+
'from TABLA '+
'where CD = '+Cod;
EjecutarSQL(strSQL);
NumRegsAfectadosSelect:=FieldByName('NumRegsSe').AsInteger;
if NumRegsAfectadosUpdate = NumRegsAfectadosSelect then
OcuparTodosRecursosEficiente:=true
else
OcuparTodosRecursosEficiente:=false;
end; // OcuparTodosRecursosEficiente
Como puede apreciarse, en el proceso existen dos transacciones. En la primera de ellas se ocupan todos los recursos que sean objeto de la modificación. Una vez ocupados todos los recursos objeto de modificación, se cierra la primera transacción. De esta forma se liberan los bloqueos establecidos por SQL Server y se potencia así la concurrencia en el acceso a los datos.
Cuando se han recogido todos los datos que el usuario quiere modificar, si éste acepta los cambios, se inicia una nueva transacción en la que, tras volcar la nueva información a la base de datos, se liberan todos los recursos que se habían ocupado. Si el usuario ha cancelado los cambios, se inicia una nueva transacción con el fin de liberar todos los recursos ocupados. Después se cierra la transacción.
Si entre la primera y la segunda transacción se produce un corte en el suministro eléctrico, el ordenador se bloquea, etc., los recursos ocupados permanecerán en este estado, hasta que su marca de ocupación caduque, por lo que en este caso no es necesario un plan de contingencia que libere los recursos, aunque es cierto que puede implementarse esta opción por si se necesitara la liberación inmediata de los recursos.
Por otro lado, en aquellos mantenimientos en los que no se requiere interactuación con el usuario, el esquema de trabajo varía en algunos aspectos. El pseudocódigo en este caso sería:
(*
* ----------------------------------------------------------------
* function ModificarDatos (Cod,DatosModif) : boolean;
* Cometido..: Modificación de datos de la BD.
* ----------------------------------------------------------------
*)
function ModificarDatos (Cod,DatosModif) : boolean;
begin
StartTransaction;
MarcaTemp:=ObtenerMarcaTemp;
if OcuparTodosRecursos(Cod,MarcaTemp) then begin
VolcarDatosABD(Cod,MarcaTemp,DatosModif);
LiberarTodosRecursos(Cod,MarcaTemp);
CommitTransaction;
ModificarDatos:=true;
end
else begin
RollBackTransaction;
ModificarDatos:=false;
end;
end; // ModificarDatos
(*
* ----------------------------------------------------------------
* rutina Principal;
* Cometido..: Esqueleto de la aplicación que maneje esta forma de
* trabajo.
* ----------------------------------------------------------------
*)
rutina Principal;
begin
if ModificarDatos(Cod,DatosModif) then
Mensaje('Datos modificados')
else
MostrarError('Imposible modificar datos');
end; // Principal
Como puede apreciarse, en este caso, la ocupación y liberación de los recursos se produce dentro de la misma transacción, dado que ya se tienen los nuevos datos a introducir en la base de datos. En este tipo de mantenimientos, el proceso que ocupa los registros siempre podrá terminar con éxito el proceso, ya que SQL Server mantiene los bloqueos que estableció sobre las filas cuando el proceso de modificación ocupó los recursos. Los procesos de este tipo deben tener la transacción abierta el menor tiempo posible para evitar reducir el nivel de concurrencia de acceso a los datos.
Los procesos que sólo visualicen datos sin llegar a modificarlos no tendrán en cuenta el valor de la columna “FC_OCUPACION”, no estando para estos procesos bloqueada la fila en cuestión.
Esta forma de trabajo impide que un usuario bloquee indefinidamente un recurso, sin embargo, puede ocasionar pérdida de tiempo de trabajo si tras introducir un gran volumen de datos no puede llevarse a cabo la modificación porque la ocupación haya caducado y otro proceso haya ocupado alguno de los recursos que necesitaba el primer proceso. Si la elección del tiempo de caducidad se ha hecho adecuadamente, esta situación no debería darse nunca, a no ser que un usuario la forzara de forma intencionada.
“Caducidad” de bloqueo sin pérdidas de actualización
En procesos muy delicados realizados por usuarios altamente cualificados que no provoquen un bloqueo innecesario de recursos y en los que exista la necesidad de interactuación con el usuario, existen dos esquemas que evitan que se pierdan datos de actualización.
El primero de estos esquemas consiste en mezclar la forma de trabajo en mantenimientos que necesitan interactuación con el usuario con la de los que no necesitan dicha interactuación, de forma que las ocupaciones no caduquen. En este caso el pseudocódigo sería:
const
DATOS_MODIF = 1;
DATOS_CANCEL = 2;
DATOS_OCUPADOS = 3;
(*
* ----------------------------------------------------------------
* function ModificarDatos (Cod) : integer;
* Cometido..: Modificación de datos de la BD.
* ----------------------------------------------------------------
*)
function ModificarDatos (Cod) : integer;
begin
StartTransaction;
MarcaTempOc:=ObtenerMarcaTemp;
if OcuparTodosRecursos(Cod,MarcaTempOc) then begin
CargarDatosModificables(Cod);
DatosModificados:=RecogerDatosAModificar(DatosModif);
if DatosModificados = OK then begin
VolcarDatosABD(Cod,MarcaTempOc,DatosModif);
LiberarTodosRecursos(Cod,MarcaTempOc);
CommitTransaction;
ModificarDatos:=DATOS_MODIF;
end;
if DatosModificados = CANCEL then begin
RollBackTransaction;
ModificarDatos:=DATOS_CANCEL;
end;
end
else begin
RollBackTransaction;
ModificarDatos:=DATOS_OCUPADOS;
end;
end; // ModificarDatos
(*
* ----------------------------------------------------------------
* rutina Principal;
* Cometido..: Esqueleto de la aplicación que maneje esta forma de
* trabajo.
* ----------------------------------------------------------------
*)
rutina Principal;
begin
Resultado:=ModificarDatos(Cod);
if Resultado = DATOS_MODIF then
Mensaje('Datos modificados');
if Resultado = DATOS_CANCEL then
Mensaje('Modificación cancelada');
if Resultado = DATOS_OCUPADOS then
MostrarError('Los datos están siendo modificados'+
' por otro usuario.'+#13+
'Por favor, inténtelo más tarde.');
end; // Principal
Como puede apreciarse, todo el proceso se realiza dentro de la misma transacción por lo que SQL Server mantiene los bloqueos establecidos sobre los recursos que bloqueó el proceso hasta el final de la misma. De esta forma, ningún otro proceso podrá bloquear los recursos, aunque la marca haya caducado. De hecho, otro proceso que intentara bloquear los recursos mediante el enfoque optimista se quedaría bloqueado hasta que se terminara la transacción.
Este esquema de trabajo sólo debe implementarse en aquellos procesos extremadamente delicados y cortos en duración, realizados por personal altamente cualificado que no provoque bloqueos innecesarios de los recursos dado que, al mantener los bloqueos establecidos por SQL Server hasta el final de la transacción, ningún otro proceso podrá bloquear dichos recursos hasta la finalización de la misma, llegando incluso a quedarse bloqueados hasta ese momento, reduciéndose de esta forma el nivel de concurrencia de acceso a los datos.
El segundo de estos esquemas presenta la ventaja de que no provocará que ningún otro proceso quede bloqueado por SQL Server, aunque representa una mayor carga para el gestor de bases de datos.
Consiste en asociar un temporizador al formulario en el que se están recogiendo los datos a actualizar de forma que, cuando se dispare éste, se actualice el campo en el que se almacena la caducidad del bloqueo, antes de que se produzca dicha caducidad. De esta forma, otros procesos podrán visualizar datos sin llegar a bloquearse y se asegura que el proceso que ocupó los recursos finalice exitosamente la actualización de los datos.
Leer más sobre:
- Gestión de transacciones en SQL Server (1/7)
- Gestión de bloqueos en SQL Server (2/7)
- Enfoques de control de transacciones en SQL Server (3/7)
- Enfoque optimista de gestión de transacciones en SQL Server (4/7)
- Enfoque pesimista de gestión de transacciones en SQL Server (5/7)
- Enfoque mixto de gestión de transacciones en SQL Server (6/7)
- Interbloqueos en SQL Server (7/7)
Más sobre: Documentación, SQL Server | Tags: bloqueos, concurrencia, SQL, T-SQL, transacciones