Transaction handling in sender JDBC adapter

[Former Member]

Hello,

In a scenario where a third party application updates a field (counting it up) and we pick up these counts and zero out the field with the sender JDBC adapter, we would like that the third party application is not able to update while the JDBC sender adapter is processing.

We were under the assumption that the select and update statements of the sender JDBC adapter where executed within the same DB transaction and set the isolation level to "repeatable read" to make sure the third party app. could not update while the adapter is processing.

This had no effect.

Do the select and update statement occur within the same transaction?

Thanks,

Dan

Edited by: Dan Hansen on May 5, 2008 9:58 AM

[Former Member · Accepted solution]

Hi Dan,

Try to have a look at the following page, it explains meaning of isolation level and it could help you choosing the most suitable one for your scenario (this is not related to XI but I find it very helpful) :

http://db.apache.org/derby/docs/10.0/manuals/develop/develop71.html

The most restrictive level is "isolation", I think locks are set at rows and tables level

Rgds

Chris

[Former Member]

Yes, isolation level is set to "repaetable_read".

\Dan

[Former Member]

Thanks for the answers so far, I have looked through this and can find no reason why the current set up with "repeatable_read" should not work... a little more info. We are running against a MSSQL 2005 database using a table with the following structure.

CREATE TABLE [dbo].[OPERATION] (

[WERKS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[AUFNR] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[VORNR] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[SSAVD] [datetime] NULL ,

[ARBPL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[MGVRG] [bigint] NULL ,

[LMNGA] [bigint] NULL ,

[LTXA1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ZCONFQTY] [numeric](18, 0) NULL ,

[ZTIMESTAMP] [datetime] NULL ,

[ZSTATUS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[STAT] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[SSEVD] [datetime] NULL ,

[FSEVD] [datetime] NULL ,

[FSAVD] [datetime] NULL ,

[XMNGA] [bigint] NULL ,

[RMNGA] [bigint] NULL ,

[RUECK] [bigint] NULL ,

[ZPACKQTY] [bigint] NOT NULL ,

[STEUS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ZSCRAPQTY] [numeric](18, 0) NULL ,

[ZREWORKQTY] [numeric](18, 0) NULL ,

[BEARZ] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[BEAZE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[RUEST] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[RSTZE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

We read information about the quantities per primary key combination with the select statement where the ZSTATUS field is "CONF" and then zero out the quantity field and set the ZSTATUS to CONF2SAP with the update statement.

Another app counts up the quantities and sets the ZSTATUS to CONF as needed.

Sometimes we see that we miss a count because we read before someone has increased the quantity but update back after they did and thereby zero out their entry.

[Former Member]

Hansen,

Have you already set the transaction isolation level to "Repeatable Read" in your sender JDBC adapter? This can be done by Selecting the "Advanced Option" check box and then selecting the required transaction level there.

According to SAP, the Select and Update occurs in the same transaction. This is described in JDBC adapter FAQ in a OSS note. OSS note 831162

Regards,

Jaishankar

Edited by: Jai Shankar on May 5, 2008 2:42 PM

[former_member556603]

Hi,

Go through thid links,

http://help.sap.com/saphelp_nw04/helpdata/en/22/b4d13b633f7748b4d34f3191529946/frameset.htm

Thanks,

Satya Kumar

[Former Member]

Hi Dan,

The Select and Update statements execute in the same transaction, when the isolation level is "repeatable read".

Refer the below link for more understanding:

[http://help.sap.com/saphelp_nw04/helpdata/en/22/b4d13b633f7748b4d34f3191529946/frameset.htm|http://help.sap.com/saphelp_nw04/helpdata/en/22/b4d13b633f7748b4d34f3191529946/frameset.htm]

Regards

Bhanu

Intelligroup.