Sybase Performance Tuning

Actually I'm relatively new to SYBASE database. Though I have worked before with Oracle, MS SQL and MYSQL, I'm finding some problems in performance tuming in SYBASE.

 

Basically I'm dealing with very huge system with tables having 4crore + records. I have a table in which I face deadlock situation between a select statement and an insert statement. (This particular table doesn't have much records, usually <5000 records).

 

The code piece looks like this.(Obviously I've morphed columns and column names due to confidentiality issues).

 

set rowcount 1 --I want only one row to be fetched

select id, description from tQueue1 (1) --Fetch first record using index 1

 

/*Actually I've verified, this is the most optimal way to select the first row of the table. Basically the table is acting as a queue from which I'll be processing records one by one. Simulataneously as I process the data, new and new records keep getting inserted into the Queue*/

 

Now this select I specified up there is deadlocking with an insert into the same table.

 

I'm using 2 Stored Procedures. One for selecting and one for inserting. There are a number of c++ threads which control the firing of these SPs. That means, at a time multiple inserts are permitted.

 

The indexes are in place. No table scan is involved, yet again I'm getting Deadlocks.

 

The deadlock is occuring at the rate of 4-6 out of 15000 records. But everyday, I'll be processing atleast 1,00,000 rows. This is eating my brains out.

 

Does anyone here knows advanced Performance tuning in Sybase? Help in any form is appreciated.

Edited August 31, 2006 by SP Rao (see edit history)

It seems that you have rather few deadlocks, typically four at once. Then, you could imagine debugging.In my opinion, the deadlock occurs because two different processes try accessing the same record for update, probably the same ID.You could probably ask your stored procedures to write down the result of the select in a file, and have a look at the file for only these who experienced deadlocks. There might be a logics error in the way the procedures are fired, and these logs could help you learning why two of them are fired on the same record, whic record it is, etc...

It seems that you have rather few deadlocks, typically four at once. Then, you could imagine debugging.In my opinion, the deadlock occurs because two different processes try accessing the same record for update, probably the same ID.
You could probably ask your stored procedures to write down the result of the select in a file, and have a look at the file for only these who experienced deadlocks. There might be a logics error in the way the procedures are fired, and these logs could help you learning why two of them are fired on the same record, whic record it is, etc...

Well, Yordan. First, the deadlocks don't occur four at once, they occur randomly. May be the way in which I phrased the sentense was not right. What I meant was, while processing 15,000 records, I get 4-6 deadlocks. Their occurence is purely random.

Second, there is no update statement involved in the deadlock I'm encountering. I have proper logs for it. I do agree that update can lock, but they is no update. The lock is between a select statement and an insert into the same table, which is more the select will not go for table scan!

The way in which the SPs get fired can not be changed. As and when the user enters a data, it has to be inserted. That's why indexes were created for the table so that for selects, table scan will be eliminated.

When I did analyze the situaltion, there is one possibility that a record might be locked. If I'm trying to insert into the table, the rowid details will be needed for the insert as it'll append the data to the end of the table. At the same time if the select tries to select that newly created row before the insert is committed, then there is a possibility of deadlock.

But, are you aware of any techniques to eliminate insert-select locks in SYBASE?
Edited August 31, 2006 by SP Rao (see edit history)

But, are you aware of any techniques to eliminate insert-select locks in SYBASE?

Unfortunately not. Moreover, I'm afraid you face a kind of bug. I'm pretty convinced that your app don't perform updates on your tables, but the way the queries are written, they probably need to update a row in a system table. When a process goes fast enought, the lock on that row is released fast, and no problem ; if a process is queued for a given reason, the lock is not released immediately and the second process has time to put a lock on another row the first process will need, and we have a perfect deadlock.
I'm afraid only Sybase support can help you if it's really a bug.

Unfortunately not. Moreover, I'm afraid you face a kind of bug. I'm pretty convinced that your app don't perform updates on your tables, but the way the queries are written, they probably need to update a row in a system table. When a process goes fast enought, the lock on that row is released fast, and no problem ; if a process is queued for a given reason, the lock is not released immediately and the second process has time to put a lock on another row the first process will need, and we have a perfect deadlock.I'm afraid only Sybase support can help you if it's really a bug.

May be what you say makes sense. But what bugs me is the fact that my select never utilizes table scan. It always runs through the index which I've forced. Moreover the select just selects the oldest record of the table. And insert will be inserting at the end of the table. The only possible clash I can think of is when there in one single row in the table.... But the lock is ocuuring even when there are 7000 records in the table! ANyway I'll check in SYBASE site and post updates if any.

Thank you so much.

Moreover the select just selects the oldest record of the table.

Unfortunately, if two jobs look at the oldest record of the table at the same time, they will probably reach the same record.

Unfortunately not. Moreover, I'm afraid you face a kind of bug. I'm pretty convinced that your app don't perform updates on your tables, but the way the queries are written, they probably need to update a row in a system table. When a process goes fast enought, the lock on that row is released fast, and no problem ; if a process is queued for a given reason, the lock is not released immediately and the second process has time to put a lock on another row the first process will need, and we have a perfect deadlock.

I'm afraid only Sybase support can help you if it's really a bug.

 

Well, after quite an amount of research and some real expert advices I was able to resolve the issue quite simply as a matter of fact. It wasn't a Sybase bug after all. (Or atleast I believ so!). Well, I still can't explain why though there was no table scan involved the table used to get locked. But, there was an information I forgot to mention. Though it was the queries which were under lock, I was using C++ threads to control them. (The queries were inside two Stored Procedures(SP) ).

 

Now I had used multithreaded programming there. So there was a possibility that there were more than one instance of the C++ control thread running. Which in turn means, there could be more than one instance of the SPs getting fired. (Now, I don't know how Sybase handles these). Though with the help of logs I could find out the queries under lock, the simulation mightn't have been accurate enough to the real time case.

 

So, I assume that when multiple instance of the SPs run, there's always a possibility of table scan. So finally after all these story pieces, the moral is "I couldn't eliminate the lock".

 

I chose the alternative option. There is a Sybase library called ctlib which I was using in my C++ code. It gives an option of clearing the deadlock. (Ofcourse a function which checks if the particular SP is under deadlock). I check if the SP is under deadlock. If there is a deadlock, I clear the deadlock first. Then I waite for a randon amount of time. And I retry running the SP.

 

I retry for a maximum of three times. So far I've not encountered the deadlock! Now, my problem is solved, though the deadlocks still continue to occur

 

Anywas Thanks for all your help Yordan. May be it was my mistake that I didn't give you the holistic picture. I beg thy pardon for not giving suffecient info.

beg thy pardon for not giving suffecient info.

No problem. The main goal is achieved, your problem is solved, and your prog is now working correctly, with a satisfactorily working deadlock detection.Nice work.
Yordan

Hi,I would say, may be your table locking was not correct and isolation levels not correct.These problems are generally encountered when using threaded application and you have a way to fix by using random retries but we need to be specific around isolation levels too.-

reply by vivek singh