0

I have a Java running process that inserts data into a SqlServer DB table every 3s, using a PreparedStatement executeBatch(). (Process A)

// Get connection here
con.setAutoCommit(false);
// Create PreparedStatement here
ps = con.prepareStatement(stmt);
// Add to batch with loop
ps.executeBatch();
con.commit();

Also, every 5s another Java process is reading the new inserted data from that same table. (Process B)

// Get connection here
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
// Create PreparedStatement here
ps = con.prepareStatement(stmt);
rs = ps.executeQuery();
rs.setFetchSize(10000);
// While ResultSet has data, add it and return it

The main issue here is:

  • Process A is inserting about 300 rows
  • Process B sometimes is able to read the data while not all the rows in the batch were persisted (kinda in the middle of the batch), so it would return 80 rows and after another select right away I'll get the whole 300 rows.

Any ideas to what could I be possibly be improved?

evildonger
  • 3
  • 2
  • Well maybe the insert step should be the one using the serializable transaction, to ensure that reading threads either see the whole batch inserted, or nothing at all. You could also research a master-slave configuration, whereby the writes happen on the master, but reads will take place on the slave. This might improve performance. – Tim Biegeleisen Jul 05 '22 at 04:11
  • Perhaps can look into using a SQL notify. – CAMD_3441 Jul 05 '22 at 04:12
  • @TimBiegeleisen thanks! I'll try that and let you know – evildonger Jul 05 '22 at 04:19
  • @CAMD_3441 thanks! how would SQL notify the process B that the entirety of the inserts where made? Not very familiar with SQL notifications – evildonger Jul 05 '22 at 04:20
  • @TimBiegeleisen diggin a bit deeper into SQL Server documentation, it appears that the transaction isolation level doesn't apply to INSERT statements. – evildonger Jul 05 '22 at 15:28
  • @evildonger so I'm semi new to SQL notify but essentially you can use it like this. app1 does some sql command (insert, update, etc) then you want to notify another app about the sql command, you can send a SQL notify, and the other apps would be notified and can take actions as a result. – CAMD_3441 Jul 06 '22 at 00:04
  • @evildonger here's some documentation of it with Postgres https://www.postgresql.org/docs/current/sql-notify.html though other databases also have SQL notify – CAMD_3441 Jul 06 '22 at 00:05

0 Answers0