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.