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).
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.
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?
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.
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??
