Skip to Content
0
Jul 29, 2016 at 12:54 PM

SAP IQ 16 SP 11 - locking in 'dbisql' and other client tools

188 Views

Hi

problem understanding table locking in IQ16 versus some client tools and not others.

In 'dbisql' if a user 'tom' runs "select * on products" it generates a lock on the table that I can see in 'sp_iqlocks'.

Any inserts, updates, deletes to the "products" table, carried out via other tools such as Toad or DataServices aren't visible in 'dbisql'.

However, these DML changes are visible to the other tools. Its like 'dbisql' sees its own little view of the data that does not change to reflect what the other tools are seeing ... unless I issue a 'commit' after the select statement in 'dbisql'.

Workflow:

1) in 'dbisql'

> select * from tom.products

>101, apples

>102, pears

2) In DS4.2

>select * from tom.products

>101,apples

>102,pears

3) In Toad

>select * from tom.products

>101,apples

>102,pears

4) In DS4.2

>insert into tom.products values (103,oranges)

>select * from tom.products

>101,apples

>102,pears

>103,oranges

5) In Toad

>select * from tom.products

>101,apples

>102,pears

>103,oranges

6) in 'dbisql'

> select * from tom.products

>101, apples

>102, pears

The workflow 6) just brings back the original dataset whereas DS4.2 and Toad can both see the new same one.

Also, if I try to drop the table from say Toad I get an error saying that "User 'another user' has the row in 'products' locked.

How can a simple select statement issued in 'dbisql' prevent the table being dropped from another conenction?

Struggling to get my head around the locking mechanism in IQ 16.

Any help appreciated.

Cheers

Mike