When locking in SQL Anywhere

[Former Member]

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!

[chris_keating]

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.

[former_member186998]

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

[VolkerBarth]

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.

[Former Member]

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?

[chris_keating]

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.