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

[mike_quinn]

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

[saroj_bagai · Accepted solution]

set auto_commit to on

KBA 2299543

[mike_quinn · Accepted solution]

Hi

great ... this works.

However, we get the same issue in Cockpit ... is there any way to alter Cockpits behaviour similarly

Cheers

Mike

[saroj_bagai · Accepted solution]

You can set auto_commit  on for user connecting to cockpit

set option DBA.auto_commit='on'

set option DBA.isql_show_multiple_results_set='on'

[mike_quinn · Accepted solution]

Works ...

Cheers

Mike

[former_member194571]

Hi Mike,

although you've marked your question as answered, I'd like to add a point. To me, it looks like you're describing the effects of versioning rather than locking. Both are ways to enforce consistency, but they use different approaches. With the classic locking approach (like in most OLTP'ish RDBMSs), we focus on the write transaction - this is the one we should keep short since it is the one that can cause lock conflicts. Read transactions will see the committed changes immediately unless they operate on Isolation Level 3 (which isn't very common in such a setting). IQ, however, always operates on IL 3, and this is guaranteed by always returning the result that would have been returned for the same query when the (read) transaction started. So in IQ, it is highly relevant to keep an eye on read transactions, too. this is due to application considerations (you'll always see a result representing the start time of your xact), but also w.r.t IQ resource considerations, since the snapshotted data versions occupy disk space (and cache space) until they can be released, which is when no more read xacts are open requiring access to this particular version.

In a setup where I'm peeping into ongoing data provision in IQ, I've made it a habit to wrap rollbacks (commits will do as good unless you get into nested xacts) around the peeping query (I don't use auto_commit, I don't trust myself enough to take away the ability to roll back some typo or other result of a thick finger), like

rollback;

select * from tom.products;

rollback;

The initial rollback ensures that I see the most recent data by starting a new xact. The final rollback makes sure I'm not keeping any old versions by leaving my read xact pending. dbisql options about results may have an impact (on top of Saroj's suggestion, I usually set isql_print_result_set to 'ALL'.

HTH

Volker

[mike_quinn]

Hello

really useful from all three of you guys.

Cheers

Mike

[markmumy]

When clients connect to IQ, the chained or unchained (auto commit on or off) mode will vary depending on the application and how they connect.  By default, dbisql puts itself into auto_commit off mode.  That means that in order to see new data, you have to issue a commit.  Open Client based applications like 'isql' have auto_commit set to on so that you don't have to worry about versions and transactions.

Mark

[mike_quinn]

Hi guys ... thanks for the answers ... I'm slowly filling in the big gaps in my IQ16 knowledge.

lol ... there are a lot more gaps than anything else.

What about Cockpit then ... any way to affect this behaviour?

Cheers

Mike