Here is the scenario,

We have three nodes in cluster and we have file polling ,every day 1000 files pickup from the polling directory,for every successful completion it will create a transaction id(TXNID) in the database table(txn_logs). we have shared folder for polling .we are receving the below error in the logs. files are processing successfully but we are not able to see the logs for the files.

Violation of PRIMARY KEY constraint ‘TXN_LOG’(table name). Cannot insert duplicate key in object ‘TXN_LOG’. The duplicate key value is (2914674-15).

2015-06-24 05:57:56 EDT [ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ADA.1.316] Cannot execute the SQL statement “INSERT INTO dbo
.TXN_LOG(TXNID, TXNDATE, FLOWDIRECTION, SENDER, RECEIVEDFILE, RECEIPTMETHOD, STATUSCODE) VALUES (?, ?, ?, ?, ?, ?, ?)”. "
(23000/2627) Violation of PRIMARY KEY constraint ‘TXN_LOG’. Cannot insert duplicate key in object ‘TXN_LOG’. The duplicate key value is (2914976-15).

Hello,

Is it related to webMethods database table or application database table (project framework).

If its application database you need to check why you have a primary key constraint error? See if the id 2914674-15 is already present in the table.

If TXNID is your primary key then it is unique by definition. Try to run and see if you have already the row that you try to create with your insert:

SELECT *
FROM dbo.TXN_LOG
WHERE TXNID= ‘2914674-15’

Hi Mahesh,

Thanks for your reply, TXN id is the primary, it is already exist in the database(application table).

the problem is when i place 1000 files in file poller, that IS is trying to create duplicate txnid’s …thats why we are receving this error…actally am looking for how to avoid this error.

If we take one duplicate key value (2914710-15). I am trying to find out from which node this key value(2914710-15) was inserted before it failed in one node(A)…we have theree nodes in cluster.

my doubt was same node is creating txnid or other nodes creating id, for all these three nodes reflecting the same transaction table.

I see your point, if I understood your correctly the txnid 2914710-15 is generated by wM each time a file is polled. Is this correct?

I have another solution in mind, can you transform this id and generate your own uuid (16 or 32 bit) by doing this you can avoid duplicate entries and unique constraint errors.

Share me your design to give you more information.

Regarding the cluster as per my understanding you really cannot predict which node is generating the same id.

May be you can increase your debug level on IS.

yes mahesh, whenever file got processed it will create a new TXINID, problem is IS clustering in polling. Thansk for your reply!!

We are generating the trnxid using a synchronized java program. The java program picks the trnxid(long number) from a db table and adds 1 to the id every time. once its generated the latest trnxid is then updated back to the db table to maintain the sequence. So since this is a synchronized java code(which is thread safe), the trnxid should be unique every time theoretically. But it doesn’t seem to be working like that. Any suggestions?

I would suggest to you re-design your java program. See if the below code works for you with minimal changes

http://www.tutorialspoint.com/java/util/uuid_randomuuid.htm

Santosh,

The synchronized Java method/block would help prevent two threads from executing that same piece of code concurrently in the same JVM. However, because you’re running 3 nodes in a cluster, you’re dealing with 3 separate JVM’s, so you could potentially have 3 threads attempting to execute that piece of code at the same time.

Since all you’re doing is pulling a number from the database and adding 1 to it, why not let the database handle it? For example, you could use an Oracle SEQUENCE or a SQL Server IDENTITY. Your DBA should be familiar with these if you’re not.

Percio

Thanks Percio and Mahesh. Your note on synchronization in a single jvm, explains why synchronized block was not working correctly in a cluster.
The other thing I noticed in the polling port was that the “Enable Clustering” property is set to “NO” in all the three nodes in cluster. I was wondering if changing this property to “yes” will resolve this issue??

Hi Santosh,

setting the Cluster support to enabled might be worth a try.

This will also avoid duplicate processing as the 3 file polling ports will be synchronizing between each other in this case.

Regards,
Holger

Yes give it a try and also if you want to have a unique id for your transaction id I would suggest you to use generateUUID from version 8.0 and above.

Let us know your updates and see that best works out for you.

Yes give it a try and also if you want to have a unique id for your transaction id I would suggest you to use generateUUID from version 8.0 and above.

Let us know your updates and see that best works out for you.

Yes give it a try and also if you want to have a unique id for your transaction id I would suggest you to use generateUUID from version 8.0 and above.

Let us know your updates and see that best works out for you.