I am working on a web app which requires the user to save some web form information i(say customer) into the database using insert sql query (with PrepraredStatement api). and then load it immediately with some predefined db column values using a select query (again with PreparedStatement api). This must be made transactional. If the insert succeeds but select fails, then the entire operation must be rolled back.
The service layer method is calling both insert and select methods in DAO class. For my connection, I have set autocommit to false and nothing else. After both operations are executed without errors, I call connection.commit() explicitly. If an exception is thrown during insert or select, I call rollback.
My problem is that after I call my select query after running an insert query, I don't see any results. Is there anything I am doing wrong? Do I have to change anything for inserts to work?
This is probably down to the transaction isolation level of the Connection, which defaults to TRANSACTION_READ_COMMITTED.
Try specifying TRANSACTION_READ_UNCOMMITTED using Connection#setTransactionIsolation(), which should allow you to read the uncommitted data you previously inserted within the same transaction.
Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life.
Joined: Nov 19, 2006
@Jelle Thanks for replying. We are using a same connection instance. The connection is stored as a thread local object and is acquired by the data access layer.
@Jan Thanks for the advice. To clarify if I set the isolation level to READ_UNCOMMITTED, my select query will return the output before commit is called. So if my insert operation is rolled back, then the select query will not return anything. On the other hand, if once commit is executed, the insert operation changes are persisted into the database. Have I understood this correctly?
This is one of those situations where you shouldn't fix the issue with database settings (reading uncommitted data for example) but with your code. Why are you trying to read data you just inserted? Why not use the data in memory you inserted into the table instead?
@Scott, Thanks for replying. We have to insert into the database because the database generates some default values for columns not populated with JDBC calls. We need that information to be displayed. One of them is the customer id. The customer id is uniquely associated with a customer and the customer should have that id when logging into our application.