sql local data: records

Showing posts with label records. Show all posts

Wednesday, March 28, 2012

LOCK REQUEST TIME OUT PERIOD EXCEEDED

Got a VB6 program creating records in an SQL 2000 DB. I have 5 client
machines creating 100,000 records each in the same table at the same time.
On a couple of machine I get the error "LOCK REQUEST TIME OUT PERIOD
EXCEEDED". This happened maybe 4 times. Would this be due to a network or
hardware limitation or is it a SQL DB factor maybe ?
The SQL server is only a P4 with 512 ram.
Clients machines vary greatly and run 2000pro and XPpro.
Thanks for any pointers.
Scott.It seems the table is locked by one process for a longer
time, and this has forced other process to throw the
error 1222.
The LOCK_TIMEOUT setting allows an application to set a
maximum time that a statement waits on a blocked
resource. When a statement has waited longer than the
LOCK_TIMEOUT setting, the blocked statement is canceled
automatically, and error message 1222 "Lock request time-
out period exceeded" is returned to the application.
I think if after every 1000 inserts, if you commit the
transaction, then the resource will not be locked for a
longer duration.
regds,
Shrikant Patil,
MCDBA
>--Original Message--
>Got a VB6 program creating records in an SQL 2000 DB. I
have 5 client
>machines creating 100,000 records each in the same table
at the same time.
>On a couple of machine I get the error "LOCK REQUEST
TIME OUT PERIOD
>EXCEEDED". This happened maybe 4 times. Would this be
due to a network or
>hardware limitation or is it a SQL DB factor maybe ?
>The SQL server is only a P4 with 512 ram.
>Clients machines vary greatly and run 2000pro and XPpro.
>Thanks for any pointers.
>Scott.
>
>.
>|||i see.
so the error was recived because one clinet/process was ready to commit 1000
records while another clinet/process was in the process of commiting - hence
the error.
the chances that a clinet machine would recive this with our software in
practice is very remotei guess as they would probablty never create that
many records at the same time to the same table. even if they did the RETRY
option seems to deal with it well anyway.
Is there other locking methods that can be employed rather than the 1000
batch one ?
Thanks for your time
Scott

Lock Records

I apologize for the cross post but I am using ADO.NET methods and such to
access SQL and I am thinking perhaps both sides will have valuable input for
this.
I have two tables one called QUOTEINFO and one called QUOTEITEMS. One
contains the specifics of the quote and the others contain the actual items
in the quote.
If someone calls up a quote I need to somehow find a way to prevent another
user from modifying the same quote while the other user has it.
I thought about an additional column such as "LOCKED" in QUOTEINFO that
would if the other user attempted to modify the record it would not allow
it. Changing the LOCKED value would be easy enough when they first enter. I
worry though the user may not communicate back to the database though that
they are not finished. (program crash and such.)
Also, once the locking and unlocking is set, I would like to find a way to
record the username of the person locking the quote so I can tell the other
user who is locked out who has locked the quote. That after the lock issue
is resolved will probably be pretty easy.
If it helps, I own the "The Guru's Guide To Transact-SQL" by Ken Henderson
and the ADO.NET book by Sceppa if you know of references within them that
may help.
Sorry to dump all of this on you guys. I just don't know how to handle this.
kelly
KellySELECT <column lists> FROM Table (UPDLOCK)
This lock hint will not block others from reading the data , it ensures
that data has not changed since you last read it
Also use BEGIN TRAN
COMMIT to wrap transactions
"scorpion53061" <scorpion_53061@.nospamhereyahoo.com> wrote in message
news:eAEaA7WUFHA.928@.TK2MSFTNGP15.phx.gbl...
> I apologize for the cross post but I am using ADO.NET methods and such to
> access SQL and I am thinking perhaps both sides will have valuable input
for
> this.
>
> I have two tables one called QUOTEINFO and one called QUOTEITEMS. One
> contains the specifics of the quote and the others contain the actual
items
> in the quote.
>
> If someone calls up a quote I need to somehow find a way to prevent
another
> user from modifying the same quote while the other user has it.
>
> I thought about an additional column such as "LOCKED" in QUOTEINFO that
> would if the other user attempted to modify the record it would not allow
> it. Changing the LOCKED value would be easy enough when they first enter.
I
> worry though the user may not communicate back to the database though that
> they are not finished. (program crash and such.)
>
> Also, once the locking and unlocking is set, I would like to find a way to
> record the username of the person locking the quote so I can tell the
other
> user who is locked out who has locked the quote. That after the lock issue
> is resolved will probably be pretty easy.
>
> If it helps, I own the "The Guru's Guide To Transact-SQL" by Ken Henderson
> and the ADO.NET book by Sceppa if you know of references within them that
> may help.
>
> Sorry to dump all of this on you guys. I just don't know how to handle
this.
>
> kelly
>
> Kelly
>|||Would you mind giving a little more detail on this on how to go about doing
this? What you are saying sounds promising.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urwb7AXUFHA.3544@.TK2MSFTNGP12.phx.gbl...
> SELECT <column lists> FROM Table (UPDLOCK)
> This lock hint will not block others from reading the data , it ensures
> that data has not changed since you last read it
> Also use BEGIN TRAN
> COMMIT to wrap transactions
>
> "scorpion53061" <scorpion_53061@.nospamhereyahoo.com> wrote in message
> news:eAEaA7WUFHA.928@.TK2MSFTNGP15.phx.gbl...
> for
> items
> another
> I
> other
> this.
>|||use a timestamp/rowversion field, retrieve this with the rest of the record,
when you update use the timestamp value in the where clause, if the record
has been changed by someone else the update fails ( you refresh the data and
present it to the user again for them to re-edit ), if the update works
no-one touched the record since the first user picked it up, thus you are
safe to update it. A timestamp/rowversion field will automatically populate
(on insert) and automatically change its value on update.
"scorpion53061" <scorpion_53061@.nospamhereyahoo.com> wrote in message
news:eAEaA7WUFHA.928@.TK2MSFTNGP15.phx.gbl...
>I apologize for the cross post but I am using ADO.NET methods and such to
>access SQL and I am thinking perhaps both sides will have valuable input
>for this.
>
> I have two tables one called QUOTEINFO and one called QUOTEITEMS. One
> contains the specifics of the quote and the others contain the actual
> items in the quote.
>
> If someone calls up a quote I need to somehow find a way to prevent
> another user from modifying the same quote while the other user has it.
>
> I thought about an additional column such as "LOCKED" in QUOTEINFO that
> would if the other user attempted to modify the record it would not allow
> it. Changing the LOCKED value would be easy enough when they first enter.
> I worry though the user may not communicate back to the database though
> that they are not finished. (program crash and such.)
>
> Also, once the locking and unlocking is set, I would like to find a way to
> record the username of the person locking the quote so I can tell the
> other user who is locked out who has locked the quote. That after the lock
> issue is resolved will probably be pretty easy.
>
> If it helps, I own the "The Guru's Guide To Transact-SQL" by Ken Henderson
> and the ADO.NET book by Sceppa if you know of references within them that
> may help.
>
> Sorry to dump all of this on you guys. I just don't know how to handle
> this.
>
> kelly
>
> Kelly
>|||Hi
Start with this article
http://www.sql-server-performance.c...sql_locking.asp
Well, try do soem searching on internet about such kind of issues
Also , you can add to the table a column with ROWVERSION (TIMESTAMP)
datatype
Now , when the user calls for the data save athe value of this column on the
client and compare the value with the value that in the database just
before the user wants to update the row. If the value on the client and a
value in the database are different raise the message that the row was
update by someone
I know you are looking for locks and lock by whom it is worth to take a look
on above example
"scorpion53061" <scorpion_53061@.nospamhereyahoo.com> wrote in message
news:uYlBoFXUFHA.4092@.TK2MSFTNGP12.phx.gbl...
> Would you mind giving a little more detail on this on how to go about
doing
> this? What you are saying sounds promising.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:urwb7AXUFHA.3544@.TK2MSFTNGP12.phx.gbl...
to
input
allow
enter.
that
>|||See if this helps:
Broadcasting Messages to Multiple Clients
http://msdn.microsoft.com/vbasic/us...net06082004.asp
AMB
"scorpion53061" wrote:

> I apologize for the cross post but I am using ADO.NET methods and such to
> access SQL and I am thinking perhaps both sides will have valuable input f
or
> this.
>
> I have two tables one called QUOTEINFO and one called QUOTEITEMS. One
> contains the specifics of the quote and the others contain the actual item
s
> in the quote.
>
> If someone calls up a quote I need to somehow find a way to prevent anothe
r
> user from modifying the same quote while the other user has it.
>
> I thought about an additional column such as "LOCKED" in QUOTEINFO that
> would if the other user attempted to modify the record it would not allow
> it. Changing the LOCKED value would be easy enough when they first enter.
I
> worry though the user may not communicate back to the database though that
> they are not finished. (program crash and such.)
>
> Also, once the locking and unlocking is set, I would like to find a way to
> record the username of the person locking the quote so I can tell the othe
r
> user who is locked out who has locked the quote. That after the lock issue
> is resolved will probably be pretty easy.
>
> If it helps, I own the "The Guru's Guide To Transact-SQL" by Ken Henderson
> and the ADO.NET book by Sceppa if you know of references within them that
> may help.
>
> Sorry to dump all of this on you guys. I just don't know how to handle thi
s.
>
> kelly
>
> Kelly
>
>|||Kelly,
What you want to do is pessimistic concurrency.
It is not standard in ADONET because that is the main difference between
connected and disconnected. Have a look in this newsgroup for it (not that
terrible much messages)
http://groups-beta.google.com/group...arch+this+group
When you real find it important, and it is ASPNET with what you are busy
with, than I would look as well (when you find all the methods proposed with
ADODB for that to difficult) for ADODB where it was more or less the
standard behaviour. Although I would avoid it as much as possible.
I hope this helps,
Cor|||What you are talking about is a pattern called Offline Optimistic Concurrenc
y
(Fowler). The best way is to maintain the offline nature of data retrieval (
get
data, disconnect) and instead check whether the data has changed since you l
ast
retrieved it (As Mark suggested). For this you can use something like timest
amps
or complicated updates statements where you check previously retrieved value
against the same values in the database.
If you need an indicator to the users that someone has the quote "checked ou
t"
ala a SCM program like SourceSafe. Then this will not work. For that I would
recommend using the Offline Pessimistic Lock (Fowler) pattern. In this
situation, you set a flag on the database when you "open" the quote. When
someone else reads that row, they get the data read only. The problem with t
his
scenario is that it gets complicated having to deal with "lock and walk away
"
scenario. The "lock and walk away" is more difficult in that it requires the
ability of an administrator to come in remove the lock and/or lock leases th
at
expire if not renewed after a certain time.
As you can see, this is a very complicated route to take and I would only ta
ke
if absolutely necessary. If there are ways to code around the possibility of
two
people working on the same quote, then do it. It will be easier and faster t
o
develop in the long run.
Thomas
"scorpion53061" <scorpion_53061@.nospamhereyahoo.com> wrote in message
news:eAEaA7WUFHA.928@.TK2MSFTNGP15.phx.gbl...
>I apologize for the cross post but I am using ADO.NET methods and such to
>access SQL and I am thinking perhaps both sides will have valuable input fo
r
>this.
>
> I have two tables one called QUOTEINFO and one called QUOTEITEMS. One cont
ains
> the specifics of the quote and the others contain the actual items in the
> quote.
>
> If someone calls up a quote I need to somehow find a way to prevent anothe
r
> user from modifying the same quote while the other user has it.
>
> I thought about an additional column such as "LOCKED" in QUOTEINFO that wo
uld
> if the other user attempted to modify the record it would not allow it.
> Changing the LOCKED value would be easy enough when they first enter. I wo
rry
> though the user may not communicate back to the database though that they
are
> not finished. (program crash and such.)
>
> Also, once the locking and unlocking is set, I would like to find a way to
> record the username of the person locking the quote so I can tell the othe
r
> user who is locked out who has locked the quote. That after the lock issue
is
> resolved will probably be pretty easy.
>
> If it helps, I own the "The Guru's Guide To Transact-SQL" by Ken Henderson
and
> the ADO.NET book by Sceppa if you know of references within them that may
> help.
>
> Sorry to dump all of this on you guys. I just don't know how to handle thi
s.
>
> kelly
>
> Kelly
>|||Kelly
Some corrections on what I wrote.

> When you real find it important, and it is ASPNET with what you are busy
> with, than I would look as well (when you find all the methods proposed
> with ADODB for that to difficult)
ADONET for that to difficult
It are not that terrible many messages

Monday, March 26, 2012

Lock problem when inserting and deleting records

Hi all.

I have an application that is using a SQL compact edition database to save/process information.

I run a test that is creating two threads:

1. one is inserting data in Table1

2. the other one is deleting records from Table1

When I run the application, I get some exceptions on both threads saying that the insert/delete could not aquire a lock on the table.

After a while, when I try to connect to the database I get an exception saying that the database file might be corrupted.

Any thoughts?

Thanks.

Hello,

Can you send me the database, and if possible, tell me (or give me) the app, so that, I can see what the issue is? Send it to goteti.udaya.bhanu@.gmail.com. And also, let me know which version of SQL CE are you using (3.1 or 3.0 etc...). Are you using ADO.NET or a native app to do this. Can you send me the code.

Thanks

Udaya.

|||

The db has a very simple structure:

2 tables with strings and datetimes as columns data types.

I am using SQLCE 3.1 version and ADO.NET. So, there is no native call from my code.

Thanks.

Mircea

|||

Hello,

I have tried to repro the issue, with SQLCE 3.1, but, could not. I need one more detail for that, I think. Which version of .NET are you using? Are you using .NET CF or .NET (on a device or on the desktop)?

Thanks

Udaya.

|||

If your both threads are doing DML operations on the same thread at the same time, there is a good chance that you experience locking issues. It could be that both operations need a lock on the same index page and one of them is denied lock for a long time (2000 msec or some thing like that). This looks okay to me. We are trying to improve the experience.

How to solve it? You might solve the above problem by either handling the error or serializing your actions. In the first approach you might want to just catch the error and retry the operation.

But why file corruption? This should not happen. Unless we look into your code, it's difficult to explain. Can you please try using different connection objects in these two threads?

Thanks

Raja [MSFT]

P.S: If this solves your problem, please mark it as answered.

Lock problem when inserting and deleting records

Hi all.

I have an application that is using a SQL compact edition database to save/process information.

I run a test that is creating two threads:

1. one is inserting data in Table1

2. the other one is deleting records from Table1

When I run the application, I get some exceptions on both threads saying that the insert/delete could not aquire a lock on the table.

After a while, when I try to connect to the database I get an exception saying that the database file might be corrupted.

Any thoughts?

Thanks.

Hello,

Thanks

Udaya.

|||

The db has a very simple structure:

2 tables with strings and datetimes as columns data types.

I am using SQLCE 3.1 version and ADO.NET. So, there is no native call from my code.

Thanks.

Mircea

|||

Hello,

Thanks

Udaya.

|||

How to solve it? You might solve the above problem by either handling the error or serializing your actions. In the first approach you might want to just catch the error and retry the operation.

But why file corruption? This should not happen. Unless we look into your code, it's difficult to explain. Can you please try using different connection objects in these two threads?

Thanks

Raja [MSFT]

P.S: If this solves your problem, please mark it as answered.

lock on a record?

Just wondering if there is a way to put a lock on a record? For example I
have the table below and a stored procedure reads the records in. Another
procedure increments the value in column 2 and then writes a new record with
this incremented value plus a bunch of other user interactions. The problem
is if a second user performs the operations before the first user is
finished, this incremented value gets thrown off. This is a .net web
application.
*******************************
* pri key * string * int * int *
*******************************
* 1 * abc001 * 3 * 6 *
* 2 * cde 002 * 5 * 9 *
user 1 gets cde 002 and with write back abc 003, note the increment.
user 2 also gets cde 002 as the latest record and also writes back abc 003.
this is incorrect. User 2 should write back abc 004.
If user 2 waited until user 1 was finish, this would take care of the proble
m.
Thanks,
--
Paul G
Software engineer.Errors in data concurrency will always be a issue for develoepr (especially
in WebApp as ong as the db-server will tell you that the row updated and you
have to reload the data, Like in SQL Server 2005)
Try reading this to be prepared for impementation.
http://msdn2.microsoft.com/library/y8fyz6xy(en-us,vs.80).aspx
http://msdn.microsoft.com/msdnmag/i.../09/DataPoints/
HTH, Jens Suessmeyer.
"Paul" <Paul@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A1112DF0-41C1-4DB7-8F8B-6AF601C877CB@.microsoft.com...
> Just wondering if there is a way to put a lock on a record? For example I
> have the table below and a stored procedure reads the records in. Another
> procedure increments the value in column 2 and then writes a new record
> with
> this incremented value plus a bunch of other user interactions. The
> problem
> is if a second user performs the operations before the first user is
> finished, this incremented value gets thrown off. This is a .net web
> application.
> *******************************
> * pri key * string * int * int *
> *******************************
> * 1 * abc001 * 3 * 6 *
> * 2 * cde 002 * 5 * 9 *
> user 1 gets cde 002 and with write back abc 003, note the increment.
> user 2 also gets cde 002 as the latest record and also writes back abc
> 003.
> this is incorrect. User 2 should write back abc 004.
> If user 2 waited until user 1 was finish, this would take care of the
> problem.
> Thanks,
> --
> Paul G
> Software engineer.|||Yep this does seem like it would be a common issue. Will take a look at the
article.
"Jens Sü?meyer" wrote:

> Errors in data concurrency will always be a issue for develoepr (especiall
y
> in WebApp as ong as the db-server will tell you that the row updated and y
ou
> have to reload the data, Like in SQL Server 2005)
> Try reading this to be prepared for impementation.
> http://msdn2.microsoft.com/library/y8fyz6xy(en-us,vs.80).aspx
> http://msdn.microsoft.com/msdnmag/i.../09/DataPoints/
>
> HTH, Jens Suessmeyer.
> "Paul" <Paul@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:A1112DF0-41C1-4DB7-8F8B-6AF601C877CB@.microsoft.com...
>
>sql

Friday, March 23, 2012

Lock escalation not working

I occassionally see one of the spid holding around 10-20 million locks in
the server. The spid is supposed to update around 1 to 2 records out of 90
million rows table and its well indexed. The spid is generated by
application server and usually there are 4-5 processes trying to
insert/update same table.
Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
server do lock escalation when it reaches certain threashold? Is it a bug?
As a result of this, I am getting error 1204, "cannot obtain lock resource
at this time". I am running SQL 2000 SP4
I appreicate your answer.If there are any other users with any shared or higher locks in that table
it can not escalate to a table lock. Since you say it is busy that sounds
like the case. But if it is taking out that many locks it is obviously not
doing what you think. My guess would be you have this in serializable mode.
Can you post the exact code for the UPDATE and the DDL for the table
including indexes. What does the estimated query plan look like?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>I occassionally see one of the spid holding around 10-20 million locks in
>the server. The spid is supposed to update around 1 to 2 records out of 90
>million rows table and its well indexed. The spid is generated by
>application server and usually there are 4-5 processes trying to
>insert/update same table.
> Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
> server do lock escalation when it reaches certain threashold? Is it a bug?
> As a result of this, I am getting error 1204, "cannot obtain lock resource
> at this time". I am running SQL 2000 SP4
> I appreicate your answer.
>|||Andrew,
Just to add one more point: The 20 million locks of mode 'U' is happening on
IDX_CA_KEY5. Which is not a good index for the searching for that update
statement.
So, although, when I saw the estimated execution plan it was using good
index which is IND_CASH_ACT_SPD1, May be in actual run on production
environment, optimizer its using wrong index once in while causing all
those millions locks.
Does that make sense? If so, Can we put index hint on update statement so
that sql server use right index to search for those records that needs to be
updated?
"James" <kush@.brandes.com> wrote in message
news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Thanks for the reply. Update statement and DDL for the table/index is
> attached. Estimated query looks good, using right indexes and returing
> expected number of rows for update.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that sounds
>> like the case. But if it is taking out that many locks it is obviously
>> not
>> doing what you think. My guess would be you have this in serializable
>> mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>I occassionally see one of the spid holding around 10-20 million locks in
>>the server. The spid is supposed to update around 1 to 2 records out of
>>90
>>million rows table and its well indexed. The spid is generated by
>>application server and usually there are 4-5 processes trying to
>>insert/update same table.
>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
>> server do lock escalation when it reaches certain threashold? Is it a
>> bug?
>> As a result of this, I am getting error 1204, "cannot obtain lock
>> resource
>> at this time". I am running SQL 2000 SP4
>> I appreicate your answer.
>>
>
>|||Well it isn't the expected number of rows that get updated that is the
factor here. It has more to do with how many it needs to look at to find
those rows. If 2 rows get updated but it has to scan an entire index to find
those 2 that is not good. Is this the index it is using?
IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
[long_short_indicator])
How many rows match those three columns for the values you are using in the
update? Are the values represented by variables or actual parameters to a
sp? Even though the estimated query plan looks good that does not mean that
is what was used when ran. You can have parameter sniffing happening here
and might be suffering from a bad query plan. But again this is only part
of the actual code so what isolation level are you running in during this
update? Are there other DML statements in the same transaction? How are
you calling this code?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Thanks for the reply. Update statement and DDL for the table/index is
> attached. Estimated query looks good, using right indexes and returing
> expected number of rows for update.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that sounds
>> like the case. But if it is taking out that many locks it is obviously
>> not
>> doing what you think. My guess would be you have this in serializable
>> mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>I occassionally see one of the spid holding around 10-20 million locks in
>>the server. The spid is supposed to update around 1 to 2 records out of
>>90
>>million rows table and its well indexed. The spid is generated by
>>application server and usually there are 4-5 processes trying to
>>insert/update same table.
>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't sql
>> server do lock escalation when it reaches certain threashold? Is it a
>> bug?
>> As a result of this, I am getting error 1204, "cannot obtain lock
>> resource
>> at this time". I am running SQL 2000 SP4
>> I appreicate your answer.
>>
>
>|||Andrew, ( I sent this earlier too but just resending it)
Just to add one more point: The 20 million locks of mode 'U' is happening on
IDX_CA_KEY5. Which is not a good index for the searching for that update
statement.
So, although, when I saw the estimated execution plan it was using good
index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
environment, optimizer its using wrong index once in while causing all
those millions locks.
Does that make sense? If so, I am planning to apply index hint to solve this
issuue.
Your comments are highly appreciated.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Well it isn't the expected number of rows that get updated that is the
> factor here. It has more to do with how many it needs to look at to find
> those rows. If 2 rows get updated but it has to scan an entire index to
> find those 2 that is not good. Is this the index it is using?
> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
> [long_short_indicator])
> How many rows match those three columns for the values you are using in
> the update? Are the values represented by variables or actual parameters
> to a sp? Even though the estimated query plan looks good that does not
> mean that is what was used when ran. You can have parameter sniffing
> happening here and might be suffering from a bad query plan. But again
> this is only part of the actual code so what isolation level are you
> running in during this update? Are there other DML statements in the same
> transaction? How are you calling this code?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the reply. Update statement and DDL for the table/index is
>> attached. Estimated query looks good, using right indexes and returing
>> expected number of rows for update.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that
>> sounds
>> like the case. But if it is taking out that many locks it is obviously
>> not
>> doing what you think. My guess would be you have this in serializable
>> mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>I occassionally see one of the spid holding around 10-20 million locks
>>in
>>the server. The spid is supposed to update around 1 to 2 records out of
>>90
>>million rows table and its well indexed. The spid is generated by
>>application server and usually there are 4-5 processes trying to
>>insert/update same table.
>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't
>> sql
>> server do lock escalation when it reaches certain threashold? Is it a
>> bug?
>> As a result of this, I am getting error 1204, "cannot obtain lock
>> resource
>> at this time". I am running SQL 2000 SP4
>> I appreicate your answer.
>>
>>
>|||OK that goes along with my original assumptions. For some reason it is
choosing the wrong plan at times. If you answer the rest of my questions
maybe it will help to narrow down the actual cause. But some things to
consider here. First is that you have two different WHERE clauses that may
require different plans of attack. I would create two sps, one for each of
those updates and call the appropriate one based on the parameters passed.
And if you find that the correct index for the update is always
IND_CASH_ACT_SPD1 you can add an index hint to force this to be the case.
But make sure it is always the correct way to deal with the updates.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James" <kush@.brandes.com> wrote in message
news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Andrew, ( I sent this earlier too but just resending it)
> Just to add one more point: The 20 million locks of mode 'U' is happening
> on
> IDX_CA_KEY5. Which is not a good index for the searching for that update
> statement.
> So, although, when I saw the estimated execution plan it was using good
> index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
> environment, optimizer its using wrong index once in while causing all
> those millions locks.
> Does that make sense? If so, I am planning to apply index hint to solve
> this issuue.
> Your comments are highly appreciated.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Well it isn't the expected number of rows that get updated that is the
>> factor here. It has more to do with how many it needs to look at to find
>> those rows. If 2 rows get updated but it has to scan an entire index to
>> find those 2 that is not good. Is this the index it is using?
>> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
>> [long_short_indicator])
>> How many rows match those three columns for the values you are using in
>> the update? Are the values represented by variables or actual parameters
>> to a sp? Even though the estimated query plan looks good that does not
>> mean that is what was used when ran. You can have parameter sniffing
>> happening here and might be suffering from a bad query plan. But again
>> this is only part of the actual code so what isolation level are you
>> running in during this update? Are there other DML statements in the
>> same transaction? How are you calling this code?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the reply. Update statement and DDL for the table/index is
>> attached. Estimated query looks good, using right indexes and returing
>> expected number of rows for update.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that
>> sounds
>> like the case. But if it is taking out that many locks it is obviously
>> not
>> doing what you think. My guess would be you have this in serializable
>> mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>I occassionally see one of the spid holding around 10-20 million locks
>>in
>>the server. The spid is supposed to update around 1 to 2 records out of
>>90
>>million rows table and its well indexed. The spid is generated by
>>application server and usually there are 4-5 processes trying to
>>insert/update same table.
>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't
>> sql
>> server do lock escalation when it reaches certain threashold? Is it a
>> bug?
>> As a result of this, I am getting error 1204, "cannot obtain lock
>> resource
>> at this time". I am running SQL 2000 SP4
>> I appreicate your answer.
>>
>>
>>
>|||Andrew,
Once again thanks for your time. I will reply to this thread once I
implemented the index hint and see if that will solve the issue or not.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
> OK that goes along with my original assumptions. For some reason it is
> choosing the wrong plan at times. If you answer the rest of my questions
> maybe it will help to narrow down the actual cause. But some things to
> consider here. First is that you have two different WHERE clauses that may
> require different plans of attack. I would create two sps, one for each of
> those updates and call the appropriate one based on the parameters passed.
> And if you find that the correct index for the update is always
> IND_CASH_ACT_SPD1 you can add an index hint to force this to be the case.
> But make sure it is always the correct way to deal with the updates.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James" <kush@.brandes.com> wrote in message
> news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Andrew, ( I sent this earlier too but just resending it)
>> Just to add one more point: The 20 million locks of mode 'U' is happening
>> on
>> IDX_CA_KEY5. Which is not a good index for the searching for that update
>> statement.
>> So, although, when I saw the estimated execution plan it was using good
>> index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
>> environment, optimizer its using wrong index once in while causing all
>> those millions locks.
>> Does that make sense? If so, I am planning to apply index hint to solve
>> this issuue.
>> Your comments are highly appreciated.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Well it isn't the expected number of rows that get updated that is the
>> factor here. It has more to do with how many it needs to look at to find
>> those rows. If 2 rows get updated but it has to scan an entire index to
>> find those 2 that is not good. Is this the index it is using?
>> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
>> [long_short_indicator])
>> How many rows match those three columns for the values you are using in
>> the update? Are the values represented by variables or actual parameters
>> to a sp? Even though the estimated query plan looks good that does not
>> mean that is what was used when ran. You can have parameter sniffing
>> happening here and might be suffering from a bad query plan. But again
>> this is only part of the actual code so what isolation level are you
>> running in during this update? Are there other DML statements in the
>> same transaction? How are you calling this code?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the reply. Update statement and DDL for the table/index is
>> attached. Estimated query looks good, using right indexes and returing
>> expected number of rows for update.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that
>> sounds
>> like the case. But if it is taking out that many locks it is
>> obviously not
>> doing what you think. My guess would be you have this in serializable
>> mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>I occassionally see one of the spid holding around 10-20 million locks
>>in
>>the server. The spid is supposed to update around 1 to 2 records out
>>of 90
>>million rows table and its well indexed. The spid is generated by
>>application server and usually there are 4-5 processes trying to
>>insert/update same table.
>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't
>> sql
>> server do lock escalation when it reaches certain threashold? Is it a
>> bug?
>> As a result of this, I am getting error 1204, "cannot obtain lock
>> resource
>> at this time". I am running SQL 2000 SP4
>> I appreicate your answer.
>>
>>
>>
>>
>|||You may also want to try using OPTIMIZE FOR instead of and index hint. This
will allow for two situations: 1) someone renames/drops the existing index
and 2) someone builds a better index for the query.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"James" <kush@.brandes.com> wrote in message
news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
> Andrew,
> Once again thanks for your time. I will reply to this thread once I
> implemented the index hint and see if that will solve the issue or not.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> OK that goes along with my original assumptions. For some reason it is
>> choosing the wrong plan at times. If you answer the rest of my questions
>> maybe it will help to narrow down the actual cause. But some things to
>> consider here. First is that you have two different WHERE clauses that
>> may require different plans of attack. I would create two sps, one for
>> each of those updates and call the appropriate one based on the
>> parameters passed. And if you find that the correct index for the update
>> is always IND_CASH_ACT_SPD1 you can add an index hint to force this to be
>> the case. But make sure it is always the correct way to deal with the
>> updates.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Andrew, ( I sent this earlier too but just resending it)
>> Just to add one more point: The 20 million locks of mode 'U' is
>> happening on
>> IDX_CA_KEY5. Which is not a good index for the searching for that update
>> statement.
>> So, although, when I saw the estimated execution plan it was using good
>> index which is IND_CASH_ACT_SPD1, Looks like in actual run on production
>> environment, optimizer its using wrong index once in while causing all
>> those millions locks.
>> Does that make sense? If so, I am planning to apply index hint to solve
>> this issuue.
>> Your comments are highly appreciated.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Well it isn't the expected number of rows that get updated that is the
>> factor here. It has more to do with how many it needs to look at to
>> find those rows. If 2 rows get updated but it has to scan an entire
>> index to find those 2 that is not good. Is this the index it is using?
>> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
>> [long_short_indicator])
>> How many rows match those three columns for the values you are using in
>> the update? Are the values represented by variables or actual
>> parameters to a sp? Even though the estimated query plan looks good
>> that does not mean that is what was used when ran. You can have
>> parameter sniffing happening here and might be suffering from a bad
>> query plan. But again this is only part of the actual code so what
>> isolation level are you running in during this update? Are there other
>> DML statements in the same transaction? How are you calling this code?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the reply. Update statement and DDL for the table/index is
>> attached. Estimated query looks good, using right indexes and returing
>> expected number of rows for update.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>> If there are any other users with any shared or higher locks in that
>> table
>> it can not escalate to a table lock. Since you say it is busy that
>> sounds
>> like the case. But if it is taking out that many locks it is
>> obviously not
>> doing what you think. My guess would be you have this in
>> serializable mode.
>> Can you post the exact code for the UPDATE and the DDL for the table
>> including indexes. What does the estimated query plan look like?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>>I occassionally see one of the spid holding around 10-20 million
>>>locks in
>>>the server. The spid is supposed to update around 1 to 2 records out
>>>of 90
>>>million rows table and its well indexed. The spid is generated by
>>>application server and usually there are 4-5 processes trying to
>>>insert/update same table.
>>>
>>> Has anyone seen a single spid holding 10-20 million locks? Shouldn't
>>> sql
>>> server do lock escalation when it reaches certain threashold? Is it
>>> a bug?
>>> As a result of this, I am getting error 1204, "cannot obtain lock
>>> resource
>>> at this time". I am running SQL 2000 SP4
>>>
>>> I appreicate your answer.
>>>
>>
>>
>>
>>
>|||Unfortunately he is running SQL2000 and can not go that route.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kui32a08dptc6@.corp.supernews.com...
> You may also want to try using OPTIMIZE FOR instead of and index hint.
> This will allow for two situations: 1) someone renames/drops the existing
> index and 2) someone builds a better index for the query.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "James" <kush@.brandes.com> wrote in message
> news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
>> Andrew,
>> Once again thanks for your time. I will reply to this thread once I
>> implemented the index hint and see if that will solve the issue or not.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> OK that goes along with my original assumptions. For some reason it is
>> choosing the wrong plan at times. If you answer the rest of my questions
>> maybe it will help to narrow down the actual cause. But some things to
>> consider here. First is that you have two different WHERE clauses that
>> may require different plans of attack. I would create two sps, one for
>> each of those updates and call the appropriate one based on the
>> parameters passed. And if you find that the correct index for the update
>> is always IND_CASH_ACT_SPD1 you can add an index hint to force this to
>> be the case. But make sure it is always the correct way to deal with the
>> updates.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Andrew, ( I sent this earlier too but just resending it)
>> Just to add one more point: The 20 million locks of mode 'U' is
>> happening on
>> IDX_CA_KEY5. Which is not a good index for the searching for that
>> update
>> statement.
>> So, although, when I saw the estimated execution plan it was using good
>> index which is IND_CASH_ACT_SPD1, Looks like in actual run on
>> production
>> environment, optimizer its using wrong index once in while causing all
>> those millions locks.
>> Does that make sense? If so, I am planning to apply index hint to solve
>> this issuue.
>> Your comments are highly appreciated.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Well it isn't the expected number of rows that get updated that is the
>> factor here. It has more to do with how many it needs to look at to
>> find those rows. If 2 rows get updated but it has to scan an entire
>> index to find those 2 that is not good. Is this the index it is using?
>> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
>> [long_short_indicator])
>> How many rows match those three columns for the values you are using
>> in the update? Are the values represented by variables or actual
>> parameters to a sp? Even though the estimated query plan looks good
>> that does not mean that is what was used when ran. You can have
>> parameter sniffing happening here and might be suffering from a bad
>> query plan. But again this is only part of the actual code so what
>> isolation level are you running in during this update? Are there
>> other DML statements in the same transaction? How are you calling
>> this code?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the reply. Update statement and DDL for the table/index is
>> attached. Estimated query looks good, using right indexes and
>> returing expected number of rows for update.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>>> If there are any other users with any shared or higher locks in that
>>> table
>>> it can not escalate to a table lock. Since you say it is busy that
>>> sounds
>>> like the case. But if it is taking out that many locks it is
>>> obviously not
>>> doing what you think. My guess would be you have this in
>>> serializable mode.
>>> Can you post the exact code for the UPDATE and the DDL for the table
>>> including indexes. What does the estimated query plan look like?
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>>
>>> "James" <kush@.brandes.com> wrote in message
>>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>>I occassionally see one of the spid holding around 10-20 million
>>>locks in
>>>the server. The spid is supposed to update around 1 to 2 records out
>>>of 90
>>>million rows table and its well indexed. The spid is generated by
>>>application server and usually there are 4-5 processes trying to
>>>insert/update same table.
>>>
>>> Has anyone seen a single spid holding 10-20 million locks?
>>> Shouldn't sql
>>> server do lock escalation when it reaches certain threashold? Is it
>>> a bug?
>>> As a result of this, I am getting error 1204, "cannot obtain lock
>>> resource
>>> at this time". I am running SQL 2000 SP4
>>>
>>> I appreicate your answer.
>>>
>>>
>>
>>
>>
>>
>|||Oopsie! Looks like I need to be more careful when reviewing the thread
prior to posting.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uvSzFHuMIHA.5040@.TK2MSFTNGP04.phx.gbl...
> Unfortunately he is running SQL2000 and can not go that route.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13kui32a08dptc6@.corp.supernews.com...
>> You may also want to try using OPTIMIZE FOR instead of and index hint.
>> This will allow for two situations: 1) someone renames/drops the
>> existing index and 2) someone builds a better index for the query.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:upMZPpsMIHA.4136@.TK2MSFTNGP03.phx.gbl...
>> Andrew,
>> Once again thanks for your time. I will reply to this thread once I
>> implemented the index hint and see if that will solve the issue or not.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23Qbf5vrMIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> OK that goes along with my original assumptions. For some reason it is
>> choosing the wrong plan at times. If you answer the rest of my
>> questions maybe it will help to narrow down the actual cause. But some
>> things to consider here. First is that you have two different WHERE
>> clauses that may require different plans of attack. I would create two
>> sps, one for each of those updates and call the appropriate one based
>> on the parameters passed. And if you find that the correct index for
>> the update is always IND_CASH_ACT_SPD1 you can add an index hint to
>> force this to be the case. But make sure it is always the correct way
>> to deal with the updates.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ue7%238krMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Andrew, ( I sent this earlier too but just resending it)
>> Just to add one more point: The 20 million locks of mode 'U' is
>> happening on
>> IDX_CA_KEY5. Which is not a good index for the searching for that
>> update
>> statement.
>> So, although, when I saw the estimated execution plan it was using
>> good
>> index which is IND_CASH_ACT_SPD1, Looks like in actual run on
>> production
>> environment, optimizer its using wrong index once in while causing
>> all
>> those millions locks.
>> Does that make sense? If so, I am planning to apply index hint to
>> solve this issuue.
>> Your comments are highly appreciated.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uai1HLrMIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Well it isn't the expected number of rows that get updated that is
>> the factor here. It has more to do with how many it needs to look at
>> to find those rows. If 2 rows get updated but it has to scan an
>> entire index to find those 2 that is not good. Is this the index it
>> is using?
>> IND_CASH_ACT_SPD1 ([POSITION_ID], [SECURITY_ALIAS],
>> [long_short_indicator])
>> How many rows match those three columns for the values you are using
>> in the update? Are the values represented by variables or actual
>> parameters to a sp? Even though the estimated query plan looks good
>> that does not mean that is what was used when ran. You can have
>> parameter sniffing happening here and might be suffering from a bad
>> query plan. But again this is only part of the actual code so what
>> isolation level are you running in during this update? Are there
>> other DML statements in the same transaction? How are you calling
>> this code?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "James" <kush@.brandes.com> wrote in message
>> news:ek$MZ4pMIHA.1208@.TK2MSFTNGP05.phx.gbl...
>>> Thanks for the reply. Update statement and DDL for the table/index
>>> is attached. Estimated query looks good, using right indexes and
>>> returing expected number of rows for update.
>>>
>>>
>>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>>> news:O70vRrpMIHA.5244@.TK2MSFTNGP03.phx.gbl...
>>> If there are any other users with any shared or higher locks in
>>> that table
>>> it can not escalate to a table lock. Since you say it is busy that
>>> sounds
>>> like the case. But if it is taking out that many locks it is
>>> obviously not
>>> doing what you think. My guess would be you have this in
>>> serializable mode.
>>> Can you post the exact code for the UPDATE and the DDL for the
>>> table
>>> including indexes. What does the estimated query plan look like?
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>>
>>> "James" <kush@.brandes.com> wrote in message
>>> news:ONenMepMIHA.292@.TK2MSFTNGP02.phx.gbl...
>>>I occassionally see one of the spid holding around 10-20 million
>>>locks in
>>>the server. The spid is supposed to update around 1 to 2 records
>>>out of 90
>>>million rows table and its well indexed. The spid is generated by
>>>application server and usually there are 4-5 processes trying to
>>>insert/update same table.
>>>
>>> Has anyone seen a single spid holding 10-20 million locks?
>>> Shouldn't sql
>>> server do lock escalation when it reaches certain threashold? Is
>>> it a bug?
>>> As a result of this, I am getting error 1204, "cannot obtain lock
>>> resource
>>> at this time". I am running SQL 2000 SP4
>>>
>>> I appreicate your answer.
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>

LOCK DB WHEN INSERTING

Hello.

I need to insert some records to an accounting table and calculate the balance after that. Thus, other users can be trying to do the same. How to lock the db and make the other users wait until the right moment? I'm using SqlDataSource to do that.

Thanks.

You don't need to lock the db, just lock the database objects (accounting table in this case). You can use SqlTransaction to control the locks in your code, with setting the IsolationLevel according to your requirement. Keep in mind that you should commit or rollback the opened transaction after you finish/cancel using the database resource, otherwise it may give rise to database blocking/deadlock issues. For example:

connection.Open();

SqlCommand command = connection.CreateCommand();
SqlTransaction transaction;

transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted,"SampleTransaction");

Here are some useful links:

SqlTransaction Class:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

Isolation levels:

http://msdn2.microsoft.com/en-us/library/system.data.isolationlevel.aspx

|||

Good.

I'm doing some research about that.

Assuming I'm using sqldatasource, the code below should work?

sqlReceiver.InsertCommand =

"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; " & insertCommand &"SET TRANSACTION ISOLATION LEVEL READ COMMITTED; "
sqlReceiver.Insert()

I couldn't find any property to use with sqldatasource for that purpose.

Thanks!

|||Good

Your sql command should work. Unfortunately it seems there is no property to set the isolation level in SqlDataSource.|||

I believe you can have the sqldatasouce's insert command join a transaction in _Inserting, and commit it in _Inserted.

Or maybe not. You should be able to catch the insert attempt in _Inserting, create your own connection, start a transaction, steal the inserting's command object (With the parameters already set up for you), then execute it, commit the transaction, and set "e.cancel=true" to cancel the original inserting request.

Or, you can put the whole thing in a stored procedure, and call it from the sqldatasource, that works too.

Or you can just wrap your original Insert statement like you did (Just put it in the sqldatasource's insertcommand property along with your insert statement). That works as well.

|||

Good, thanks for the answers.

I was monitoring the commands via Sql Server Profiler. I thought I would be able to see theSET TRANSACTION ISOLATION LEVEL SERIALIZABLEcommand being executed there, but I couldn't. That made me think that the command was not being executed.

Now, I'm using something like that to set the isolation level:

(...)
transaction = connection.BeginTransaction(System.Data.IsolationLevel.Serializable)

Any additional comments?

Thanks.

|||

Hey,

about my problem, I still have it. Let me explain.

I'm doing several SELECTS, to get the balances from different accounts. So, until I finish that and insert the new records, the table would be really locked. I couldn't do that using a transaction level isolation. Well, I guess I can do, but that would make me to rewrite lots of code.

So, it's possible to lock the entire table with one command, and unlock only after another command?

Thanks.

|||

sqldatasource1.selectcommand="BEGIN TRANSACTION SELECT TOP 0 * FROM Table1 WITH (TABLOCKX,HOLDLOCK) More SQL Statements COMMIT TRANSACTION"

With that said, you are probably coding your T-SQL poorly, and could do what you want to do in one or two statements. There are also a lot better ways of handling the locking than this as well. This will cause concurrency issues, and you will probably start hitting deadlock issues if you use this technique very much.

Monday, March 19, 2012

Locate records that meet ALL the requirements using IN or EXIST? Help?

Using SQL Server 2000...
I have a front end that provides the user with a 'search engine' to
pass search parameters. The stored procedure it calls joins multiple
tables/views to return the proper result.
I am running into a problem that I solved in the interface using code
(that takes WAY too long), but I know there must be a way to have SQL
Server do the work using T-SQL... as this seems a very simple issue.
The following is just a snippet of the pertinent information:
Assume I have a main Customer table with a unique CustID field.
I have another table of descriptive Flags with a unique FlagID field.
A third table Customer_Flag_Link has a unique ID field and contains 2
columns, the CustID and the FlagID.
Obvioulsy, the role of this 3rd table is to be able to assign multiple
Flags to each Customer.
Now assume I have the following data in the Customer_Flag_Link table:
UniqueID CustID FlagID
===============================
1 123 333
2 123 444
3 123 222
4 987 444
5 987 222
6 567 111
7 567 222
My issue is that I want to be able to locate Customers who have ALL of
the passed Flags associated with them, I do not know how many Flags
will be passed (and there are other search parameters passed as well -
but this is the piece that is giving me trouble, though I am sure it
is simple!)
For example, I want to return CustID where exists FlagID 222 AND 444.
Based on the above data it should return CustID 123 and 987, but not
567.
Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
this does not give me the results I want.
Any ideas would be appreciated!
Thanks,
AK
This might help.
SELECT COUNT(distinct FlagID)
FROM Customer_Flag_Link
WHERE FlagID IN (222,444)
AND Customer = 'Ralph'
HAVING COUNT(distinct FlagID) = 2
The number of items in the IN clause is what is used in the HAVING
test. Of course this could be written as an EXISTS subquery,
correlated on Customer.
Roy Harvey
Beacon Falls, CT
On Thu, 21 Jun 2007 10:50:45 -0700, aklein <abklein@.optonline.net>
wrote:

>Using SQL Server 2000...
>I have a front end that provides the user with a 'search engine' to
>pass search parameters. The stored procedure it calls joins multiple
>tables/views to return the proper result.
>I am running into a problem that I solved in the interface using code
>(that takes WAY too long), but I know there must be a way to have SQL
>Server do the work using T-SQL... as this seems a very simple issue.
>The following is just a snippet of the pertinent information:
>Assume I have a main Customer table with a unique CustID field.
>I have another table of descriptive Flags with a unique FlagID field.
>A third table Customer_Flag_Link has a unique ID field and contains 2
>columns, the CustID and the FlagID.
>Obvioulsy, the role of this 3rd table is to be able to assign multiple
>Flags to each Customer.
>Now assume I have the following data in the Customer_Flag_Link table:
>
>UniqueID CustID FlagID
>===============================
>1 123 333
>2 123 444
>3 123 222
>4 987 444
>5 987 222
>6 567 111
>7 567 222
>My issue is that I want to be able to locate Customers who have ALL of
>the passed Flags associated with them, I do not know how many Flags
>will be passed (and there are other search parameters passed as well -
>but this is the piece that is giving me trouble, though I am sure it
>is simple!)
>For example, I want to return CustID where exists FlagID 222 AND 444.
>Based on the above data it should return CustID 123 and 987, but not
>567.
>Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
>this does not give me the results I want.
>Any ideas would be appreciated!
>Thanks,
>AK
|||Hmmmm...
I have to see how to stick that idea into the larger procedure... but
it gives me a starting point.
Thanks for the idea!
AK

Locate records that meet ALL the requirements using IN or EXIST? Help?

Using SQL Server 2000...
I have a front end that provides the user with a 'search engine' to
pass search parameters. The stored procedure it calls joins multiple
tables/views to return the proper result.
I am running into a problem that I solved in the interface using code
(that takes WAY too long), but I know there must be a way to have SQL
Server do the work using T-SQL... as this seems a very simple issue.
The following is just a snippet of the pertinent information:
Assume I have a main Customer table with a unique CustID field.
I have another table of descriptive Flags with a unique FlagID field.
A third table Customer_Flag_Link has a unique ID field and contains 2
columns, the CustID and the FlagID.
Obvioulsy, the role of this 3rd table is to be able to assign multiple
Flags to each Customer.
Now assume I have the following data in the Customer_Flag_Link table:
UniqueID CustID FlagID
=============================== 1 123 333
2 123 444
3 123 222
4 987 444
5 987 222
6 567 111
7 567 222
My issue is that I want to be able to locate Customers who have ALL of
the passed Flags associated with them, I do not know how many Flags
will be passed (and there are other search parameters passed as well -
but this is the piece that is giving me trouble, though I am sure it
is simple!)
For example, I want to return CustID where exists FlagID 222 AND 444.
Based on the above data it should return CustID 123 and 987, but not
567.
Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
this does not give me the results I want.
Any ideas would be appreciated!
Thanks,
AKThis might help.
SELECT COUNT(distinct FlagID)
FROM Customer_Flag_Link
WHERE FlagID IN (222,444)
AND Customer = 'Ralph'
HAVING COUNT(distinct FlagID) = 2
The number of items in the IN clause is what is used in the HAVING
test. Of course this could be written as an EXISTS subquery,
correlated on Customer.
Roy Harvey
Beacon Falls, CT
On Thu, 21 Jun 2007 10:50:45 -0700, aklein <abklein@.optonline.net>
wrote:
>Using SQL Server 2000...
>I have a front end that provides the user with a 'search engine' to
>pass search parameters. The stored procedure it calls joins multiple
>tables/views to return the proper result.
>I am running into a problem that I solved in the interface using code
>(that takes WAY too long), but I know there must be a way to have SQL
>Server do the work using T-SQL... as this seems a very simple issue.
>The following is just a snippet of the pertinent information:
>Assume I have a main Customer table with a unique CustID field.
>I have another table of descriptive Flags with a unique FlagID field.
>A third table Customer_Flag_Link has a unique ID field and contains 2
>columns, the CustID and the FlagID.
>Obvioulsy, the role of this 3rd table is to be able to assign multiple
>Flags to each Customer.
>Now assume I have the following data in the Customer_Flag_Link table:
>
>UniqueID CustID FlagID
>===============================>1 123 333
>2 123 444
>3 123 222
>4 987 444
>5 987 222
>6 567 111
>7 567 222
>My issue is that I want to be able to locate Customers who have ALL of
>the passed Flags associated with them, I do not know how many Flags
>will be passed (and there are other search parameters passed as well -
>but this is the piece that is giving me trouble, though I am sure it
>is simple!)
>For example, I want to return CustID where exists FlagID 222 AND 444.
>Based on the above data it should return CustID 123 and 987, but not
>567.
>Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
>this does not give me the results I want.
>Any ideas would be appreciated!
>Thanks,
>AK|||Hmmmm...
I have to see how to stick that idea into the larger procedure... but
it gives me a starting point.
Thanks for the idea!
AK

Wednesday, March 28, 2012

LOCK REQUEST TIME OUT PERIOD EXCEEDED

Lock Records

Monday, March 26, 2012

Lock problem when inserting and deleting records

Lock problem when inserting and deleting records

lock on a record?

Friday, March 23, 2012

Lock escalation not working

Lock escalation not working

Lock escalation not working

LOCK DB WHEN INSERTING

Monday, March 19, 2012

Locate records that meet ALL the requirements using IN or EXIST? Help?

Locate records that meet ALL the requirements using IN or EXIST? Help?

sql local data

Blog Archive

About Me