Locking

I have a Stored Procedure serving an web application that returns data from
a
number of tables.
This dat resides on three seperate tables, two of which I join in a single
select statement, the third I use to lookup data dependent on some data
retrieved from the original select statement.
On some infrequent occasions the user will receive an error message stating
they are the victim of a deadlock etc, etc.
I have checked the logs on SQL but find no error message pertaining to this.
I then created a script in QA that access this Stored procedure in a loop at
1000 and then 10000 times, but whenever I run it, I receive no error
messages, just the recordset(s) returned correctly.
The data being accessed is live and updateable 24/7 by other users. This
Stored Procedure does not moify the data in any shape or form, simply reads
it.
Another developer I work with has suggested using the NOLOCK hint on all
reads, but I am loathed to use this as it may cause dirty reads.
1. Does the scenario indicate this is truly a deadlock situation or could
there be another cause for this error message (the web app is in .NET)
2. What is the general point of view on using the NOLOCK hint and should it
be used as a matter of course or very sparingly?
I am obliged for any assistance or guidance in this matter
(Note: This question has also been posted on SQLTeam Site)> 1. Does the scenario indicate this is truly a deadlock situation or could
> there be another cause for this error message (the web app is in .NET)
Most probably. You didn't post the exact error message, but the way you phra
sed it sounds like the
error was indeed an SQL Server error message.

> 2. What is the general point of view on using the NOLOCK hint and should i
t
> be used as a matter of course or very sparingly?
Only use NOLOCK if you can foresee the consequences and accept them. But I s
uggest you look into
other possibilities first. These can include things like keeping locks for a
s short time as possible
(do not include things in the transaction that doesn't have to be there, tun
e queries etc),
accessing the objects in the same sequence, adding or removing indexes.
The reason you didn't see deadlocks in your loop is that you probably only h
ad one connection.
Deadlock (generally) occurs when you have two or more workers accessing data
in a way so that the
deadlock arises.
You can Google on deadlock (etc) and find plenty of text on the subject, inc
luding SQL server
specific text.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tony Scott" <TonyScott@.discussions.microsoft.com> wrote in message
news:D6FCCDA1-DE27-4A79-B801-CDC9B3038B92@.microsoft.com...
>I have a Stored Procedure serving an web application that returns data from
a
> number of tables.
> This dat resides on three seperate tables, two of which I join in a single
> select statement, the third I use to lookup data dependent on some data
> retrieved from the original select statement.
> On some infrequent occasions the user will receive an error message statin
g
> they are the victim of a deadlock etc, etc.
> I have checked the logs on SQL but find no error message pertaining to thi
s.
> I then created a script in QA that access this Stored procedure in a loop
at
> 1000 and then 10000 times, but whenever I run it, I receive no error
> messages, just the recordset(s) returned correctly.
> The data being accessed is live and updateable 24/7 by other users. This
> Stored Procedure does not moify the data in any shape or form, simply read
s
> it.
> Another developer I work with has suggested using the NOLOCK hint on all
> reads, but I am loathed to use this as it may cause dirty reads.
> 1. Does the scenario indicate this is truly a deadlock situation or could
> there be another cause for this error message (the web app is in .NET)
> 2. What is the general point of view on using the NOLOCK hint and should i
t
> be used as a matter of course or very sparingly?
> I am obliged for any assistance or guidance in this matter
> (Note: This question has also been posted on SQLTeam Site)|||The error message was thus...
Transaction (Process ID 99) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior
behavior) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data,
Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable
dataTable, IDbCommand command, CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at ...etc, etc
which strongly indicates that SQL generated it.
In respect of the loop test, I was attempting emulate the web application
when it requests the data to ascertain whether it was on the server or not.
I
recently read an artcle on MSDN that indicated deadlocking problems could be
caused by Applications opening dual threads that block each other - leading
to a deadlock situation - but it didn't state what error message would be
created in this case over and above the regular SQL 1204/1205 error.
Any further thoughts of where to look for this?
"Tibor Karaszi" wrote:

> Most probably. You didn't post the exact error message, but the way you ph
rased it sounds like the
> error was indeed an SQL Server error message.
>
> Only use NOLOCK if you can foresee the consequences and accept them. But I
suggest you look into
> other possibilities first. These can include things like keeping locks for
as short time as possible
> (do not include things in the transaction that doesn't have to be there, t
une queries etc),
> accessing the objects in the same sequence, adding or removing indexes.
> The reason you didn't see deadlocks in your loop is that you probably only
had one connection.
> Deadlock (generally) occurs when you have two or more workers accessing da
ta in a way so that the
> deadlock arises.
> You can Google on deadlock (etc) and find plenty of text on the subject, i
ncluding SQL server
> specific text.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Tony Scott" <TonyScott@.discussions.microsoft.com> wrote in message
> news:D6FCCDA1-DE27-4A79-B801-CDC9B3038B92@.microsoft.com...
>sql

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

Location of RDL files?

I'm unable to deploy using Visual Studio and would like to upload my files
manually. I've searched the web server for the existing files and can't find
them. I also searched books online. Any ideas?
ThanksNo rdl files exist on the server. They are all stored in the database.
Deploying a report is more than the report being copied over. To deploy from
outside of VS requires using scripting. Search Books Online for the work
scripting and then open up the samples link.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Leslie" <Leslie@.discussions.microsoft.com> wrote in message
news:58086A07-F1DD-40BE-B0D5-80D3F34891A8@.microsoft.com...
> I'm unable to deploy using Visual Studio and would like to upload my files
> manually. I've searched the web server for the existing files and can't
> find
> them. I also searched books online. Any ideas?
> Thanks|||Thanks for the response. I ftp'd the files over to my server and then
uploaded them using the Report Manager. A little easier than using scripting.
"Bruce L-C [MVP]" wrote:
> No rdl files exist on the server. They are all stored in the database.
> Deploying a report is more than the report being copied over. To deploy from
> outside of VS requires using scripting. Search Books Online for the work
> scripting and then open up the samples link.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Leslie" <Leslie@.discussions.microsoft.com> wrote in message
> news:58086A07-F1DD-40BE-B0D5-80D3F34891A8@.microsoft.com...
> > I'm unable to deploy using Visual Studio and would like to upload my files
> > manually. I've searched the web server for the existing files and can't
> > find
> > them. I also searched books online. Any ideas?
> >
> > Thanks
>
>|||Just an FYI, you can also use the Report Manager to retrieve the rdl if you
ever need to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Leslie" <Leslie@.discussions.microsoft.com> wrote in message
news:434F327E-9286-47BA-8B6E-6DB079E58461@.microsoft.com...
> Thanks for the response. I ftp'd the files over to my server and then
> uploaded them using the Report Manager. A little easier than using
> scripting.
> "Bruce L-C [MVP]" wrote:
>> No rdl files exist on the server. They are all stored in the database.
>> Deploying a report is more than the report being copied over. To deploy
>> from
>> outside of VS requires using scripting. Search Books Online for the work
>> scripting and then open up the samples link.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Leslie" <Leslie@.discussions.microsoft.com> wrote in message
>> news:58086A07-F1DD-40BE-B0D5-80D3F34891A8@.microsoft.com...
>> > I'm unable to deploy using Visual Studio and would like to upload my
>> > files
>> > manually. I've searched the web server for the existing files and can't
>> > find
>> > them. I also searched books online. Any ideas?
>> >
>> > Thanks
>>

Localization issue

Hi All,

I already have a web site running with SQL Server as a backend (in
english)For future growth, I would like to make it localized. Regarding
the database, I have come up with several approaches.

1) just simply add the column in those table which needs different
language.

2) add additional tables to do it.

3) create a new database to store different language's information

As mentioned, my database have already been implemented, so the minimum
modification is preferred. Could you guys suggest me the best way to do
it?

Another thing, if I alter my existing database into UTF-8 now, will it
affect the original data (ie. English).

Thanks.

Iceiceriver (hfung@.hotmail.com) writes:
> I already have a web site running with SQL Server as a backend (in
> english)For future growth, I would like to make it localized. Regarding
> the database, I have come up with several approaches.
> 1) just simply add the column in those table which needs different
> language.
> 2) add additional tables to do it.
> 3) create a new database to store different language's information
> As mentioned, my database have already been implemented, so the minimum
> modification is preferred. Could you guys suggest me the best way to do
> it?

Adding multi-language support is a task with an impact. Without knowing
the nature of your database it is hard to give recommendations.

In the system I work with, we once faced this problem. At that time,
many our tables had two name columns, for instance countryname and
countrynamefor, holding the Swedish and English name respectively. As
we entered the Finnish market, we needed support for a third language,
since in Finland, both Swedish and Finnish are official languages.

After some discussion, we decided to take the big step: the names were
moved out specific name tables. For instance the countries table
would get a subtable countrynames with the key (countrycode, languageid).
For simplicity we did keep a name column in the main table, so we
can use that as a fall back if there is no name in the current language
in the name table.

Adding an extra column may be easy for the first language you support,
but if you add specific tables, you have the infrastructre built for
your third, fourth language etc.

> Another thing, if I alter my existing database into UTF-8 now, will it
> affect the original data (ie. English).

There is no support for storing data in SQL Server as UTF-8. To store
Unicode data, use UCS-2. (The same as UTF-16, but SQL 2000 does not
support surrogates.) That is the nchar/nvarchar datatypes. Note that
depending on which languages you plan to support, you can still make it with
char/varchar.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

Thanks for your reply. I am wondering if there is any sample DB schema
available on the web. I would like to see an example that how several
related-tables also need to be localized. Any suggestion to re-arrange
those existing relationship? I am kinda new to localization problem,
please give me some hints.

"After some discussion, we decided to take the big step: the names were
moved out specific name tables. For instance the countries table
would get a subtable countrynames with the key (countrycode,
languageid).
For simplicity we did keep a name column in the main table, so we
can use that as a fall back if there is no name in the current language
in the name table."

Erland Sommarskog wrote:
> iceriver (hfung@.hotmail.com) writes:
> > I already have a web site running with SQL Server as a backend (in
> > english)For future growth, I would like to make it localized.
Regarding
> > the database, I have come up with several approaches.
> > 1) just simply add the column in those table which needs different
> > language.
> > 2) add additional tables to do it.
> > 3) create a new database to store different language's information
> > As mentioned, my database have already been implemented, so the
minimum
> > modification is preferred. Could you guys suggest me the best way
to do
> > it?
> Adding multi-language support is a task with an impact. Without
knowing
> the nature of your database it is hard to give recommendations.
> In the system I work with, we once faced this problem. At that time,
> many our tables had two name columns, for instance countryname and
> countrynamefor, holding the Swedish and English name respectively. As
> we entered the Finnish market, we needed support for a third
language,
> since in Finland, both Swedish and Finnish are official languages.
> After some discussion, we decided to take the big step: the names
were
> moved out specific name tables. For instance the countries table
> would get a subtable countrynames with the key (countrycode,
languageid).
> For simplicity we did keep a name column in the main table, so we
> can use that as a fall back if there is no name in the current
language
> in the name table.
> Adding an extra column may be easy for the first language you
support,
> but if you add specific tables, you have the infrastructre built for
> your third, fourth language etc.
> > Another thing, if I alter my existing database into UTF-8 now, will
it
> > affect the original data (ie. English).
> There is no support for storing data in SQL Server as UTF-8. To store
> Unicode data, use UCS-2. (The same as UTF-16, but SQL 2000 does not
> support surrogates.) That is the nchar/nvarchar datatypes. Note that
> depending on which languages you plan to support, you can still make
it with
> char/varchar.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||iceriver wrote:
> Erland,
> Thanks for your reply. I am wondering if there is any sample DB
schema
> available on the web. I would like to see an example that how several
> related-tables also need to be localized. Any suggestion to
re-arrange
> those existing relationship? I am kinda new to localization problem,
> please give me some hints.

Why change the database, other than just for the different language?
I'd suggest that keeping table and field names in the original language
would be simplest.
It's the user interface really needs to have local name of fields and
(perhaps) localised error messages.
How you'd do this best depends on what the user interface is
specifically written in.

One method...
Stick these in an xml file distributed with the app or a table in the
database with a structure something like
Language,
Message_No,
Message
Use something in the app to specify the language your user has and look
up the entry in the message field for each text box/error.
Potentially a fair bit of work. The last app I wrote is multi-national
but the company's standard language for computer systems is english for
this reason.

You also have to remember to handle different formatting of dates,
numbers ( comma or full stop as decimal place ).|||iceriver (hfung@.hotmail.com) writes:
> Thanks for your reply. I am wondering if there is any sample DB schema
> available on the web. I would like to see an example that how several
> related-tables also need to be localized. Any suggestion to re-arrange
> those existing relationship? I am kinda new to localization problem,
> please give me some hints.

I have no idea what is out there. But I have a strong feeling that the
answer depends on your business, and you are asking me question about a
database I don't know anything about. What was the right answer for us
may not be for you.

But there is one thing I forgot to mention in my prevoius reply: the
database is the easy part. The hard and expensive work is all the
translation. With a good architecture, you can easily add an other
language from a technical point of view. But the translation work will
be the same each time. And the maintenance of all languages will increase
for each new language you add.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Andy: Thanks, I also heard about the XML stuff may help, but I don't
know too much details in it.

Erland: Thanks, how a architecture and language addition will increase
the maintenance load?

If you guys encounter any good book/web reference regarding the
localization issue, please feel free to let me know. Thanks so much.|||Andy: Thanks, I also heard about the XML stuff may help, but I don't
know too much details in it.

Erland: Thanks, how a architecture and language addition will increase
the maintenance load?

If you guys encounter any good book/web reference regarding the
localization issue, please feel free to let me know. Thanks so much.|||iceriver (hfung@.hotmail.com) writes:
> Erland: Thanks, how a architecture and language addition will increase
> the maintenance load?

You don't localize once. As you change your site, you will have localize
all those changes as well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Local Report Confusion

Okay, so I'm working with a local report via the Web ReportViewer
control, and there is something going on that I don't quite get. I
created my object datasource and it was available to me via the "data
source" window when i created my RDLC report. All good here. Well along
the way, I have made changes to the class that represents my data
source object, and here is what I'm seeing being auto-inserted into my
ASPX file:
<rsweb:ReportViewer ID="ReportViewer1" runat="server" >
<LocalReport ReportPath="MyReport.rdlc">
<DataSources>
<rsweb:ReportDataSource DataSourceId="ObjectDataSource1"
Name="MyObject" />
<rsweb:ReportDataSource DataSourceId="ObjectDataSource1"
Name="MyObject1" />
<rsweb:ReportDataSource DataSourceId="ObjectDataSource1"
Name="MyObject2" />
<rsweb:ReportDataSource DataSourceId="ObjectDataSource1"
Name="MyObject3" />
<rsweb:ReportDataSource DataSourceId="ObjectDataSource1"
Name="MyObject4" />
</DataSources>
</LocalReport>
</rsweb:ReportViewer>
<asp:ObjectDataSource ID="ObjectDataSource2" runat="server"
SelectMethod="GetMyObjects" TypeName="Merchant"></asp:ObjectDataSource>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
SelectMethod="GetMyObjects" TypeName="Merchant"></asp:ObjectDataSource>
By the time the "MyObject3" I started wondering if there is some hidden
(or at least not obvious) versioning going on with my object data
class, in that each time I change it and rebuild it, it is given a new
name in the data source pane. What's really odd though, is that only
the most recent appears to be available (assuming that is what is going
on). So why do all these new <rsweb:ReportDataSource /> rows keep
appearing in my ASPX? Am I somehow creating multiple connections to my
datasource? Is this a result from each time I change the class that
represents my underlying data source object? Do I need to keep all
these entries, or can I dump all the old ones and just keep the newest
one? Finally, how did I end up with multiple "OjectDataSource" objects?
I started deleting them, and then having to repoint the reportviewer
control to an existing ObjectDataSource control.
If anyone can clarify what is going on, I would be very grateful.
Thanks!
ChrisQuick update: I see that changing my class isn't what causes the
insertion of new <rsweb:ReportDataSource /> items in my ASPX. Just now
I added a new table, and now when I run it I get the error "A data
source instance has not been supplied for the data source 'MyObject5'."
If you look at my previous post, there was no "MyObject5". Now when I
click on the smart tag for the reportviewer object in design view and
select "Choose Data Sources" I get the pop up grid that now asks me to
associate an ObjectDataSource with this new MyObject5. So again, in
this dialog, I have a column called "Report Data Source" and a column
called "Data Source Instance". Right now my grid (after associating
this latest one) looks something like this:
Report Data Source Data Source Instance
MyObject ObjectDataSource1
MyObject1 ObjectDataSource1
MyObject2 ObjectDataSource1
MyObject3 ObjectDataSource1
MyObject4 ObjectDataSource1
MyObject5 ObjectDataSource1
Thanks!

Local mode in asp.net web app

Dear all,
Can i use local mode of reporting service in asp.net web application?yes, if you use VS2005 you can create and use RDLC reports based on your own
datasets.
no server required.
abd there is a webcontrol in the toolbox to render the report
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:2EBBBA15-EC80-417E-9755-071685810970@.microsoft.com...
> Dear all,
> Can i use local mode of reporting service in asp.net web application?