Question: I'm writing a SQL Server stored procedure in which I want to lock a table for update before executing the body of the stored procedure. I don't want to prevent other processes from reading the table, but I do want to prevent other processes updating the table.

Here is my first attempt:

```
CREATE PROCEDURE someProcedure
BEGIN
   SET TRANSACTION ISOLATION LEVEL READ COMITTED
   BEGIN TRANSANCTION
     SELECT COUNT(*) FROM TheTable WITH (UPDLOCK, TABLOCK)

     -- Pause procedure so that we can view the locks with sp_lock
     WAITFOR DELAY '00:15'

     -- Do stuff
   COMMIT
END

```

When I execute the stored procedure, and invoke sp\_lock, I see that the table is indeed locked. However, it's locked with an Exclusive lock instead of an update lock:

```
spid | dbid | ObjId     | IndId | Type | Resource | Mode | Status
------------------------------------------------------------------
63   | 10   | 233208031 | 0     | TAB  |          | X    | GRANT

```

How can I get an update (U) lock instead?

Answer: 
You said:

> 
> I don't want to prevent other
>  processes from reading the table, but
>  I do want to prevent other processes
>  updating the table.
> 
> 
> 

You simply need a shared read lock for the duration of the TXN. This means no other process can get a "write" lock, in conjunction with a TABLOCK. And you don't need COUNT either.

```
...
   BEGIN TRANSANCTION
     SELECT TOP 1 KeyCol FROM TheTable WITH (TABLOCK, HOLDLOCK)
...

```

Why do you think you want an UPDATE LOCK?

[HOLDLOCK or SERIALIZABLE](http://msdn.microsoft.com/en-us/library/ms187373.aspx)

> 
> Is equivalent to SERIALIZABLE. For more
>  information, see SERIALIZABLE later in
>  this topic. HOLDLOCK applies only to
>  the table or view for which it is
>  specified and only for the duration of
>  the transaction defined by the
>  statement that it is used in.
>  ...
>  Makes shared locks more restrictive by
>  holding them until a transaction is
>  completed, instead of releasing the
>  shared lock as soon as the required
>  table or data page is no longer
>  needed, whether the transaction has
>  been completed or not.
> 
> 
> 

**Edit, after comment:**

* "exclusive lock" means "only one process using the data".
* "SERIALIZABLE" basically means hold the locks (shared, exclusive, whatever) for a lot longer.

You can't specify "exclusive lock" *and* allow other processes to read. The concepts are mutually exclusive. You want to prevent writes to the entire table, which a *persisted* shared/read lock will do. This is where SERIALIZABLE comes in.

From ["Lock Modes"](http://msdn.microsoft.com/en-us/library/ms175519.aspx)

> 
> Shared Locks 
> 
> 
> ...No other
>  transactions can modify the data while
>  shared (S) locks exist on the
>  resource. Shared (S) locks on a
>  resource are released as soon as the
>  read operation completes, unless the
>  transaction isolation level is set to
>  repeatable read or higher, or a
>  locking hint is used to retain the
>  shared (S) locks for the duration of
>  the transaction.
> 
> 
> 

**So: a shared lock disallows writes and can be made to persist by making it SERIALIZABLE**
**TABLOCK** 

Specifies that a lock is taken on the table and held until the end-of-statement. If data is being read, a shared lock is taken. If data is being modified, an exclusive lock is taken. If HOLDLOCK is also specified, a shared table lock is held until the end of the transaction.

When used with the OPENROWSET bulk rowset provider to import data into a table without indexes, TABLOCK enables multiple clients to concurrently load data into the target table with optimized logging and locking.

Taken from: <http://msdn.microsoft.com/en-us/library/ms187373(SQL.90).aspx>