数据库行锁实验一：两个删除操作处理的记录存在交集则会死锁

删除程序之一，用于删除id=1的一条记录，在删除后停住断点，另一个删除程序会进行不下去。

package com.hy.multidelete;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

public class Deleter {
    private static Logger log = Logger.getLogger(Deleter.class);
    
    public void doDelete() {
        Connection conn = null;
        Statement stmt = null;
        
        try{
            Class.forName(DBParam.Driver).newInstance();
            conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            
            String sql="delete from TestTB17 where id=1";
            int deleted=stmt.executeUpdate(sql);
            
            // 在此处停住断点，另一个删除程序CleanExpiredMocker会执行不下去
            log.info("Deleter deleted "+deleted+" records.");
            
            // 直到接下来回滚或提交CleanExpiredMocker才可以执行
            conn.rollback();
            log.info("Rollbacked.");
        } catch (Exception e) {
            System.out.print(e.getMessage());
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                System.out.print("Can't close stmt/conn because of " + e.getMessage());
            }
        }
    }
    
    public static void main(String[] args) {
        Deleter d=new Deleter();
        d.doDelete();
    }
}

另一删除程序，用于删除整表全部记录记录，此删除与上一程序删除id=1的操作存在交集，因此如果deleter先执行，要等deleter提交或回滚后，此程序才能执行下去：

package com.hy.multidelete;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

public class CleanExpiredMocker {
    private static Logger log = Logger.getLogger(CleanExpiredMocker.class);
    
    public void doClean() {
        Connection conn = null;
        Statement stmt = null;
        
        try{
            Class.forName(DBParam.Driver).newInstance();
            conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);
            stmt = conn.createStatement();
            
            String sql="delete from TestTB17 where 1=1";
            int deleted=stmt.executeUpdate(sql);
            log.info("CleanExpiredMocker deleted "+deleted+" records.");;    
        } catch (Exception e) {
            System.out.print(e.getMessage());
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                System.out.print("Can't close stmt/conn because of " + e.getMessage());
            }
        }
    }
    
    public static void main(String[] args) {
        CleanExpiredMocker c=new CleanExpiredMocker();
        c.doClean();
    }
}

 Select ...for update 也有同样阻断其它session(操作记录与select...for update语句的结果集存在交集)的效果:

package tablelock;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

public class Selecter {
    private static Logger log = Logger.getLogger(Selecter.class);
    
    public void doDelete() {
        Connection conn = null;
        Statement stmt = null;
        
        try{
            Class.forName(DBParam.Driver).newInstance();
            conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            
            String sql="select * from TestTB17 for update";
            stmt.executeUpdate(sql);
            
            log.info("Will block other session before commit/rollback.");
            
            conn.rollback();
            log.info("Rollbacked.");
        } catch (Exception e) {
            System.out.print(e.getMessage());
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                System.out.print("Can't close stmt/conn because of " + e.getMessage());
            }
        }
    }
    
    public static void main(String[] args) {
        Selecter d=new Selecter();
        d.doDelete();
    }
}

往下是一些数据准备工作，可以一笑而过。

建表程序：

package com.hy.multidelete;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

/**
 * 此类用于管理表的创建和销毁
 * @author 逆火
 *
 * 2019年11月16日 下午5:40:22
 */
public class TableHandler {
    private static Logger log = Logger.getLogger(TableHandler.class);
    
    private String[] tablenames= {    "TestTB17",
                                    
                                  };
    
    /**
     * Get the create table ddl of a table
     * @param table
     * @return
     */
    private String getCreateTbSql(String table) {
        StringBuilder sb=new StringBuilder();
        sb.append("CREATE TABLE "+table);
        sb.append("(");
        sb.append(""ID" NUMBER(8,0) not null primary key,");
        sb.append(""NAME" NVARCHAR2(60) not null,");
        sb.append(""AGE" NUMBER(3,0) DEFAULT 0 not null ,");
        sb.append(""CREATEDTIME" TIMESTAMP (6) not null");
        sb.append(")");
        
        return sb.toString();
    }
    
    /**
     * Judge if a table is exist
     * @param table
     * @param stmt
     * @return
     * @throws SQLException
     */
    private boolean isTableExist(String table,Statement stmt) throws SQLException {
        String sql="SELECT COUNT (*) as cnt FROM ALL_TABLES WHERE table_name = UPPER('"+table+"')";
        
        ResultSet rs = stmt.executeQuery(sql);
        
        while (rs.next()) {
            int count = rs.getInt("cnt");
            return count==1;
        }
        
        return false;
    }
    
    /**
     * Crate tables
     */
    public void createTables() {
        Connection conn = null;
        Statement stmt = null;
        
        try{
            Class.forName(DBParam.Driver).newInstance();
            conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);
            stmt = conn.createStatement();
            
            int i=0;
            for(String table:tablenames) {
                i++;
                
                String sql=getCreateTbSql(table);
                stmt.executeUpdate(sql);
                
                if(isTableExist(table,stmt)) {
                    log.info("#"+i+" "+table+" created.");
                }
                
                
            }
    
        } catch (Exception e) {
            System.out.print(e.getMessage());
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                System.out.print("Can't close stmt/conn because of " + e.getMessage());
            }
        }
    }
    
    /**
     * Remove all the tables
     */
    public void dropTables() {
        Connection conn = null;
        Statement stmt = null;
        
        try{
            Class.forName(DBParam.Driver).newInstance();
            conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);
            stmt = conn.createStatement();
            
            int i=0;
            for(String table:tablenames) {
                i++;
                String sql="drop table "+table;
                stmt.executeUpdate(sql);
                
                if(isTableExist(table,stmt)==false) {
                    log.info("#"+i+" "+table+" dropped.");
                }
            }
    
        } catch (Exception e) {
            System.out.print(e.getMessage());
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                System.out.print("Can't close stmt/conn because of " + e.getMessage());
            }
        }
    }
    
    /**
     * Kick start
     * @param args
     */
    public static void main(String[] args) {
        TableHandler th=new TableHandler();
        th.createTables();
    }
}

给表插入五百记录的程序：

package com.hy.multidelete;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import com.hy.DBParam;

class TbCnt{
    String tb;// tablename
    int cnt;// count should be inserted
    
    public TbCnt(String tb,int cnt) {
        this.tb=tb;
        this.cnt=cnt;
    }
}

class Field{
    String name;    // 列名
    int size;        // 列容量
    String type;    // 列数据类型
    boolean allowNull;// 列是否允許为空
}
/**
  *      输入表名和需要插入的记录数，自动将所需记录插入表，不需要指定主键和必填字段
 * @author 逆火
 *
 * 2019年11月23日 下午2:19:02
 */
public class TableAutoInserter {
    private static Logger log = Logger.getLogger(TableAutoInserter.class);
    
    private final int BatchSize=250;// 一次性插入记录数
    
    private List<TbCnt> tbCntList;// 容纳表名和插入数量的链表
    
    /**
     * 构造函数
     */
    public TableAutoInserter() {
        tbCntList=new ArrayList<TbCnt>();
    }
    
    /**
     * 加入一张要插入的表
     * @param tc
     * @return
     */
    public TableAutoInserter add(TbCnt tc) {
        tbCntList.add(tc);
        return this;
    }
    
    /**
     * 给已有的所有表插值
     */
    public void insertAllTables() {
        Connection conn = null;
        Statement stmt = null;
        
        try{
            Class.forName(DBParam.Driver).newInstance();
            conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);
            stmt = conn.createStatement();
            log.info("Begin to access "+DBParam.DbUrl+" as "+DBParam.User+"...");
            
            int index=0;
            for(TbCnt tc:tbCntList) {
                String table=tc.tb;
                int count=tc.cnt;
                long startTime = System.currentTimeMillis();
                
                truncateTable(table,conn,stmt);
                List<TypeField> fieldList=fetchTableFields(conn,stmt,table,false);
                
                insertDataToTable(index,table,count,fieldList,conn,stmt);

                if(isAllInserted(count,table,stmt)) {
                    long endTime = System.currentTimeMillis();
                    log.info("#"+index+" "+count+" records were inserted to table:'" + table + "' used " + sec2DHMS(startTime,endTime) );
                }
                
                index++;
            }
            
        } catch (Exception e) {
            System.out.print(e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                log.error("Can't close stmt/conn because of " + e.getMessage());
            }
        }
    }
    
    /**
               * 从表中取字段信息
     * @param conn
     * @param stmt
     * @param table
     * @param isFetchall:此参数为真时取所有字段，为假时取非空字段
     * @return
     * @throws Exception
     */
    private List<TypeField> fetchTableFields(Connection conn,Statement stmt,String table,boolean isFetchall)  throws SQLException{
        List<TypeField> fields=new ArrayList<TypeField>();
        
        String sql = "select * from "+table+"";
        
        ResultSet rs = stmt.executeQuery(sql);
        ResultSetMetaData rsMetadata = rs.getMetaData();

        int count = rsMetadata.getColumnCount();
        for (int i=0; i<count; i++) {
            int index=i+1;
            
            String columnName = rsMetadata.getColumnLabel(index);// 列名
            String columnType = rsMetadata.getColumnTypeName(index);// 列数据类型
            boolean allowNull=(rsMetadata.isNullable(index)!=0);// 列是否允許为空
            //int size =rsMetadata.getColumnDisplaySize(index);// 列容量 此值在数据超过列容量限制时能用到
            
            TypeField tf=new TypeField();
            tf.field=columnName;
            tf.type=getShortType(columnName,columnType);
            
            if(isFetchall==true) {
                fields.add(tf);
            }else {
                if(allowNull==false) {
                    fields.add(tf);
                }
            }
        }
        
        rs.close();

        return fields;
    }
    
    /**
     * 得到列类型的短名称，此短名称在getInsertSql函数中会用到
     * @param columnType
     * @return
     * @throws SQLException
     */
    private String getShortType(String field,String columnType) throws SQLException{
        if(field.equalsIgnoreCase("id")) {
            return "PK";
        }
        
        if(columnType.equalsIgnoreCase("timestamp")) {
            return "DT";
        }else if(columnType.equalsIgnoreCase("number")) {
            return "NB";
        }else if(columnType.equalsIgnoreCase("nvarchar2")) {
            return "CH";
        }else {
            throw new SQLException("Unknown columnType:'"+columnType+"'.");
        }
    }
    
    /**
     * delete all data in a table quickly
     * @param tableName
     * @param conn
     * @param stmt
     * @throws SQLException
     */
    private void truncateTable(String tableName,Connection conn,Statement stmt) throws SQLException{
        String sql="truncate table "+tableName;
        stmt.execute(sql);
        log.info("truncated table:"+tableName);
    }
    
    /**
     * Insert date to a table
     * @param tableIndex
     * @param tableName
     * @param count
     * @param innerArr
     * @param conn
     * @param stmt
     * @throws SQLException
     */
    private void insertDataToTable(int tableIndex,String tableName,int count,List<TypeField> typefields,Connection conn,Statement stmt) throws SQLException{
       
        List<String> fields=new ArrayList<String>();
        List<String> values=new ArrayList<String>();
        int index=0;
        for(TypeField tf:typefields) {
            fields.add(tf.field);
            values.add("''{"+index+"}''");
            index++;
        }
        
        int interval=2*365*24*60*60/count;// 涓ゅ勾鐨勭�鏁伴櫎浠ユ�讳釜鏁板嵆涓洪棿闅�
        
        index=0;
        int times=count/BatchSize;
        for(int i=0;i<times;i++) {
            StringBuilder sb=new StringBuilder();
            sb.append("INSERT ALL ");
            
            for(int j=0;j<BatchSize;j++) {
                index=i*BatchSize+j;
                sb.append(getInsertSql(tableName,typefields,index,interval));
            }
            
            sb.append(" select * from dual");
            String sql = sb.toString();
            
            long startTime = System.currentTimeMillis();
            stmt.executeUpdate(sql);
            long endTime = System.currentTimeMillis();
            log.info("#"+tableIndex+"-"+i+" "+BatchSize+" records inserted to '"+tableName+"' used " + sec2DHMS(startTime,endTime));
        }
    }
    
    /**
     * get insert sql
     * @param tableName
     * @param typefields
     * @param index
     * @return
     */
    private String getInsertSql(String tableName,List<TypeField> typefields,int index,int interval) {
        String currTime=getDatetimeBefore(index,interval);
        
        StringBuilder sb=new StringBuilder();
        sb.append(" INTO "+tableName+"(");
        List<String> fields=new ArrayList<String>();
        for(TypeField tf:typefields) {
            fields.add(tf.field);
        }
        sb.append(String.join(",",fields));
        
        sb.append(") values(");
        List<String> values=new ArrayList<String>();
        for(TypeField tf:typefields) {
            if(tf.type.equals("PK")) {
                values.add("'"+String.valueOf(index)+"'");
            }else if(tf.type.equals("CH")) {
                values.add("'0'");
            }else if(tf.type.equals("NB")) {
                values.add("'0'");
            }else if(tf.type.equals("DT")) {
                values.add("to_date('"+currTime+"','yyyy-MM-dd HH24:mi:ss')");
            }
        }
        sb.append(String.join(",",values));
        sb.append(")");
        
        String insertSql=sb.toString();
        return insertSql;
    }
    
    /**
     * get datetime n seconds before
     * @param n
     * @param interval
     * @return
     */
    private static String getDatetimeBefore(int n,int interval) {
        try {
            Calendar now = Calendar.getInstance();
            
            now.add(Calendar.SECOND,-n*interval);//鏃ユ湡鍑忓幓n*10绉�
            
            Date newDate=now.getTime();
            
            SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String retval = sdf.format(newDate);
            return retval;
        }
        catch(Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }
    
    /**
     * judge if all records are inserted
     * @param count
     * @param table
     * @param stmt
     * @return
     * @throws SQLException
     */
    private boolean isAllInserted(int count,String table,Statement stmt) throws SQLException {
        String sql="SELECT COUNT (*) as cnt FROM "+table;
        
        ResultSet rs = stmt.executeQuery(sql);
        
        while (rs.next()) {
            int cnt = rs.getInt("cnt");
            return cnt==count;
        }
        
        return false;
    }
    
    /**
     * change seconds to DayHourMinuteSecond format
     * @param stratMs
     * @param endMs
     * @return
     */
    private static String sec2DHMS(long stratMs,long endMs) {
        String retval = null;
        long secondCount=(endMs-stratMs)/1000;
        
        long days = secondCount / (60 * 60 * 24);
        long hours = (secondCount % (60 * 60 * 24)) / (60 * 60);
        long minutes = (secondCount % (60 * 60)) / 60;
        long seconds = secondCount % 60;
    
        if (days > 0) {
            retval = days + "d" + hours + "h" + minutes + "m" + seconds + "s";
        } else if (hours > 0) {
            retval = hours + "h" + minutes + "m" + seconds + "s";
        } else if (minutes > 0) {
            retval = minutes + "m" + seconds + "s";
        } else {
            retval = seconds + "s";
        }
    
        return retval;
    }
    
    protected static final class TypeField{
        String type;
        String field;
    }
    
    public static void main(String[] args) {
        TableAutoInserter ti=new TableAutoInserter();
        
        String[] tablenames= {    "TestTB17",};
        
        for(String table:tablenames) {
            ti.add(new TbCnt(table,500));
        }

        ti.insertAllTables();
    }
}

Oracle数据库的连接参数：

package com.hy.multidelete;

/**
 * 数据库连接参数
 * @author 逆火
 *
 * 2019年11月16日 上午8:09:24
 */
public final class DBParam {
    public final static String Driver = "oracle.jdbc.driver.OracleDriver";
    public final static String DbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    public final static String User = "ufo";
    public final static String Pswd = "1234";
}

整体程序下载：

 https://files.cnblogs.com/files/xiandedanteng/multidelete20191128.rar

--END-- 2019年11月28日20:53:39