Hello,
in EM (SQL7) i got the following errormessage, when i click on
current activity:
"Error: 1222 lock request time out period exceeded"
I run the sp_who /sp_who2, but nothing is blocked.
I set the lock_timeout th 5000, but it does not help.
Does anyone have an idea ?
best regards
Martinnetwork traffic is an idea
try to change for instance from tcp/ip to netbeui, or vice versa as the protocol you use to connect to the db server
see what happens.
also try to run a performance monitor to the db server.
check for cpu usage and latches.
if avg.latch wait time is high, ie. over 1000 and total latch wait time is also high i.e. over 100 for instance, then you probably experience sql server locks (watch for lock timeout /second)...
this can mean that the server is small, the app is poor, problem with your net h/w...
but this is some place to start with...
Cheers.|||Thanks for help Arounis,
network traffic is not possible, because i can log on to the server an got
the same error message.
For remember: I only get the error message, when i click on current activity in EM. No User told me that a problem still exits.
Checking avg.latch time and total latch time results:
520 constant for avg latch time
0 - 16 for total latch time.
You don't think that the problem occours about a "user - transaction" ?
Martin|||Try seeing if there is a locked resource which doesn't have a corresponding entry in sysprocesses
select *
from master..syslocks a
left join master..sysprocesses b on a.spid = b.spid
where b.spid is null
You shouldn't get any results.
Don't know what to tell you if you get any results.|||Thanks Wilso_s, but i get no results.
One more detail i detected, is that i can't access the
tempdb in EM. EM hangs up.
This short KB artikel Q308518 describes problems with
tempdb, but there is no workaround.
Maybe a restart will help ?
Thnx
Showing posts with label request. Show all posts
Showing posts with label request. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Lock Timeout
I am getting this error on a 2000 server:
"Server: Msg 1222, Level 16, State 54, Line 15
Lock request time out period exceeded."
when I run the following query
"SET ROWCOUNT 10000
declare @.counter bigint
-- Also try 5000, 10000 etc
set @.counter = 0
WHILE 1 = 1
BEGIN
set @.counter = @.counter + 1
print '@.counter = ' + cast(@.counter as varchar(10))
delete from dbname..tabname
where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0"
I checked in my Query Analyzer options and I've got lock timeout set to 120
seconds. But the above query fails instantly, so it's not even waiting the
120 seconds. What gives? Any tips would be much appreciated.
Nevermind. I figured it out.
"CLM" wrote:
> I am getting this error on a 2000 server:
> "Server: Msg 1222, Level 16, State 54, Line 15
> Lock request time out period exceeded."
> when I run the following query
> "SET ROWCOUNT 10000
> declare @.counter bigint
> -- Also try 5000, 10000 etc
> set @.counter = 0
> WHILE 1 = 1
> BEGIN
> set @.counter = @.counter + 1
> print '@.counter = ' + cast(@.counter as varchar(10))
> delete from dbname..tabname
> where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0"
> I checked in my Query Analyzer options and I've got lock timeout set to 120
> seconds. But the above query fails instantly, so it's not even waiting the
> 120 seconds. What gives? Any tips would be much appreciated.
>
"Server: Msg 1222, Level 16, State 54, Line 15
Lock request time out period exceeded."
when I run the following query
"SET ROWCOUNT 10000
declare @.counter bigint
-- Also try 5000, 10000 etc
set @.counter = 0
WHILE 1 = 1
BEGIN
set @.counter = @.counter + 1
print '@.counter = ' + cast(@.counter as varchar(10))
delete from dbname..tabname
where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0"
I checked in my Query Analyzer options and I've got lock timeout set to 120
seconds. But the above query fails instantly, so it's not even waiting the
120 seconds. What gives? Any tips would be much appreciated.
Nevermind. I figured it out.
"CLM" wrote:
> I am getting this error on a 2000 server:
> "Server: Msg 1222, Level 16, State 54, Line 15
> Lock request time out period exceeded."
> when I run the following query
> "SET ROWCOUNT 10000
> declare @.counter bigint
> -- Also try 5000, 10000 etc
> set @.counter = 0
> WHILE 1 = 1
> BEGIN
> set @.counter = @.counter + 1
> print '@.counter = ' + cast(@.counter as varchar(10))
> delete from dbname..tabname
> where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0"
> I checked in my Query Analyzer options and I've got lock timeout set to 120
> seconds. But the above query fails instantly, so it's not even waiting the
> 120 seconds. What gives? Any tips would be much appreciated.
>
Lock Timeout
I am getting this error on a 2000 server:
"Server: Msg 1222, Level 16, State 54, Line 15
Lock request time out period exceeded."
when I run the following query
"SET ROWCOUNT 10000
declare @.counter bigint
-- Also try 5000, 10000 etc
set @.counter = 0
WHILE 1 = 1
BEGIN
set @.counter = @.counter + 1
print '@.counter = ' + cast(@.counter as varchar(10))
delete from dbname..tabname
where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0"
I checked in my Query Analyzer options and I've got lock timeout set to 120
seconds. But the above query fails instantly, so it's not even waiting the
120 seconds. What gives? Any tips would be much appreciated.Nevermind. I figured it out.
"CLM" wrote:
> I am getting this error on a 2000 server:
> "Server: Msg 1222, Level 16, State 54, Line 15
> Lock request time out period exceeded."
> when I run the following query
> "SET ROWCOUNT 10000
> declare @.counter bigint
> -- Also try 5000, 10000 etc
> set @.counter = 0
> WHILE 1 = 1
> BEGIN
> set @.counter = @.counter + 1
> print '@.counter = ' + cast(@.counter as varchar(10))
> delete from dbname..tabname
> where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0"
> I checked in my Query Analyzer options and I've got lock timeout set to 12
0
> seconds. But the above query fails instantly, so it's not even waiting th
e
> 120 seconds. What gives? Any tips would be much appreciated.
>
"Server: Msg 1222, Level 16, State 54, Line 15
Lock request time out period exceeded."
when I run the following query
"SET ROWCOUNT 10000
declare @.counter bigint
-- Also try 5000, 10000 etc
set @.counter = 0
WHILE 1 = 1
BEGIN
set @.counter = @.counter + 1
print '@.counter = ' + cast(@.counter as varchar(10))
delete from dbname..tabname
where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0"
I checked in my Query Analyzer options and I've got lock timeout set to 120
seconds. But the above query fails instantly, so it's not even waiting the
120 seconds. What gives? Any tips would be much appreciated.Nevermind. I figured it out.
"CLM" wrote:
> I am getting this error on a 2000 server:
> "Server: Msg 1222, Level 16, State 54, Line 15
> Lock request time out period exceeded."
> when I run the following query
> "SET ROWCOUNT 10000
> declare @.counter bigint
> -- Also try 5000, 10000 etc
> set @.counter = 0
> WHILE 1 = 1
> BEGIN
> set @.counter = @.counter + 1
> print '@.counter = ' + cast(@.counter as varchar(10))
> delete from dbname..tabname
> where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0"
> I checked in my Query Analyzer options and I've got lock timeout set to 12
0
> seconds. But the above query fails instantly, so it's not even waiting th
e
> 120 seconds. What gives? Any tips would be much appreciated.
>
Lock Timeout
I am getting this error on a 2000 server:
"Server: Msg 1222, Level 16, State 54, Line 15
Lock request time out period exceeded."
when I run the following query
"SET ROWCOUNT 10000
declare @.counter bigint
-- Also try 5000, 10000 etc
set @.counter = 0
WHILE 1 = 1
BEGIN
set @.counter = @.counter + 1
print '@.counter = ' + cast(@.counter as varchar(10))
delete from dbname..tabname
where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0"
I checked in my Query Analyzer options and I've got lock timeout set to 120
seconds. But the above query fails instantly, so it's not even waiting the
120 seconds. What gives? Any tips would be much appreciated.Nevermind. I figured it out.
"CLM" wrote:
> I am getting this error on a 2000 server:
> "Server: Msg 1222, Level 16, State 54, Line 15
> Lock request time out period exceeded."
> when I run the following query
> "SET ROWCOUNT 10000
> declare @.counter bigint
> -- Also try 5000, 10000 etc
> set @.counter = 0
> WHILE 1 = 1
> BEGIN
> set @.counter = @.counter + 1
> print '@.counter = ' + cast(@.counter as varchar(10))
> delete from dbname..tabname
> where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0"
> I checked in my Query Analyzer options and I've got lock timeout set to 120
> seconds. But the above query fails instantly, so it's not even waiting the
> 120 seconds. What gives? Any tips would be much appreciated.
>
"Server: Msg 1222, Level 16, State 54, Line 15
Lock request time out period exceeded."
when I run the following query
"SET ROWCOUNT 10000
declare @.counter bigint
-- Also try 5000, 10000 etc
set @.counter = 0
WHILE 1 = 1
BEGIN
set @.counter = @.counter + 1
print '@.counter = ' + cast(@.counter as varchar(10))
delete from dbname..tabname
where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0"
I checked in my Query Analyzer options and I've got lock timeout set to 120
seconds. But the above query fails instantly, so it's not even waiting the
120 seconds. What gives? Any tips would be much appreciated.Nevermind. I figured it out.
"CLM" wrote:
> I am getting this error on a 2000 server:
> "Server: Msg 1222, Level 16, State 54, Line 15
> Lock request time out period exceeded."
> when I run the following query
> "SET ROWCOUNT 10000
> declare @.counter bigint
> -- Also try 5000, 10000 etc
> set @.counter = 0
> WHILE 1 = 1
> BEGIN
> set @.counter = @.counter + 1
> print '@.counter = ' + cast(@.counter as varchar(10))
> delete from dbname..tabname
> where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0"
> I checked in my Query Analyzer options and I've got lock timeout set to 120
> seconds. But the above query fails instantly, so it's not even waiting the
> 120 seconds. What gives? Any tips would be much appreciated.
>
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
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
Lock request time out period exceeded
Hi,
What is the default for @.@.LOCK_TIMEOUT?
When it is not set, how long it is?
Thanks,
JanosThe default is -1 which is it won't timeout, it will wait
forever. See the books online topic: SET LOCK_TIMEOUT
for more information.
-Sue
On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
<jhoranszky@.chartelltechnology.com> wrote:
>Hi,
>What is the default for @.@.LOCK_TIMEOUT?
>When it is not set, how long it is?
>Thanks,
>Janos
>|||Thanks Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:6gf0n1dh5lfvos0ua3ic5sdg08vnsmja27@.4ax.com...
> The default is -1 which is it won't timeout, it will wait
> forever. See the books online topic: SET LOCK_TIMEOUT
> for more information.
> -Sue
> On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
> <jhoranszky@.chartelltechnology.com> wrote:
>>Hi,
>>What is the default for @.@.LOCK_TIMEOUT?
>>When it is not set, how long it is?
>>Thanks,
>>Janos
>sql
What is the default for @.@.LOCK_TIMEOUT?
When it is not set, how long it is?
Thanks,
JanosThe default is -1 which is it won't timeout, it will wait
forever. See the books online topic: SET LOCK_TIMEOUT
for more information.
-Sue
On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
<jhoranszky@.chartelltechnology.com> wrote:
>Hi,
>What is the default for @.@.LOCK_TIMEOUT?
>When it is not set, how long it is?
>Thanks,
>Janos
>|||Thanks Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:6gf0n1dh5lfvos0ua3ic5sdg08vnsmja27@.4ax.com...
> The default is -1 which is it won't timeout, it will wait
> forever. See the books online topic: SET LOCK_TIMEOUT
> for more information.
> -Sue
> On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
> <jhoranszky@.chartelltechnology.com> wrote:
>>Hi,
>>What is the default for @.@.LOCK_TIMEOUT?
>>When it is not set, how long it is?
>>Thanks,
>>Janos
>sql
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
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 request time out period exceeded
Hi,
What is the default for @.@.LOCK_TIMEOUT?
When it is not set, how long it is?
Thanks,
JanosThe default is -1 which is it won't timeout, it will wait
forever. See the books online topic: SET LOCK_TIMEOUT
for more information.
-Sue
On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
<jhoranszky@.chartelltechnology.com> wrote:
>Hi,
>What is the default for @.@.LOCK_TIMEOUT?
>When it is not set, how long it is?
>Thanks,
>Janos
>|||Thanks Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:6gf0n1dh5lfvos0ua3ic5sdg08vnsmja27@.
4ax.com...
> The default is -1 which is it won't timeout, it will wait
> forever. See the books online topic: SET LOCK_TIMEOUT
> for more information.
> -Sue
> On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
> <jhoranszky@.chartelltechnology.com> wrote:
>
>
What is the default for @.@.LOCK_TIMEOUT?
When it is not set, how long it is?
Thanks,
JanosThe default is -1 which is it won't timeout, it will wait
forever. See the books online topic: SET LOCK_TIMEOUT
for more information.
-Sue
On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
<jhoranszky@.chartelltechnology.com> wrote:
>Hi,
>What is the default for @.@.LOCK_TIMEOUT?
>When it is not set, how long it is?
>Thanks,
>Janos
>|||Thanks Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:6gf0n1dh5lfvos0ua3ic5sdg08vnsmja27@.
4ax.com...
> The default is -1 which is it won't timeout, it will wait
> forever. See the books online topic: SET LOCK_TIMEOUT
> for more information.
> -Sue
> On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
> <jhoranszky@.chartelltechnology.com> wrote:
>
>
Lock request time out period exceeded
Hi,
What is the default for @.@.LOCK_TIMEOUT?
When it is not set, how long it is?
Thanks,
Janos
The default is -1 which is it won't timeout, it will wait
forever. See the books online topic: SET LOCK_TIMEOUT
for more information.
-Sue
On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
<jhoranszky@.chartelltechnology.com> wrote:
>Hi,
>What is the default for @.@.LOCK_TIMEOUT?
>When it is not set, how long it is?
>Thanks,
>Janos
>
|||Thanks Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:6gf0n1dh5lfvos0ua3ic5sdg08vnsmja27@.4ax.com...
> The default is -1 which is it won't timeout, it will wait
> forever. See the books online topic: SET LOCK_TIMEOUT
> for more information.
> -Sue
> On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
> <jhoranszky@.chartelltechnology.com> wrote:
>
What is the default for @.@.LOCK_TIMEOUT?
When it is not set, how long it is?
Thanks,
Janos
The default is -1 which is it won't timeout, it will wait
forever. See the books online topic: SET LOCK_TIMEOUT
for more information.
-Sue
On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
<jhoranszky@.chartelltechnology.com> wrote:
>Hi,
>What is the default for @.@.LOCK_TIMEOUT?
>When it is not set, how long it is?
>Thanks,
>Janos
>
|||Thanks Sue.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:6gf0n1dh5lfvos0ua3ic5sdg08vnsmja27@.4ax.com...
> The default is -1 which is it won't timeout, it will wait
> forever. See the books online topic: SET LOCK_TIMEOUT
> for more information.
> -Sue
> On Mon, 7 Nov 2005 12:12:04 -0700, "Janos Horanszky"
> <jhoranszky@.chartelltechnology.com> wrote:
>
LOCK REQUEST TIME OUT PERIOD EXCEEDED
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
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 request time out period exceded
Hi,
while my db is executing a store procedure i try to view the current activity in the managent but it returns to me 'Lock request time out period exceded'. It happens until the store procedure is finished. After that everything is ok. However i can see the activity executing sp_who and the db seems to work ok, maybe a little slow.
what does it means??
Sorry for my english.
Thanks. EduardoYou should understand the database engine's locking mechanism. Your stored procedures probably is modifying your data, which means an exclusive lock. If you or another process is trying to view the data, the engine tries to get a shared lock. Since there is already an exclusive lock, the request for getting the shared lock expires, which is shown to you.sql
while my db is executing a store procedure i try to view the current activity in the managent but it returns to me 'Lock request time out period exceded'. It happens until the store procedure is finished. After that everything is ok. However i can see the activity executing sp_who and the db seems to work ok, maybe a little slow.
what does it means??
Sorry for my english.
Thanks. EduardoYou should understand the database engine's locking mechanism. Your stored procedures probably is modifying your data, which means an exclusive lock. If you or another process is trying to view the data, the engine tries to get a shared lock. Since there is already an exclusive lock, the request for getting the shared lock expires, which is shown to you.sql
Monday, March 26, 2012
Lock on system table
Hi all,
One of my stored procedure is creating around 7 #tables.
This procedure comes out after 5 mins with error
1222 'Lock request time-out period exceeded'.
My @.@.lock_timeout is -1.
Analysis showed that this stored procedures holds numerous
shared and exclusive locks on sysobjects, sysindexes and
syscolumns on tempdb. It blocks all other processes and
nobody can do anything.
Can anybody suggests why this could be happening and way
out?
Thanks in advance
Himanshu JaniHi Himanshu ,
If your stored procedure uses SELECT ..INTO to create and populate the
temporary tables this can cause locking problems as it will take out a lock
on the system tables to create the temporary table, but will hold this lock
for the duration of the insert. You can solve this problem by creating the
temporary table explicitly with either CREATE TABLE or with SELECT ..
INTO... WHERE 1=0. You then have to insert the data into the temporary table
with a normal insert. If the table creation happens inside a transaction the
locks will be taken for the duration of the transaction, so it makes sense
to create the temporary tables outside a long running transaction. If you
use SQL Server 2000 you can also use table variables instead of temporary
tables, table variables do not participate in transactions, and so you won't
have any locks on the system tables in tempdb.
--
Jacco Schalkwijk
SQL Server MVP
"Himanshu Jani" <himanshu@.ocwen.co.in> wrote in message
news:0a0301c38c94$a10602c0$a401280a@.phx.gbl...
> Hi all,
> One of my stored procedure is creating around 7 #tables.
> This procedure comes out after 5 mins with error
> 1222 'Lock request time-out period exceeded'.
> My @.@.lock_timeout is -1.
> Analysis showed that this stored procedures holds numerous
> shared and exclusive locks on sysobjects, sysindexes and
> syscolumns on tempdb. It blocks all other processes and
> nobody can do anything.
> Can anybody suggests why this could be happening and way
> out?
> Thanks in advance
> Himanshu Jani|||It seems to me that SELECT INTO is just a single command
that combines both the CREATE TABLE and INSERT statements
(with the benefit of minimumally logging the INSERT).
This means that the X-lock(s) on the system tables in
tempdb is held just for the duration of the CREATE and not
for the duration of the entire SELECT INTO statement.
On SQL Server 2000, SP3, I just ran 'SELECT INTO' into a
#temp table from a 12 million record table and was able to
simultaneously run and complete a second SELECT INTO into
another #temp table while the first SELECT INTO was still
running.
Thoughts?
Thanks, -- Brian
>--Original Message--
>Hi Himanshu ,
>If your stored procedure uses SELECT ..INTO to create and
populate the
>temporary tables this can cause locking problems as it
will take out a lock
>on the system tables to create the temporary table, but
will hold this lock
>for the duration of the insert. You can solve this
problem by creating the
>temporary table explicitly with either CREATE TABLE or
with SELECT ..
>INTO... WHERE 1=0. You then have to insert the data into
the temporary table
>with a normal insert. If the table creation happens
inside a transaction the
>locks will be taken for the duration of the transaction,
so it makes sense
>to create the temporary tables outside a long running
transaction. If you
>use SQL Server 2000 you can also use table variables
instead of temporary
>tables, table variables do not participate in
transactions, and so you won't
>have any locks on the system tables in tempdb.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Himanshu Jani" <himanshu@.ocwen.co.in> wrote in message
>news:0a0301c38c94$a10602c0$a401280a@.phx.gbl...
>> Hi all,
>> One of my stored procedure is creating around 7 #tables.
>> This procedure comes out after 5 mins with error
>> 1222 'Lock request time-out period exceeded'.
>> My @.@.lock_timeout is -1.
>> Analysis showed that this stored procedures holds
numerous
>> shared and exclusive locks on sysobjects, sysindexes and
>> syscolumns on tempdb. It blocks all other processes and
>> nobody can do anything.
>> Can anybody suggests why this could be happening and way
>> out?
>> Thanks in advance
>> Himanshu Jani
>
>.
>
One of my stored procedure is creating around 7 #tables.
This procedure comes out after 5 mins with error
1222 'Lock request time-out period exceeded'.
My @.@.lock_timeout is -1.
Analysis showed that this stored procedures holds numerous
shared and exclusive locks on sysobjects, sysindexes and
syscolumns on tempdb. It blocks all other processes and
nobody can do anything.
Can anybody suggests why this could be happening and way
out?
Thanks in advance
Himanshu JaniHi Himanshu ,
If your stored procedure uses SELECT ..INTO to create and populate the
temporary tables this can cause locking problems as it will take out a lock
on the system tables to create the temporary table, but will hold this lock
for the duration of the insert. You can solve this problem by creating the
temporary table explicitly with either CREATE TABLE or with SELECT ..
INTO... WHERE 1=0. You then have to insert the data into the temporary table
with a normal insert. If the table creation happens inside a transaction the
locks will be taken for the duration of the transaction, so it makes sense
to create the temporary tables outside a long running transaction. If you
use SQL Server 2000 you can also use table variables instead of temporary
tables, table variables do not participate in transactions, and so you won't
have any locks on the system tables in tempdb.
--
Jacco Schalkwijk
SQL Server MVP
"Himanshu Jani" <himanshu@.ocwen.co.in> wrote in message
news:0a0301c38c94$a10602c0$a401280a@.phx.gbl...
> Hi all,
> One of my stored procedure is creating around 7 #tables.
> This procedure comes out after 5 mins with error
> 1222 'Lock request time-out period exceeded'.
> My @.@.lock_timeout is -1.
> Analysis showed that this stored procedures holds numerous
> shared and exclusive locks on sysobjects, sysindexes and
> syscolumns on tempdb. It blocks all other processes and
> nobody can do anything.
> Can anybody suggests why this could be happening and way
> out?
> Thanks in advance
> Himanshu Jani|||It seems to me that SELECT INTO is just a single command
that combines both the CREATE TABLE and INSERT statements
(with the benefit of minimumally logging the INSERT).
This means that the X-lock(s) on the system tables in
tempdb is held just for the duration of the CREATE and not
for the duration of the entire SELECT INTO statement.
On SQL Server 2000, SP3, I just ran 'SELECT INTO' into a
#temp table from a 12 million record table and was able to
simultaneously run and complete a second SELECT INTO into
another #temp table while the first SELECT INTO was still
running.
Thoughts?
Thanks, -- Brian
>--Original Message--
>Hi Himanshu ,
>If your stored procedure uses SELECT ..INTO to create and
populate the
>temporary tables this can cause locking problems as it
will take out a lock
>on the system tables to create the temporary table, but
will hold this lock
>for the duration of the insert. You can solve this
problem by creating the
>temporary table explicitly with either CREATE TABLE or
with SELECT ..
>INTO... WHERE 1=0. You then have to insert the data into
the temporary table
>with a normal insert. If the table creation happens
inside a transaction the
>locks will be taken for the duration of the transaction,
so it makes sense
>to create the temporary tables outside a long running
transaction. If you
>use SQL Server 2000 you can also use table variables
instead of temporary
>tables, table variables do not participate in
transactions, and so you won't
>have any locks on the system tables in tempdb.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Himanshu Jani" <himanshu@.ocwen.co.in> wrote in message
>news:0a0301c38c94$a10602c0$a401280a@.phx.gbl...
>> Hi all,
>> One of my stored procedure is creating around 7 #tables.
>> This procedure comes out after 5 mins with error
>> 1222 'Lock request time-out period exceeded'.
>> My @.@.lock_timeout is -1.
>> Analysis showed that this stored procedures holds
numerous
>> shared and exclusive locks on sysobjects, sysindexes and
>> syscolumns on tempdb. It blocks all other processes and
>> nobody can do anything.
>> Can anybody suggests why this could be happening and way
>> out?
>> Thanks in advance
>> Himanshu Jani
>
>.
>
Subscribe to:
Posts (Atom)