作者:泥瓦匠 原文链接:传送门
1. DDL – Data Definition Language
数据库定义语言:定义数据库的结构。
其主要命令有CREATE,ALTER,DROP等,下面用例子详解。该语言不需要commit,因此慎重。
CREATE – to create objects in the database 在数据库创建对象
例:CREATE DATABASE test; // 创建一个名为test的数据库
ALTER – alters the structure of the database 修改数据库结构
例:ALTER TABLE test ADD birthday date; // 修改test表,新增date类型的birthday列
DROP – delete objects from the database 从数据库中删除对象
例:DROP DATABASE test;// 删除test数据库
还有其他的:
TRUNCATE – 截断表内容(开发期,还是挺常用的)
COMMENT – 为数据字典添加备注
2. DML – Data Manipulation Language
数据库操作语言:SQL中处理数据库中的数据
其主要命令有SELECT,INSERT,UPDATE,DELETE等,这些例子大家常用就不一一介绍了。该语言需要commit。还有常用的 LOCK TABLE ,记得写过锁的博客 – 传送门
还有其他不熟悉的:
CALL – 调用一个PL/SQL或Java子程序
EXPLAIN PLAN – 解析分析数据访问路径
3. DCL – Data Control Language
数据库控制语言:授权,角色控制等
GRANT – 为用户赋予访问权限
REVOKE – 撤回授权权限
4. TCL – Transaction Control Language
事务控制语言
COMMIT – 保存已完成的工作
SAVEPOINT – 在事务中设置保存点,可以回滚到此处
ROLLBACK – 回滚
SET TRANSACTION – 改变事务选项
例子:Java中JDBC封装了对事务的支持。比如我们首先新建一个表:test
test.sql
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `city`
-- ----------------------------
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
`id` int(11) NOT NULL DEFAULT '0' COMMENT '城市ID',
`name` varchar(20) DEFAULT NULL COMMENT '名称',
`state` varchar(20) DEFAULT NULL COMMENT '状态',
`country` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET FOREIGN_KEY_CHECKS = 1;
JDBC事务回滚第一个例子 -JDBC数据库事务回滚:
/**
* 描述:JDBC数据库事务回滚
*
* Created by bysocket on 16/6/6.
*/
public class TransactionRollBack extends BaseJDBC {
public static void main(String[] args) throws SQLException {
Connection conn = null;
try {
// 加载数据库驱动
Class.forName(DRIVER);
// 数据库连接
conn = DriverManager.getConnection(URL,USER,PWD);
// 关闭自动提交的事务机制
conn.setAutoCommit(false);
// 设置事务隔离级别 SERIALIZABLE
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
Statement stmt = conn.createStatement();
int rows = stmt.executeUpdate("INSERT INTO city VALUES (3,'china',1,'cc')");
rows = stmt.executeUpdate("UPDATE city set country = 'TAIWAN' WHERE id = 4");
// 提交事务
conn.commit();
} catch (Exception e) {
e.printStackTrace();
// 回滚事务
if (conn != null) {
conn.rollback();
}
} finally {
/** 关闭数据库连接 */
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
第 19 行:设置了事务隔离级别为 SERIALIZABLE 底层调用的是TCL语言的SET TRANSACTION
第 22 行:执行通过,插入数据
第 23 行:执行不通过,没有主键为4的记录,直接抛出异常
第 31 行:事务回滚,封装的就是 TCL 语句的ROLLBACK
休息下,一个例子不够,再来一个。代码都在github主页上。https://github.com/JeffLi1993/jee-component-learning
JDBC事务回滚第二个例子-JDBC数据库事务回滚,回滚到特定的保存点:
/**
* 描述:JDBC数据库事务回滚,回滚到特定的保存点
*
* Created by bysocket on 16/6/6.
*/
public class TransactionRollBack2 extends BaseJDBC {
public static void main(String[] args) throws SQLException {
Connection conn = null;
Savepoint svpt = null;
try {
// 加载数据库驱动
Class.forName(DRIVER);
// 数据库连接
conn = DriverManager.getConnection(URL,USER,PWD);
// 关闭自动提交的事务机制
conn.setAutoCommit(false);
// 设置事务隔离级别 SERIALIZABLE
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
Statement stmt = conn.createStatement();
int rows = stmt.executeUpdate("INSERT INTO city VALUES (3,'china',1,'cc')");
// 设置事务保存点
svpt = conn.setSavepoint();
rows = stmt.executeUpdate("UPDATE city set country = 'TAIWAN' WHERE id = 4");
// 提交事务
conn.commit();
} catch (Exception e) {
e.printStackTrace();
// 回滚事务
if (conn != null) {
conn.rollback(svpt);
}
} finally {
/** 关闭数据库连接 */
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
和第一个例子重复的就不提了。
第 9 行:声明了一个保存点
第 24 行:设置了保存点
第 33 行:回滚事务到该保存点
上面的代码涉及到的是 TCL语言中的 SAVEPOINT
最后来张图总结:
附录:锁实例
一、前言
针对并发,老生常谈了。目前一个通用的做法有两种:锁机制:1.悲观锁;2.乐观锁。
但是这篇我主要用于记录我这次处理的经历,另外希望能看的大神,大牛,技师者,学长,兄长,大哥们能在评论中发表自己的看法和解决技巧等。
二、故事是这样的
一个表,暂且叫 wallet,其中3个字段是 金额。初始值为0,如下图所示:
然后我们写了一个极为简单的Controller,并写了下面的Service代码:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
@Override
public void testLock(int lockId)
{
Wallet wallet = walletMapper.selectByPrimaryKey(4);
BigDecimal one = new BigDecimal(1.00);
BigDecimal two = new BigDecimal(2.00);
BigDecimal three = new BigDecimal(3.00);
wallet.setWalletAmount(wallet.getWalletAmount().add(one));
wallet.setWalletAvailableAmount(wallet.getWalletAvailableAmount().subtract(two));
wallet.setOldAmount(wallet.getOldAmount().add(three));
walletMapper.updateByPrimaryKeySelective(wallet);
}
|
就简单的通过主键读取到一个对象,注意这个对象是没加锁的。也就是说,所对应的SQL如下:
1
2
3
4
|
SELECT
<
include
refid
=
"Base_Column_List"
/>
FROM wallet
WHERE wallet_id = #{walletId,jdbcType=INTEGER}
|
我这边是MyBiatis,大家应该看得懂的。然后一个增加1 一个减少2 一个增加 3。
三、测试是这样
我用了Web应用压力测试工具:Boom。https://github.com/rakyll/boom Go编写的HTTP(S)负载生成器,ApacheBench(AB)的替代工具。Boom是一个微型程序,能够对Web应用程序进行负载测试。它类似于 Apache Bench ,但在不同的平台上有更好的可用性,安装使用也比较简单。
简单使用方式如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
boom -n 1000 -c 200 http://www.baidu.com
Options:
-n Number of requests to run.
-c Number of requests to run concurrently. Total number of requests cannot
be smaller than the concurency level.
-q Rate limit, in seconds (QPS).
-o Output type. If none provided, a summary is printed.
"csv" is the only supported alternative. Dumps the response
metrics in comma-seperated values format.
-m HTTP method, one of GET, POST, PUT, DELETE, HEAD, OPTIONS.
-h Custom HTTP headers, name1:value1;name2:value2.
-d HTTP request body.
-T Content-type, defaults to "text/html".
-a Basic authentication, username:password.
-allow-insecure Allow bad/expired TLS/SSL certificates.
|
所以我就如图进行压力测试,可见这个小工具还挺美的,这里我连接数1000,并发数100:
可见后台程序报错了。什么错误呢?
1
|
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
|
原来并发导致update死表了。数据库的数据不用看了肯定是错误的。
四、FOR UPDATE的使用
先补一下其知识:利用select * for update 可以锁表/锁行。自然锁表的压力远大于锁行。所以我们采用锁行。什么时候锁表呢?
假设有个表单products ,里面有id跟name二个栏位,id是主键。
例1: (明确指定主键,并且有此笔资料,row lock)
SELECT * FROM wallet WHERE id=’3′ FOR UPDATE;
例2: (明确指定主键,若查无此笔资料,无lock)
SELECT * FROM wallet WHERE id=’-1′ FOR UPDATE;
例2: (无主键,table lock)
SELECT * FROM wallet WHERE name=’Mouse’ FOR UPDATE;
例3: (主键不明确,table lock)
SELECT * FROM wallet WHERE id<>’3′ FOR UPDATE;
例4: (主键不明确,table lock)
SELECT * FROM wallet WHERE id LIKE ‘3’ FOR UPDATE;
因此我们更新了下Service层的Mapper方法:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
@Override
public void testLock(int lockId)
{
Wallet wallet = walletMapper.selectForUpdate(4);
BigDecimal one = new BigDecimal(1.00);
BigDecimal two = new BigDecimal(2.00);
BigDecimal three = new BigDecimal(3.00);
wallet.setWalletAmount(wallet.getWalletAmount().add(one));
wallet.setWalletAvailableAmount(wallet.getWalletAvailableAmount().subtract(two));
wallet.setOldAmount(wallet.getOldAmount().add(three));
walletMapper.updateByPrimaryKeySelective(wallet);
}
|
所对应的SQL如下:
1
2
3
4
5
6
7
|
<
select
id
=
"selectForUpdate"
resultMap
=
"BaseResultMap"
parameterType
=
"java.lang.Integer"
>
SELECT
<
include
refid
=
"Base_Column_List"
/>
FROM wallet
WHERE wallet_id = #{walletId,jdbcType=INTEGER}
FOR UPDATE
</
select
>
|
自然大家可以看到,我这边加了锁,是通过主键锁行。
按着上面的测试连接数1000,并发数100,控制台没报错。
数据库结果也是很不错。
五、加大压力
按着上面的测试连接数5000,并发数350,控制台还是没报错。
少update了很多值。为什么呢?
六、jvisualvm 小工具检测,发现Tomcat线程连接数默认不够
然后我用jvisualvm 小工具检测。多测了几次,发现连接数5000,并发数350,并发数上升。有一个图的值始终不变。如图:
发现图中 tomcat的守护线程一直在200左右。后来我去找了下tomcat的server.xml发现了,使用了默认,大概就是200左右。
所以就配置了一下,大致配置方法有两种如下:
第1种方式:配置Connector
maxThreads:tomcat可用于请求处理的最大线程数
minSpareThreads:tomcat初始线程数,即最小空闲线程数
maxSpareThreads:tomcat最大空闲线程数,超过的会被关闭
acceptCount:当所有可以使用的处理请求的线程数都被使用时,可以放到处理队列中的请求数,超过这个数的请求将不予处理
1
|
<
Connectorport
=
"8080"
maxHttpHeaderSize
=
"8192"
maxThreads
=
"150"
minSpareThreads
=
"25"
maxSpareThreads
=
"75"
enableLookups
=
"false"
redirectPort
=
"8443"
acceptCount
=
"100"
connectionTimeout
=
"20000"
disableUploadTimeout
=
"true"
/>
|
第2种方式:配置Executor和Connector
name:线程池的名字
class:线程池的类名
namePrefix:线程池中线程的命名前缀
maxThreads:线程池的最大线程数
minSpareThreads:线程池的最小空闲线程数
maxIdleTime:超过最小空闲线程数时,多的线程会等待这个时间长度,然后关闭
threadPriority:线程优先级
1
2
3
|
<
Executorname
=
"tomcatThreadPool"
namePrefix
=
"req-exec-"
maxThreads
=
"1000"
minSpareThreads
=
"50"
maxIdleTime
=
"60000"
/>
<
Connectorport
=
"8080"
protocol
=
"HTTP/1.1"
executor
=
"tomcatThreadPool"
/>
|
maxThreads:线程池的最大线程数,直接配置1000,然后用连接数10000,并发数800测试。轻松见图:
七、总结
感谢帮助我的人。希望有大牛在此讨论相关。小生感激不尽。