lock/ deadlock questions

I got 4 deadlocks in 15 minutes yesterday. The first three happened in a 1
second time frame and the last 1 happened 15 minutes later. From looking in
the SQL error log it what appear that they all got hosed by the same spid.
Ive been reading up on Locking this morning and how it affects Deadlocks and
have some questions. Im pretty clueless about Locking from what Im finding
out so please be gentle. According to BOL-Update Locks:
--start
Update Locks
Update (U) locks prevent a common form of deadlock. A typical update pattern
consists of a transaction reading a record, acquiring a shared (S) lock on
the resource (page or row), and then modifying the row, which requires lock
conversion to an exclusive (X) lock. If two transactions acquire shared-mode
locks on a resource and then attempt to update data concurrently, one
transaction attempts the lock conversion to an exclusive (X) lock. The
shared-mode-to-exclusive lock conversion must wait because the exclusive
lock for one transaction is not compatible with the shared-mode lock of the
other transaction; a lock wait occurs. The second transaction attempts to
acquire an exclusive (X) lock for its update. Because both transactions are
converting to exclusive (X) locks, and they are each waiting for the other
transaction to release its shared-mode lock, a deadlock occurs.
To avoid this potential deadlock problem, update (U) locks are used. Only
one transaction can obtain an update (U) lock to a resource at a time. If a
transaction modifies a resource, the update (U) lock is converted to an
exclusive (X) lock. Otherwise, the lock is converted to a shared-mode lock.
--finish
But then in Deadlocks, Avoiding it also says (Gauranteed Deadlock picture):
Transaction 1 Transaction 2
Begin Trans Begin Trans
Update Supplier Update Part
Update Part Update Supplier
Commit Trans Commit Trans
So my questions are:
Do the Begin Trans above actually mean having BEGIN TRANS inside your Proc?
Also, shouldnt these type of Deadlocks not occur since becuase of Update
Locks saving the day?
--
SQL2K SP3
TIA, ChrisROn Sat, 18 Dec 2004 08:26:53 -0800, "ChrisR" <bla@.noemail.com> wrote:
>But then in Deadlocks, Avoiding it also says (Gauranteed Deadlock picture):
>Transaction 1 Transaction 2
>Begin Trans Begin Trans
>Update Supplier Update Part
>Update Part Update Supplier
>Commit Trans Commit Trans
>So my questions are:
>Do the Begin Trans above actually mean having BEGIN TRANS inside your Proc?
>Also, shouldnt these type of Deadlocks not occur since becuase of Update
>Locks saving the day?
Yes, actually use BEGIN TRANS, that gives you a multi-statement atomic
transaction. Of course, multi-statement transactions are exactly what
get tangled most often in deadlocks, but you do want atomic
transactions, right?
The #1 way to get deadlocks is as illustrated - get the same things in
a different order. If you get them in the SAME order, you avoid
deadlocks. You may occassionally get them anyway from obscure effects
of larger statements that maybe get internally deadlocked over index
pages or other things that are normally invisible, but the rule is:
define a fixed order for getting multiple resources, and you eliminate
about 98% of deadlock problems.
These internal lock types are things you shouldn't ever have to worry
about, IMHO.
J.|||Are deadlocks possible without specifying a transaction?
> >Connection 1 Connection 2
> >Update Supplier Update Part
> >Update Part Update Supplier
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:esq8s0hlo06qr3e3nr4e9rv6688jg8f8c7@.4ax.com...
> On Sat, 18 Dec 2004 08:26:53 -0800, "ChrisR" <bla@.noemail.com> wrote:
> >But then in Deadlocks, Avoiding it also says (Gauranteed Deadlock
picture):
> >
> >Transaction 1 Transaction 2
> >Begin Trans Begin Trans
> >Update Supplier Update Part
> >Update Part Update Supplier
> >Commit Trans Commit Trans
> >
> >So my questions are:
> >
> >Do the Begin Trans above actually mean having BEGIN TRANS inside your
Proc?
> >Also, shouldnt these type of Deadlocks not occur since becuase of Update
> >Locks saving the day?
> Yes, actually use BEGIN TRANS, that gives you a multi-statement atomic
> transaction. Of course, multi-statement transactions are exactly what
> get tangled most often in deadlocks, but you do want atomic
> transactions, right?
> The #1 way to get deadlocks is as illustrated - get the same things in
> a different order. If you get them in the SAME order, you avoid
> deadlocks. You may occassionally get them anyway from obscure effects
> of larger statements that maybe get internally deadlocked over index
> pages or other things that are normally invisible, but the rule is:
> define a fixed order for getting multiple resources, and you eliminate
> about 98% of deadlock problems.
> These internal lock types are things you shouldn't ever have to worry
> about, IMHO.
> J.
>|||Yes - transactions can also be declared above the stored proc, either in
TSQL that calls the stored proc or by a client transaction monitor such as
COM+.
Regards,
Greg Linwood
SQL Server MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ueoyI6S5EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Are deadlocks possible without specifying a transaction?
>> >Connection 1 Connection 2
>> >Update Supplier Update Part
>> >Update Part Update Supplier
>
>
>
> "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> news:esq8s0hlo06qr3e3nr4e9rv6688jg8f8c7@.4ax.com...
>> On Sat, 18 Dec 2004 08:26:53 -0800, "ChrisR" <bla@.noemail.com> wrote:
>> >But then in Deadlocks, Avoiding it also says (Gauranteed Deadlock
> picture):
>> >
>> >Transaction 1 Transaction 2
>> >Begin Trans Begin Trans
>> >Update Supplier Update Part
>> >Update Part Update Supplier
>> >Commit Trans Commit Trans
>> >
>> >So my questions are:
>> >
>> >Do the Begin Trans above actually mean having BEGIN TRANS inside your
> Proc?
>> >Also, shouldnt these type of Deadlocks not occur since becuase of Update
>> >Locks saving the day?
>> Yes, actually use BEGIN TRANS, that gives you a multi-statement atomic
>> transaction. Of course, multi-statement transactions are exactly what
>> get tangled most often in deadlocks, but you do want atomic
>> transactions, right?
>> The #1 way to get deadlocks is as illustrated - get the same things in
>> a different order. If you get them in the SAME order, you avoid
>> deadlocks. You may occassionally get them anyway from obscure effects
>> of larger statements that maybe get internally deadlocked over index
>> pages or other things that are normally invisible, but the rule is:
>> define a fixed order for getting multiple resources, and you eliminate
>> about 98% of deadlock problems.
>> These internal lock types are things you shouldn't ever have to worry
>> about, IMHO.
>> J.
>|||I may have phrased my question wrong. Say these two guys aren't in a
transaction:
> >> >Connection 1 Connection 2
> >> >Update Supplier Update Part
> >> >Update Part Update Supplier
> >
Is it still possible to have a deadlock?
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:#gY2YAZ5EHA.4028@.TK2MSFTNGP15.phx.gbl...
> Yes - transactions can also be declared above the stored proc, either in
> TSQL that calls the stored proc or by a client transaction monitor such as
> COM+.
> Regards,
> Greg Linwood
> SQL Server MVP
> "ChrisR" <bla@.noemail.com> wrote in message
> news:ueoyI6S5EHA.2180@.TK2MSFTNGP12.phx.gbl...
> > Are deadlocks possible without specifying a transaction?
> >
> >> >Connection 1 Connection 2
> >> >Update Supplier Update Part
> >> >Update Part Update Supplier
> >
> >
> >
> >
> >
> >
> > "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> > news:esq8s0hlo06qr3e3nr4e9rv6688jg8f8c7@.4ax.com...
> >> On Sat, 18 Dec 2004 08:26:53 -0800, "ChrisR" <bla@.noemail.com> wrote:
> >> >But then in Deadlocks, Avoiding it also says (Gauranteed Deadlock
> > picture):
> >> >
> >> >Transaction 1 Transaction 2
> >> >Begin Trans Begin Trans
> >> >Update Supplier Update Part
> >> >Update Part Update Supplier
> >> >Commit Trans Commit Trans
> >> >
> >> >So my questions are:
> >> >
> >> >Do the Begin Trans above actually mean having BEGIN TRANS inside your
> > Proc?
> >> >Also, shouldnt these type of Deadlocks not occur since becuase of
Update
> >> >Locks saving the day?
> >>
> >> Yes, actually use BEGIN TRANS, that gives you a multi-statement atomic
> >> transaction. Of course, multi-statement transactions are exactly what
> >> get tangled most often in deadlocks, but you do want atomic
> >> transactions, right?
> >>
> >> The #1 way to get deadlocks is as illustrated - get the same things in
> >> a different order. If you get them in the SAME order, you avoid
> >> deadlocks. You may occassionally get them anyway from obscure effects
> >> of larger statements that maybe get internally deadlocked over index
> >> pages or other things that are normally invisible, but the rule is:
> >> define a fixed order for getting multiple resources, and you eliminate
> >> about 98% of deadlock problems.
> >>
> >> These internal lock types are things you shouldn't ever have to worry
> >> about, IMHO.
> >>
> >> J.
> >>
> >
> >
>|||On Sat, 18 Dec 2004 10:22:53 -0800, "ChrisR" <bla@.noemail.com> wrote:
>Are deadlocks possible without specifying a transaction?
>> >Connection 1 Connection 2
>> >Update Supplier Update Part
>> >Update Part Update Supplier
In theory, this should eliminate deadlock problems.
But note that instead, you have "race" problems, where first #1
updates the supplier, then #2 updates the part, then #1 tries to
update the part but doesn't know what #2 already updated it, yada
yada.
And on occassion there have been SQLServer bugs with parallelism that
caused deadlocks from even single statements, just to spice up your
life a little!
J.|||ChrisR wrote:
> I may have phrased my question wrong. Say these two guys aren't in a
> transaction:
>> Connection 1 Connection 2
>> Update Supplier Update Part
>> Update Part Update Supplier
> Is it still possible to have a deadlock?
>
See Autocommit Transactions in BOL. That should explain what you are
asking. From a tool like QA, the answer is no. You won't see a deadlock
since each statement is atomic. This assumes you have autocommit
transactions turned on (the default). If you have implicit transactions
on (see SET IMPLICIT_TRANSACTIONS in BOL), a transaction is
automatically started for you on the first statement. In that case, you
would have a deadlock possibility. If these were executed from an
application, it would depend on the properties of the connection and how
the library handles this case.
--
David Gugick
Imceda Software
www.imceda.com|||Also, don't confuse an UPDATE statement with an UPDATE LOCK. In the
example, the normal Share to Exclusive lock conversion will happen, if the
two statements were written thus, a deadlock could be avoided:
Connection 1 Connection 2
-- --
--
UPDATE Supplier WITH (UPDLOCK) UPDATE Part WITH (UPDLOCK)
UPDATE Part WITH(UPDLOCK) UPDATE Supplier WITH (UPDLOCK)
If you coded this update within a stored procedure, you could gain further
control by accessing the resources in the same order, thus, further reducing
the chances for deadlocking.
Sincerely,
Anthony Thomas
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eLNZbff5EHA.3376@.TK2MSFTNGP12.phx.gbl...
ChrisR wrote:
> I may have phrased my question wrong. Say these two guys aren't in a
> transaction:
>> Connection 1 Connection 2
>> Update Supplier Update Part
>> Update Part Update Supplier
> Is it still possible to have a deadlock?
>
See Autocommit Transactions in BOL. That should explain what you are
asking. From a tool like QA, the answer is no. You won't see a deadlock
since each statement is atomic. This assumes you have autocommit
transactions turned on (the default). If you have implicit transactions
on (see SET IMPLICIT_TRANSACTIONS in BOL), a transaction is
automatically started for you on the first statement. In that case, you
would have a deadlock possibility. If these were executed from an
application, it would depend on the properties of the connection and how
the library handles this case.
--
David Gugick
Imceda Software
www.imceda.com|||Conceptually no, but if you really want to get to the bottom of your
deadlock source, I suggest you trace the deadlock graph to the error log
usiny DBCC TRACEON (1204, 2605, -1) so we can give you a concise answer to
what's actually going on in your db when a deadlock occurs.
JXStern's given you some good advice - there are the wel documented deadlock
scenarios such as cyclical deadlocking (what you're describing), conversion
deadlocking (update lock escalation conflicts) but also other types which
occur deeper within the DB such as parallelism deadlocks. Examples of the
single statement deadlocking he referred to includes Update Supplier, where
Supplier has multiple indexes. The same update statement might update the
various indexes on the table in conflicting orders and cause an internal
deadlock. This happens and is fairly common. If you log & post your deadlock
trace graph, you'll get the full picture about what's going on when you
experience your deadlocks.
Regards,
Greg Linwood
SQL Server MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ugSJ4Ue5EHA.3616@.TK2MSFTNGP11.phx.gbl...
>I may have phrased my question wrong. Say these two guys aren't in a
> transaction:
>> >> >Connection 1 Connection 2
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >
> Is it still possible to have a deadlock?
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:#gY2YAZ5EHA.4028@.TK2MSFTNGP15.phx.gbl...
>> Yes - transactions can also be declared above the stored proc, either in
>> TSQL that calls the stored proc or by a client transaction monitor such
>> as
>> COM+.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "ChrisR" <bla@.noemail.com> wrote in message
>> news:ueoyI6S5EHA.2180@.TK2MSFTNGP12.phx.gbl...
>> > Are deadlocks possible without specifying a transaction?
>> >
>> >> >Connection 1 Connection 2
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >
>> >
>> >
>> >
>> >
>> >
>> > "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
>> > news:esq8s0hlo06qr3e3nr4e9rv6688jg8f8c7@.4ax.com...
>> >> On Sat, 18 Dec 2004 08:26:53 -0800, "ChrisR" <bla@.noemail.com> wrote:
>> >> >But then in Deadlocks, Avoiding it also says (Gauranteed Deadlock
>> > picture):
>> >> >
>> >> >Transaction 1 Transaction 2
>> >> >Begin Trans Begin Trans
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >> >Commit Trans Commit Trans
>> >> >
>> >> >So my questions are:
>> >> >
>> >> >Do the Begin Trans above actually mean having BEGIN TRANS inside your
>> > Proc?
>> >> >Also, shouldnt these type of Deadlocks not occur since becuase of
> Update
>> >> >Locks saving the day?
>> >>
>> >> Yes, actually use BEGIN TRANS, that gives you a multi-statement atomic
>> >> transaction. Of course, multi-statement transactions are exactly what
>> >> get tangled most often in deadlocks, but you do want atomic
>> >> transactions, right?
>> >>
>> >> The #1 way to get deadlocks is as illustrated - get the same things in
>> >> a different order. If you get them in the SAME order, you avoid
>> >> deadlocks. You may occassionally get them anyway from obscure effects
>> >> of larger statements that maybe get internally deadlocked over index
>> >> pages or other things that are normally invisible, but the rule is:
>> >> define a fixed order for getting multiple resources, and you eliminate
>> >> about 98% of deadlock problems.
>> >>
>> >> These internal lock types are things you shouldn't ever have to worry
>> >> about, IMHO.
>> >>
>> >> J.
>> >>
>> >
>> >
>>
>|||The send to error log trace flag is 3605 not 2605.
Sincerely,
Anthony Thomas
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uCZgY5o5EHA.2156@.TK2MSFTNGP10.phx.gbl...
Conceptually no, but if you really want to get to the bottom of your
deadlock source, I suggest you trace the deadlock graph to the error log
usiny DBCC TRACEON (1204, 2605, -1) so we can give you a concise answer to
what's actually going on in your db when a deadlock occurs.
JXStern's given you some good advice - there are the wel documented deadlock
scenarios such as cyclical deadlocking (what you're describing), conversion
deadlocking (update lock escalation conflicts) but also other types which
occur deeper within the DB such as parallelism deadlocks. Examples of the
single statement deadlocking he referred to includes Update Supplier, where
Supplier has multiple indexes. The same update statement might update the
various indexes on the table in conflicting orders and cause an internal
deadlock. This happens and is fairly common. If you log & post your deadlock
trace graph, you'll get the full picture about what's going on when you
experience your deadlocks.
Regards,
Greg Linwood
SQL Server MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ugSJ4Ue5EHA.3616@.TK2MSFTNGP11.phx.gbl...
>I may have phrased my question wrong. Say these two guys aren't in a
> transaction:
>> >> >Connection 1 Connection 2
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >
> Is it still possible to have a deadlock?
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:#gY2YAZ5EHA.4028@.TK2MSFTNGP15.phx.gbl...
>> Yes - transactions can also be declared above the stored proc, either in
>> TSQL that calls the stored proc or by a client transaction monitor such
>> as
>> COM+.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "ChrisR" <bla@.noemail.com> wrote in message
>> news:ueoyI6S5EHA.2180@.TK2MSFTNGP12.phx.gbl...
>> > Are deadlocks possible without specifying a transaction?
>> >
>> >> >Connection 1 Connection 2
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >
>> >
>> >
>> >
>> >
>> >
>> > "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
>> > news:esq8s0hlo06qr3e3nr4e9rv6688jg8f8c7@.4ax.com...
>> >> On Sat, 18 Dec 2004 08:26:53 -0800, "ChrisR" <bla@.noemail.com> wrote:
>> >> >But then in Deadlocks, Avoiding it also says (Gauranteed Deadlock
>> > picture):
>> >> >
>> >> >Transaction 1 Transaction 2
>> >> >Begin Trans Begin Trans
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >> >Commit Trans Commit Trans
>> >> >
>> >> >So my questions are:
>> >> >
>> >> >Do the Begin Trans above actually mean having BEGIN TRANS inside your
>> > Proc?
>> >> >Also, shouldnt these type of Deadlocks not occur since becuase of
> Update
>> >> >Locks saving the day?
>> >>
>> >> Yes, actually use BEGIN TRANS, that gives you a multi-statement atomic
>> >> transaction. Of course, multi-statement transactions are exactly what
>> >> get tangled most often in deadlocks, but you do want atomic
>> >> transactions, right?
>> >>
>> >> The #1 way to get deadlocks is as illustrated - get the same things in
>> >> a different order. If you get them in the SAME order, you avoid
>> >> deadlocks. You may occassionally get them anyway from obscure effects
>> >> of larger statements that maybe get internally deadlocked over index
>> >> pages or other things that are normally invisible, but the rule is:
>> >> define a fixed order for getting multiple resources, and you eliminate
>> >> about 98% of deadlock problems.
>> >>
>> >> These internal lock types are things you shouldn't ever have to worry
>> >> about, IMHO.
>> >>
>> >> J.
>> >>
>> >
>> >
>>
>|||Thanks for picking that typo up!
Cheers,
Greg Linwood
SQL Server MVP
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:ek9x4sp5EHA.2012@.TK2MSFTNGP15.phx.gbl...
> The send to error log trace flag is 3605 not 2605.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uCZgY5o5EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Conceptually no, but if you really want to get to the bottom of your
> deadlock source, I suggest you trace the deadlock graph to the error log
> usiny DBCC TRACEON (1204, 2605, -1) so we can give you a concise answer to
> what's actually going on in your db when a deadlock occurs.
> JXStern's given you some good advice - there are the wel documented
> deadlock
> scenarios such as cyclical deadlocking (what you're describing),
> conversion
> deadlocking (update lock escalation conflicts) but also other types which
> occur deeper within the DB such as parallelism deadlocks. Examples of the
> single statement deadlocking he referred to includes Update Supplier,
> where
> Supplier has multiple indexes. The same update statement might update the
> various indexes on the table in conflicting orders and cause an internal
> deadlock. This happens and is fairly common. If you log & post your
> deadlock
> trace graph, you'll get the full picture about what's going on when you
> experience your deadlocks.
> Regards,
> Greg Linwood
> SQL Server MVP
> "ChrisR" <bla@.noemail.com> wrote in message
> news:ugSJ4Ue5EHA.3616@.TK2MSFTNGP11.phx.gbl...
>>I may have phrased my question wrong. Say these two guys aren't in a
>> transaction:
>> >> >Connection 1 Connection 2
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >
>> Is it still possible to have a deadlock?
>>
>> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
>> news:#gY2YAZ5EHA.4028@.TK2MSFTNGP15.phx.gbl...
>> Yes - transactions can also be declared above the stored proc, either in
>> TSQL that calls the stored proc or by a client transaction monitor such
>> as
>> COM+.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "ChrisR" <bla@.noemail.com> wrote in message
>> news:ueoyI6S5EHA.2180@.TK2MSFTNGP12.phx.gbl...
>> > Are deadlocks possible without specifying a transaction?
>> >
>> >> >Connection 1 Connection 2
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >
>> >
>> >
>> >
>> >
>> >
>> > "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
>> > news:esq8s0hlo06qr3e3nr4e9rv6688jg8f8c7@.4ax.com...
>> >> On Sat, 18 Dec 2004 08:26:53 -0800, "ChrisR" <bla@.noemail.com> wrote:
>> >> >But then in Deadlocks, Avoiding it also says (Gauranteed Deadlock
>> > picture):
>> >> >
>> >> >Transaction 1 Transaction 2
>> >> >Begin Trans Begin Trans
>> >> >Update Supplier Update Part
>> >> >Update Part Update Supplier
>> >> >Commit Trans Commit Trans
>> >> >
>> >> >So my questions are:
>> >> >
>> >> >Do the Begin Trans above actually mean having BEGIN TRANS inside
>> >> >your
>> > Proc?
>> >> >Also, shouldnt these type of Deadlocks not occur since becuase of
>> Update
>> >> >Locks saving the day?
>> >>
>> >> Yes, actually use BEGIN TRANS, that gives you a multi-statement
>> >> atomic
>> >> transaction. Of course, multi-statement transactions are exactly
>> >> what
>> >> get tangled most often in deadlocks, but you do want atomic
>> >> transactions, right?
>> >>
>> >> The #1 way to get deadlocks is as illustrated - get the same things
>> >> in
>> >> a different order. If you get them in the SAME order, you avoid
>> >> deadlocks. You may occassionally get them anyway from obscure
>> >> effects
>> >> of larger statements that maybe get internally deadlocked over index
>> >> pages or other things that are normally invisible, but the rule is:
>> >> define a fixed order for getting multiple resources, and you
>> >> eliminate
>> >> about 98% of deadlock problems.
>> >>
>> >> These internal lock types are things you shouldn't ever have to worry
>> >> about, IMHO.
>> >>
>> >> J.
>> >>
>> >
>> >
>>
>>
>

Lock requests/sec

I have just played a little with the performance monitor on my SQL-server and tried to look at the lock request per second. I really don't know what an acceptable range for this is but the server is running a pretty advanced website with quite alot of transactions. With only a modest 20 users online I have an average of about 5500 locks per second, is this acceptable and what are limits I should look for? The server is a Win2k dual AMD 1,6Mhz 512MB SP4 with RAID1...I don't know if you can look at the number of locks and determine if hardware is adequate. One should look at the database design, queries and stored procedures that access and modify data. Look at the type of locks, are they shared or exclusive? Maybe 95% of the locks are shared and that is due to scanning or large datasets coming back. Maybe one mass update is aquiry exclusive locks and will escalate to a table lock.

You sholud look at the type of locks, locks/session and the queries aquiring the locks.|||I'm not really concerned with the hardware as it seemingly performs well, but I was wondering if this was a really high number of locks and if I should be concerned about it. And how do I issue exclusive locks for my inserts and updates?|||achorozy is absoolutely right, the number of locks is arbitrary, are you having deadlocks, blocking, table locks, etc are more important questions, not to repeat achorozy but these could be shared locks which could be just queries which is perfectly fine.

HTH

Localization problem

Hi
I have a report with a "Jump to report" navigation field to a second report
, some of the parameters to the second report are DateTime values, when my
Local Settings are setted to English (United States) the link works fine,
but when I change my Local Settings to Spanish I get a Type Mismatch
message for this DateTime parameters. The "Language" property of both
reports are "Default", and when I execute by my self the second report
specifying each parameter with the correct locale datetime format it works
fine.
Any idea?
Thanksthis could happend because of session/caching of the subreport.
Click "Refresh" button on the toolbar when viewing the subreport.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
news:uqK6c$2mEHA.404@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have a report with a "Jump to report" navigation field to a second
> report
> , some of the parameters to the second report are DateTime values, when my
> Local Settings are setted to English (United States) the link works fine,
> but when I change my Local Settings to Spanish I get a Type Mismatch
> message for this DateTime parameters. The "Language" property of both
> reports are "Default", and when I execute by my self the second report
> specifying each parameter with the correct locale datetime format it works
> fine.
> Any idea?
> Thanks
>|||Thanks for your answer, but I think that is not the problem.
Let me explain the problem again, I have a first report with two parameters,
"Start Date" and "End Date" that are used when the report is executed to get
some information from some DB for this time range, this information is
showed in a table where the first column values are links to other report
(not a subrerport), this second report also uses date range parameters and
its values should be the same of the first report parameters, this means
that also should be specified in the link.
So when I click the link to open the second report and I have Spanish or
other locale selected for my system regional options a type mismatch message
is showed instead of the second report:
"The value provided for the report parameter 'EndDate' is not valid for its
type. (rsReportParameterTypeMismatch)"
My intuition is that the problem is with the date format, the date format
for Spanish locales is 'dd/mm/yyyy' and for english locale 'mm/dd/yyyy' I
think that the second report is trying to build its date time parameters
with the English locale and here is where the exception is raised. E.g. set
a value greater than 12 as month.
When I execute the second report specifying the parameters values manually
with the correct locale date format the report is showed without problems.
When I see the "Language" property of both reports in design mode is set to
default, I suppose that means that the report should works with the system
locale settings and use the correct date formats.
Thanks
Mauricio
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:e8ju$c4mEHA.648@.tk2msftngp13.phx.gbl...
> this could happend because of session/caching of the subreport.
> Click "Refresh" button on the toolbar when viewing the subreport.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
> news:uqK6c$2mEHA.404@.TK2MSFTNGP12.phx.gbl...
> > Hi
> > I have a report with a "Jump to report" navigation field to a second
> > report
> > , some of the parameters to the second report are DateTime values, when
my
> > Local Settings are setted to English (United States) the link works
fine,
> > but when I change my Local Settings to Spanish I get a Type Mismatch
> > message for this DateTime parameters. The "Language" property of both
> > reports are "Default", and when I execute by my self the second report
> > specifying each parameter with the correct locale datetime format it
works
> > fine.
> > Any idea?
> > Thanks
> >
> >
>|||What are parameters values that you pass to second report? What do you put
into Parameter Value cell of Parameters dialog?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
news:%23HlTA0AnEHA.3196@.TK2MSFTNGP10.phx.gbl...
> Thanks for your answer, but I think that is not the problem.
>
> Let me explain the problem again, I have a first report with two
> parameters,
> "Start Date" and "End Date" that are used when the report is executed to
> get
> some information from some DB for this time range, this information is
> showed in a table where the first column values are links to other report
> (not a subrerport), this second report also uses date range parameters and
> its values should be the same of the first report parameters, this means
> that also should be specified in the link.
>
> So when I click the link to open the second report and I have Spanish or
> other locale selected for my system regional options a type mismatch
> message
> is showed instead of the second report:
>
> "The value provided for the report parameter 'EndDate' is not valid for
> its
> type. (rsReportParameterTypeMismatch)"
>
> My intuition is that the problem is with the date format, the date format
> for Spanish locales is 'dd/mm/yyyy' and for english locale 'mm/dd/yyyy' I
> think that the second report is trying to build its date time parameters
> with the English locale and here is where the exception is raised. E.g.
> set
> a value greater than 12 as month.
>
> When I execute the second report specifying the parameters values manually
> with the correct locale date format the report is showed without problems.
> When I see the "Language" property of both reports in design mode is set
> to
> default, I suppose that means that the report should works with the system
> locale settings and use the correct date formats.
>
> Thanks
> Mauricio
>
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:e8ju$c4mEHA.648@.tk2msftngp13.phx.gbl...
>> this could happend because of session/caching of the subreport.
>> Click "Refresh" button on the toolbar when viewing the subreport.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
>> news:uqK6c$2mEHA.404@.TK2MSFTNGP12.phx.gbl...
>> > Hi
>> > I have a report with a "Jump to report" navigation field to a second
>> > report
>> > , some of the parameters to the second report are DateTime values, when
> my
>> > Local Settings are setted to English (United States) the link works
> fine,
>> > but when I change my Local Settings to Spanish I get a Type Mismatch
>> > message for this DateTime parameters. The "Language" property of both
>> > reports are "Default", and when I execute by my self the second report
>> > specifying each parameter with the correct locale datetime format it
> works
>> > fine.
>> > Any idea?
>> > Thanks
>> >
>> >
>>
>|||The same values of the first report parameters:
Parameter Name Parameter Value
StartDate =Parameters!StartDate.Value
EndDate =Parameters!EndDate.Value
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:%23uc293FnEHA.3452@.TK2MSFTNGP15.phx.gbl...
> What are parameters values that you pass to second report? What do you put
> into Parameter Value cell of Parameters dialog?
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
> news:%23HlTA0AnEHA.3196@.TK2MSFTNGP10.phx.gbl...
> > Thanks for your answer, but I think that is not the problem.
> >
> >
> >
> > Let me explain the problem again, I have a first report with two
> > parameters,
> > "Start Date" and "End Date" that are used when the report is executed to
> > get
> > some information from some DB for this time range, this information is
> > showed in a table where the first column values are links to other
report
> > (not a subrerport), this second report also uses date range parameters
and
> > its values should be the same of the first report parameters, this means
> > that also should be specified in the link.
> >
> >
> >
> > So when I click the link to open the second report and I have Spanish or
> > other locale selected for my system regional options a type mismatch
> > message
> > is showed instead of the second report:
> >
> >
> >
> > "The value provided for the report parameter 'EndDate' is not valid for
> > its
> > type. (rsReportParameterTypeMismatch)"
> >
> >
> >
> > My intuition is that the problem is with the date format, the date
format
> > for Spanish locales is 'dd/mm/yyyy' and for english locale 'mm/dd/yyyy'
I
> > think that the second report is trying to build its date time parameters
> > with the English locale and here is where the exception is raised. E.g.
> > set
> > a value greater than 12 as month.
> >
> >
> >
> > When I execute the second report specifying the parameters values
manually
> > with the correct locale date format the report is showed without
problems.
> > When I see the "Language" property of both reports in design mode is set
> > to
> > default, I suppose that means that the report should works with the
system
> > locale settings and use the correct date formats.
> >
> >
> >
> > Thanks
> >
> > Mauricio
> >
> >
> >
> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > news:e8ju$c4mEHA.648@.tk2msftngp13.phx.gbl...
> >> this could happend because of session/caching of the subreport.
> >> Click "Refresh" button on the toolbar when viewing the subreport.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >>
> >> "Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
> >> news:uqK6c$2mEHA.404@.TK2MSFTNGP12.phx.gbl...
> >> > Hi
> >> > I have a report with a "Jump to report" navigation field to a second
> >> > report
> >> > , some of the parameters to the second report are DateTime values,
when
> > my
> >> > Local Settings are setted to English (United States) the link works
> > fine,
> >> > but when I change my Local Settings to Spanish I get a Type Mismatch
> >> > message for this DateTime parameters. The "Language" property of both
> >> > reports are "Default", and when I execute by my self the second
report
> >> > specifying each parameter with the correct locale datetime format it
> > works
> >> > fine.
> >> > Any idea?
> >> > Thanks
> >> >
> >> >
> >>
> >>
> >
> >
>|||I have having the very same problem. Did you ever solve this? And how
if you did?!
Thanks|||I have this problem as well, even though my report language is set to English
(United Kingdom), my Asp.Net app that calls the reporting service web service
is using en-GB as culture. It still thinks that my parameter End Date should
be in mm/dd/yyyy format and pukes when it sees something like 14/12/2004
(December 14, 2004) come over.
"Gearoid" wrote:
> I have having the very same problem. Did you ever solve this? And how
> if you did?!
> Thanks
>|||Adam,
Check the "Language Settings" for your browser.
Open IE, goto Tools->Internet Options->Click 'Languages...' button on the
General tab. Check that your language is English (United Kingdom)
I have got MSRS to work fine with UK English format dates... apart from
when reports are linked using 'Jump to' action.
Joe
"AdamB" wrote:
> I have this problem as well, even though my report language is set to English
> (United Kingdom), my Asp.Net app that calls the reporting service web service
> is using en-GB as culture. It still thinks that my parameter End Date should
> be in mm/dd/yyyy format and pukes when it sees something like 14/12/2004
> (December 14, 2004) come over.
> "Gearoid" wrote:
> > I have having the very same problem. Did you ever solve this? And how
> > if you did?!
> >
> > Thanks
> >
> >|||Try this and see if it works
Parameter Name Parameter Value
StartDate =Format(Parameters!StartDate.Value,
"dd-MMMM-yyyy")
EndDate =Format(Parameters!EndDate.Value,
"dd-MMMM-yyyy")
Cheers,
Harry
"Mauricio Cadima" wrote:
> The same values of the first report parameters:
> Parameter Name Parameter Value
> StartDate =Parameters!StartDate.Value
> EndDate =Parameters!EndDate.Value
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:%23uc293FnEHA.3452@.TK2MSFTNGP15.phx.gbl...
> > What are parameters values that you pass to second report? What do you put
> > into Parameter Value cell of Parameters dialog?
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
> > news:%23HlTA0AnEHA.3196@.TK2MSFTNGP10.phx.gbl...
> > > Thanks for your answer, but I think that is not the problem.
> > >
> > >
> > >
> > > Let me explain the problem again, I have a first report with two
> > > parameters,
> > > "Start Date" and "End Date" that are used when the report is executed to
> > > get
> > > some information from some DB for this time range, this information is
> > > showed in a table where the first column values are links to other
> report
> > > (not a subrerport), this second report also uses date range parameters
> and
> > > its values should be the same of the first report parameters, this means
> > > that also should be specified in the link.
> > >
> > >
> > >
> > > So when I click the link to open the second report and I have Spanish or
> > > other locale selected for my system regional options a type mismatch
> > > message
> > > is showed instead of the second report:
> > >
> > >
> > >
> > > "The value provided for the report parameter 'EndDate' is not valid for
> > > its
> > > type. (rsReportParameterTypeMismatch)"
> > >
> > >
> > >
> > > My intuition is that the problem is with the date format, the date
> format
> > > for Spanish locales is 'dd/mm/yyyy' and for english locale 'mm/dd/yyyy'
> I
> > > think that the second report is trying to build its date time parameters
> > > with the English locale and here is where the exception is raised. E.g.
> > > set
> > > a value greater than 12 as month.
> > >
> > >
> > >
> > > When I execute the second report specifying the parameters values
> manually
> > > with the correct locale date format the report is showed without
> problems.
> > > When I see the "Language" property of both reports in design mode is set
> > > to
> > > default, I suppose that means that the report should works with the
> system
> > > locale settings and use the correct date formats.
> > >
> > >
> > >
> > > Thanks
> > >
> > > Mauricio
> > >
> > >
> > >
> > > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > > news:e8ju$c4mEHA.648@.tk2msftngp13.phx.gbl...
> > >> this could happend because of session/caching of the subreport.
> > >> Click "Refresh" button on the toolbar when viewing the subreport.
> > >>
> > >> --
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > >>
> > >>
> > >> "Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
> > >> news:uqK6c$2mEHA.404@.TK2MSFTNGP12.phx.gbl...
> > >> > Hi
> > >> > I have a report with a "Jump to report" navigation field to a second
> > >> > report
> > >> > , some of the parameters to the second report are DateTime values,
> when
> > > my
> > >> > Local Settings are setted to English (United States) the link works
> > > fine,
> > >> > but when I change my Local Settings to Spanish I get a Type Mismatch
> > >> > message for this DateTime parameters. The "Language" property of both
> > >> > reports are "Default", and when I execute by my self the second
> report
> > >> > specifying each parameter with the correct locale datetime format it
> > > works
> > >> > fine.
> > >> > Any idea?
> > >> > Thanks
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>
>