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
No comments:
Post a Comment