Posted in Working Skills, 技术

数据库必会必知 之 SQL四种语言:DDL DML DCL TCL

作者:泥瓦匠
今天群里面讨论,DDL 还是 DML,我这种小白还是总结下他们的区别吧。

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中处理数据库中的数据

其主要命令有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

 

最后来张图总结:(SELECT属于DQL哈。)

QQ20160612-0


如以上文章或链接对你有帮助的话,别忘了分享到朋友圈,让更多的人阅读这篇文章。

 

Posted in Working Skills, 技术

Mybatis 3 配置 Log4j,打印SQL

Writer      :BYSocket(泥沙砖瓦浆木匠)

微         博:BYSocket

豆         瓣:BYSocket

FaceBook:BYSocket

Twitter    :BYSocket

MyBatis Logging可以对包、类、命名空间做语句记录。Log4J为例。配置日志功能非常简单:

步骤1:添加Log4J的jar

无论你是web还是企业应用,将jar添加lib下。或者是对日志系统独立应用,将jar添加到-calsspath下。

步骤2:配置Log4J

配置及其简单,在log4j.properties文件下:

# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
log4j.logger.org.mybatis.example.BlogMapper=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

log4j把日志分为ALL、TRACE&(跟踪)、DEBUG(调试)、INFO(信息)、WARNING(警告)、ERROR(错误)、FITAL(致命)、OFF等几个级别,级别依次升高。级别高的Level会屏蔽级别低的信息。

ConversionPattern设置输出格式的参数说明:

 %p: 输出日志信息优先级,即DEBUG,INFO,WARN,ERROR,FATAL,

 %d: 输出日志时间点的日期或时间,默认格式为ISO8601,也可以在其后指定格式,比如:%d{yyy MMM dd HH:mm:ss,SSS},输出类似:2011年08月30日 15:00:00,921

%r: 输出自应用启动到输出该log信息耗费的毫秒数

%c: 输出日志信息所属的类目,通常就是所在类的全名

%t: 输出产生该日志事件的线程名

%l: 输出日志事件的发生位置,相当于%C.%M(%F:%L)的组合,包括类目名、发生的线程,以及在代码中的行数。举例:Testlog4.main(TestLog4.java:10)

%x: 输出和当前线程相关联的NDC(嵌套诊断环境),尤其用到像java servlets这样的多客户多线程的应用中。

%%: 输出一个”%”字符 %F: 输出日志消息产生时所在的文件名称 %L: 输出代码中的行号 %m: 输出代码中指定的消息,产生的日志具体信息

%n: 输出一个回车换行符,Windows平台为”\r\n”,Unix平台为”\n”输出日志信息换行

可以在%与模式字符之间加上修饰符来控制其最小宽度、最大宽度、和文本的对齐方式。如:

 1)%20c:指定输出category的名称,最小的宽度是20,如果category的名称小于20的话,默认的情况下右对齐。

 2)%-20c:指定输出category的名称,最小的宽度是20,如果category的名称小于20的话,”-”号指定左对齐。

 3)%.30c:指定输出category的名称,最大的宽度是30,如果category的名称大于30的话,就会将左边多出的字符截掉,但小于30的话也不会有空格。

 4)%20.30c:如果category的名称小于20就补空格,并且右对齐,如果其名称长于30字符,就从左边交远销出的字符截掉。
Posted in Working Skills, 技术

JavaEE 并发:一、FOR UPDATE 实战,监测并解决。

synchronized

 

Writer:BYSocket(泥沙砖瓦浆木匠)

微博:BYSocket

豆瓣:BYSocket

 

一、前言

针对并发,老生常谈了。目前一个通用的做法有两种:锁机制:1.悲观锁;2.乐观锁。

但是这篇我主要用于记录我这次处理的经历,另外希望能看的大神,大牛,技师者,学长,兄长,大哥们能在评论中发表自己的看法和解决技巧等。

 

二、故事是这样的

一个表,暂且叫 wallet,其中3个字段是 金额。初始值为0,如下图所示:
image

 

然后我们写了一个极为简单的Controller,并写了下面的Service代码:

@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如下:

SELECT 
    <include refid="Base_Column_List" />
    FROM wallet
    WHERE wallet_id = #{walletId,jdbcType=INTEGER}

我这边是MyBiatis,大家应该看得懂的。然后一个增加1 一个减少2 一个增加 3。

 

三、测试是这样

我用了Web应用压力测试工具:Boomhttps://github.com/rakyll/boom Go编写的HTTP(S)负载生成器,ApacheBench(AB)的替代工具。Boom是一个微型程序,能够对Web应用程序进行负载测试。它类似于 Apache Bench ,但在不同的平台上有更好的可用性,安装使用也比较简单。

简单使用方式如下:

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
image
可见后台程序报错了。什么错误呢?

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方法:

@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如下:

  <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,控制台没报错。

image

数据库结果也是很不错。

image

 

五、加大压力

按着上面的测试连接数5000,并发数350,控制台还是没报错。

image

数据库结果却是很出错了!!!
image

少update了很多值。为什么呢?

 

六、jvisualvm 小工具检测,发现Tomcat线程连接数默认不够

然后我用jvisualvm 小工具检测。多测了几次,发现连接数5000,并发数350,并发数上升。有一个图的值始终不变。如图:

QQ截图20150322124739

发现图中 tomcat的守护线程一直在200左右。后来我去找了下tomcat的server.xml发现了,使用了默认,大概就是200左右。

 

所以就配置了一下,大致配置方法有两种如下:

第1种方式:配置Connector
maxThreads:tomcat可用于请求处理的最大线程数
minSpareThreads:tomcat初始线程数,即最小空闲线程数
maxSpareThreads:tomcat最大空闲线程数,超过的会被关闭
acceptCount:当所有可以使用的处理请求的线程数都被使用时,可以放到处理队列中的请求数,超过这个数的请求将不予处理

<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:线程优先级

<Executorname="tomcatThreadPool"namePrefix="req-exec-"maxThreads="1000"minSpareThreads="50"maxIdleTime="60000"/>

<Connectorport="8080"protocol="HTTP/1.1"executor="tomcatThreadPool"/>

 

maxThreads:线程池的最大线程数,直接配置1000,然后用连接数10000,并发数800测试。轻松见图:

UFRJFLLO6@F1)LWQ6EQJ8P8

image

七、总结

感谢帮助我的人。希望有大牛在此讨论相关。小生感激不尽。