pengranxiang
- 浏览: 461863 次
- 性别:
- 来自: 长沙
-
文章分类
- 全部博客 (132)
- Java (17)
- Eclipse (2)
- Struts2 (6)
- SWT (9)
- Java Web Start (2)
- Heritrix (4)
- Nutch (1)
- Internet (2)
- J2me (2)
- Windows (4)
- Swing (8)
- JavaScript (11)
- Hibernate (1)
- Spring (6)
- Mysql (9)
- Oracle (10)
- Linux (6)
- RESTful (3)
- XML (1)
- Flex (4)
- EL (1)
- Apache (4)
- VC (3)
- OpenSourceLicence (1)
- Tomcat (4)
- Tiles2 (1)
- nosql (6)
- else (4)
- Nginx (2)
最新评论
-
mzlogin：
然而并没有讲 hash 函数
深入理解HashMap（及hash函数的真正巧妙之处） -
czp11210：
hi,你这篇文章很好。有两个细节跟你确认下：1.你使用的amo ...
Mysql 基于 Amoeba 的 水平和垂直 分片 -
Mybeautiful：
It seems the amoeba doesn't sup ...
Mysql 基于 Amoeba 的 水平和垂直 分片 -
xs.cctv：
言简意赅。。。。。。
深入理解HashMap（及hash函数的真正巧妙之处） -
mnhkahn：
h & (length-1)这个其实还是一个模运算，只 ...
深入理解HashMap（及hash函数的真正巧妙之处）
环境：
Servers
Amoeba Server (Linux): 192.168.14.129
Mysql 1 Server (Linux): 192.168.14.131
Mysql 2 Server (Linux): 192.168.14.133
Clients
Mysql GUI Tools (Windows): 192.168.14.28
Java Programs (Eclipse): 192.168.14.28
假设以上程序都已经安装好了。
1. Mysql数据库远程访问授权
mysql 1 server 和 mysql 2 server 的 test 数据库，允许 amoeba server 访问。用户名：test_user 密码：1234
grant all on test.* to test_user@192.168.14.129 identified by '1234';
2. 创建测试表
在 mysql 1 server 中：
t_user:
mysql> create table test.t_user (
-> user_id integer unsigned not null,
-> user_name varchar(45),
-> user_address varchar(100),
-> primary key (user_id)
-> )engine=innodb;
Query OK, 0 rows affected (0.01 sec)
t_blog:
mysql> create table test.t_blog (
-> blog_id integer unsigned not null,
-> blog_title varchar(45),
-> blog_content text,
-> user_id integer,
-> primary key (blog_id)
-> )engine=innodb;
Query OK, 0 rows affected (0.00 sec)
t_message:
mysql> create table test.t_message (
-> message_id integer unsigned not null,
-> message_content varchar(255),
-> user_id integer
-> )engine=innodb;
Query OK, 0 rows affected (0.01 sec)
在 mysql 2 server 中
t_user 同上。
t_attention:
mysql> create table test.t_attention (
-> attention_id integer unsigned not null,
-> user_id integer,
-> blog_id integer
-> )engine=innodb;
Query OK, 0 rows affected (0.01 sec)
t_blog_comment:
mysql> create table test.t_blog_comment (
-> comment_id integer unsigned not null,
-> commnet_content text,
-> blog_id integer
-> )engine=innodb;
Query OK, 0 rows affected (0.01 sec)
3. 配置 Amoeba 的切分数据库的规则
Amoeba 的详细使用说明请参见：http://docs.hexnova.com/amoeba/
dbServers.xml
<?xml version="1.0" encoding="gbk"?>
<!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">
<amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">
<!--
Each dbServer needs to be configured into a Pool,
If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration:
add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name factoryConfig
such as 'multiPool' dbServer
-->
<dbServer name="abstractServer" abstractive="true">
<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
<property name="manager">${defaultManager}</property>
<property name="sendBufferSize">64</property>
<property name="receiveBufferSize">128</property>
<!-- mysql port -->
<property name="port">3306</property>
<!-- mysql schema -->
<property name="schema">test</property>
<!-- mysql user -->
<property name="user">test_user</property>
<!-- mysql password -->
<property name="password">1234</property>
</factoryConfig>
<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
<property name="maxActive">500</property>
<property name="maxIdle">500</property>
<property name="minIdle">10</property>
<property name="minEvictableIdleTimeMillis">600000</property>
<property name="timeBetweenEvictionRunsMillis">600000</property>
<property name="testOnBorrow">true</property>
<property name="testWhileIdle">true</property>
</poolConfig>
</dbServer>
<dbServer name="server1" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.14.131</property>
</factoryConfig>
</dbServer>
<dbServer name="server2" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.14.133</property>
</factoryConfig>
</dbServer>
</amoeba:dbServers>
amoeba.xml
<?xml version="1.0" encoding="gbk"?>
<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">
<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">
<proxy>
<!-- service class must implements com.meidusa.amoeba.service.Service -->
<service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">
<!-- port -->
<property name="port">8066</property>
<!-- bind ipAddress -->
<!--
<property name="ipAddress">127.0.0.1</property>
-->
<property name="manager">${clientConnectioneManager}</property>
<property name="connectionFactory">
<bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">
<property name="sendBufferSize">128</property>
<property name="receiveBufferSize">64</property>
</bean>
</property>
<property name="authenticator">
<bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
<property name="user">root</property>
<property name="password">root</property>
<property name="filter">
<bean class="com.meidusa.amoeba.server.IPAccessController">
<property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
</bean>
</property>
</bean>
</property>
</service>
<!-- server class must implements com.meidusa.amoeba.service.Service -->
<service name="Amoeba Monitor Server" class="com.meidusa.amoeba.monitor.MonitorServer">
<!-- port -->
<!-- default value: random number
<property name="port">9066</property>
-->
<!-- bind ipAddress -->
<property name="ipAddress">127.0.0.1</property>
<property name="daemon">true</property>
<property name="manager">${clientConnectioneManager}</property>
<property name="connectionFactory">
<bean class="com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"></bean>
</property>
</service>
<runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">
<!-- proxy server net IO Read thread size -->
<property name="readThreadPoolSize">20</property>
<!-- proxy server client process thread size -->
<property name="clientSideThreadPoolSize">30</property>
<!-- mysql server data packet process thread size -->
<property name="serverSideThreadPoolSize">30</property>
<!-- per connection cache prepared statement size -->
<property name="statementCacheSize">500</property>
<!-- query timeout( default: 60 second , TimeUnit:second) -->
<property name="queryTimeout">60</property>
</runtime>
</proxy>
<!--
Each ConnectionManager will start as thread
manager responsible for the Connection IO read , Death Detection
-->
<connectionManagerList>
<connectionManager name="clientConnectioneManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
<property name="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property>
<!--
default value is avaliable Processors
<property name="processors">5</property>
-->
</connectionManager>
<connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
<property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>
<!--
default value is avaliable Processors
<property name="processors">5</property>
-->
</connectionManager>
</connectionManagerList>
<!-- default using file loader -->
<dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader">
<property name="configFile">${amoeba.home}/conf/dbServers.xml</property>
</dbServerLoader>
<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
<property name="ruleLoader">
<bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
<property name="ruleFile">${amoeba.home}/conf/rule.xml</property>
<property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
</bean>
</property>
<property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
<property name="LRUMapSize">1500</property>
<property name="defaultPool">server1</property><!-- 默认数据库，即主数据库 -->
<!--
<property name="writePool">server1</property>
<property name="readPool">server1</property>
-->
<property name="needParse">true</property>
</queryRouter>
</amoeba:configuration>
rule.xml
t_user 表 根据 user_id 字段的奇偶性 水平切分 ， 偶数分到 server1 ， 奇数分到 server2。其中 server1，server2 是在 dbServers.xml 中定义的。
t_attention 表垂直切分到 server2
主数据库是 server1 ， 在 amoeba.xml 中定义
<property name="defaultPool">server1</property>
主数据库说明：连接到 Amoeba 代理的时候，主数据库的所有表均可访问。 但是其他的数据库的表，需要在分片规则中有涉及的表，才能访问，其他表不能访问。
<?xml version="1.0" encoding="gbk"?>
<!DOCTYPE amoeba:rule SYSTEM "rule.dtd">
<amoeba:rule xmlns:amoeba="http://amoeba.meidusa.com/">
<tableRule name="t_user" schema="test" defaultPools="server1,server2">
<rule name="rule1">
<parameters>user_id</parameters>
<expression><![CDATA[ user_id % 2 == 0 ]]></expression>
<defaultPools>server1</defaultPools>
</rule>
<rule name="rule2">
<parameters>user_id</parameters>
<expression><![CDATA[ user_id % 2 == 1 ]]></expression>
<defaultPools>server2</defaultPools>
</rule>
</tableRule>
<tableRule name="t_attention" schema="test" defaultPools="server2" />
<tableRule name="t_blog_comment" schema="test" defaultPools="server2" />
</amoeba:rule>
启动 msyql1, mysql2, amoeba 切分完成。
4. 测试
访问的时候，需要通过 Amoeba 代理来访问。不能直接访问实体数据库，不然切分无效。
使用 Mysql GUI Tools 连接 Amoeba
用户名：root 密码： root 端口号：8066
以上信息在 amoeba.xml 中定义
<service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">
<!-- port 连接端口号-->
<property name="port">8066</property>
<!-- bind ipAddress -->
<!--
<property name="ipAddress">127.0.0.1</property>
-->
<property name="manager">${clientConnectioneManager}</property>
<property name="connectionFactory">
<bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">
<property name="sendBufferSize">128</property>
<property name="receiveBufferSize">64</property>
</bean>
</property>
<property name="authenticator">
<bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
<!--用户名，密码 -->
<property name="user">root</property>
<property name="password">root</property>
<property name="filter">
<bean class="com.meidusa.amoeba.server.IPAccessController">
<property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
</bean>
</property>
</bean>
</property>
</service>
可以发现，在GUI 工具中，能看到的表 只有在 server1 中，创建的表。 上面已经说明了，因为 server1 中主数据库。
验证垂直切分的 t_attention 表
为了验证，垂直切分出去的 t_attention 表。 直接执行 select * from test.t_attention;
可以看到，查询成功。 说明的确可以访问，切分出去的 t_attention 表。
验证水平切分的 t_user 表
t_user 的水平切分规则是 根据 user_id 的奇偶性进行切分。
上图的左下角可以看到：2 rows affected by the last command ， 说明刚才那个插入语句，作用到了两条记录
查询看一下插入结果：
发现有两条一样的数据。到网上找了一下原因。
后来知道 amoeba 是根据 sql 解析来进行 水平切分的， 需要把切分的关键字段（这里是user_id），加入到 sql 中。否则 切分规则无效。无效后，会在 server1, server2 均都插入数据。
即变为：insert into t_user(user_id, user_name, user_address) values(1, 'n1', 'a1')
分别检查一下，两个数据库
两个数据库均插入了一条相同的数据。 同时也了解到，amoeba 的查询会将水平切分的表在两个数据库的结果合并。
修改 sql 语句后再执行一次。
可以看到左下角：只作用到一条记录
看下查询结果
因为 user_id 为 偶数， 所有分派到 server 1 了。
水平切分的排序与分页
排序结果：分别查询的 server1 和 server2 然后简单合并。 所以 排序不正确。
分页结果：同样是分别执行的 server1 和 server2 然后合并。 每个 server 取两条记录，记录也变为了 4 条。分页不正确
验证连接查询
在 t_message , t_blog, t_attention 表分别插入两个数据
分别执行以上查询语句。
发现只有 第二 和 第四 可以执行成功。
结果：
1. 如果 表1 被水平分片，连接查询时，需要连接的表，在 表1 分片的所有数据库中均存在（即相同的分片规则），才可以连接查询。
2. 如果 表1 被垂直分片，连接查询时，需要连接的表也同样分片到同一个数据库中（即相同的分片规则）。
3. 综上，只有在连接的表，在同一个数据库中均存在时，才能连接操作。即 不支持跨数据库的连接。
验证事务管理
使用程序验证。
1. 验证没有分片的表的事务操作（无回滚）。
程序片段：
package prx.dao;
import java.util.List;
import java.util.Map;
import prx.core.string.StringUtil;
import prx.dao.core.IPrxDao;
import prx.dao.core.impl.MySqlDao;
public class Test {
public static void main(String[] args) throws Exception {
IPrxDao dao = new MySqlDao();
//开启事务，插入几条数据
dao.startTransaction();
for(int i=3; i<10; i++) {
dao.executeUpdate("insert into t_message(message_id, message_content, user_id) values(?, ?, ?)", new Object[]{i, "c" + i, i});
}
dao.commitTransaction();
//查看结果
List<Map<String, Object>> list = dao.queryForMapList("select * from t_message", new Object[]{});
for(Map<String, Object> map : list) {
System.out.println(StringUtil.valueOfMap(map));
}
}
}
执行结果：
2011/08/12 10:17:33 [INFO] MLog clients using log4j logging.
2011/08/12 10:17:34 [INFO] Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
2011/08/12 10:17:34 [INFO] Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hge3oc8h1g3olvycduja2|7a78d3, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge3oc8h1g3olvycduja2|7a78d3, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://192.168.14.129:8066/test?autoReconnect=true&characterEncoding=UTF-8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
2011/08/12 10:17:36 [INFO] [开始事务]
2011/08/12 10:17:36 [INFO] insert into t_message(message_id, message_content, user_id) values(?, ?, ?)
2011/08/12 10:17:36 [INFO] values:[3,c3,3]
2011/08/12 10:17:36 [INFO] insert into t_message(message_id, message_content, user_id) values(?, ?, ?)
2011/08/12 10:17:36 [INFO] values:[4,c4,4]
2011/08/12 10:17:36 [INFO] insert into t_message(message_id, message_content, user_id) values(?, ?, ?)
2011/08/12 10:17:36 [INFO] values:[5,c5,5]
2011/08/12 10:17:36 [INFO] insert into t_message(message_id, message_content, user_id) values(?, ?, ?)
2011/08/12 10:17:36 [INFO] values:[6,c6,6]
2011/08/12 10:17:36 [INFO] insert into t_message(message_id, message_content, user_id) values(?, ?, ?)
2011/08/12 10:17:36 [INFO] values:[7,c7,7]
2011/08/12 10:17:36 [INFO] insert into t_message(message_id, message_content, user_id) values(?, ?, ?)
2011/08/12 10:17:36 [INFO] values:[8,c8,8]
2011/08/12 10:17:36 [INFO] insert into t_message(message_id, message_content, user_id) values(?, ?, ?)
2011/08/12 10:17:36 [INFO] values:[9,c9,9]
2011/08/12 10:17:36 [INFO] [事务提交]
2011/08/12 10:17:36 [INFO] select * from t_message
[message_id:1, message_content:c1, user_id:1]
[message_id:2, message_content:c2, user_id:2]
[message_id:3, message_content:c3, user_id:3]
[message_id:4, message_content:c4, user_id:4]
[message_id:5, message_content:c5, user_id:5]
[message_id:6, message_content:c6, user_id:6]
[message_id:7, message_content:c7, user_id:7]
[message_id:8, message_content:c8, user_id:8]
[message_id:9, message_content:c9, user_id:9]
执行正确无误。
2. 验证没有分片的表的操作（有回滚）。
程序片段：
public static void main(String[] args) throws Exception {
IPrxDao dao = new MySqlDao();
//开启事务，插入几条数据
dao.startTransaction();
for(int i=10; i<20; i++) {
dao.executeUpdate("insert into t_message(message_id, message_content, user_id) values(?, ?, ?)", new Object[]{i, "c" + i, i});
}
//主键冲突，出错，回滚。
dao.executeUpdate("insert into t_user(user_id) values(?)", new Object[]{1});
dao.commitTransaction();
//查看结果
List<Map<String, Object>> list = dao.queryForMapList("select * from t_message", new Object[]{});
for(Map<String, Object> map : list) {
System.out.println(StringUtil.valueOfMap(map));
}
}
执行结果：
2011/08/12 10:24:37 [INFO] MLog clients using log4j logging.
2011/08/12 10:24:37 [INFO] Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
2011/08/12 10:24:37 [INFO] Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hge3oc8h1g3xpaud8zfzf|b169f8, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge3oc8h1g3xpaud8zfzf|b169f8, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://192.168.14.129:8066/test?autoReconnect=true&characterEncoding=UTF-8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
2011/08/12 10:24:38 [INFO] [开始事务]
2011/08/12 10:24:38 [INFO] insert into t_message(message_id, message_content, user_id) values(?, ?, ?)
2011/08/12 10:24:38 [INFO] values:[10,c10,10]
2011/08/12 10:24:38 [INFO] insert into t_message(message_id, message_content, user_id) values(?, ?, ?)
2011/08/12 10:24:38 [INFO] values:[11,c11,11]
2011/08/12 10:24:38 [INFO] insert into t_message(message_id, message_content, user_id) values(?, ?, ?)
2011/08/12 10:24:38 [INFO] values:[12,c12,12]
2011/08/12 10:24:38 [INFO] insert into t_message(message_id, message_content, user_id) values(?, ?, ?)
2011/08/12 10:24:38 [INFO] values:[13,c13,13]
2011/08/12 10:24:38 [INFO] insert into t_message(message_id, message_content, user_id) values(?, ?, ?)
2011/08/12 10:24:38 [INFO] values:[14,c14,14]
2011/08/12 10:24:38 [INFO] insert into t_message(message_id, message_content, user_id) values(?, ?, ?)
2011/08/12 10:24:38 [INFO] values:[15,c15,15]
2011/08/12 10:24:38 [INFO] insert into t_message(message_id, message_content, user_id) values(?, ?, ?)
2011/08/12 10:24:38 [INFO] values:[16,c16,16]
2011/08/12 10:24:38 [INFO] insert into t_message(message_id, message_content, user_id) values(?, ?, ?)
2011/08/12 10:24:38 [INFO] values:[17,c17,17]
2011/08/12 10:24:38 [INFO] insert into t_message(message_id, message_content, user_id) values(?, ?, ?)
2011/08/12 10:24:38 [INFO] values:[18,c18,18]
2011/08/12 10:24:38 [INFO] insert into t_message(message_id, message_content, user_id) values(?, ?, ?)
2011/08/12 10:24:38 [INFO] values:[19,c19,19]
2011/08/12 10:24:38 [INFO] insert into t_user(user_id) values(?)
2011/08/12 10:24:38 [INFO] values:[1]
2011/08/12 10:24:38 [ERROR] Duplicate entry '1' for key 'PRIMARY'
com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1169)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:693)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1404)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1318)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1303)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
at prx.dao.core.AbstractPrxDao.executeUpdate(AbstractPrxDao.java:215)
at prx.dao.Test.main(Test.java:21)
2011/08/12 10:24:38 [ERROR] [事务失败，回滚]
2011/08/12 10:24:38 [INFO] select * from t_message
[message_id:1, message_content:c1, user_id:1]
[message_id:2, message_content:c2, user_id:2]
[message_id:3, message_content:c3, user_id:3]
[message_id:4, message_content:c4, user_id:4]
[message_id:5, message_content:c5, user_id:5]
[message_id:6, message_content:c6, user_id:6]
[message_id:7, message_content:c7, user_id:7]
[message_id:8, message_content:c8, user_id:8]
[message_id:9, message_content:c9, user_id:9]
执行正确无误。
3. 验证垂直分片的表的事务（无回滚）
public static void main(String[] args) throws Exception {
IPrxDao dao = new MySqlDao();
//开启事务，插入几条数据
dao.startTransaction();
for(int i=3; i<10; i++) {
dao.executeUpdate("insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)", new Object[]{i, i, i});
}
//主键冲突，出错，回滚。
//dao.executeUpdate("insert into t_user(user_id) values(?)", new Object[]{1});
dao.commitTransaction();
//查看结果
List<Map<String, Object>> list = dao.queryForMapList("select * from t_attention", new Object[]{});
for(Map<String, Object> map : list) {
System.out.println(StringUtil.valueOfMap(map));
}
}
执行结果：
2011/08/12 10:30:49 [INFO] MLog clients using log4j logging.
2011/08/12 10:30:50 [INFO] Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
2011/08/12 10:30:50 [INFO] Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hge3oc8h1g45oipe8gwx3|b169f8, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge3oc8h1g45oipe8gwx3|b169f8, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://192.168.14.129:8066/test?autoReconnect=true&characterEncoding=UTF-8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
2011/08/12 10:30:50 [INFO] [开始事务]
2011/08/12 10:30:50 [INFO] insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)
2011/08/12 10:30:50 [INFO] values:[3,3,3]
2011/08/12 10:30:50 [INFO] insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)
2011/08/12 10:30:50 [INFO] values:[4,4,4]
2011/08/12 10:30:50 [INFO] insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)
2011/08/12 10:30:50 [INFO] values:[5,5,5]
2011/08/12 10:30:50 [INFO] insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)
2011/08/12 10:30:50 [INFO] values:[6,6,6]
2011/08/12 10:30:50 [INFO] insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)
2011/08/12 10:30:50 [INFO] values:[7,7,7]
2011/08/12 10:30:50 [INFO] insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)
2011/08/12 10:30:50 [INFO] values:[8,8,8]
2011/08/12 10:30:50 [INFO] insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)
2011/08/12 10:30:50 [INFO] values:[9,9,9]
2011/08/12 10:30:50 [INFO] [事务提交]
2011/08/12 10:30:50 [INFO] select * from t_attention
[attention_id:1, user_id:1, blog_id:1]
[attention_id:2, user_id:2, blog_id:2]
[attention_id:2, user_id:2, blog_id:2]
[attention_id:3, user_id:3, blog_id:3]
[attention_id:4, user_id:4, blog_id:4]
[attention_id:5, user_id:5, blog_id:5]
[attention_id:6, user_id:6, blog_id:6]
[attention_id:7, user_id:7, blog_id:7]
[attention_id:8, user_id:8, blog_id:8]
[attention_id:9, user_id:9, blog_id:9]
执行成功。
4. 验证垂直分片的表（有回滚）
public static void main(String[] args) throws Exception {
IPrxDao dao = new MySqlDao();
//开启事务，插入几条数据
dao.startTransaction();
for(int i=10; i<20; i++) {
dao.executeUpdate("insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)", new Object[]{i, i, i});
}
//主键冲突，出错，回滚。
dao.executeUpdate("insert into t_user(user_id) values(?)", new Object[]{1});
dao.commitTransaction();
//查看结果
List<Map<String, Object>> list = dao.queryForMapList("select * from t_attention", new Object[]{});
for(Map<String, Object> map : list) {
System.out.println(StringUtil.valueOfMap(map));
}
}
执行结果：
2011/08/12 10:32:46 [INFO] MLog clients using log4j logging.
2011/08/12 10:32:46 [INFO] Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
2011/08/12 10:32:46 [INFO] Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hge3oc8h1g486lb1n4c9c4|b169f8, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge3oc8h1g486lb1n4c9c4|b169f8, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://192.168.14.129:8066/test?autoReconnect=true&characterEncoding=UTF-8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
2011/08/12 10:32:47 [INFO] [开始事务]
2011/08/12 10:32:47 [INFO] insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)
2011/08/12 10:32:47 [INFO] values:[10,10,10]
2011/08/12 10:32:47 [INFO] insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)
2011/08/12 10:32:47 [INFO] values:[11,11,11]
2011/08/12 10:32:47 [INFO] insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)
2011/08/12 10:32:47 [INFO] values:[12,12,12]
2011/08/12 10:32:47 [INFO] insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)
2011/08/12 10:32:47 [INFO] values:[13,13,13]
2011/08/12 10:32:47 [INFO] insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)
2011/08/12 10:32:47 [INFO] values:[14,14,14]
2011/08/12 10:32:47 [INFO] insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)
2011/08/12 10:32:47 [INFO] values:[15,15,15]
2011/08/12 10:32:47 [INFO] insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)
2011/08/12 10:32:47 [INFO] values:[16,16,16]
2011/08/12 10:32:47 [INFO] insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)
2011/08/12 10:32:47 [INFO] values:[17,17,17]
2011/08/12 10:32:47 [INFO] insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)
2011/08/12 10:32:47 [INFO] values:[18,18,18]
2011/08/12 10:32:47 [INFO] insert into t_attention(attention_id, user_id, blog_id) values(?, ?, ?)
2011/08/12 10:32:47 [INFO] values:[19,19,19]
2011/08/12 10:32:47 [INFO] insert into t_user(user_id) values(?)
2011/08/12 10:32:47 [INFO] values:[1]
2011/08/12 10:32:47 [ERROR] Duplicate entry '1' for key 'PRIMARY'
com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1169)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:693)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1404)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1318)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1303)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
at prx.dao.core.AbstractPrxDao.executeUpdate(AbstractPrxDao.java:215)
at prx.dao.Test.main(Test.java:21)
2011/08/12 10:32:47 [ERROR] [事务失败，回滚]
2011/08/12 10:32:47 [INFO] select * from t_attention
[attention_id:1, user_id:1, blog_id:1]
[attention_id:2, user_id:2, blog_id:2]
[attention_id:2, user_id:2, blog_id:2]
[attention_id:3, user_id:3, blog_id:3]
[attention_id:4, user_id:4, blog_id:4]
[attention_id:5, user_id:5, blog_id:5]
[attention_id:6, user_id:6, blog_id:6]
[attention_id:7, user_id:7, blog_id:7]
[attention_id:8, user_id:8, blog_id:8]
[attention_id:9, user_id:9, blog_id:9]
[attention_id:10, user_id:10, blog_id:10]
[attention_id:11, user_id:11, blog_id:11]
[attention_id:12, user_id:12, blog_id:12]
[attention_id:13, user_id:13, blog_id:13]
[attention_id:14, user_id:14, blog_id:14]
[attention_id:15, user_id:15, blog_id:15]
[attention_id:16, user_id:16, blog_id:16]
[attention_id:17, user_id:17, blog_id:17]
[attention_id:18, user_id:18, blog_id:18]
[attention_id:19, user_id:19, blog_id:19]
执行错误， 事务回滚失败
5. 验证水平分片的表的事务（无回滚）
public static void main(String[] args) throws Exception {
IPrxDao dao = new MySqlDao();
//开启事务，插入几条数据
dao.startTransaction();
for(int i=10; i<20; i++) {
dao.executeUpdate("insert into t_user(user_id, user_name, user_address) values(?, ?, ?)", new Object[]{i, "n"+i, "a"+i});
}
//主键冲突，出错，回滚。
//dao.executeUpdate("insert into t_user(user_id) values(?)", new Object[]{1});
dao.commitTransaction();
//查看结果
List<Map<String, Object>> list = dao.queryForMapList("select * from t_user", new Object[]{});
for(Map<String, Object> map : list) {
System.out.println(StringUtil.valueOfMap(map));
}
}
执行结果：
2011/08/12 10:41:20 [INFO] MLog clients using log4j logging.
2011/08/12 10:41:23 [INFO] Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
2011/08/12 10:41:24 [INFO] Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hge3oc8h1g4j7pb1170bmp|929206, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge3oc8h1g4j7pb1170bmp|929206, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://192.168.14.129:8066/test?autoReconnect=true&characterEncoding=UTF-8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
2011/08/12 10:41:27 [INFO] [开始事务]
2011/08/12 10:41:27 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:41:27 [INFO] values:[10,n10,a10]
2011/08/12 10:41:27 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:41:27 [INFO] values:[11,n11,a11]
2011/08/12 10:41:27 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:41:27 [INFO] values:[12,n12,a12]
2011/08/12 10:41:27 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:41:27 [INFO] values:[13,n13,a13]
2011/08/12 10:41:27 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:41:27 [INFO] values:[14,n14,a14]
2011/08/12 10:41:27 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:41:27 [INFO] values:[15,n15,a15]
2011/08/12 10:41:28 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:41:28 [INFO] values:[16,n16,a16]
2011/08/12 10:41:28 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:41:28 [INFO] values:[17,n17,a17]
2011/08/12 10:41:28 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:41:28 [INFO] values:[18,n18,a18]
2011/08/12 10:41:28 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:41:28 [INFO] values:[19,n19,a19]
2011/08/12 10:41:28 [INFO] [事务提交]
2011/08/12 10:41:28 [INFO] select * from t_user
[user_id:2, user_name:n2, user_address:a2]
[user_id:4, user_name:n4, user_address:a4]
[user_id:6, user_name:n6, user_address:a6]
[user_id:10, user_name:n10, user_address:a10]
[user_id:12, user_name:n12, user_address:a12]
[user_id:14, user_name:n14, user_address:a14]
[user_id:16, user_name:n16, user_address:a16]
[user_id:18, user_name:n18, user_address:a18]
[user_id:1, user_name:n1, user_address:a1]
[user_id:3, user_name:n3, user_address:a3]
[user_id:5, user_name:n5, user_address:a5]
[user_id:11, user_name:n11, user_address:a11]
[user_id:13, user_name:n13, user_address:a13]
[user_id:15, user_name:n15, user_address:a15]
[user_id:17, user_name:n17, user_address:a17]
[user_id:19, user_name:n19, user_address:a19]
执行成功。
6. 验证水平分片的表的事务（有回滚）
public static void main(String[] args) throws Exception {
IPrxDao dao = new MySqlDao();
//开启事务，插入几条数据
dao.startTransaction();
for(int i=20; i<30; i++) {
dao.executeUpdate("insert into t_user(user_id, user_name, user_address) values(?, ?, ?)", new Object[]{i, "n"+i, "a"+i});
}
//主键冲突，出错，回滚。
dao.executeUpdate("insert into t_user(user_id) values(?)", new Object[]{1});
dao.commitTransaction();
//查看结果
List<Map<String, Object>> list = dao.queryForMapList("select * from t_user", new Object[]{});
for(Map<String, Object> map : list) {
System.out.println(StringUtil.valueOfMap(map));
}
}
2011/08/12 10:44:05 [INFO] MLog clients using log4j logging.
2011/08/12 10:44:05 [INFO] Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
2011/08/12 10:44:05 [INFO] Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hge3oc8h1g4mqbxeevnxo|b169f8, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge3oc8h1g4mqbxeevnxo|b169f8, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://192.168.14.129:8066/test?autoReconnect=true&characterEncoding=UTF-8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
2011/08/12 10:44:05 [INFO] [开始事务]
2011/08/12 10:44:05 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:44:05 [INFO] values:[20,n20,a20]
2011/08/12 10:44:06 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:44:06 [INFO] values:[21,n21,a21]
2011/08/12 10:44:06 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:44:06 [INFO] values:[22,n22,a22]
2011/08/12 10:44:06 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:44:06 [INFO] values:[23,n23,a23]
2011/08/12 10:44:06 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:44:06 [INFO] values:[24,n24,a24]
2011/08/12 10:44:06 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:44:06 [INFO] values:[25,n25,a25]
2011/08/12 10:44:06 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:44:06 [INFO] values:[26,n26,a26]
2011/08/12 10:44:06 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:44:06 [INFO] values:[27,n27,a27]
2011/08/12 10:44:06 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:44:06 [INFO] values:[28,n28,a28]
2011/08/12 10:44:06 [INFO] insert into t_user(user_id, user_name, user_address) values(?, ?, ?)
2011/08/12 10:44:06 [INFO] values:[29,n29,a29]
2011/08/12 10:44:06 [INFO] insert into t_user(user_id) values(?)
2011/08/12 10:44:06 [INFO] values:[1]
2011/08/12 10:44:06 [ERROR] Duplicate entry '1' for key 'PRIMARY'
com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1169)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:693)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1404)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1318)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1303)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
at prx.dao.core.AbstractPrxDao.executeUpdate(AbstractPrxDao.java:215)
at prx.dao.Test.main(Test.java:21)
2011/08/12 10:44:06 [ERROR] [事务失败，回滚]
2011/08/12 10:44:06 [INFO] select * from t_user
[user_id:1, user_name:n1, user_address:a1]
[user_id:3, user_name:n3, user_address:a3]
[user_id:5, user_name:n5, user_address:a5]
[user_id:11, user_name:n11, user_address:a11]
[user_id:13, user_name:n13, user_address:a13]
[user_id:15, user_name:n15, user_address:a15]
[user_id:17, user_name:n17, user_address:a17]
[user_id:19, user_name:n19, user_address:a19]
[user_id:21, user_name:n21, user_address:a21]
[user_id:23, user_name:n23, user_address:a23]
[user_id:25, user_name:n25, user_address:a25]
[user_id:27, user_name:n27, user_address:a27]
[user_id:29, user_name:n29, user_address:a29]
[user_id:2, user_name:n2, user_address:a2]
[user_id:4, user_name:n4, user_address:a4]
[user_id:6, user_name:n6, user_address:a6]
[user_id:10, user_name:n10, user_address:a10]
[user_id:12, user_name:n12, user_address:a12]
[user_id:14, user_name:n14, user_address:a14]
[user_id:16, user_name:n16, user_address:a16]
[user_id:18, user_name:n18, user_address:a18]
结果：
user_id 为偶数的部分都没有插入数据，回滚成功。 user_id 为偶数 分片到 server1 为主数据库
user_id 为奇数的部分都已经插入数据，回滚失败。 user_id 为奇数 分片到 server2
综上： 所有分片的表，都不能支持事务。
验证存储过程
这个就不写验证过程了。 直接写结果好了。
结果：
1. 只可以调用 server1 主数据库 的 procedure , 不能调用 server2 的。
2. server1 的 procedure 只能操作 server1 中的表， 不支持水平和垂直分片。水平切分的 t_user， 不管 user_id 奇偶 均插入到 server1。垂直切分的t_attention 表不能访问。
- 大小: 35.5 KB
- 大小: 38.6 KB
- 大小: 41.1 KB
- 大小: 42.9 KB
- 大小: 46.8 KB
- 大小: 40.2 KB
- 大小: 41 KB
- 大小: 46.6 KB
- 大小: 49.3 KB
- 大小: 40.6 KB
- 大小: 40.9 KB
- 大小: 50 KB
- 大小: 49.1 KB
- 大小: 52.3 KB
分享到：
评论
2 楼
czp11210
2016-03-07
hi,你这篇文章很好。有两个细节跟你确认下：
1.你使用的amoeba版本是多少？
2.后端两个数据库名称一定要是一样嘛？比如你这里都叫test。
1.你使用的amoeba版本是多少？
2.后端两个数据库名称一定要是一样嘛？比如你这里都叫test。
1 楼
Mybeautiful
2015-10-02
It seems the amoeba doesn't support utf8mb4.
发表评论
-
Mysql 基于 Amoeba 的 读写分离
2011-08-13 15:49 2229首先说明一下amoeba 跟 MySQL proxy在读写分离 ... -
MySQL性能调优与架构设计-架构篇
2011-08-09 18:04 0架构篇（1） ... -
【转】MySQL使用Amoeba作为Proxy时的注意事项
2011-08-09 16:29 0与MySql 官方的MySQL Pr ... -
Mysql 授权
2011-08-08 17:53 0先设置该用户只有show database权限 gran ... -
Mysql 主从复制，读写分离
2011-08-05 16:09 31938一个简单完整的 Mysql ... -
【转】MySQL 5.5编译安装
2011-08-02 12:51 1832之前写过一篇Linux下编译安装MySQL，但是在5.5版本之 ... -
【转】编译安装MySQL
2011-08-02 12:43 1652原文地址：http://www.orczh ... -
mysql 的 last_insert_id
2010-08-04 09:56 1095LAST_INSERT_ID 自动返回最后一个 INSERT ... -
linux下mysql server的默认字符集修改和默认数据库引擎的修改
2010-07-24 10:22 1170原文地址：http://dengyin2000.iteye.c ... -
Mysql日期和时间函数不求人
2009-12-10 16:43 1254--选择所有记录，其date_col的值是在最后30天以内： ... -
Mysql中根据记录是否存在选择insert和update
2009-08-20 11:27 3735根据记录是否存在来选择insert和update操作： ...
相关推荐
- **易于管理和扩展**：Amoeba提供了直观易用的配置方式，使得系统的管理和维护变得更加简便，并支持灵活的水平和垂直扩展。 #### 三、Amoeba的核心概念 - **数据库代理**：Amoeba作为客户端与后端数据库之间的中间...
- **中间件**：使用数据库中间件如MyCAT、Amoeba等，它们可以自动处理数据路由和分片逻辑，减轻开发人员的负担。 - **MySQL内置功能**： - **分区（Partitioning）**：MySQL 5.1及以上版本支持表分区，可按范围、...
分片是将数据分布到多个数据库上，分为水平分片和垂直分片。水平分片是按数据属性进行分割，而垂直分片则是按表结构划分。Sharding与Partition的区别在于，前者通常跨越数据库，后者多限于同一数据库内的不同表空间...
水平分片是基于数据的某些属性将数据分散到多个数据库，而垂直分片是将数据表的不同列分配到不同的数据库。 - **Sharding vs Partitioning**: 分片通常指的是跨越数据库的水平分区，允许数据分布在不同的物理机器上...
在实现数据库分片和读写分离时，可以采用两种方式：一是使用独立的DAL（数据访问层）代理服务器，如MySQL的Amoeba或PostgreSQL的PL/Proxy；二是通过DAL API，如Java的Hibernate Shard、Ibatis Shard、HiveDB，Python...
分片分为水平分区和垂直分区。水平分区是根据数据的某些属性将其分散到多个数据库，如将用户数据按用户ID均匀分布；垂直分区则是将数据库表的列分割到不同的表，以减少单个表的复杂性。Sharding可以提高数据处理能力...
此外，Sharding（分片）是解决大数据量问题的有效手段，分为水平分区和垂直分区。水平分区是根据数据的某些属性将其分散到多个数据库中，而垂直分区则是按数据列进行划分，分别存储在不同的表或数据库中。Sharding和...
数据库分片（Sharding）是另一种扩展策略，分为水平分区和垂直分区。水平分区将数据按照特定规则分散到多个数据库中，而垂直分区则是将表的不同列分配到不同的数据库中。Sharding与Partition的区别在于，Partition...
- **水平分区**：按照一定的规则（如哈希值）将数据划分到不同的分片上。 - **垂直分区**：按照业务模块的不同将数据存储在不同的数据库中。 #### 六、数据访问层（DAL） 数据访问层（Data Access Layer, DAL）是...