I'm writing my own DAO layer with Spring JDBC.
I have a method called T insert(T entity); which inserts an entity and returns the updated version to the user. In my code, the logic is made up of two parts, a call to NamedParameterJdbcTemplate.update to save the entity and then NamedParameterJdbcTemplate.queryForObject to retrieve the updated entity from the database.
The first part executes a query similar to this:
INSERT INTO client (email_address, company_name)
VALUES ('some@email.com', 'some company name');
And the second part executes one similar to this:
SELECT * FROM client t
WHERE t.id = currval('client_id_seq');
I'm using Postgres, where client is actually an updatable view, not a table, and client_id_seq is a manually defined sequence to make client behave like a table.
And it works fine, except for the fact that those two statements aren't transactional. In theory, between the time of executing the first and second statement, another parallel statement could be executed, which alters currval('client_id_seq'), making the result of the second part of my method erroneous.
Is there a way of executing and fetching data all at once with Spring Jdbc? I tried multi-statement queries and the driver complained that there are multiple ResultSets, I also tried writing a transaction query like this:
BEGIN;
INSERT INTO client (email_address, company_name)
VALUES ('some@email.com', 'some company name');
SELECT * FROM client t
WHERE t.id = currval('client_id_seq');
COMMIT;
And the driver complained that the ResultSet was null.
How do I ensure consistency here between these two fetches?
If I am understanding @Mar-Z's answer correctly, if the value of client_id_seq is 0, and SESSION 1 calls nextval('client_id_seq'), then SESSION 2 calls nextval('client_id_seq'), the ACTUAL value of client_id_seq is now 2. BUT, if I call currval('client_id_seq') in SESSION 1, I'll get back 1, and if I call currval('client_id_seq') in SESSION 2, I'll get back a 2. That seems promising.
The only other thing I might be worried about is, I know that Spring Boot uses a connection pool (HikariCP by default I think), and 1 authenticated connection = 1 postgres session. Could it be possible that, lets say we have some connection SESS_1 in the connection pool. The first update call to insert the client is submitted to the DB via SESS_1. INBETWEEN the first and second call of my insert() method, another process also submits some query to the DB via SESS_1, hence altering the currval('client_id_seq') for SESS_1, and then the second part of my method runs the SELECT query, again, on SESS_1, so in the end, I have erroneous data. Or even worse, what if the connection pool decides to close SESS_1 and supply a different connection, say SESS_2, to the second part of my insert() method?
Does Spring JDBC have some things to assure this doesn't happen? Do I need to configure anything?
When marking a method with @Transactional, Spring Boot binds the connection to the thread which is running the method, and only releases the connection back to the pool when the transaction completes or is reversed. So in theory, annotating my insert() method with @Transactional should guarantee data consistency.
You don't need to worry about transactions and parallel changes to the sequence. With the call of currval('client_id_seq') you will get exactly the value of the sequence which has been obtained with nextval('client_id_seq') in YOUR current session only. Other sessions will use different value. This is garrantied by the database.
More details here: https://www.postgresql.org/docs/current/functions-sequence.html