Retreive, Update, Delete problem....

[Daniel]

Hi All,
I have question regarding to the read, and update the row in the table.

for example:
user A, read the row of data from the table.
user B, read the row of data from the table as well.
user A, update the row of data. (lock)
user B, update the row of data. (fail due to user A lock)
user A, release the lock. (unlock)
At the user B side, the data auto refresh in the data grid table.
This time, user B update the row of data (lock).
Lastly, user B unlock the row of data. (unlock).

My question is:
how to implement this in the web form.
Any additional setting is needed in sql server 2000?
What is the additional code is needed to the coding page?
What is the additional T-SQL language is needed in our query? "? Insert..?
Select.. ? Update..? Delete..".

Any ideas? Any guidelines?
Your help will be appreciated.

Thank you in advance.

Best regards,
Daniel.

[jasonkester]

If you know you are going to modify and save a record after reading it,
you should pull it up inside the same transaction as your Update, and
use WITH (UPDLOCK) on the Select to hold the lock until you perform
your Update.

so,

(A) begin transaction A
(A) select row WITH (UPDLOCK)
(B) begin transaction B
(B) select row // will block...
(A) update row
(A) commit transaction A
(B) // finally receive row requested above because transaction A no
longer blocks.
Hope this helps!

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

[Daniel]

Hi jason,
If use UPDLOCK, what is the unlock command for it?
If the user B is trying to view the row of record which is updating by user
A, what the user B can display? Is it empty record in the data grid table or
still allow to view but not the update access?

Please guide me thru..thank you.

"jasonkester" wrote:

If you know you are going to modify and save a record after reading it,
you should pull it up inside the same transaction as your Update, and
use WITH (UPDLOCK) on the Select to hold the lock until you perform
your Update.

so,

(A) begin transaction A
(A) select row WITH (UPDLOCK)
(B) begin transaction B
(B) select row // will block...
(A) update row
(A) commit transaction A
(B) // finally receive row requested above because transaction A no
longer blocks.
Hope this helps!

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

[jasonkester]

You can dig around in the SQL documentation for a walkthru, but
basically any records that you Select WITH (UPDLOCK) inside of a
Transaction will be unavailable to any other Select statement that also
uses WITH (UPDLOCK) until you either Commit or Rollback the
Transaction.

The easiest way to watch this in action is inside Query analyzer. Open
a new connection, paste the following code and run it (substituting a
table and recordID from your database):

begin transaction
select *
from Location WITH (UPDLOCK)
where LocationID = 10

Next open another query window, paste the same query and run it too.
It should just sit there, not returning anything.
Next, go back to the first query window and add the following line,
then select it and hit F5:

commit transaction

If you look at window #2, you will see that it has stopped blocking,
and has returned a record.

In your application, you'll use the SqlTransaction object in ADO.NET
rather than the BEGIN & COMMIT statements above.

Good luck!

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

[Daniel]

thank you Jason..

"Daniel" wrote:

Hi All,
I have question regarding to the read, and update the row in the table.

for example:
user A, read the row of data from the table.
user B, read the row of data from the table as well.
user A, update the row of data. (lock)
user B, update the row of data. (fail due to user A lock)
user A, release the lock. (unlock)
At the user B side, the data auto refresh in the data grid table.
This time, user B update the row of data (lock).
Lastly, user B unlock the row of data. (unlock).

My question is:
how to implement this in the web form.
Any additional setting is needed in sql server 2000?
What is the additional code is needed to the coding page?
What is the additional T-SQL language is needed in our query? "? Insert..?
Select.. ? Update..? Delete..".

Any ideas? Any guidelines?
Your help will be appreciated.

Thank you in advance.

Best regards,
Daniel.