JDBC & Deadlock avoidance question (Basic)

Question

I'm using JDBC (through Spring's JDBCTemplate) to access a small number of tables in a database. Although I haven't had anything happen yet, I'm concerned about the possibility of deadlock.

I was under the impression there was a way to specify a lock order for queries that access multiple tables for deadlock avoidance, but I don't know if this is the type of thing that gets set up at the DB level when creating my tables, or if I have to do something explicitly with my JDBC queries.

i.e. is there a global setting or something for specifying lock order, or if it has to be done on each query/update.

Thanks.

Answer 1

This is to be managed at transaction level. You usually only risk a deadlock when there's means of a chicken-egg issue. I.e. there are two simultaneous row-locking transactions with each multiple queries whose results depends on the other transaction. If the other transaction isn't finished while the query is been executed, then the other transaction won't be able to finish its own query.

I am not sure how JDBCTemplate manages the transactions, but a JDBC connection is by default not transactional. Once you set the Connection#setAutoCommit() to false (or configure it to be by default), then the transaction will start and it will finish when you call Connection#commit().

To avoid deadlocks, rule #1 is avoiding mixing SELECT with INSERT/UPDATE/DELETE statements in a single transaction. When mixing is -at first sight- mandatory, then you should at least try to rewrite it into a single/nested statement. This is often just possible. This way you don't need to execute those queries in a transaction.

Further, some databases like PostgreSQL and Oracle can autodetect deadlocks and will automatically rollback one of the transactions, usually the one which was initiated later. In the JDBC end you will receive a specific SQLException for that.