Any takers, now that sixteen months have passed?

---

How do I determine that it is a schema lock that is blocking an event?

Does sa_conn_info().LockRowId = 0 necessarily imply "schema lock"?

The documentation talks about null values, but not zero, and it is vague about nulls: http://dcx.sybase.com/index.html#1201/en/dbreference/sa-conn-info-sysproc.html

---

In the following example using SQL Anywhere 12.0.1.3298, it is easy to tell that connection 1000000042 is blocked by a schema lock held by connection 1.

HOWEVER, in the real world, sa_locks() may display hundreds or thousands of locks held by EACH of those connections, and there doesn't seem to be any information in the sa_conn_info() output that would lead directly to the schema lock as the culprit.

The only clearly-useful information sa_conn_info() shows about the lock is "LockTable = DBA.t1" and "BlockedOn = 1".

Further compounding the problem is that LastReqTime and LastStatement are both empty for the blocked connection 1000000042, even though PROPERTY ( 'RememberLastStatement' ) is 'Yes'.

In this case, connection 1000000042 is an EVENT, and the blocked statement is (most likely) an ALTER TABLE inside the EVENT.

SELECT * FROM sa_conn_info() WHERE Name NOT LIKE 'Foxhound%' ORDER BY Number;
Number,     Name,                 Userid,DBNumber,
                                            LastReqTime,               ReqType,       CommLink,NodeAddr,
                                                                                                     ClientPort,
                                                                                                        ServerPort,
                                                                                                           BlockedOn,
                                                                                                              LockRowID,
                                                                                                                 LockIndexID,
                                                                                                                   LockTable,
                                                                                                                             UncommitOps,
                                                                                                                                ParentConnection
1,          'ddd12-1',            'DBA', 0, '2012-06-13 16:18:43.411', 'FETCH',       'local', '',   0, 0, 0, 0, , '',       1,
1000000042, 'event_on_insert_t2', 'DBA', 0, '',                        'unknown (0)', 'NA',    'NA', 0, 0, 1, 0, , 'DBA.t1', 0,

SELECT * FROM sa_locks() WHERE table_name = 't1' ORDER BY conn_id;
conn_name,            conn_id,    user_id,
                                         table_type,
                                                 creator,
                                                        table_name,
                                                              index_id,
                                                                lock_class,
                                                                          lock_duration, lock_type,   row_identifier
'ddd12-1',            1,          'DBA', 'BASE', 'DBA', 't1', , 'Row',    'Transaction', 'Surrogate', 47203552
'ddd12-1',            1,          'DBA', 'BASE', 'DBA', 't1', , 'Schema', 'Transaction', 'Shared',
'event_on_insert_t2', 1000000042, 'DBA', 'BASE', 'DBA', 't1', , 'Schema', 'Transaction', 'Shared',

SELECT * FROM sa_conn_properties() WHERE Number = 1000000042
   AND PropName IN ( 'LastReqTime', 'LastStatement' ) ORDER BY Number, PropName;
Number,     PropNum, PropName,        PropDescription,     Value
1000000042, 208,     'LastReqTime',   'Last request time',
1000000042, 306,     'LastStatement', 'Last statement',
---------------------------------------------------------------------
SELECT PROPERTY ( 'RememberLastStatement' );
'Yes'