wood burning stoves 2.0*
The moose likes JDBC and the fly likes delay in db insertion Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "delay in db insertion" Watch "delay in db insertion" New topic
Author

delay in db insertion

shweta misra
Greenhorn

Joined: Jun 06, 2012
Posts: 10
Hi,

Following is the snippet of the java code which inserts a record into a table.
The insert query insertCDRSQL2 is also given below. When this code is executed the observation is that the logger CDR generation successfully is printed at 11.31 pm in the log file but in the db the column TRANSACTION_DATETIME of the record inserted shows 11.34 pm. There is a 3 min delay in actually writing the record. This happens intermittently for some inserts.

I want to know what could be the problem here and why there could be a delay.




try {

conn = ocsgds.getConnection();
pre_stmt = conn.prepareStatement(insertCDRSQL2);


…..
pre_stmt.executeUpdate();
logger.info("CDR generation successfully");

}catch (Exception e) {
logger.error("Error in generating CDRs in the DB",e);
throw e;

}





INSERT INTO CUSTOM_CDR(TRANSACTION_ID,TRANSACTION_DATETIME, CIRCLE ......)
" VALUES(?,systimestamp,??........)

Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

1. Are the clock of the database server and the clock of the machine where your application runs set at the same time ?
2. When is the transaction committed ?


[My Blog]
All roads lead to JavaRanch
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

The log message is the same for every call. How do you match the log messages with inserted rows? How often is the method called?
shweta misra
Greenhorn

Joined: Jun 06, 2012
Posts: 10
Hi Christophe,
We assumed the clocks should be the same because we see this issue intermittently. not for all the requests. I will however get the clocks checked.
Since auto commit is on by default we havent committed the transactions explicitly.


Hi Martin,
The log message actually looks something like this logger.info("CDR generated successfully for transcation id "+transcationId);
We compare the transactionid in the log and the one in the TRANSACTION_ID column of the table
We have a load of 400-500TPS and a record is written in the db for every successfull transaction. So this method is called for every successfull transaction. in our analysis so far , for every 50 successful transactions we see this issue in 7-8 transactions and the delay is always 3 min.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Is the message to the log actually written before or after the insert statement has been executed?

(If the message is actually logged after the insert statement returns, I wouldn't have any explanation for that. If it is logged before the insert statement returns, well - something in the database or on the network might cause delay in the insert. I'm not sure I would be able to help with that myself, but at least we would know what we're looking for.)
shweta misra
Greenhorn

Joined: Jun 06, 2012
Posts: 10
The message is logged after the insert statement is executed.

when we execute an insert statement , is the record immediately written into the db or is it put into some db queue from where its picked and then executed ?? in our case , the logger is printed after the insert statement. does this mean that the record should be written into the db by that time or is it possible that the query is accepted by the db but not processed
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 9924
    
158

The insert query insertCDRSQL2 is also given below. When this code is executed the observation is that the logger CDR generation successfully is printed at 11.31 pm in the log file but in the db the column TRANSACTION_DATETIME of the record inserted shows 11.34 pm. There is a 3 min delay in actually writing the record. This happens intermittently for some inserts.


Your code shows that you are getting a connection from a datasource:



DataSources typically enroll the connection in a transaction. The data is committed only when the transaction completes. It does not necessarily have to be when the method which does the insert ends. It all depends on the transaction boundaries i.e. when the transaction is created and completed. You'll have to take a look at the entire code flow to understand what controls the transaction lifespan.

[My Blog] [JavaRanch Journal]
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Jaikiran Pai wrote:DataSources typically enroll the connection in a transaction. The data is committed only when the transaction completes. It does not necessarily have to be when the method which does the insert ends. It all depends on the transaction boundaries i.e. when the transaction is created and completed. You'll have to take a look at the entire code flow to understand what controls the transaction lifespan.

True, but the systimestamp function is evaluated at the time the insert statement happens, isn't it? Even if the commit happens a minute or an hour later, the value in the column should match the time when the insert statement was executed.

Which database are you using, Shweta? Do you have any triggers defined in your database? Do you have any other processes that could modify the table?

Edit: I don't know of any database which would queue up statements, returned to the caller and then processed them, at least unless explicitly instructed to process the query asynchronously. Furthermore, I'm not aware of JDBC supporting asynchronous executions.
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 9924
    
158

Martin Vajsar wrote:
Jaikiran Pai wrote:DataSources typically enroll the connection in a transaction. The data is committed only when the transaction completes. It does not necessarily have to be when the method which does the insert ends. It all depends on the transaction boundaries i.e. when the transaction is created and completed. You'll have to take a look at the entire code flow to understand what controls the transaction lifespan.

True, but the systimestamp function is evaluated at the time the insert statement happens, isn't it? Even if the commit happens a minute or an hour later, the value in the column should match the time when the insert statement was executed.

That's a good point. I hadn't paid attention to that part.


shweta misra
Greenhorn

Joined: Jun 06, 2012
Posts: 10
We are using Oracle 10g DB and no ... we are not using any triggers or process to modify this table.

btw we just found that the clock of one our DB instance was actually fast. so we have raised a request to bring it in sync. will then monitor the system.
hopefully this will resolve the issue. thanks for this pointer.


Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

So to sum it up: you have a RAC and records inserted by one instance appear right, while records inserted by the other instance have the insertion date set to the future. Neat!

A quick search on the internet seems to indicate that unsynced clock on a RAC can potentially lead to other, much more serious problems. You probably should set up a time server to keep the clocks in sync (preferably over all of your infractructure, of course )
 
wood burning stoves
 
subject: delay in db insertion
 
Similar Threads
Too many connections
Insert statement not inserting correct value
could not insert
which generator type to use..?
JDBC THIN gives Oracle invalid character error, though SQL works fine in TOAD