SAP Knowledge Base Article - Preview

2779796 - Under Blocking option to 'ON', Selected table in Update query has 'Write' lock until commit -- SAP IQ

Symptom

What we have observed on SP02 PL14 is that when you use a update a temporary table from a base table and the 'blocking' db option is enabled
then write lock on selected table is created. This Write lock caused our application to fail to obtain a lock.
But in SP02.03 , only Shared lock is cerated on the selected table.

  • In SP02.14


(DBA)> Set Temporary option Blocking='ON' ;

(DBA)> update #TEST_TMP m set m.a = t.a from DBA.TEST t where m.a = t.a;
2 row(s) updated

(DBA)> sp_iqlocks;
conn_name conn_id user_id table_type creator table_name index_id lock_class lock_duration lock_type row_identifier ...
-------------------------------------------------------------------------------------------------------------------------------------
SQL_DBC_7fd740584bf0 2 DBA BASE DBA TEST (NULL) Table Transaction Write (NULL) (NULL)
SQL_DBC_7fd740584bf0 2 DBA BASE DBA TEST (NULL) Schema Transaction Shared (NULL) (NULL)

(2 rows)

But after 'Commit', all lock is removed.

  • In SP02.03


(DBA)> Set Temporary option Blocking='ON' ;

(DBA)> update #TEST_TMP m set m.a = t.a from DBA.TEST t where m.a = t.a;
2 row(s) updated

(DBA)> sp_iqlocks;
conn_name conn_id user_id table_type creator table_name index_id lock_class lock_duration lock_type row_identifier ...
------------------------------------------------------------------------------------------------------------------------------------
SQL_DBC_7fd740584bf0 2 DBA BASE DBA TEST (NULL) Schema Transaction Shared (NULL) (NULL)

(1 rows)

Also all lock is removed, after 'Commit'.


Read more...

Environment

SAP IQ16.1 SP02.xx

Product

SAP IQ 16.1

Keywords

Blocking , sp_iqlocks, WRITE, Shared , , KBA , BC-SYB-IQ , Sybase IQ , Problem

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.