| Author |
obtain a lock on table
|
|
|
| I want to generate a timeout error when inserting a record. I would like to
have 2 process, one is locking the table and one is waiting on the other one
releasing the lock in order to insert a new record into table. The second
process will get timeout eventually. How can I obtain a lock on table?
| |
| Russell Fields 2002-11-20, 4:23 pm |
| Mimi,
You can use a table hint in the FROM clause. From the BOL:
TABLOCK
Specifies that a shared lock is taken on the table held until the
end-of-statement. If HOLDLOCK is also specified, the shared table lock is
held until the end of the transaction.
TABLOCKX
Specifies that an exclusive lock is taken on the table held until the
end-of-statement or end-of-transaction.
Russell Fields
"Mimi" <mh2521@hotmail.com> wrote in message
news:uRCsCqNkCHA.3752@tkmsftngp08...
> I want to generate a timeout error when inserting a record. I would like
to
> have 2 process, one is locking the table and one is waiting on the other
one
> releasing the lock in order to insert a new record into table. The second
> process will get timeout eventually. How can I obtain a lock on table?
>
>
| |
| Mike John 2002-11-20, 4:23 pm |
|
Process 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN1
SELECT * from t1 where ---
GO
Locks will be held (shared locks) on row retrieved and page that contains
it - if no where clause or no useful indexes then it will be holding a
shared lock on all pages.
In process 2
INSERT INTO ---
Watch it wait - forever unless you have set lock timeout
Mike John
"Mimi" <mh2521@hotmail.com> wrote in message
news:uRCsCqNkCHA.3752@tkmsftngp08...
> I want to generate a timeout error when inserting a record. I would like
to
> have 2 process, one is locking the table and one is waiting on the other
one
> releasing the lock in order to insert a new record into table. The second
> process will get timeout eventually. How can I obtain a lock on table?
>
>
|
|
|
|