Skip to Content

When locking in SQL Anywhere

Hi, everyone!

Please tell me whether locking will occur while executing "inner join" in SQL Anywhere.
If not, please tell me what kind of SQL statement locking occurs.

Please help me!

Add comment
10|10000 characters needed characters exceeded

  • It will depend on the isolation level and the specific lock type you are interested. You can use the system procedures sa_conn_info and sa_locks to see what locks are in use by a connection. To more easily identify the connection, either use a connection name or enable remember last statement.

  • Get RSS Feed

3 Answers

  • Apr 18 at 07:18 AM

    Thank you.

    I want to know the reason (processing) that is locked, so I have a question to continue.

    First of all, I forgot to write the current environment in which the problem has occurred, so I add it.
    SQL Anywhere - Server
    17.0.7 build#3382

    Next, I was watching the change history, but after the version I was using,
    As locking fixes are done, please tell me about its contents.
    SQL Anywhere - Server
    ================(Build #4085 - Engineering Case #811587)================
    In rare cases a server may hang while trying to estimate a selectivity of
    a particular predicate using an index. The hang is the result of a deadlock
    between cleaner process and index based selectivity estimation. This has
    been fixed.

    =================================================================

    What is "cleaner process and index based selectivity estimation" where deadlock occurs?

    Add comment
    10|10000 characters needed characters exceeded

    • Lets not get distracted by this issue as it is identified a rare. Lets rule out the more common contention issues that may appear to be a hang but blocking. You do so using the output of sa_conn_info and sa_locks.

      To answer the specifics of you question, you can review the documentation on these topics. Here is a summary:

      Some database tasks, such as processing snapshot isolation transactions, index maintenance, and deleting rows, can execute more efficiently if some portions of the request are deferred to a later time. These deferrable activities typically involve cleanup by removing deleted, historical, and otherwise unnecessary entries from database pages, or reorganizing database pages for more efficient access. This is the database cleaner process.

      The selectivity estimate (in very basic form) is a determination during optimization of what is believe to be the percentage of matching rows for a given condition (i..e, join, predicate). They are used to determine query plans.

  • Apr 18 at 01:37 PM

    You may check the SQL Anywhere docs, particularly the whole "How locking works" chapter.

    For example, there's blocking happening in the tutorial "Tutorial: Understanding non-repeatable reads / Lesson 1: Creating a non-repeatable read" in step 7.

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 20 at 12:38 AM

    Hi,

    As Chris says, you should get lock information using the SA_CONN_INFO and SA_LOCKS stored procedures.
    Following KBA explains the procedure.

    1963392 - In SQL Anyhwere, how to determine if there are any locks in the system?

    Thanks,
    Atsushi

    Add comment
    10|10000 characters needed characters exceeded