usar un update sin bloquear a usuarios

[hdspeed]

Saludos
En un servidor SQL 7.0 tengo ejecutar a diario actualizaciones a los catalogos principale pero tengo el problema que es mucha la informacion que debo actualizar y los usuarios que solo consulta se bloquean ...

ahora como puedo hacer un update sin que los demas que consultan las tablas afectadas se bloqueen??


les agradeceria si me pueden contestar lo mas pronto posible gracias....

[Mithrandir]

Revisa los "hints", que son instrucciones especiales para forzar ciertos tipos de bloqueo. Si la actualización es grande es comprensible que el optimizador opte por bloquear la tabla completa

Table Hints
A table hint specifies a table scan, one or more indexes to be used by the query optimizer, or a locking method to be used by the query optimizer with this table and for this SELECT. Although this is an option, the query optimizer can usually pick the best optimization method without hints being specified.



Caution Because the query optimizer of SQL Server usually selects the best execution plan for a query, it is recommended that <join_hint>, <query_hint>, <table_hint>, and <view_hint> only be used as a last resort by experienced developers and database administrators.


The table hints are ignored if the table is not accessed by the query plan. This may be a result of the optimizer's choice not to access the table at all, or because an indexed view is accessed instead. In the latter case, the use of an indexed view may be prevented by using the OPTION (EXPAND VIEWS) query hint.

The use of commas between table hints is optional but encouraged. Separation of hints by spaces rather than commas is supported for backward compatibility.

The use of the WITH keyword is encouraged, although it is not currently required. In future releases of SQL Server, WITH may be a required keyword.

In SQL Server 2000, all lock hints are propagated to all the base tables and views that are referenced in a view. In addition, SQL Server performs the corresponding lock consistency checks.

If a table (including system tables) contains computed columns and the computed columns are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables (the table hints are not propagated). For example, a NOLOCK table hint is specified on a table in the query. This table has computed columns that are computed by a combination of expressions and functions (accessing columns in another table). The tables referenced by the expressions and functions do not use the NOLOCK table hint when accessed.

SQL Server does not allow more than one table hint from each of the following groups for each table in the FROM clause:

Granularity hints: PAGLOCK, NOLOCK, ROWLOCK, TABLOCK, or TABLOCKX.


Isolation level hints: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.
The NOLOCK, READUNCOMMITTED, and READPAST table hints are not allowed for tables that are targets of delete, insert, or update operations.

Syntax
< table_hint > ::=
{ INDEX ( index_val [ ,...n ] )
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
}

USE pubs
BEGIN TRAN
SELECT COUNT(*)
FROM authors WITH (TABLOCK, HOLDLOCK)

[Dynk]

Intenta usar algo como:

UPDATE
TABLA
With(RowLock) --Bloquea la row actual sin bloquear toda la tabla
SET...