Lock row.

Hello everyone,
I have a web project where users access a aspx page to view information stored in an SQL database.
My client want that one user can access a row of information and see it, allother users shouldn't be able to view or update thesame row?
it means whenever a row of data is displayed by some user, this row should be locked even for beeing viewed by all other users, when this user close this page, this row will be available. ?I should do this in code behind or something in sql...
How can I do that?

It is not easy to do but the link below will take you in the right direction. Hope this helps.

http://www.sql-server-performance.com/lock_contention_tamed_article.asp

|||

Thanks for your reply,
All I want to do is: if a userA access a row, all other users can't access this row even for reading.
I read the article but I didn't understand how can I do this.
Any help...

|||

No, not really. You'd have to roll your own method of locking out views when someone is viewing the same record.

Really, it sounds like a bad design.

|||

Goodway:

Thanks for your reply,
All I want to do is: if a userA access a row, all other users can't access this row even for reading.
I read the article but I didn't understand how can I do this.
Any help...

(UPDATE Users WITH (ROWLOCK)
SET Username = 'fred' WHERE Username = 'foobar'

By specifically requesting row-level locks, these problems are avoided.)

I got this from that link but I have told you it is not easy to lock access because as relational algebra expert Chris Date puts it a SELECT returns a table so you could be locking a table instead of one row through lock escalation managed by SQL Server. Hope this helps.

|||

That Update doesn't do what he asked for.

A) The WITH ROWLOCK really doesn't help. Sure, it (might) help with reducing contention by holding less granular locks, but... It doesn't hold the lock for any longer than the update statement takes to complete. That is unless you wrap it in a highly isolated transaction.

B) Transactions complete when the execution of the transaction variable is destroyed or the connection object is closed. This normally happens when the execution of the page completes. In order to avoid that (If it's even possible), you would need to stuff the transaction into either a session or application object so it doesn't go out of scope with the page finishes executing.

The workaround for B causes problems C,D and E.

C) Because the transaction (and locks) are now being held for LONG periods of time, you'll start having all kinds of performance and timeout problems within the database.

D) Memory usage within the webserver will skyrocket because of all the transaction/connection objects being held across postbacks. This will lead to additional scaling issues. Possibly consuming enough memory to trigger the .NET framework to recycle the application. Make sure to add plenty of memory to the webserver, and set the recycling threshold very high to avoid the locks being lost randomly when the system recycles the process.

E) Abandoned sessions will cause the record to be locked indefinately. What if the user loses power, or closes the browser? The server will continue to lock that record forever (Or until the session dies after a very long period of activity if you've stored the information in session, or until you recycle the application if you stored them in application). Sure the user can then log back in to the website, and he'll have to wait for the record to unlock itself before even he can do anything with the record.

The idea is flawed. Don't lock the record. Remember the original values, and when you go to update the record make sure the record still looks exactly the same prior to actually doing the update. The sql wizard will do this for you if you tell it to compare all values.

|||

(My client want that one user can access a row of information and see it, all other users shouldn't be able to view or update the same row?)

I was replying his original post and I understand he is trying to lock the viewing of scalar value well that is not something you could do without problems with RDBMS(relational database management systems).