Locks

MiniMe · Post by **MiniMe** » Wed Nov 28, 2001 3:09 pm

Are locks FIFO or do certain calls take precedence. If they are FIFO is there a way to make certain types of locks take precedence over others.

Post by **John** » Wed Nov 28, 2001 3:19 pm

The locks are queued, and any lock that does not conflict with the one at the head of the line, or any other granted lock, is granted, so it is sort of FIFO, and guarantees lack of starvation. What sort of precedence were you looking for.

MiniMe · Post by **MiniMe** » Wed Nov 28, 2001 3:33 pm

the problem lies in deleting records from a 18 million record database that has 22 indexes on it while it is being searched at very high rate. I have been watching in ltest and when this happens the searches back up. Ltest looks like this sometimes.

books(18921)Xxxiririririiriirrriiririririririririiririr

Post by **John** » Wed Nov 28, 2001 3:56 pm

The field in the where clause of the delete is indexed right? In this case there is an index write lock on the table, so the reads will wait for it. You should also make sure that the processes doing the deletes aren't niced back, otherwise the OS might not give them enough CPU to complete the job. All processes talking to the database should be at the same priority level.

MiniMe · Post by **MiniMe** » Wed Nov 28, 2001 4:01 pm

Yes.. the key from which the delete is run is indexed. I am assuming that the lock stays longer than most due to the 22 indexes that need to be updated from the delete. My long term thoughts are to add a flag column to the table and update the flag if the inventory is sold and then do a batch delete late at night. but that is long term and with XMAS pounding us I would like to find a short term boost to performance and the stop the timeouts that this problem is creating.

Post by **John** » Wed Nov 28, 2001 4:09 pm

It looks as if there are 2 more index write locks queued up. Do you have several deletes occurring at the same time? If so and it's possible to serialize them that would help. The other thing you could do in the search script would be to set fairlock to 0. This would allow the searches to get a lock as long as there wasn't a delete actually running, and could basically prevent the delete from happening until there were no searches happening.

MiniMe · Post by **MiniMe** » Wed Nov 28, 2001 4:15 pm

Unfortunately there are about 10 autonomous applications that can delete from that table so serializing the deletes could be a problem. Ill research the fairlock idea further.. thanks

bart · Post by **bart** » Thu Nov 29, 2001 8:29 am

If you change the applications to record the records that they want to be deleted into a table then you can perform the deletes at a convenient time and also serialize them.

MiniMe · Post by **MiniMe** » Fri Dec 07, 2001 2:15 pm

I am currently changing all the applications in house to update a flag on the records to be deleted so that this happens during non-peak time.

Thanks