Sybase IQ 16 SP 08.32,problems with concurrent operations

[Former Member]

Hi, i´m facing a problen with a project that involves multiple concurrent inserts and updates on a IQ table, i have read that in sybase IQ 16 you have the "blocking" option to deal with this, so we turned it on , but we are still facing the problem, the message says this :

transaction <number> attempted to access <table name>  created by transaction <number>.

We tried with the default value (0 for indefinied) for the blocking_timeout option, as well as a very large value (100000) but still no luck,does anything else needs to be configurated for the "blocking" option to work?

Then we also tried with the RLV(row level versioning) aplied to this table, and it seemed to work well, however sometimes(and sometimes don´t)  the Insert statements returns 2 duplicate values for the primary key(identity column).

For the problem of the duplicate identity column, we found the note "2208642 - SAP IQ: duplicate insert issue with RLV table" ,this note mentions an error that is very similar to mine and says that it is fixed in IQ 16 sp 10.03, but wen we go to service.sap to downloads, we can´t find this version ,the last  one is :16.0 SP10 PL02   -> IQSERV160010P_2-20011184.ZIP EBF 25000  .

The inserts and updates have been tested  outside stored procedures, inside stored procedures, with "commits" at the end, with commits at the beggining  and we could not make it work.

Does anybody has any suggestions?

Thank you!

[saroj_bagai · Accepted solution]

blocking_timeout: When the blocking option is on, any transaction attempting to obtain a lock that conflicts with an existing lock waits for the indicated number of milliseconds for the conflicting lock to be released. If the lock is not released within blocking_timeout milliseconds, an error is returned for the waiting transaction.

Set the option to 0 to force all transactions attempting to obtain a lock to wait until all conflicting transactions release their locks.

Identity column support for RLV tables have been added in IQ 16 sp10.04  1-off (linuxamd64)  and sp11 due in Dec.

You can also get around duplicate insert value insert by creating unique HG index on identity column or setting identity_enforce_uniqueness database option, 

You will need to upgrade to IQ 16 sp10.03 full release and then applying sp10.04 patch

https://service.support.com/sap/support/notes/2217529

https://service.support.com/sap/support/notes/2208642

IQSERV160010P_3-20011180.TGZ

EBF 25175: 16.0 SP10 PL03

3

Info

1196144

02.09.2015

[Former Member · Accepted solution]

Hi, thank you so much for your help, i alredy tried the blocking option(with blocking_timeout=0) and had no luck , also tried your suggestion of seting identity_enforce_uniqueness  option, but that also didn´t work,i see that the identity oclumn suport was added on iq 16 sp 10.04 but for Linux, i have a windows environment(at this moment we have IQ16 sp8.32), is there any upgrade for windows? or this is just for linux?

Again, thanks for your time and your help!

[Gisung · Accepted solution]

Hi,

As you know, Blocking is not supported on secondary nodes.

Is IQ running on MPX or Simplex?

-----------------------------------------------------------------------------------------------------------------------------------

BLOCKING Option

Controls the behavior in response to locking conflicts. BLOCKING is not supported on secondary nodes of a multiplex.

-----------------------------------------------------------------------------------------------------------------------------------

And You can find more details with the following associated KBA.

2217529 - RLV: duplicate values in the identity column

https://i7p.wdf.sap.corp/sap/support/notes/2217529

==

Gi-Sung Jang

[Former Member · Accepted solution]

Hi, thank you so much, we have IQ running on simplex environment, but the blocking option seems like it's not working, we have alredy tried with a very large "blocking_timeout" value and a 0 value(so it waits until the lock it´s released) but still no luck. He have also seen the note you mention, but we can´t find the upgrade/patched mentioned for Windows, only for Linux, we have also tried the workaround in the note:

• set option identity_Enforce_Uniqueness='on'

But it didn´t work either. Any suggestions?

Thank you.

[saroj_bagai · Accepted solution]

IQ  allows one writer per table,  blocking/blocking_timeout only gives time for other transaction to wait for that amount of time, if lock is released in that time, transaction will proceed , else will raise error.  If application can handle it can submit failed transaction.

For concurrent writers to same table, but not same page, you will need to use  RLV,  and for identity column support, upgrade server to IQ 10.04. you will need to open incident and request fix for windows platform.