|
Home > Archive > MS SQL Server > July 2005 > Sql Server 2000" Lock" Doubt.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Sql Server 2000" Lock" Doubt.
|
|
| Pradeep 2005-07-27, 8:23 pm |
| Hi Friends,
This is Pradeep. I wanna know how to use the SqL Server locks. I would like
to expalin u in detail where i wanna to use the lock.
I am having a table in which more than 40 users are accessing.
A single record is fetched from the table and shown to each user. when a
single record is fetching by all the user the i have a problem, ie. if a
record is shown to one user it should not come to the other. So at this
moment i need to use the locking concept. i.e when i use the "select " i have
to use the lock.. so how i can over come this problem pls help me out...
thnks & rgds Pradeep.
| |
| rkusenet 2005-07-27, 8:23 pm |
| "Pradeep" <Pradeep@discussions.microsoft.com> wrote
> Hi Friends,
> This is Pradeep. I wanna know how to use the SqL Server locks. I would like
> to expalin u in detail where i wanna to use the lock.
> I am having a table in which more than 40 users are accessing.
> A single record is fetched from the table and shown to each user. when a
> single record is fetching by all the user the i have a problem, ie. if a
> record is shown to one user it should not come to the other. So at this
> moment i need to use the locking concept. i.e when i use the "select " i have
> to use the lock.. so how i can over come this problem pls help me out...
> thnks & rgds Pradeep.
You should use isolation level committed read (do not use serializable read
or repeatable read as it is an overkill for this purpose).
The trick is to keep the lock period as short as possible. Hence
locking the row while the row is being processed by the user is
unacceptable. In that case other sessions will wait for this user
to commit or rollback. Hence this is what you should do.
In the table create a column (say read_status). Initially it can be
NULL. Here is a pseudo code.
BEGIN TRAN
SET ISOLATION LEVEL READ COMMITTED
DECLARE UPDATE CURSOR FOR THE TABLE WHERE ... AND READ_STATUS IS NULL
OPEN THE CURSOR AND FETCH THE PRIMARY KEY
UPDATE THE READ_STATUS VALUE TO 'R'
COMMIT TRAN
What this will do is to allow only one user to read a row. The above code
guarantees that. Once the transaction is committed, no other user can
fetch that row.
Next use the primary key fetched in the above code to re-read the row
and display it to the user. This can be a simple read with no lock.
The above mechanism assures minimum lock retention time.
|
|
|
|
|