模拟一次数据库死锁（并发更新数据）

1、使用spring-jdbc简单实现；
代码实现：

package test.Dao;

import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CountDownLatch;


public class JdbcTemplateDao {
    private JdbcTemplate jdbcTemplate;
    //初始化jdbcTemplate
    public JdbcTemplateDao() {
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("/spring.xml");
        this.jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate");
    }
    //更新1
    public void update1() {
        jdbcTemplate.execute("update t_order set order_id=order_id+1 where id=257025");
        System.out.println("update1 commit ...");
    }
    //更新2
    public void update2() {
        jdbcTemplate.execute("update t_order set order_id=order_id+1 where order_id=726821956;");
        System.out.println("update2 commit ...");
    }
    //并发执行
    public void concurrencyUpdate( List<Runnable>  task)throws Exception{
        
        final CountDownLatch start=new CountDownLatch(1);//计数器 设1
        final CountDownLatch end=new CountDownLatch(task.size());

        for ( Runnable run:task) {
            myThread thread = new myThread(run, start, end);
            thread.start();
        }
        long startTime = System.nanoTime();
        System.out.println(startTime + " [" + Thread.currentThread() + "] All thread is ready, concurrent going...");
        start.countDown();//计数器减一，两个线程同时执行
        end.await();
        System.out.println("****end*****");
    }
    public static void main(String[] args) throws Exception{
        final JdbcTemplateDao dao = new JdbcTemplateDao();
        Runnable run1=new Runnable() {
            public void run() {
                dao.update1();
            }
        };
        Runnable run2=new Runnable() {
            public void run() {
                dao.update2();
            }
        };
        List<Runnable> list=new ArrayList<Runnable>();
        list.add(run1);
        list.add(run2);
        dao.concurrencyUpdate(list);
    }
    private class  myThread extends Thread{
        private Runnable run;
        private CountDownLatch start;
        private CountDownLatch end;
        myThread(Runnable run,CountDownLatch start,CountDownLatch end){
            this.run=run;
            this.start=start;
            this.end=end;
        }
        @Override
        public void run() {
            try {
                start.await();//线程等待一起执行
                run.run();
            } catch (InterruptedException e) {
                System.out.println("error...");
            } finally {
                end.countDown();
            }

        }
    }
}

错误堆栈信息：

1099704237434000 [Thread[main,5,main]] All thread is ready, concurrent going...
update1 commit ...
五月 04, 2020 5:46:17 下午 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
信息: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
五月 04, 2020 5:46:17 下午 org.springframework.jdbc.support.SQLErrorCodesFactory <init>
信息: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]
****end*****
Exception in thread "Thread-2" org.springframework.dao.DeadlockLoserDataAccessException: StatementCallback; SQL [update t_order set order_id=order_id+1 where order_id=726821956;]; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:263)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:416)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:440)
	at test.Dao.JdbcTemplateDao.update2(JdbcTemplateDao.java:30)
	at test.Dao.JdbcTemplateDao$2.run(JdbcTemplateDao.java:56)
	at test.Dao.JdbcTemplateDao$myThread.run(JdbcTemplateDao.java:77)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
	at com.mysql.jdbc.Util.getInstance(Util.java:387)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:948)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2546)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2504)
	at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:840)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:740)
	at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:432)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:405)
	... 4 more

Process finished with exit code 0

数据库死锁原因：
1、t_order 表中 id为主键索引，order_id 为普通索引；
2、A步:update t_order set order_id=order_id+1 where id=257025 语句按主键id更新 order_id ,会对主键索引加锁，加锁后对更新的 order_id 索引加锁；
3、B步：update t_order set order_id=order_id+1 where order_id=726821956 语句按 order_id 更新，首先对普通索引order_id 加锁，普通索引加锁后会继续对其主键索引加锁；
4、A步 先持有主键索引锁 再去获取普通索引锁，B步 先持有普通索引锁再去获取主键索引锁，互相持有对方想要得到的锁，都没释放，最终死锁；