Sybase IQ: Concurrency control and blocking option in catalog store tables

Otype · #1 (**permalink**) 12-12-07, 04:19

Hi everybody!

I have come to situation when two transactions are handling the same
row and Sybase IQ throws an error because of it. Error is 210. "User
'%1' has the row in '%2' locked."

Blocking option seems to be constant with value OFF. Tables are
created with ON SYSTEM clause. So they are created into Catalog store.

So what to do when real concurrency control is needed (in Catalog
store)? Later transaction is failing, because of earlier's row lock.
Is there a reason why this blocking option -- as an option not
constant -- is not implemented into IQ?

Best regards,
Harri

trvishi · #2 (**permalink**) 12-13-07, 05:36

I think you need to turn ON the Blocking option in order for concurrency to work based on the following writeup from sybase site.

You have attempted to read or write a row and it is locked by another user. You also receive this error if you try to drop a global temporary table while another user is using it. Note that this error will only be received if the database option BLOCKING is set to OFF. Otherwise, the requesting transaction will block until the row lock is released.

trvishi · #3 (**permalink**) 12-13-07, 05:38

Also, remember IQ is not an OLTP. And its architecture is certainly not built to accommodate parallel concurrency. Thats probably why they have this as a constant rather than a default OLTP feature.

trvishi · #4 (**permalink**) 12-13-07, 05:47

Hm.. Checked a little further. Theres ONLY one option for BLOCKING which is OFF in the manual. So, looks like they put it in for later versions. Thats odd. Ignore my previous posts

Otype · #5 (**permalink**) 12-13-07, 08:54

Yes, that is the problem, only OFF is allowed value. And I know it is not OLTP database. So maybe there is no other way.

trvishi · #6 (**permalink**) 12-13-07, 09:07

Isnt the catalog store maintained in ASA instead of IQ. In that case, try looking at ASA (Adaptive Server Anywhere) topics and if I come across anything I will let you know.

Also, if you have a support contract, worth opening a case.

trvishi · #7 (**permalink**) 12-13-07, 09:11

Hm check this thread.

http://www.dbforums.com/archive/index.php/t-620419.html

It looks like in standalone ASA you can have it ON. But maybe with IQ and frontend of ASA, its not possible. If thats the case, ON SYSTEM is not really that flexible for concurrency.

Otype · #8 (**permalink**) 12-13-07, 12:40

Yes, IQ manual says that the allowed values are OFF

and yes for sure for ASA there is also the ON option. Somehow sad.
EDIT: Yea, ON SYSTEM is used to have the tables in Catalog store and to have the benefits of ASA, but...

Thanks for your contribution, trvishi!

trvishi · #9 (**permalink**) 12-13-07, 13:11

Its been about 4 years since I touched IQ. But I do vaguely remember that DDL is serialized in IQ. And the unavailability of concurrency would explain why, if that is infact true.

Otype · #10 (**permalink**) 12-13-07, 13:21

Ok. To be precise, there is some concurrency control (MVCC) for IQ store, but for catalog store it seems no. At least sophisticated control. Because in the end the simple locking is some concurrency control.

trvishi · #11 (**permalink**) 12-13-07, 21:14

Quote:

Originally Posted by Otype

Ok. To be precise, there is some concurrency control (MVCC) for IQ store, but for catalog store it seems no. At least sophisticated control. Because in the end the simple locking is some concurrency control.

Thats the problem.. You're talking about two different databases... When you say ON SYSTEM or create a table in Catalog store, you are NOT anywhere close to IQ. I know u know that, but just want to re-iterate to the public. Its tough I know, you wanted something normal in ASA being in IQ, but it looks like u cant get it... But again, dont rule it out totally... Maybbe you can workaround it.

Can I ask why you do ON SYSTEM in the first place?

Good luck

Otype · #12 (**permalink**) 12-14-07, 02:41

Yes, that is why I said IQ, because its ASA/Catalog store/ON SYSTEM behaves different than ASA by itself. But of course IQ is the product.

ON SYSTEM because of legacy code.

Yea, I haven't given up!

Otype · #13 (**permalink**) 02-29-08, 09:07

Hi,
just to inform, that I managed to get rid off the below errors by setting for every connection the isolation level required. With jConnect it worked only when putting this "SET OPTION etlrep.ISOLATION_LEVEL=0" into SQLINITSTRING. No other way did it succeed.

Well of course problems occur, if some random client locks the rows.

Also I found Sybase's comment on those SYSTEM storage tables:

"Customer Audit Trails
- Used to track who executed particular reports, etc.
- If still needed, create in system (ASA) vs. IQ base table
- One problem is that such tables need concurrent user
access…not possible in IQ"

http://www.uksug.co.uk/download_publ...0%20Soundy.pdf