数据库初始值为450 ==============================脏读代码==================================
public class ACIDTest {
public static void main(String[] args) throws InterruptedException {
Object object = new Object();
Setting setting = new Setting(new File("src/main/resources/conf/db.setting"), CharsetUtil.CHARSET_UTF_8, false);
DSFactory ds = DSFactory.create(setting);
Thread t1 = new Thread(() -> {
synchronized (object) {
DataSource dataSource = ds.getDataSource();
Connection connection = null;
try {
connection = dataSource.getConnection();
connection.setAutoCommit(false);
connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
PreparedStatement ps = connection.prepareStatement("update account set balance =balance-50 where id=1");
System.out.println("线程1更新结果:" + ps.executeUpdate());
object.wait();
int i = 5 / 0;
connection.commit();
} catch (Exception throwables) {
try {
connection.rollback();
object.notify();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}, "t1");
Thread t2 = new Thread(() -> {
synchronized (object) {
DataSource dataSource = ds.getDataSource();
Connection connection = null;
try {
connection = dataSource.getConnection();
connection.setAutoCommit(false);
connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
PreparedStatement ps = connection.prepareStatement("select balance from account where id=1");
ResultSet rsbefore = ps.executeQuery();
while (rsbefore.next()) {
System.out.println("线程T2,T1提交前结果：" + rsbefore.getString(1));
}
object.notify();
object.wait();
ResultSet reafter = ps.executeQuery();
while (reafter.next()) {
System.out.println("线程T2,T1回滚后结果：" + reafter.getString(1));
}
connection.commit();
} catch (Exception throwables) {
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
} finally {
}
}
}, "t2");
//add by Hide on jdk 让t1更新数据库线程先执行
t1.start();
TimeUnit.SECONDS.sleep(1);
t2.start();
}
}
结果：线程2读取到了线程1提交前的数据，线程1发生异常后数据回滚，有读取到回滚后的数据，这就是脏读
=======================TRANSACTION_READ_COMMITTED隔离级别============
public class ACIDTest1 {
public static void main(String[] args) throws InterruptedException {
Object object = new Object();
Setting setting = new Setting(new File("src/main/resources/conf/db.setting"), CharsetUtil.CHARSET_UTF_8, false);
DSFactory ds = DSFactory.create(setting);
Thread t1 = new Thread(() -> {
synchronized (object) {
DataSource dataSource = ds.getDataSource();
Connection connection = null;
try {
connection = dataSource.getConnection();
connection.setAutoCommit(false);
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
PreparedStatement ps = connection.prepareStatement("update account set balance =balance-50 where id=1");
System.out.println("线程1更新结果:" + ps.executeUpdate());
object.wait();
int i = 5 / 0;
connection.commit();
} catch (Exception throwables) {
try {
connection.rollback();
object.notify();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}, "t1");
Thread t2 = new Thread(() -> {
synchronized (object) {
DataSource dataSource = ds.getDataSource();
Connection connection = null;
try {
connection = dataSource.getConnection();
connection.setAutoCommit(false);
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
PreparedStatement ps = connection.prepareStatement("select balance from account where id=1");
ResultSet rsbefore = ps.executeQuery();
while (rsbefore.next()) {
System.out.println("线程T2,T1提交前结果：" + rsbefore.getString(1));
}
object.notify();
object.wait();
ResultSet reafter = ps.executeQuery();
while (reafter.next()) {
System.out.println("线程T2,T1回滚后结果：" + reafter.getString(1));
}
connection.commit();
} catch (Exception throwables) {
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
} finally {
}
}
}, "t2");
//add by Hide on jdk 让t1更新数据库线程先执行
t1.start();
TimeUnit.SECONDS.sleep(1);
t2.start();
}
}
结果：提交前后的结果一样 ，脏读解决了
第二种情况：
public class ACIDTest1 {
public static void main(String[] args) throws InterruptedException {
Object object = new Object();
Setting setting = new Setting(new File("src/main/resources/conf/db.setting"), CharsetUtil.CHARSET_UTF_8, false);
DSFactory ds = DSFactory.create(setting);
Thread t1 = new Thread(() -> {
synchronized (object) {
DataSource dataSource = ds.getDataSource();
Connection connection = null;
try {
connection = dataSource.getConnection();
connection.setAutoCommit(false);
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
PreparedStatement ps = connection.prepareStatement("update account set balance =balance-50 where id=1");
System.out.println("线程1更新结果:" + ps.executeUpdate());
object.wait();
connection.commit();
object.notify();
} catch (Exception throwables) {
try {
connection.rollback();
object.notify();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}, "t1");
Thread t2 = new Thread(() -> {
synchronized (object) {
DataSource dataSource = ds.getDataSource();
Connection connection = null;
try {
connection = dataSource.getConnection();
connection.setAutoCommit(false);
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
PreparedStatement ps = connection.prepareStatement("select balance from account where id=1");
ResultSet rsbefore = ps.executeQuery();
while (rsbefore.next()) {
System.out.println("线程T2,T1提交前结果：" + rsbefore.getString(1));
}
object.notify();
object.wait();
ResultSet reafter = ps.executeQuery();
while (reafter.next()) {
System.out.println("线程T2,T1回滚后结果：" + reafter.getString(1));
}
connection.commit();
} catch (Exception throwables) {
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
} finally {
}
}
}, "t2");
//add by Hide on jdk 让t1更新数据库线程先执行
t1.start();
TimeUnit.SECONDS.sleep(1);
t2.start();
}
}
结果：线程1没有提交，线程2读取到450，唤醒线程1，然后线程1提交事务，这是线程2来读取，结果变为400，一个session前后2次读取的结果不一样。显然读已提交这种隔离级别会导致不可重复读。
=================TRANSACTION_REPEATABLE_READ=================
public class ACIDTest2 {
public static void main(String[] args) throws InterruptedException {
Object object = new Object();
Setting setting = new Setting(new File("src/main/resources/conf/db.setting"), CharsetUtil.CHARSET_UTF_8, false);
DSFactory ds = DSFactory.create(setting);
Thread t1 = new Thread(() -> {
synchronized (object) {
DataSource dataSource = ds.getDataSource();
Connection connection = null;
try {
connection = dataSource.getConnection();
connection.setAutoCommit(false);
connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
PreparedStatement ps = connection.prepareStatement("update account set balance =balance-50 where id=1");
System.out.println("线程1更新结果:" + ps.executeUpdate());
object.wait();
connection.commit();
object.notify();
} catch (Exception throwables) {
try {
connection.rollback();
object.notify();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}, "t1");
Thread t2 = new Thread(() -> {
synchronized (object) {
DataSource dataSource = ds.getDataSource();
Connection connection = null;
try {
connection = dataSource.getConnection();
connection.setAutoCommit(false);
connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
PreparedStatement ps = connection.prepareStatement("select balance from account where id=1");
ResultSet rsbefore = ps.executeQuery();
while (rsbefore.next()) {
System.out.println("线程T2,T1提交前结果：" + rsbefore.getString(1));
}
object.notify();
object.wait();
ResultSet reafter = ps.executeQuery();
while (reafter.next()) {
System.out.println("线程T2,T1回滚后结果：" + reafter.getString(1));
}
connection.commit();
} catch (Exception throwables) {
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
} finally {
}
}
}, "t2");
//add by Hide on jdk 让t1更新数据库线程先执行
t1.start();
TimeUnit.SECONDS.sleep(1);
t2.start();
}
}
结果：不管线程1提交的顺序如何，线程2读到的金额都是同一个，这就是可重复读的隔离级别，也是mysql默认的隔离级别。
