Select query not working with Jdbc for transactional calls.

[kartik krishnan]

Hi,

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?

Any advice would be appreciated.

Thanks,

Kartik

[Jan Cumps]

Are the insert and the select done with the same database connection?

[Jelle Klap]

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.

[kartik krishnan]

@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?

[Scott Selikoff]

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?

[kartik krishnan]

@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.

[Scott Selikoff]

Have you tried using getGeneratedKeys()?