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
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.
SAP Knowledge Base Article - Preview