RE: no more lock?
Error numbers tend to help in getting more accurate answers.
I would guess that your delete statement was trying to delete to many records
at once. As you delete records in a transaction you acquire locks on all
the records you are deleting.
If my guess is correct you have three options.
1. Write a program which runs a select to get the records you want
to delete and then deletes the records one at a time base on the
select statement, committing every so often..2. Lock the table in exclusive mode then do you delete. (You might run
out of log space doing this)
3. Increase the number of locks. (You might run out of log space and
as the amount of data you are deleting goes up you might run into this
problem again.)
Hope that this helps,
Will
>===== Original Message From Raymond Chui <raymond.chui@noaa.gov> =====
>This is a multi-part message in MIME format.
>--------------7876B99A51A2305FD6ACBF0D
>Content-Type: text/plain; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
>I tried to execute some SQL statements like
>
>SELECT UNIQUE id FROM aTable;>or
>DELETE FROM aTable WHERE theTime < TODAY-10;>
>where that data type for column theTime is timestamp.
>
>I got "no more lock" error message. Is because I use buffered log?
>Should I make my database to unbuffered log or no log?
>Or something else?
>
>We insert a row to aTable every 15 minute for more than 8,000 IDs.
>The column id is not a primary nor unique key, it is foreign key.
>
>I can do
>
>DELETE FROM aTable WHERE id = '12345678' AND theTime < TODAY-10;>
>that's OK, but I need to get the unique id number without "no more lock"
>
>error message.
>How can I avoid that?
>
>--
>Why we want to teach our babies to talk and walk,
>then later we tell them "sit down!", "be quiet!" ?
>
>Democracy is not a better way for a solution,
>it is just another way to spread the blames.
>
>--Raymond
>
>
>--------------7876B99A51A2305FD6ACBF0D
>Content-Type: text/x-vcard; charset=us-ascii;
> name="raymond.chui.vcf"
>Content-Transfer-Encoding: 7bit
>Content-Description: Card for Raymond Chui
>Content-Disposition: attachment;
> filename="raymond.chui.vcf"
>
>begin:vcard
>n:Chui;Raymond
>tel;fax:(301)713-0963
>tel;home:ICQ #: 16722494
>tel;work:(301)713-0624 Ext. 168
>x-mozilla-html:TRUE
>url:http://members.xoom.com/rchui/
>org:NWS, NOAA
>version:2.1
>email;internet:Raymond.Chui@noaa.gov
>title:SA, DBA
>adr;quoted-printable:;;NOAA, NWS, Office of Hydrology, W/OH2=0D=0A1325
East-West Highway, Room 8112;Silver Spring;MD;20910-3283;U.S.A.
>x-mozilla-cpt:;30256
>fn:Raymond Chui
>end:vcard
>
>--------------7876B99A51A2305FD6ACBF0D--
------------------------------------------------------------
This e-mail has been sent to you courtesy of OperaMail, as a free service from
Opera Software, makers of the award-winning Web Browser, Opera. Visit us at
http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail
account is waiting at: http://www.operamail.com/
------------------------------------------------------------