这两天在学习数据库事务隔离级别,就想写点程序模拟一下:
数据库中有表account,account中有一个数值类型的字段balance表示账户余额。
package mysql;import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 线程1对balance字段进行更新
* @author peng
*
*/
public class TransactionThread1 implements Runnable{ public void run() {
System.out.println( "thread1 start" );
Connection conn = DB.getConn();
String updateSql = "update account set balance=balance+100";
try {
conn.setTransactionIsolation( Connection.TRANSACTION_REPEATABLE_READ );
conn.setAutoCommit( false );
Statement statement = conn.createStatement();
/*更新和删除组成一个事务*/
Thread.sleep( 3000 );
statement.executeUpdate( updateSql );
System.out.println( "线程1完成更新" );
conn.commit();
System.out.println( "线程1执行完成" );
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}}package mysql;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;/**
* 线程2两次读取balace字段
* @author peng
*
*/
public class TransactionThread2 implements Runnable{ @Override
public void run() {
System.out.println( "thread2 start" );
Connection conn = DB.getConn();
String selectSql = "select balance from account";
try {
conn.setTransactionIsolation( Connection.TRANSACTION_REPEATABLE_READ );//允许读未提交的数据,可以读脏数据
conn.setAutoCommit( false );
Statement statement = conn.createStatement();
System.out.println( "线程2开始第一次查询" );
ResultSet rs = statement.executeQuery(selectSql);
while( rs.next() ){
System.out.println( "balance:" + rs.getInt( "balance" ) );
}
System.out.println( "线程2完成第一次查询" );
Thread.sleep( 6000 );
System.out.println( "线程2开始第二次查询" );
rs = statement.executeQuery(selectSql);
while( rs.next() ){
System.out.println( "balance:" + rs.getInt( "balance" ) );
}
System.out.println( "线程2完成第二次查询" );
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
} catch (InterruptedException e) {
e.printStackTrace();
}
}}package mysql;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class TestTransaction { private static void insertData(){
Connection conn = DB.getConn();
String selectSql = "select count(*) as num from account where userName='张三'";
try {
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery( selectSql );
if( rs.next() ){
int count = rs.getInt( "num" );
if( count == 0 ){
String insertSql1 = "insert into account values('9555500100071120','张三',10000.00,sysdate())";
String insertSql2 = "insert into account values('9555507551227787','张三',20000.00,sysdate())";
statement.addBatch(insertSql1);
statement.addBatch(insertSql2);
statement.executeBatch();
}
}
System.out.println( "插入数据完成" );
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void main(String[] args) {
insertData();
Thread thread1 = new Thread( new TransactionThread1() );
thread1.start();
Thread thread2 = new Thread( new TransactionThread2() );
thread2.start();
}}程序的运行结果为:
插入数据完成
thread1 start
thread2 start
线程2开始第一次查询
balance:10000
balance:20000
线程2完成第一次查询
线程1完成更新
线程1执行完成
线程2开始第二次查询
balance:10000
balance:20000
线程2完成第二次查询
表中balance字段的最终结果其实已经变成了10100和20100了,但是为什么线程2两次读到的结果还都是10000和20000呢,这确实“可重复读”了,但是读到的结果并不对啊?是不是我对可重复度的理解有问题呢?应该怎样理解呢?谢谢
数据库中有表account,account中有一个数值类型的字段balance表示账户余额。
package mysql;import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 线程1对balance字段进行更新
* @author peng
*
*/
public class TransactionThread1 implements Runnable{ public void run() {
System.out.println( "thread1 start" );
Connection conn = DB.getConn();
String updateSql = "update account set balance=balance+100";
try {
conn.setTransactionIsolation( Connection.TRANSACTION_REPEATABLE_READ );
conn.setAutoCommit( false );
Statement statement = conn.createStatement();
/*更新和删除组成一个事务*/
Thread.sleep( 3000 );
statement.executeUpdate( updateSql );
System.out.println( "线程1完成更新" );
conn.commit();
System.out.println( "线程1执行完成" );
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}}package mysql;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;/**
* 线程2两次读取balace字段
* @author peng
*
*/
public class TransactionThread2 implements Runnable{ @Override
public void run() {
System.out.println( "thread2 start" );
Connection conn = DB.getConn();
String selectSql = "select balance from account";
try {
conn.setTransactionIsolation( Connection.TRANSACTION_REPEATABLE_READ );//允许读未提交的数据,可以读脏数据
conn.setAutoCommit( false );
Statement statement = conn.createStatement();
System.out.println( "线程2开始第一次查询" );
ResultSet rs = statement.executeQuery(selectSql);
while( rs.next() ){
System.out.println( "balance:" + rs.getInt( "balance" ) );
}
System.out.println( "线程2完成第一次查询" );
Thread.sleep( 6000 );
System.out.println( "线程2开始第二次查询" );
rs = statement.executeQuery(selectSql);
while( rs.next() ){
System.out.println( "balance:" + rs.getInt( "balance" ) );
}
System.out.println( "线程2完成第二次查询" );
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
} catch (InterruptedException e) {
e.printStackTrace();
}
}}package mysql;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class TestTransaction { private static void insertData(){
Connection conn = DB.getConn();
String selectSql = "select count(*) as num from account where userName='张三'";
try {
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery( selectSql );
if( rs.next() ){
int count = rs.getInt( "num" );
if( count == 0 ){
String insertSql1 = "insert into account values('9555500100071120','张三',10000.00,sysdate())";
String insertSql2 = "insert into account values('9555507551227787','张三',20000.00,sysdate())";
statement.addBatch(insertSql1);
statement.addBatch(insertSql2);
statement.executeBatch();
}
}
System.out.println( "插入数据完成" );
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void main(String[] args) {
insertData();
Thread thread1 = new Thread( new TransactionThread1() );
thread1.start();
Thread thread2 = new Thread( new TransactionThread2() );
thread2.start();
}}程序的运行结果为:
插入数据完成
thread1 start
thread2 start
线程2开始第一次查询
balance:10000
balance:20000
线程2完成第一次查询
线程1完成更新
线程1执行完成
线程2开始第二次查询
balance:10000
balance:20000
线程2完成第二次查询
表中balance字段的最终结果其实已经变成了10100和20100了,但是为什么线程2两次读到的结果还都是10000和20000呢,这确实“可重复读”了,但是读到的结果并不对啊?是不是我对可重复度的理解有问题呢?应该怎样理解呢?谢谢
你的问题,"那如果数据被别的事务修改了怎么办?可重复读应该可以避免数据被修改的吧?",在读取数据时避免其被修改,应该是Serializable(可串行化),也就是加锁了,这个隔离级别使得各个事务之间有序进行,互补干扰。
纯个人理解,给你个连接,希望对你有帮助,谢谢。
http://xm-king.iteye.com/blog/770721