Como aplicar LOCK IN SHARE MODE en SQLserver2000

[zxs23]

He probado en Mysql5 LOCK IN SHARE MODE

SELECT * FROM tabla WHERE cod='150' LOCK IN SHARE MODE

Solo he hecho pruebas simples pero en SQLserver2000 no se puede por que no existe, que debo utilizar para hacer lo mismo que el LOCK IN?.

[ssccaann43]

SQL Server bloquea los recursos con diferentes modos de bloqueo que determinan cómo las transacciones simultáneas pueden tener acceso a los recursos.

SQL Server utiliza estos modos de bloqueo de recursos.

Modo de bloqueo

Compartido (S): Utilizado para operaciones que no cambian o actualizan datos (operaciones de sólo lectura), como la instrucción SELECT.

Actualizar (U): Utilizado en recursos que se pueden actualizar. Evita una forma común de interbloqueo que se produce cuando hay varias sesiones que leen, bloquean y, a continuación, posiblemente actualizan recursos.

Exclusivo (X): Se utiliza para operaciones de modificación de datos, como INSERT, UPDATE o DELETE. Asegura que no se pueden realizar varias actualizaciones en el mismo recurso al mismo tiempo.

Intención: Utilizado para establecer una jerarquía de bloqueos. Los tipos de bloqueo con intención son: bloqueo con intención compartido (IS), bloqueo con intención exclusivo (IX), y bloqueo con intención compartido y exclusivo (SIX).

Esquema: Utilizado cuando se ejecuta una operación que depende del esquema de una tabla. Los tipos de bloqueo de esquema son: modificación del esquema (Sch-M) y estabilidad del esquema (Sch-S).
 
Actualización masiva (BU): Utilizado cuando se copian datos de forma masiva en una tabla y se especifica la sugerencia TABLOCK.

Bloqueos compartidos

Los bloqueos compartidos (S) permiten que varias transacciones simultáneas lean (SELECT) un recurso. Ninguna otra transacción puede modificar los datos mientras haya bloqueos compartidos (S) en el recurso. Los bloqueos compartidos (S) en un recurso se liberan tan pronto como se han leído los datos, a menos que se haya establecido el nivel de aislamiento de la transacción como lectura repetible o más alto, o bien se utilice una sugerencia de bloqueo para mantener los bloqueos compartidos (S) durante la transacción.

Bloqueos de actualización
Los bloqueos de actualización (U) evitan una forma común de interbloqueo. Un patrón típico de actualización consiste en una transacción que lee un registro, adquiere un bloqueo compartido (S) en el recurso (página o fila) y, a continuación, modifica la fila, que necesita la conversión del bloqueo en bloqueo exclusivo (X). Si dos transacciones adquieren bloqueos compartidos en un recurso y, a continuación, intentan actualizar los datos simultáneamente, una de ellas intenta convertir el bloqueo en bloqueo exclusivo (X). La conversión de bloqueo compartido en exclusivo debe esperar, ya que el bloqueo exclusivo de una transacción no es compatible con el bloqueo compartido de la otra. Por tanto, se produce una espera de bloqueos. La segunda transacción intenta adquirir un bloqueo exclusivo (X) para realizar su actualización. Debido a que ambas transacciones intentan convertir los bloqueos en exclusivos (X) y cada una espera a que la otra libere su bloqueo de modo compartido, se produce un interbloqueo.

Para evitar este posible problema de interbloqueo, se utilizan los bloqueos de actualización (U). Dos transacciones no pueden obtener simultáneamente un bloqueo de actualización (U) para un recurso. Si una transacción modifica un recurso, el bloqueo de actualización (U) se convierte en bloqueo exclusivo (X). En caso contrario, el bloqueo se convierte en bloqueo de modo compartido.

Bloqueos exclusivos
Los bloqueos exclusivos (X) evitan que las transacciones simultáneas tengan acceso a un recurso. Ninguna otra transacción puede leer o modificar los datos bloqueados con un bloqueo exclusivo (X).

Bloqueos con intención
Un bloqueo con intención indica que SQL Server desea adquirir un bloqueo compartido (S) o exclusivo (X) en alguno de los recursos inferiores de la jerarquía. Por ejemplo, si se aplica un bloqueo con intención compartido a una tabla, significa que una transacción intenta aplicar bloqueos compartidos (S) a las páginas o filas de esa tabla. Establecer un bloqueo con intención en una tabla evita que otra transacción adquiera un bloqueo exclusivo (X) en la tabla que contiene esa página. Los bloqueos con intención mejoran el rendimiento, porque SQL Server examina los bloqueos con intención sólo en el nivel de tabla para determinar si una transacción puede adquirir un bloqueo en dicha tabla de forma segura. Esto elimina el requisito de examinar cada bloqueo de fila o de página de la tabla para determinar si una transacción puede bloquear toda la tabla.

Los bloqueos con intención incluyen los compartidos (IS), con intención exclusivos (IX) y los compartidos con intención exclusivos (SIX).

Modo de bloqueo
Intención compartido (IS): Indica que una transacción tiene la intención de leer una parte de los recursos inferiores de la jerarquía mediante la colocación de bloqueos S en esos recursos concretos.

Intención exclusivo (IX): Indica que una transacción tiene la intención de modificar una parte de los recursos inferiores de la jerarquía mediante la colocación de bloqueos X en esos recursos concretos. IX es un superconjunto de IS.

Compartido con intención exclusivo (SIX): Indica que la transacción tiene la intención de leer todos los recursos inferiores de la jerarquía y modificar una parte de ellos colocando bloqueos IX en esos recursos concretos. Se permiten los bloqueos IS simultáneos en el recurso de nivel superior. Por ejemplo, un bloqueo SIX en una tabla coloca un bloqueo SIX en la tabla (permite lecturas simultáneas) y bloqueos IX en las páginas que se van a modificar (y bloqueos X en las filas modificadas). Sólo puede haber un bloqueo SIX simultáneo por recurso para impedir que otras transacciones lo actualicen, aunque otras transacciones pueden leer los recursos inferiores de la jerarquía obteniendo bloqueos IS en el nivel de tabla.


Bloqueos de esquema
Los bloqueos de modificación del esquema (Sch-M) se utilizan cuando se realiza una operación de lenguaje de definición de datos (DDL) en tablas como, por ejemplo, agregar una columna o quitar una tabla.

Los bloqueos de estabilidad del esquema (Sch-S) se utilizan al compilar consultas. Los bloqueos de estabilidad del esquema (Sch-S) no impiden los bloqueos de transacciones, incluidos los bloqueos exclusivos (X). Por tanto, otras transacciones, incluidas las que tienen bloqueos exclusivos (X) en una tabla, pueden seguir ejecutándose mientras se compila una consulta. Sin embargo, no se pueden realizar operaciones de DDL en la tabla.

Bloqueos de actualización masiva
Los bloqueos de actualización masiva (BU) se obtienen cuando se copian datos en una tabla de forma masiva, y se especifica la sugerencia TABLOCK o se establece la opción de tabla table lock on bulk load con sp_tableoption. Los bloqueos de actualización masiva (BU) permiten a los procesos copiar datos de forma masiva y simultánea en la misma tabla, pero impiden que otros procesos que no están copiando datos de forma masiva tengan acceso a la tabla.

Ejemplo de una transacción:

Código: [Seleccionar]

USE pubs
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT au_lname FROM authors (ROWLOCK)
GO

Puedo acotar que generalmente esto no es muy usado en SQL Server, muy rara vez algunos programadores usan estas transacciones. De hecho yo no las uso. Pero no está demás aprender un poco.

Adjunto un link en ingles, revisalo de allí saque el ejemplo de transacción:
http://dotnetguts.blogspot.com/2007/08/faq-on-lock-in-sql-server.html

Cualquier cosa, sigue posteando. Saludos.

[zxs23]

Dejame revisar el texto, y como se hace ó haces para bloquear un registro que no debe de ser eliminado desde otro punto ya que se estaria utilizando desde vb6.0

[ssccaann43]

Bien,

Para usarlo desde VB, tan sencillo que haces la consulta SQL indicando el tipo de bloqueo.
Ejem.

Código: [Seleccionar]

SQL="SELECT * FROM visitante WITH (HOLDLOCK) WHERE (cedula = '17078838') 'Allí indico el tipo de bloqueo.

Espero puedas leer en Ingles: 

Locking optimizer hints
SQL Server 7.0/2000 supports the following Locking optimizer hints:

# NOLOCK
# HOLDLOCK
# UPDLOCK
# TABLOCK
# PAGLOCK
# TABLOCKX
# READCOMMITTED
# READUNCOMMITTED
# REPEATABLEREAD
# SERIALIZABLE
# READPAST
# ROWLOCK


NOLOCK is also known as "dirty reads". This option directs SQL Server not to issue shared locks and not to honor exclusive locks. So, if this option is specified, it is possible to read an uncommitted transaction. This results in higher concurrency and in lower consistency.

HOLDLOCK directs SQL Server to hold a shared lock until completion of the transaction in which HOLDLOCK is used. You cannot use HOLDLOCK in a SELECT statement that includes the FOR BROWSE option. HOLDLOCK is equivalent to SERIALIZABLE.

UPDLOCK instructs SQL Server to use update locks instead of shared locks while reading a table and holds them until the end of the command or transaction.

TABLOCK takes a shared lock on the table that is held until the end of the command. If you also specify HOLDLOCK, the lock is held until the end of the transaction.

PAGLOCK is used by default. Directs SQL Server to use shared page locks.

TABLOCKX takes an exclusive lock on the table that is held until the end of the command or transaction.

READCOMMITTED
Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation level. By default, SQL Server operates at this isolation level.

READUNCOMMITTED
Equivalent to NOLOCK.

REPEATABLEREAD
Perform a scan with the same locking semantics as a transaction running at the REPEATABLE READ isolation level.

SERIALIZABLE
Perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE isolation level. Equivalent to HOLDLOCK.

READPAST
Skip locked rows. This option causes a transaction to skip over rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

ROWLOCK
Use row-level locks rather than use the coarser-grained page- and table-level locks.

[zxs23]

Hola bueno estoy intentando hacer esto y no me funciona, seguro das con el problema.

En el form1 tengo este boton que utiliza el idemployee='05' supuestamente bloqueado.

Código: [Seleccionar]

Private Sub Command1_Click()
Set rs = New Recordset
rs.Open "SELECT * FROM employee WITH (HOLDLOCK) WHERE idemployee='05'", cnx, adOpenStatic, adLockOptimistic
Set MSHFlexGrid1.DataSource = rs
End Sub

Y desde el form2 este codigo que permite borrar los empleados, supuestamente si desde este form intento borrar el id=05 me debe enviar un error por que ese id esta siendo utilizado desde otro punto, todavia no le he puesto el On Error GoTo pero igual me deberia enviar un error y no lo hace ya que de todas formas me elimina el id=05

Código: [Seleccionar]

Private Sub Command1_Click()
'Borro el registro
Set rs = New Recordset
rs.Open "DELETE FROM employee WHERE idEmployee='" & Text1.Text & "'", cnx, adOpenStatic, adLockOptimistic

'Vuelvo a cargar el grid
rs.Open "SELECT * FROM employee", cnx, adOpenStatic, adLockOptimistic
Set MSHFlexGrid1.DataSource = rs
End Sub

Como puedo lograr lo que necesito, saludos

[ssccaann43]

En SQL Server, informa si el campo ha sido actualizado o no. Y de eliminarlo, pues si lo elimina e informa igualmente. Yo hice la prueba desde el mismo SQL Server y funcionó (elimina el registro, pero advierte al usuario si desea actualizarlo con la eliminación o actualizar la tabla sin la eliminación). Ahora desde el VB, como te dije, no lo he usado puesto que no me ha sido necesario. De hecho yo particularmente, estas prohibiciones las haría desde codigo.

Modificación:
El equivalente de LOCK TABLES READ de MySQL en SQL Server seria:

SELECT * FROM tabla WITH TABLOCKX

Esto lo que produce es que ningun otro usuario pueda leer o actualizar dicha data de la tabla consultada por ti hasta que no finalize tu transaccion (te retorne los datos).

(Esta información la he consultado con otro experto en BD)

[zxs23]

Perdona la insistencia pero no logro que funcione, en que contexto has hecho las pruebas, yo lo pruebo desde el analizador de cosultas y simplemente lo toma como un select normal y el delete lo hace sin problemas y no me muestra ningun tipo de mensaje solo el "(1 filas afectadas)", incluso lo he probado haciendo en HOLDLOCK y el delete desde distintas ventanas, estoy utilizando solo una makina local ya q no dispongo de red por el momento.

Abro el analizador de consultas, ejecuto las sentencias y no me hace ningun tipo de bloqueo

SELECT * FROM empleado WITH (HOLDLOCK) WHERE (idEmpleado = '05')
DELETE FROM empleado WHERE (idEmpleado = '05')

Logicamente espero un mensaje p.e: "no se puede eliminar por que esta siendo utilizado el id=05

Voy a probar el TABLOCKX pero es seguro que no resulte ya q algo estoy haciendo mal, como debo hacer para probar el bloqueo?

[zxs23]

Si lo hago por codigo desde vb6.0 como capturo el error por que en varias situaciones devuelve el mismo numero de error o como lo trabajas tu?

saludos

[ssccaann43]

Vaya, en mi primera respuesta te dije que yo no uso bloqueos...

En tal caso lo haría mediante codigos, si me encuentro ejecutando un módulo, tan sencillo lo deshabilito para que otro usuario no pueda accesar desde otra máquina...

[zxs23]

oks, cuando logre el bloqueo desde sqlserver lo posteo para todos

saludos

[zxs23]

Ya logre hacer las pruebas utilizando rowlock y demas bloqueos aunque segun dicen no es recomendable.

usuario 1 - bloquea el registro hasta aplicar el COMMIT
BEGIN TRANSACTION
SELECT * FROM empleado with(ROWLOCK) WHERE idEmpleado='01'
--COMMIT

usuario 2 - Para evitar que se quede esperando el COMMIT del user 1 se le coloca un tiempo de p.e 3 segundos, de este modo enviara un error y no se colgara
SET lock_timeout 3000
DELETE FROM empleado WHERE idEmpleado='01'

Con este ejemplo se bloque el idEmpleado='01' para evitar que otro usuario lo borre mientras este en uso.

saludos



saludos