Retreive, Update, Delete problem....

G

Guest

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.
 
J

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/
 
G

Guest

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.
 
J

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/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,898
Latest member
BlairH7607

Latest Threads

Top