mysql 死锁

2019独角兽企业重金招聘Python工程师标准>>>

1、查询是否锁表
show OPEN TABLES where In_use > 0;

2、查询进程

    show processlist
  查询到相对应的进程===然后 kill    id

补充：

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 

查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 

查看死锁日志：

show engine innodb status \G; 

找到“LATEST DETECTED DEADLOCK”一节内容

模拟死锁的代码

package com.deadLock;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class Update {
	public static Connection getDirectConnection(String host, int port, String db, String user, String password) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://" + host + ":" + port + "/" + db;
			return DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static void op1() {
		Connection conn = getDirectConnection("localhost", 3306, "tt", "root", "11111111");
		try {
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("update city set state='河北2' where name='石家庄' ");
			stmt.executeUpdate("update city_4 set state='河北2' where name='石家庄' ");
			conn.commit();
			stmt.close();
			conn.close();
			stmt = null;
			conn = null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void op2() {
		Connection conn = getDirectConnection("localhost", 3306, "tt", "root", "11111111");
		try {
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("update city_4 set state='河北1' where name='石家庄' ");
			stmt.executeUpdate("update city set state='河北1' where name='石家庄' ");
			conn.commit();
			stmt.close();
			conn.close();
			stmt = null;
			conn = null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		ExecutorService service=Executors.newFixedThreadPool(10);
		for (int i = 0; i <5; i++) {
			service.execute( new Runnable() {
				@Override
				public void run() {
					while(true)op1();
				
				}
			});
		}
		for (int i = 0; i <5; i++) {
			service.execute( new Runnable() {
				@Override
				public void run() {
					while(true)op2();
				}
			});
		}
		try {
			 Thread.sleep(6*10*1000);
		} catch (InterruptedException e) {
			e.printStackTrace();
		}
	}

}

 

转载于:https://my.oschina.net/internetafei/blog/1825259