JDBC&死锁避免问题（基本） [英] JDBC & Deadlock avoidance question (Basic)

问题描述

我使用JDBC（通过Spring的JDBCTemplate）访问数据库中的少量表。虽然我还没有发生任何事情，但我担心死锁的可能性。

我的印象是有一种方法来指定锁定顺序查询访问多个表以防止死锁，但我不知道这是否是在创建我的表时在数据库级别设置的类型，或者如果我必须使用JDBC查询显式地执行某些操作。 p>

ie是否有一个全局设置或某事指定锁定顺序，或者如果它必须在每个查询/更新完成。

谢谢。

解决方案

这是在事务级别管理。你通常只有在有鸡蛋问题的手段时才会陷入僵局。也就是说有两个同时的行锁定事务，每个多个查询的结果取决于其他事务。如果在执行查询时其他交易尚未完成，则其他交易将无法完成其自己的查询。

我不知道JDBCTemplate如何管理事务，但是JDBC连接默认不是事务性的。设置 连接＃setAutoCommit（） 到 false （或将其配置为默认）并且当您调用 Connection＃commit（） 。

为了避免死锁，规则＃1避免将 SELECT 与 INSERT / UPDATE / DELETE 语句。当混合是第一次看到强制性，那么你应该至少尝试将其重写为单个/嵌套语句。这通常是可能的。这样，您不需要在事务中执行这些查询。

此外，一些数据库（如PostgreSQL和Oracle）可以自动检测死锁，并自动回滚其中一个事务，通常是后来启动的。在JDBC端，你会收到一个特定的 SQLException 。

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.

解决方案

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.

这篇关于JDBC&死锁避免问题（基本）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！