Siaw Ys Siaw Ys - 4 months ago 11
Java Question

Record inserted but still not found

I was currently using java spring jdbctemplate to insert and select record. However, Now the system have go for production, some issue has happen. This system will prevent record duplicate by select the record by certain key to check is it already exist. However, I notice the 1st transaction after inserted into database, then the 2nd transaction come in, jdbc template select still show that record was not found? I notice this will happen if the 2 transaction was submitted within a milliseconds different...

The record time was

e.g.

1st Record insert - 15/6/17 12:22:39,986 - insert success

2nd record select - 15/6/17 12:22:44,680 - search record show not found?

Sample insert log



15/6/17 12:22:39,937 DEBUG [30:http-nio-8080-exec-2] (org.springframework.jdbc.core.JdbcTemplate:869) Executing prepared SQL update
15/6/17 12:22:39,938 DEBUG [30:http-nio-8080-exec-2] (org.springframework.jdbc.core.JdbcTemplate:616) Executing prepared SQL statement [INSERT INTO ...;]
15/6/17 12:22:39,950 DEBUG [30:http-nio-8080-exec-2] (org.springframework.jdbc.core.JdbcTemplate:879) SQL update affected 1 rows
15/6/17 12:22:39,951 DEBUG [30:http-nio-8080-exec-2] (org.springframework.jdbc.core.JdbcTemplate:869) Executing prepared SQL update
15/6/17 12:22:39,952 DEBUG [30:http-nio-8080-exec-2] (org.springframework.jdbc.core.JdbcTemplate:616) Executing prepared SQL statement [INSERT INTO ...;]
15/6/17 12:22:39,965 DEBUG [30:http-nio-8080-exec-2] (org.springframework.jdbc.core.JdbcTemplate:879) SQL update affected 1 row
15/6/17 12:22:39,970 DEBUG [30:http-nio-8080-exec-2] (org.springframework.jdbc.datasource.DataSourceUtils:327) Returning JDBC Connection to DataSource


Sample select log



15/6/17 12:22:44,680 DEBUG [29:http-nio-8080-exec-1] (org.springframework.jdbc.core.JdbcTemplate:682) Executing prepared SQL query
15/6/17 12:22:44,680 DEBUG [29:http-nio-8080-exec-1] (org.springframework.jdbc.core.JdbcTemplate:616) Executing prepared SQL statement [SELECT * FROM ...]


Please note that this system was set in 2 different server by using load balancing. They both point to the same database. The insert was completed on server 1 because it already return the connection to the jdbc connection pool. A new transaction come in the server 2, select but no record found?

I wonder why it still cannot found event after insert was finish? Is it because of the time delay?

Answer Source

This question had been resolved. It was due to the server time not consistent. Thanks RealSkeptic