RowLock

I have to lock the row while insertion or at updation when many users
accessthe particular row.i f the first user access the row after at that tim
e
the row should be locked after his updation the row will unlock since many
users not have to updation at the same time.so ihave lock and release the ro
w
in the stored procedureIf I understood your post correctly, you need some sort of check-out
management. This is not provided natively by SQL Server 2000, and can be
achieved in several ways.
If you specify your needs and provide the DDL and some sample data, maybe we
can help you find a solution.
ML|||hi ML,
I will explain in clearly.
By Using frontend(PHP forms)my clients can update a particular row in
particular table at that same time.while updating i have to update th versio
n
number in the table.so i took the maxvalue of that column at that particular
row and update version mumber.while at that time of updating max count two o
r
more clients gets the same value of that the particular column at that same
time.so it will crasing the number.so i have to lock that paricular while on
e
updating if another tries it displays a message some one is updating,so
please wait a moment and try after his updation is over the row lockis
released and another client able to sccess that row and update.
so how can lock that row and release the lock
it is long query,the query depends many so i can't able to send the query
directly
regards,
balakarthik
"ML" wrote:

> If I understood your post correctly, you need some sort of check-out
> management. This is not provided natively by SQL Server 2000, and can be
> achieved in several ways.
> If you specify your needs and provide the DDL and some sample data, maybe
we
> can help you find a solution.
>
> ML|||balakarthik
You have already asked this in .server and already have been given some
references there. Please do not multipost, it make is very confusing for
those people who are trying to help, who don't know what has already been
said.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:E98C44C0-69AC-489A-9B52-D43785C4D684@.microsoft.com...
> hi ML,
> I will explain in clearly.
> By Using frontend(PHP forms)my clients can update a particular row in
> particular table at that same time.while updating i have to update th
> version
> number in the table.so i took the maxvalue of that column at that
> particular
> row and update version mumber.while at that time of updating max count two
> or
> more clients gets the same value of that the particular column at that
> same
> time.so it will crasing the number.so i have to lock that paricular while
> one
> updating if another tries it displays a message some one is updating,so
> please wait a moment and try after his updation is over the row lockis
> released and another client able to sccess that row and update.
>
> so how can lock that row and release the lock
> it is long query,the query depends many so i can't able to send the query
> directly
>
> regards,
> balakarthik
> "ML" wrote:
>|||You need to use SELECT MAX...FROM...WITH(UPDLOCK, HOLDLOCK) to get the
maximum value, and the locks must be held until the INSERT/UPDATE completes.
If you issue INSERT...SELECT MAX...FROM ...WITH(UPDLOCK, HOLDLOCK) then the
INSERT statement provides an implicit transaction. If you issue SELECT
@.localVariable = MAX...FROM...WITH(UPDLOCK, HOLDLOCK) INSERT...VALUES, then
you need to enclose both statements within a transaction. WITH(UPDLOCK,
HOLDLOCK) prevents other transactions from issuing an insert or update that
has a value for the column that is between MAX and infinity until the lock
is released. Both UPDLOCK and HOLDLOCK are required because you need an
update range-lock, not an individual update lock, or a shared range-lock.
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:E98C44C0-69AC-489A-9B52-D43785C4D684@.microsoft.com...
> hi ML,
> I will explain in clearly.
> By Using frontend(PHP forms)my clients can update a particular row in
> particular table at that same time.while updating i have to update th
version
> number in the table.so i took the maxvalue of that column at that
particular
> row and update version mumber.while at that time of updating max count two
or
> more clients gets the same value of that the particular column at that
same
> time.so it will crasing the number.so i have to lock that paricular while
one
> updating if another tries it displays a message some one is updating,so
> please wait a moment and try after his updation is over the row lockis
> released and another client able to sccess that row and update.
>
> so how can lock that row and release the lock
> it is long query,the query depends many so i can't able to send the query
> directly
>
> regards,
> balakarthik
> "ML" wrote:
>
maybe we|||I see.
Let us forget for a minute about rows, columns, tables and databases. Let's
discuss entities.
Let's say your entity is a single document in a drawer. But this is not an
ordinary drawer, since many users can read the same document at any given
moment. However, only one of them should be allowed to change the contents o
f
that document at any given time.
What you need to do is to let other users know that changes to a document
cannot be made if that document is "checked-out".
In a database you need to do something like this:
1) make sure all data access is done through procedures; and
2) make sure procedures cannot make changes to documents that are
checked-out; and
3) make sure the appropriate procedures report check-outs to the database.
One way is to add a column (or two) to the master table (BTW: since I
haven't seen your DDL this is more a guess than a fully valid suggestion):
CheckedOutBy - it holds the username of the person who checked out the
document. When null, the document is not checked out.
(Maybe even CheckedOutSince - it holds the time of the check-out.)
So, in a use case you'd do something like:
when a user gets a document that hasn't been checked-out (CheckOutBy is
null), the reading procedure sets CheckedOutBy to store the name of the
current user; and when a user finishes his changes the procedure then sets
CheckedOutBy back to null;
any user that tries to check out a document that is already checked out by
another user is warned that his changes will not succeed, and the applicatio
n
should prevent any changes to be made.
But, this is just *one* way of doing it.
ML|||Thanks sir i wil try
"ML" wrote:

> I see.
> Let us forget for a minute about rows, columns, tables and databases. Let'
s
> discuss entities.
> Let's say your entity is a single document in a drawer. But this is not an
> ordinary drawer, since many users can read the same document at any given
> moment. However, only one of them should be allowed to change the contents
of
> that document at any given time.
> What you need to do is to let other users know that changes to a document
> cannot be made if that document is "checked-out".
> In a database you need to do something like this:
> 1) make sure all data access is done through procedures; and
> 2) make sure procedures cannot make changes to documents that are
> checked-out; and
> 3) make sure the appropriate procedures report check-outs to the database.
> One way is to add a column (or two) to the master table (BTW: since I
> haven't seen your DDL this is more a guess than a fully valid suggestion):
> CheckedOutBy - it holds the username of the person who checked out the
> document. When null, the document is not checked out.
> (Maybe even CheckedOutSince - it holds the time of the check-out.)
> So, in a use case you'd do something like:
> when a user gets a document that hasn't been checked-out (CheckOutBy is
> null), the reading procedure sets CheckedOutBy to store the name of the
> current user; and when a user finishes his changes the procedure then sets
> CheckedOutBy back to null;
> any user that tries to check out a document that is already checked out by
> another user is warned that his changes will not succeed, and the applicat
ion
> should prevent any changes to be made.
> But, this is just *one* way of doing it.
>
> ML