java - 无法从 jdbc 获得对 oracle 表的独占锁

Question

我正在用 jsp 编写一个简单的代码来从表中删除一些数据。

当我运行这条语句时，

lock table tbl_Booking, tbl_Ticket in exclusive mode

在 oracle 控制台中，它工作正常。

现在当我在 java 代码中使用它时，我无法获得锁。

代码如下:

String req = request.getParameter("T_NO");
    if (con != null){
        String sql = "lock table tbl_Booking, tbl_Ticket in exclusive mode";
        String sql1 = "DELETE FROM tbl_Ticket WHERE T_NO=?";
        String sql2 = "DELETE FROM tbl_Booking WHERE T_NO IN ?";
        Statement stmt = con.createStatement();
        PreparedStatement ps1 = con.prepareStatement(sql1);
        PreparedStatement ps2 = con.prepareStatement(sql2);
        ps1.setString(1, req);
        ps2.setString(1, req);
        if (stmt.execute(sql)){
            if ((ps1.executeUpdate() == 1)&&(ps2.executeUpdate() == 1)){
                response.setContentType("text");
                response.setCharacterEncoding("UTF-8");
                response.getWriter().write("Your ticket has been cancelled!");
            }
            else{
                response.setContentType("text");
                response.setCharacterEncoding("UTF-8");
                response.getWriter().write("Couldn't Cancel your ticket!");
            }
        }
        else{
            response.setContentType("text");
            response.setCharacterEncoding("UTF-8");
            response.getWriter().write("Couldn't get lock!");
        }
    }

每次我得到，

Couldn't get lock!

作为输出。

固定代码:

<%@page import="java.sql.Statement"%>
<%@page contentType="java" pageEncoding="UTF-8"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@include file="oracle_connection.jsp"%>

<%    String req = request.getParameter("T_NO");
    if (con != null) {
        try {
            String sql = "lock table tbl_Booking, tbl_Ticket in exclusive mode";
            String sql1 = "DELETE FROM tbl_Ticket WHERE TICKET_NO=?";
            String sql2 = "DELETE FROM tbl_Booking WHERE TICKET_NO=?";
            Statement stmt = con.createStatement();
            PreparedStatement ps1 = con.prepareStatement(sql1);
            PreparedStatement ps2 = con.prepareStatement(sql2);
            ps1.setString(1, req);
            ps2.setString(1, req);
            stmt.execute(sql);
            if (ps1.executeUpdate() == 1) {
                ps2.executeUpdate();
                response.setContentType("text");
                response.setCharacterEncoding("UTF-8");
                response.getWriter().write("Your ticket has been cancelled!");
            } else {
                response.setContentType("text");
                response.setCharacterEncoding("UTF-8");
                response.getWriter().write("Couldn't Cancel your ticket!");
            }

        } catch (Exception e) {
            response.setContentType("text");
            response.setCharacterEncoding("UTF-8");
            response.getWriter().write(e.toString());
        }
    }
%>

Answer 1

尝试将其包装在 try-catch block 中。

仅仅因为它返回 false 并不意味着它失败了。

try
    {
        stmt.execute(sql);
            if ((ps1.executeUpdate() == 1)&&(ps2.executeUpdate() == 1)){
                response.setContentType("text");
                response.setCharacterEncoding("UTF-8");
                response.getWriter().write("Your ticket has been cancelled!");
            }
            else{
                response.setContentType("text");
                response.setCharacterEncoding("UTF-8");
                response.getWriter().write("Couldn't Cancel your ticket!");
            }
    }
    catch (Exception e)
    {
        response.setContentType("text");
        response.setCharacterEncoding("UTF-8");
        response.getWriter().write(e.getMessage());
    }