Re: What Happens When You Try To Lock A Table That's Already Locked?

Thanks for all of your help! It turns out I was just missing the NOWAIT parameter which I am already using for my SELECT FOR UPDATE command but wasn't aware you could use for LOCK TABLE.

A couple comments:

1. I'm not really "fond" of saying Deadlock. It brings me absolutely no joy. I just mixed up the terminology. Sorry, I'm somewhat of a newbie.
2. A SELECT * FOR UPDATE NOWAIT would prevent a user from changing any EXISTING row in the table. What I also need(ed) to do was prevent any new records from being added until I had "cleaned" it out.

Now, armed with the LOCK TABLE NOWAIT I have the answer. In the interim I came up with a hack, and I'm curious what y'all think of its efficacy.

What I want, as I have mentioned, is before doing an insert to check to see if the table has the max number of records allowed and when it does to lock it (prevent new records form being inserted), copy those records into a new table and delete them. This all takes place before doing ANY insert on the table.

So my hack was if the max has been reached:

·"Lock" all records with "SELECT * FOR UPDATE NOWAIT"
·Create a new table
·Copy the records
·Delete them from the old table
·Count the number of records in the newly create table.
·If this number is less than the max ROLLBACK, send an error back!

If the COUNT is less than the max allowed SOMETHING went wrong! That "something" would probably be that RIGHT BEFORE the records were deleted from the old table, another transaction did the COUNT and saw the max value. By the time this new transaction went to lock the table, however, the previous transaction had already deleted the records and (maybe) also done the INSERT of the new record. So what happened was ANOTHER new table was created unnecessarily with 0 or 1, or very few records. This would screw me up totally because I want a definite number of records in every table.

Again, it's a hack and now that I can lock the table it's superfluous. But ... I think it works ... At least I couldn't get it to bomb!

Another quick, unrelated question - If you create a sequence like this:

CREATE SEQUENCE t_joc1_seq INCREMENT BY 1 START WITH 1 ORDER NOCACHE;

Does this ABSOLUTELY GUARANTEE that the sequence will NEVER be out of order? I would like to use this column value in some calculations but I don't trust it so instead, for now, I am simply relying on having a max number of records in a table ...

Thanks again for any help / comments!
John

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 22 1999 - 05:56:30 CDT