数据库连接池是为了解决数据库连接资源的低效管理而提出的解决方案,有助于解决资源的频繁分配和释放所造成一系列问题。
打个比方,如果多个用户要同时用到connection(并发问题),假如不用连接池是不是要等你用完connection,并且释放后,另外几个才能用?connection是个线程兼容类。所以不是线程安全的,但是可以通过正确使用同步而在并发环境中安全地使用。
打个比方,如果多个用户要同时用到connection(并发问题),假如不用连接池是不是要等你用完connection,并且释放后,另外几个才能用?connection是个线程兼容类。所以不是线程安全的,但是可以通过正确使用同步而在并发环境中安全地使用。
public static void main(String[] args)throws Exception {
Connection conn = getConn("localhost");
for(int i=0;i<10;i++){
Worker person = new Worker("select * from person",conn);
Worker users = new Worker("select * from users",conn);
person.start();
users.start();
}
}
private static class Worker extends Thread{
private String sql;
private Connection conn ;
public Worker(String sql,Connection conn){
this.sql=sql;
this.conn = conn;
}
public void run(){
try{
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
System.out.println(Thread.currentThread().getName()+","+sql+","+rs.getString(1));
}
rs.close();
stmt.close();
}catch(Exception e){e.printStackTrace();}
}
}
//数据库的数据:
mysql> select * from users;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+------+----------+
3 rows in set (0.00 sec)mysql> select * from person;
+------+-----------+----------+
| id | firstname | lastname |
+------+-----------+----------+
| 1 | zhang | san |
| 2 | li | si |
+------+-----------+----------+
2 rows in set (0.00 sec)
//输出结果:
Thread-1,select * from person,1
Thread-1,select * from person,2
Thread-11,select * from person,1
Thread-11,select * from person,2
Thread-18,select * from users,1
Thread-18,select * from users,2
Thread-18,select * from users,3
Thread-7,select * from person,1
Thread-7,select * from person,2
Thread-14,select * from users,1
Thread-14,select * from users,2
Thread-14,select * from users,3
Thread-10,select * from users,1
Thread-10,select * from users,2
Thread-10,select * from users,3
Thread-17,select * from person,1
Thread-17,select * from person,2
Thread-13,select * from person,1
Thread-13,select * from person,2
Thread-6,select * from users,1
Thread-6,select * from users,2
Thread-6,select * from users,3
Thread-9,select * from person,1
Thread-9,select * from person,2
Thread-3,select * from person,1
Thread-3,select * from person,2
Thread-5,select * from person,1
Thread-5,select * from person,2
Thread-2,select * from users,1
Thread-2,select * from users,2
Thread-2,select * from users,3
Thread-20,select * from users,1
Thread-20,select * from users,2
Thread-20,select * from users,3
Thread-16,select * from users,1
Thread-16,select * from users,2
Thread-16,select * from users,3
Thread-8,select * from users,1
Thread-8,select * from users,2
Thread-8,select * from users,3
Thread-12,select * from users,1
Thread-12,select * from users,2
Thread-12,select * from users,3
Thread-19,select * from person,1
Thread-19,select * from person,2
Thread-4,select * from users,1
Thread-4,select * from users,2
Thread-4,select * from users,3
Thread-15,select * from person,1
Thread-15,select * from person,2
看上去是支持并发的使用方法的。
打个比方,如果多个用户要同时用到connection(并发问题),假如不用连接池是不是要等你用完connection,并且释放后,另外几个才能用?connection是个线程兼容类。所以不是线程安全的,但是可以通过正确使用同步而在并发环境中安全地使用。
//加大客户端的任务数目,貌似也没问题啊!
public static void main(String[] args)throws Exception {
Connection conn = getConn("localhost");
ExecutorService service = Executors.newFixedThreadPool(100);
int times = 20000;
CountDownLatch stop = new CountDownLatch(times);
for(int i=0;i<times/2;i++){
Worker person = new Worker("select * from person",conn,stop);
Worker users = new Worker("select * from users",conn,stop);
service.submit(person);
service.submit(users);
}
stop.await();
service.shutdown();
}
private static class Worker extends Thread{
private String sql;
private Connection conn ;
private CountDownLatch stop;
public Worker(String sql,Connection conn,CountDownLatch stop){
this.sql=sql;
this.conn = conn;
this.stop = stop;
}
public void run(){
try{
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
System.out.println(Thread.currentThread().getName()+","+sql+","+rs.getString(1));
Thread.sleep(1);
}
rs.close();
stmt.close();
}catch(Exception e){e.printStackTrace();
}finally{
stop.countDown();
}
}
}
你用上面的代码说明什么呢
我想说明的是,大家共用一个connection,好像也没问题啊
我的意思是,不需要支持事物。但是,有select也有update,如果出错不需要rollback。
你的测试代码很明显有问题:
1、你的select的数据很小,查询速度很快,基本不存在并发的为问题。
2、你的for循环其实也是顺序执行,只是查询肯定不会乱。
3、我建议你如何测试:
你写个sql比较耗时间的,建议把这个connectin直接一直占用,然后在执行其他sql操作,你看会不会有问题就行了!
We require that all operations on all the java.sql objects be multi-thread safe and able to cope correctly with having several threads simultaneously calling the same object.
Some drivers may allow more concurrent execution than others. Developers can assume fully concurrent execution; if the driver requires some form of synchronization, it will provide it. The only difference visible to the developer will be that applications will run with reduced concurrency.
For example, two Statements on the same Connection can be executed concurrently and their ResultSets can be processed concurrently (from the perspective of the developer). Some drivers will provide this full concurrency. Others may execute one statement and wait until it completes before sending the next.
One specific use of multi-threading is to cancel a long running statement. This is done by using one thread to execute the statement and another to cancel it with its Statement.cancel() method.
In practice we expect that most of the JDBC objects will only be accessed in a single threaded way. However some multi-thread support is necessary, and our attempts in previous drafts to specify some classes as MT safe and some as MT unsafe appeared to be adding more confusion than light.
The Oracle JDBC drivers provide full support for programs that use Java multithreading.
The following example creates a specified number of threads and lets you determine whether or not the threads will share a connection.
If you choose to share the connection, then the same JDBC connection object will be used by all threads (each thread will have its own statement object, however).Because all Oracle JDBC API methods are synchronized, if two threads try to use the connection object simultaneously,
then one will be forced to wait until the other one finishes its use.
银行会对数据的一致性要求比较严格,除此之外,大多数系统对数据一致性要求并没有那么高,做好日志就可以了,而且,transaction很明显会影响性能。据我所知,互联网公司很少有做事物处理的。
其实已经说明了,使用单一连接的问题可能就在并发等待的时候影响效能,当然连接数多也不好,连接池就是帮你管理这些连接的,如果特殊原因限制连接数,使用一个连接完全没问题。就算有事物相关的问题也可以用数据库procedure、function解决。
如果在程序中不同线程,同一线程,同时或者多次使用一个Connection不为因为下面的这些状态导致程序出错,那么你就可以共享使用一个Connection,如果你不能保证,则使用互斥的方式使用同一个Connection,甚至每次使用都创建一个新的线程。
线程池这时就可以解决每次使用都创建一个新的Connection,因为创建一个新的Connection很耗资源。
public partial class FormConnPool : Form
{
SqlConnection conn;
public FormConnPool()
{
InitializeComponent();
string connStr = "Data Source=.;Initial Catalog=;User ID=;Password=";
conn = new SqlConnection(connStr);
} private void button1_Click(object sender, EventArgs e)
{
//NewMethod();
Parallel.Invoke(new Action[] { new Action(NewMethod),new Action(NewMethod1)});
} private void NewMethod()
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from a";
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
MessageBox.Show("Keep Connection");
conn.Close();
}
private void NewMethod1()
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "update b set cHtml='' where Id='477A5DFF-AA41-4EE8-84B3-02B7FC59CFBB'";
cmd.ExecuteNonQuery();
MessageBox.Show("Wait Connection");
conn.Close();
} private void button2_Click(object sender, EventArgs e)
{
NewMethod1();
}
}写了个并行获取同一个连接的例子。
出现很多异常,代码无法正常运行。
这个时候就不应该共享同一个连接,而是使用连接池。
for循环就是顺序的?for里面开了新的线程呢?懒得搭理你
2:b线程用连接执行sql
3:a线程读取执行结果。
这样a线程读到的实际是b线程的执行结果。