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:

In the following example using SQL Anywhere, 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,
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,
                                                                          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' );

asked 13 Jun '12, 17:03

Breck%20Carter's gravatar image

Breck Carter
accept rate: 21%

edited 06 Oct '13, 16:20

Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 13 Jun '12, 17:03

question was seen: 838 times

last updated: 06 Oct '13, 16:20