Foros Club Delphi - Bloqueo de tablas en MS Sql Server

Foros Club Delphi (https://www.clubdelphi.com/foros/index.php)

- Conexión con bases de datos (https://www.clubdelphi.com/foros/forumdisplay.php?f=2)

- - Bloqueo de tablas en MS Sql Server (https://www.clubdelphi.com/foros/showthread.php?t=74919)

Bloqueo de tablas en MS Sql Server

Buenos Dias...
tengo una inquietud que me gustaria compartir con ustedes y aprovechar sus comentarios

En Visual FOX yo podia bloquear una tabla (DBF) con el comando Flock('Tabla'), esto hacia que cuando algun usuario queria utilizar el sistema.. por la tabla bloqueada, el sistema se mantenia "en espera" (Mientras se actualizaba informacion), hasta que la tabla fuera liberada (UNLOCK)

Al tener la tabla Bloqueada, se podia grabar con mas precision los datos, sobre todo el numero de documento correlativo (ya uds. entienden la problematica)

Normalmente, tenia una tabla Vacia, a la cual se le efectuaba el bloqueo, hacia esto para no tener que bloquear las tablas de movimientos que mantenian mucha informacion

Ya no Uso Visual FOX, Uso Delphi, ya no Uso DBF uso MS Sql Server, me gustaria saber si el procedimiento es el mismo, o Solo Bloquea la Tabla en cuestion para su imposible actualizacion de datos

gracias por los comentarios

Tengo entendido que sí es el mismo proceso, pero ¿para que usar "bloqueos" cuando puedes manejar "Transacciones"?

saludos.

y podrias explicarme un poco el tema de transacciones por favor...

Te dejo algunas notas de Microsoft SQL SERVER 2005

Código:

Marca el punto de inicio de una transacción local explícita. La instrucción BEGIN TRANSACTION incrementa @@TRANCOUNT en 1.



 Convenciones de sintaxis de Transact-SQL



Sintaxis

 

BEGIN { TRAN | TRANSACTION } 

    [ { transaction_name | @tran_name_variable }

      [ WITH MARK [ 'description' ] ]

    ]

[ ; ]

 



Argumentos

transaction_name

Es el nombre asignado a la transacción. El parámetro transaction_name debe cumplir las reglas de los identificadores, pero no se admiten identificadores de más de 32 caracteres. Utilice nombres de transacciones solamente en la pareja más externa de instrucciones BEGIN…COMMIT o BEGIN…ROLLBACK anidadas.



@tran_name_variable

Se trata del nombre de una variable definida por el usuario que contiene un nombre de transacción válido. La variable debe declararse con un tipo de datos char, varchar, nchar o nvarchar. Si se pasan más de 32 caracteres a la variable, sólo se utilizarán los primeros 32; el resto de caracteres se truncará.



WITH MARK [ 'description' ]

Especifica que la transacción está marcada en el registro. El parámetro description es una cadena que describe la marca. Si description es una cadena Unicode, los valores de más de 255 caracteres se truncan a 255 antes de almacenarse en la tabla msdb.dbo.logmarkhistory. Si description no es una cadena Unicode, los valores de más de 510 caracteres se truncan a 510 caracteres.



Si utiliza WITH MARK, debe especificar un nombre de transacción. WITH MARK permite restaurar un registro de transacciones hasta una marca con nombre.



Notas

BEGIN TRANSACTION representa un punto en el que los datos a los que hace referencia una conexión son lógica y físicamente coherentes. Si se producen errores, se pueden revertir todas las modificaciones realizadas en los datos después de BEGIN TRANSACTION para devolver los datos al estado conocido de coherencia. Cada transacción dura hasta que se completa sin errores y se emite COMMIT TRANSACTION para hacer que las modificaciones sean una parte permanente de la base de datos, o hasta que se produzcan errores y se borren todas las modificaciones con la instrucción ROLLBACK TRANSACTION.



BEGIN TRANSACTION inicia una transacción local para la conexión que emite la instrucción. Según la configuración del nivel de aislamiento de la transacción actual, la transacción bloquea muchos recursos adquiridos para aceptar las instrucciones Transact-SQL emitidas por la conexión hasta que la misma finaliza con una instrucción COMMIT TRANSACTION o ROLLBACK TRANSACTION. Las transacciones que quedan pendientes durante mucho tiempo pueden impedir que otros usuarios tengan acceso a estos recursos bloqueados y pueden impedir también el truncamiento del registro.



Aunque BEGIN TRANSACTION inicia una transacción local, ésta no se guardará en el registro de transacciones hasta que la aplicación realice posteriormente una acción que se deba almacenar en el registro, como la ejecución de una instrucción INSERT, UPDATE o DELETE. Una aplicación puede realizar acciones tales como adquirir bloqueos para proteger el nivel de aislamiento de transacciones de instrucciones SELECT, pero no se guarda ningún dato en el registro hasta que la aplicación realiza una acción de modificación.



Asignar un nombre a varias transacciones en un conjunto de transacciones anidadas afecta mínimamente a la transacción. Solamente el nombre de la primera transacción (la más externa) se registra en el sistema. Revertir a otro nombre (que no sea un nombre de punto de almacenamiento válido) genera un error. De hecho, no se revierte ninguna de las instrucciones ejecutadas antes de la operación de revertir en el momento en que se produce este error. Sólo se revierten las instrucciones cuando se revierte la transacción externa.



La transacción local iniciada por la instrucción BEGIN TRANSACTION aumenta al nivel de transacción distribuida si se realizan las siguientes acciones antes de confirmarla o revertirla: 



Se ejecuta una instrucción INSERT, DELETE o UPDATE que hace referencia a una tabla remota de un servidor vinculado. La instrucción INSERT, UPDATE o DELETE causa un error si el proveedor OLE DB utilizado para obtener acceso al servidor vinculado no es compatible con la interfaz ITransactionJoin.





Se realiza una llamada a un procedimiento almacenado remoto cuando la opción REMOTE_PROC_TRANSACTIONS es ON.





La copia local de SQL Server se convierte en el controlador de la transacción y utiliza el Coordinador de transacciones distribuidas de Microsoft (MS DTC) para administrar la transacción distribuida.



Una transacción se puede ejecutar explícitamente como una transacción distribuida utilizando BEGIN DISTRIBUTED TRANSACTION. Para obtener más información, vea BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).



Transacciones marcadas

La opción WITH MARK coloca el nombre de la transacción en el registro de transacciones. Al restaurar una base de datos a su estado anterior, se puede utilizar la transacción marcada en lugar de la fecha y la hora. Para obtener más información, vea Usar transacciones marcadas (modelo de recuperación completa) y RESTORE (Transact-SQL). 



Además, se necesitan las marcas del registro de transacciones si tiene la intención de recuperar un conjunto de bases de datos relacionadas a un estado coherente lógicamente. Una transacción distribuida puede colocar marcas en los registros de transacción de las bases de datos relacionadas. Recuperar el conjunto de bases de datos relacionadas hasta estas marcas da como resultado un conjunto de bases de datos coherente en cuanto a las transacciones. La colocación de las marcas en las bases de datos relacionadas requiere procedimientos especiales.



La marca se coloca en el registro de transacciones solamente si la transacción marcada actualiza la base de datos. No se marcan las transacciones que no modifican los datos.



Se puede anidar BEGIN TRAN new_name WITH MARK en una transacción existente que no esté marcada. De ese modo, new_name se convierte en el nombre de marca de la transacción, aunque esa transacción ya tenga uno. En el siguiente ejemplo, M2 es el nombre de la marca.



 Copiar código 

BEGIN TRAN T1;

UPDATE table1 ...;

BEGIN TRAN M2 WITH MARK;

UPDATE table2 ...;

SELECT * from table1;

COMMIT TRAN M2;

UPDATE table3 ...;

COMMIT TRAN T1;

 



Al anidar transacciones, intentar marcar una transacción ya marcada da lugar a un mensaje de advertencia (no de error):



"BEGIN TRAN T1 WITH MARK ...;"



"UPDATE tabla1 ...;"



"BEGIN TRAN M2 WITH MARK ...;"



"Servidor: mensaje 3920, nivel 16, estado 1, línea 3"



"La opción WITH MARK sólo se aplica a la primera instrucción BEGIN TRAN WITH MARK."



"Se omitirá la opción."



Permisos

Debe pertenecer a la función public.



Ejemplos

A. Asignar un nombre a una transacción

En el siguiente ejemplo se muestra cómo asignar un nombre a una transacción.



 Copiar código 

DECLARE @TranName VARCHAR(20);

SELECT @TranName = 'MyTransaction';



BEGIN TRANSACTION @TranName;

USE AdventureWorks;

DELETE FROM AdventureWorks.HumanResources.JobCandidate

    WHERE JobCandidateID = 13;



COMMIT TRANSACTION @TranName;

GO

 



B. Marcar una transacción

En el siguiente ejemplo se muestra cómo marcar una transacción. Se marca la transacción CandidateDelete.



 Copiar código 

BEGIN TRANSACTION CandidateDelete

    WITH MARK N'Deleting a Job Candidate';

GO

USE AdventureWorks;

GO

DELETE FROM AdventureWorks.HumanResources.JobCandidate

    WHERE JobCandidateID = 13;

GO

COMMIT TRANSACTION CandidateDelete;

GO

Espero que te sirvan, Un saludo.

El bloque de tablas no es aplicable (o no debería serlo) a SGBD como SQL Server (justo va en contra de la idea de un SGBD).

La alternativa, tal y como han comentado, son las transacciones. Utilizándolas puedes conseguir que una operación o varias se realicen de forma individual. Con eso se suele solventar el problema que comentas de los números correlativos; Es bastante habitual, por ejemplo, en el tema de facturación.

Revisa la ayuda de SQL Server y de ADO sobre "Isolation Level"; Para las transacciones y dependiendo de lo que quieras hacer pueder definir un nivel diferente o una forma diferente de trabajar.

Si buscas en los foros, también encontrarás hilos al respecto, puesd ya hemos hablado otras veces de estos temas.

encontre este ejemplo en una web, me gustaria saber sus comentarios

Código Delphi [-]va Level: integer;
begin
Level:= Conexion.BeginTrans;  
try
// Hacer Algo.. con las tablas.. Actualizar, Adicionar, Eliminar
   Conexion.CommitTrans;  
except 
    on E:Exception do Conexion.RollbackTrans;  
end//try
end;

Bueno, más o menos es la forma estandard de hacer las cosas desde Delphi.

(1) Abres la transacción
(2) Ejecutas las operaciones que quieres que se hagan TODAS como una unidad
(3) Si ha ido todo bien, realizas el COMMIT (que acepta TODOS los cambios)
(4) Si ha ocurrido algun error, realizas un COMMIT que deshace TODOS los cambios.

Código Delphi [-]var 
  Level: integer; 
begin 
  // Iniciar la transacción
  Level:= Conexion.BeginTrans;  
  // Proteccion por si hay errores 
  try   // Hacer Algo.. con las tablas.. 
    // Actualizar, Adicionar, Eliminar
    // Todas estas operaciones se realizarán en bloque; O TODAS o NINGUNA
    ...

    // Si llegas aquí es que no ha habido ningun error, por lo tanto ACEPTAMOS TODO
    Conexion.CommitTrans;   
  except 
    // en caso de algun error, llegará aquí
    on E:Exception do begin
      // con esto cancelamos TODAS las operaciones
      Conexion.RollbackTrans;  
    end; 
  end  //try

pues eso es básicamente lo que tendrías que hacer. Solamente la variable "Level" es opcional en caso de vayas a manejar varias transacciones anidadas y quieres llevar el control de cada trasancacción, si no quieres llevar el control específico por cada transacción, pues no sería necesaria esa variable.

Como "cuestión de estilos" a mi no me gusta usar la excepción general "E:Exception", prefiero usar la clase específica para darle un manejo específico a cada tipo de error.

Y Finalmente, en el bloque de código que pones, en caso de ocurrir un error, se hace el "rollback", pero no se muestra ningún mensaje por lo que el usuario nunca se enteraría del error.

tendría que ser algo como:

Código Delphi [-].... 
except   
 on E:Exception do begin   
  Conexion.RollbackTrans;     
  showmessage(E.ClassName + ' - ' +E.Message);   
 end; 
end//try

Importante: el mensaje debe de ir siempre después del "rollback", si lo pones antes, el "rollback" se ejecutará hasta después de que le den "aceptar" y está científicamente comprobado que si el usuario se está tomando un café (o apenas fue a hacerlo) se va a tardar mucho en darle aceptar y la base de datos se queda bloqueada por que la transacción no termina.

retomando este tema
se me ha presentado un problema

tengo una tabla que esta en constante actualización, el asunto es que tengo que agregar nuevos registros a esta tabla
hay un campo que se llama formulario, lo obtengo generando el MAX del campo y le sumo 1, el asunto es que cuando estoy tratando de obtener el ultimo numero para sumarle uno, alguien mas en la web (php) que usa esta misma tabla ya capturo y grabo ese numero, por lo cual me aparece el mensaje de error (porque es un campo PK) que no acepta duplicados

uso transacciones

Código Delphi [-]    
try
    qryTemporal.ExecSQL;
    cnxFeban.Commit;
    Except
      cnxFeban.Rollback;
      raise;

el tema es que ahora estoy usando Firedac

que estoy haciendo mal ?

no podria bloquear la tabla para grabar el registro q quiero y despues liberarla ?
o estoy usando mal las transaciones

La primera opción sería usar un campo AutoIncremental, si es que lo tienes disponible, con eso evitas trabajo (buscar el máximo y sumar 1) y evitas problemas.
Si no puedes usar Autoincrementales, debes hacer ambas operaciones dento de la misma transacción. Si es necesario, modificas el IsolationLevel de la transacción y con eso deberías eviar duplicados.
Aunque como he comentado, no es la práctica recomendable en SGBD's bloquear tablas. Las transacciones son para otros menesteres.

Cita:

Empezado por oscarac (Mensaje 534279)

alguien mas en la web (php) que usa esta misma tabla ya capturo y grabo ese numero, por lo cual me aparece el mensaje de error (porque es un campo PK) que no acepta duplicados

Un rdbms esta pensado para multiples usuarios/app. DEJA DE PENSAR QUE TIENES EL CONTROL TOTAL.

Esta es la clave.

Cita:

Empezado por oscarac (Mensaje 534279)

lo obtengo generando el MAX del campo y le sumo 1, el asunto es que cuando estoy tratando de obtener el ultimo numero para sumarle uno,

Primer problema. Esta no es la forma de tener IDs propios. Lo que haces es subotpimo:

https://stackoverflow.com/questions/...y-alternatives

Como funciona un max?

Tienes un MILLON de registros. La BD por defecto, recorre el MILLON de registro comparando si este es mayor al anterior. Al final, da un resultado. Luego le sumas 1. Esos significa que estas introduciendo una ESPERA. Eso genera una ventana para que otra operacion compita contigo. (Con un indice se puede agilizar el proceso. Igual, es una demora)

En otras palabras, el orden de ejecucion en una RDBMS NO ES deterministico, sino aleatorio. (a menos que uses una transaccion, pero nota mas abajo).

La forma de tener tu propio generador:

Usa un autoincrement, es lo mejor
Usa una tabla "consecutivos" de un solo registro. Guardas el ultimo y tomas y actualizas de ella. Es MUCHO mas rapido que hacerle max a tus tablas.
Abandona los IDs consecutivos y usa un campo GUID o similar, que estan hechos para IDs GLOBALES.

Cita:

Empezado por oscarac (Mensaje 534279)

uso transacciones

Solo son LOCALES A TU OPERACION. No hay una forma *eficiente* de hacer transacciones globales, y lo que hay implica meter un monton de asuntos que es mejor no lidiar con ellos.

Cita:

Empezado por oscarac (Mensaje 534279)

no podria bloquear la tabla para grabar el registro q quiero y despues liberarla ?
o estoy usando mal las transaciones

Eso es NEGAR la forma como funciona un rdbms. Lo que en otras palabras, es malo. Por ejemplo, si pones un campo booleano para indicar que el registro esta "ocupado" y luego lo deschuleas, eso puede PARECER que funciona. Pero si la app muere a mitad del camino? Dejas el registro bloqueado y generas un DEAD LOCK. Algo que empieza por "dead" no es nada bueno!

-----
Asi, que que hacer? El problema que tienes te grita: Tu diseño esta errado. Deja que la BD genere IDs.

A menos que presentes un escenario justificable, yo pararía ahi.