Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2007
    Posts
    7

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

    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

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    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.

  3. #3
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    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.

  4. #4
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    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

  5. #5
    Join Date
    Dec 2007
    Posts
    7

    Blocking OFF

    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.

  6. #6
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    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.

  7. #7
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    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.

  8. #8
    Join Date
    Dec 2007
    Posts
    7

    OFF and ON

    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!
    Last edited by Otype; 12-13-07 at 12:47.

  9. #9
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    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.

  10. #10
    Join Date
    Dec 2007
    Posts
    7
    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.

  11. #11
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    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
    Last edited by trvishi; 12-13-07 at 21:18.

  12. #12
    Join Date
    Dec 2007
    Posts
    7
    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!

  13. #13
    Join Date
    Dec 2007
    Posts
    7

    SQLINITSTRING and isolation level

    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
    Last edited by Otype; 02-29-08 at 09:13.

  14. #14
    Join Date
    Feb 2010
    Posts
    1
    Hi, when I try
    SET OPTION blocking='ON'
    the error message is
    SQL Anywhere Error -201: Invalid setting for option 'Blocking'

  15. #15
    Join Date
    Mar 2010
    Posts
    1
    Hi guys... it sounds like one of your procs has a taken out a write mode lock on a table and it is interfering with the other proc. You can manage this situation by using the lock table command... (doc excerpted..)

    LOCK TABLE statement

    Description Prevents other concurrent transactions from accessing or modifying a table
    within the specified time.

    Syntax LOCK TABLE table-list [ WITH HOLD ] IN { SHARE | WRITE | EXCLUSIVE }
    MODE [ WAIT time ]

    Parameters table-list:
    [ owner. ] table-name [ , [ owner. ] table-name, … ]

    time:
    string

    Examples Example 1 For example, the following statement obtains a WRITE lock on the
    Customers and Employees tables, if available within 5 minutes and 3 seconds:

    LOCK TABLE Customers, Employees IN WRITE MODE WAIT
    '00:05:03'

    Example 2 The following statement waits indefinitely, until the WRITE lock
    on the Customers and Employees tables, if available or an interrupt occurs:

    LOCK TABLE Customers, Employees IN WRITE MODE WAIT

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •