468,121 Members | 1,523 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,121 developers. It's quick & easy.

Retreive, Update, Delete problem....

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.
Nov 19 '05 #1
4 1096
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/

Nov 19 '05 #2
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/

Nov 19 '05 #3
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/

Nov 19 '05 #4
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.

Nov 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Jason | last post: by
1 post views Thread by Jon | last post: by
17 posts views Thread by kalamos | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
16 posts views Thread by robert | last post: by
25 posts views Thread by Neo Geshel | last post: by
7 posts views Thread by Jon Maz | last post: by
18 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.