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

Local Report not changing back to original report

Hi
I have an aspx page which contains the report viewer control. I am using
this control in local processing mode.
Everything is working correctly except the following case
If I view a report (Report1) and then click on a hyperlink which drills down
to another report (Report2), the new report loads corectly and the
parameters are passed.
However, If I then select my generate report button on the form, it seems to
create the correct datasource and pass the right parameters, but instead of
displaying Report1 again, Report2 is still displayed and the data in the
report is incorrect.
I have dubugged the code and it all seems correct to me.When the generate
report button is clicked, the report path for Report1 is set, the new
collection of report parameters are created (which are correct) and the
datasources are setup.
The only thing that is not changing correctly is the report being rendered.
Here is some of my code
'btnGenerateReport_Click Method
rptReportViewer.LocalReport.ReportPath = MapPath("../Reports/" &
objReport.URL & ".rdlc")
rptReportViewer.LocalReport.DisplayName = objReport.Name
rptReportViewer.LocalReport.DataSources.Clear()
rptReportViewer.LocalReport.DataSources.Add(New
Microsoft.Reporting.WebForms.ReportDataSource("myDataset",
dsData.Tables(0)))
rptReportViewer.LocalReport.SetParameters(objReportParameters)
rptReportViewer.LocalReport.Refresh()
rptReportViewer.Visible = True
Is there anything else I need to do to refresh the report?
Any help would reeally be appreciated as I cannot find any help or examples
on how to do this.
Thanks
Lewis HolmesHi Lewis,
Thank you for your post.
This behavior is by design for the WebForm.ReportViewer control.
It is possible to change the DataSource of the localreport
It is not possible to change the report definition in the WebForms control
in LocalReport mode after it has been set once.
To work around the design limitation of the WebForms ReportViewer control,
you can remove the existing instance and create a new one which uses the
new report definition.
Here is a sample:
//Get the new report definition
StreamReader tr = new StreamReader( < your rdlc file path>);
string sDef = tr.ReadToEnd();
tr.Close();
//Create the new viewer and remove the old one
ControlCollection col = ReportViewer1.Parent.Controls;
int index = col.IndexOf(ReportViewer1);
ReportViewer newReportView = new ReportViewer();
col.AddAt(index, newReportView);
col.Remove(ReportViewer1);
newReportView.Visible = false;
newReportView.ProcessingMode = ProcessingMode.Local;
StringReader sr = new StringReader(sDef);
newReportView.LocalReport.LoadReportDefinition(sr);
newReportView.LocalReport.DataSources.Clear();
newReportView.LocalReport.DataSources.Add(new
Microsoft.Reporting.WebForms.ReportDataSource("DataSet1_DataTable1",
ObjectDataSource1));
newReportView.LocalReport.Refresh();
newReportView.Visible = true;
Hope this will be helpful!
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.