java - Oracle 数据库 SELECT ... FOR UPDATE 自动提交

Question

我正在针对 Oracle 数据库 (11g) 运行此查询。连接具有自动提交的默认值，即“真”。

Connection con = driver.connect(url, properties);
String query = "SELECT EMPID, NAME FROM EMPLOYEE FOR UPDATE";

Statement statement = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet resultSet = statement.executeQuery(query);

SELECT... FOR UPDATE声明它“锁定选定的行，以便其他用户在您结束交易之前无法锁定或更新这些行。”使用自动提交应该在查询执行后立即进行。但是在连接关闭之前锁一直存在。

(我通过在 sqlplus 上运行查询 select * FROM DBA_DML_LOCKS WHERE NAME = 'EMPLOYEE'; 来检查这一点。)

这是一个错误还是有人可以解释为什么？提前谢谢你。

Answer 1

如 documentation 中所述:

the default is for a SQL statement to be committed when it is completed, not when it is executed. A statement is completed when all of its result sets and update counts have been retrieved. In almost all cases, however, a statement is completed, and therefore committed, right after it is executed

另一个documentation :

Enabling auto-commit may be more convenient, but gives you less control. For example, you have no option to roll back changes. In addition, some SQLJ or JDBC features are incompatible with auto-commit mode. For example, you must disable the auto-commit flag for update batching or SELECT FOR UPDATE syntax to work properly.

所以对于这种查询类型你应该使用手动提交