1 事务

数据库事务是指一个逻辑工作单元中执行的一系列操作,要么完全地执行,要么完全地不执行，它还有哪些特征？

它具有如下特征： （原子性、一致性、隔离性、持久性），或称（ACID），由 Andreas Reuter 于 1983 年提出。

它是数据库管理系统（DBMS）中的一个概念，它标识了一组用于保证数据库可靠性的标准属性。

单个事务的属性:

	原子性	事务所有步骤必须全部成功，否则 任何步骤都不会被提交
	一致性	事务所有步骤必须全部成功，否则，所有数据恢复到事务开始之前
	隔离性	事务完成之前，所有已执行步骤必须与系统保持隔离
	持久性	所有提交的数据，系统必须加以恰当保存，并保证万一系统发生故障时仍然能恢复数据到有效

ACID 属性可确保所有数据库事务保持准确且一致，并支持从处理操作期间可能发生的故障中恢复。

因此，几乎所有关系数据库都实现了它。而Mysql的InnoDB的支持相对最严谨。

事实证明，为事务提供支持的 DBMS 会自动强制实施四个 ACID 属性。

我们将查看如何在存储过程中使用事务来防止数据不一致。

举个例子: 我们将钱从支票帐户转移到储蓄帐户。这个动作实际上由两部分组成：

	1 从银行帐户中取资金。
	2 将资金存入储蓄银行。

现在，想象一下如果第一步之后停电了会发生什么。

如果从支票帐户中扣除了资金但未将其添加到储蓄帐户中，我认为我们都同意那是一个问题！

正如你不希望在财务交易中发生这种情况一样，我们也不希望更新一个数据库表而没有更新引用它的表。通过使用事务，可以保证两个操作一起成功或一起失败。

这样，所有涉及的实体将保持一致状态。

2 事务控制语句

BEGIN 或 START TRANSACTION 显式地开启一个事务
COMMIT 也可以使用COMMIT WORK，不过二者是等价的，COMMIT 提交事务，并使得对数据库进行的所有修改成为永久

ROLLBACK 也可以使用 ROLLBACK WORK，二者等价，回滚将结束用户事务，并撤销正在进行的所有未提交的事务修改

SAVEPOINT pointname， SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT
RELEASE SAVEPOINT pointname 删除一个事务的保存点，当没有指定保存点时，执行该语句抛出一个异常

ROLLBACK TO pointname 把事务回滚到标记点
RELEASE SAVEPOINT savepoint_name; // 删除指定保留点

脏读：读到了还未提交事务的数据。

幻读：两次事务读到的数据不一致。中间有新事务提交。

不可重复读：一次事务中不允许多次读，会发生数据不一致。中间有新事务提交。

SET TRANSACTION 用来设置事务的隔离级别，InnoDB 存储引起提供事务隔离级别如下

                    脏读      不可重复读     幻读	      序列化事务
READ UNCOMMITTED    可能	  可能	   可能	    可能
                        当前连接	  (此时其他连接修改将被卡住)	
                                                  表数据为 最后由提交事务确定。  
        特征：读取未提交数据，即临时数据。 A可以读取到B还未提交的事务。
        优点：  redo日志，可以看到其他事务的数据。 比如买票。
        缺点：安全性较差，隔离级别低。

READ COMMITTED   不可能	不可能	不可能		可能 (此时其他事务的写将被卡住)	
        表数据为 最后由提交事务确定。 
        特征： 读取已提交数据。 A只能读取到B已经提交的事务。A事务中两次读取相同数据，内容可以不一致，因为包括B事务的提交数据 。
        当前事务只能读取其他事务已经提交的数据，未提交的临时数据是读取不到的；
        优点：性能适中，适用范围广，比如银行转账
        缺点： 仍然可以读取其他事务的数据，可能导致严格业务的错误。

REPEATABLE READ	不可能	不可能   不可能	 可能
                  (此时其他事务的写将被卡住)	
         表数据为 最后由提交事务确定。
        特征： 重复读取 数据一致。  A不读取到B已经提交的事务。 并且A事务中两次读到的内容一致，A事务结束后再读取会读取到B提交事务。

        优点：业务隔离更严格，适用于电商业务；
                当前的事务能读到的数据是事务开始之前的数据，事务开启之后，其他事务提交的数据是读不到的；
        缺点：
                将增加 间隙锁冲突的 可能。 性能可能下降
                有索引时，为间隙锁，没有索引 则锁全表。
                频繁的插入、更新过程中更容易产生锁等待。甚至是死锁。
                RR级别使用MVCC(多版本并发控制) + 间隙锁来解决幻读的情况

SERIALIZABLE    不可能	    不可能		不可能	 不可能
        特征：序列化。 A事务未提交，B事务就等待。 直到失败
        优点：避免一起业务问题，
        缺点：数据库的并发性能急剧下降。

"READ-UNCOMMITTED", A 可以 读取 B 未提交的更改
"READ-COMMITTED",   A 只能读取 B 已提交的更改
"REPEATABLE-READ", 事务只能读取事务内的数据
"SERIALIZABLE",    事务 按顺序执行，不能相互读取任何内容，A 执行时，B 不能进行操作。

3 示例：

		SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
		BEGIN
		...
		COMMIT;

• 深入问题常用操作

    1.数据库默认隔离级别:  mysql ---repeatable,
    			oracle,sql server ---read commited
    			postgresql --- read commited
    2.mysql binlog的格式三种：statement,row,mixed
    3.为什么mysql用的是repeatable而不是read committed:
    	在 5.0之前只有statement一种格式，而主从复制存在了大量的不一致，故选用repeatable
    4.为什么默认的隔离级别都会选用read commited 
    	原因有二：
    		repeatable存在间隙锁会使死锁的概率增大，在RR隔离级别下，条件列未命中索引会锁表！
    		而在RC隔离级别下，只锁行****
    	2.在RC级用别下，主从复制用什么binlog格式：row格式，是基于行的复制！
  

• MYSQL 事务处理主要两种方法

  1， BEGIN 开始事务

   ROLLBACK 事务回滚
   COMMIT 事务确认
  

  2， 直接用SET 改变MySQL自动提交模式

   SET AUTOCOMMIT=0  禁止自动提交
   SET AUTOCOMMIT=1 开启自动提交
  

  MySql的默认事务隔离级别是RR（可重复读），设置 为 RC 读提交

   1、使用RR事务隔离级别，能避免幻读，但是由于引入间隙锁导致加锁的范围可能扩大，从而会影响并发，还容易造成死锁，因为间隙锁和间隙锁是不冲突的；
   2、在大多数业务场景下，事务隔离级别RC基本上能满足业务需求，幻读出现的机率较少；
  

从够用的角度来看，选择RC隔离级别是可以的

4 设置事务的几种方式

	1全局
	直接改配置文件
		set global transaction isolation level repeatable read;
	2当前session
		set tx_isolation = 'repeatable-read';
		set session transaction isolation level repeatable read;
	3下一个事务
		set transaction isolation level repeatable read;

• 例子
  go 启动 mysql 事务
  DSN = “admin:admin20@tcp(127.0.0.1:3306)/mystate?multiStatements=true&allowNativePasswords=false&checkConnLiveness=false&maxAllowedPacket=0”

创建连接池，默认无限制

	db, err = sql.Open("mysql", DSN) 
	if err != nil {
		log.Fatal(err)
	}
	db.SetConnMaxLifetime(time.Minute * 3)
	db.SetMaxOpenConns(2000)
	db.SetMaxIdleConns(1000)

	Logg.Println("conn mysql success with", DSN)
	defer db.Close()

	ptx, err := db.Begin()
	ptx, err := db1.Begin()
	if err != nil {
		msg := fmt.Sprintf("start translation failure wuth db1 connection.: %+v\n", err)
		panic(msg)
	}
	_,err = ptx.Exec("ROLLBACK;")
	rst, isoerr := ptx.Exec("SET TRANSACTION ISOLATION LEVEL " +isolationLevel)
	if isoerr != nil {
		msg := fmt.Sprintf("set fault to :%v, %v\n", isolationLevel, isoerr)
		panic(msg)
	}
	_,err = ptx.Exec("BEGIN;")
	name, err := ptx.Exec("SELECT name FROM users where id = 1;")

补充：不同的数据库中，SQL语句使用的占位符语法不尽相同。

数据库 占位符语法

	MySQL	?
	PostgreSQL	$1, $2等
	SQLite	? 和$1
	Oracle	:name

5 innoDB 的对比

mysql支持事务的默认引擎为innoDB.

InnoDB的行锁
InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION),并且事务是默认自动进行提交的(可修改autocommit变量)；二是采用了行级锁。

行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。

这里注意一个问题，和锁没什么关系，可以跳过：InnoDB没设置主键使用隐式ROW_ID.

• 1. 查看行锁的争用情况

     show status like “innodb_row_lock%”;
     ±------------------------------±------+
     | Variable_name | Value |
     ±------------------------------±------+
     | Innodb_row_lock_current_waits | 0 |
     | Innodb_row_lock_time | 0 |
     | Innodb_row_lock_time_avg | 0 |
     | Innodb_row_lock_time_max | 0 |
     | Innodb_row_lock_waits | 0 |
     ±------------------------------±------+
     5 rows in set (0.09 sec)

  如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高.
  还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

  并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

• 2 锁的粒度
  SQL Server可以锁定表、分页、行等级别的数据资源。它同样可以锁定索引键及一定范围内的索引键。
  谨记如果表上存在聚集索引，数据行就在聚集索引的叶级，并且是由键锁而不是行锁来锁定它们的。

  SQL中的锁(行锁、页锁、表锁、共享锁、排它锁、乐观锁、悲观锁)

  SQL中的锁按照锁颗粒对锁进行划分行锁页锁表锁从数据库管理的角度对锁进行划分共享锁排它锁从程序员的角度对锁进行划分
  乐观锁 (Optimistic Locking）悲观锁（Pessimistic Locking）适用场景避免死锁的发生 锁用来对数据进行锁定，我们可以从锁定对象的粒度大小来对锁进行划分，分别为行锁、页锁和表锁。
  按照锁颗粒对锁进行划分 行锁 就是按照行的粒度对数据进行锁定。
  锁定力度小，发生锁冲突概率低，可以实现的并发度高，但是对于锁的开销比较大，加锁会比较慢，容易出现死锁情况。

6 引擎设置

数据源设置 ,完整形式的 DSN：

username:password@protocol(address)/dbname?param=value

除数据库名称外，所有值都是可选的。所以最小的DSN是：

/dbname	

不希望预选数据库，请dbname 为空

/

这与空DSN字符串具有相同效果,创建表时可选引擎

	CREATE TABLE  IF NOT EXISTS  `users` ( 
	`name` VARCHAR(40) NOT NULL, 
	PRIMARY KEY (name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

7 如何进行sql优化

优化数据库的方法,选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置NOTNULL，例如’省份’、’性别’最好适用ENUM.

 使用连接(JOIN)来代替子查询
 适用联合(UNION)来代替手动创建的临时表
 事务处理
 锁定表、优化事务处理
 适用外键，优化锁定表
 建立索引
 优化查询语句

如何进行SQL优化？答：

（1）选择正确的存储引擎

以 MySQL为例，包括有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。
MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。但是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

（2）优化字段的数据类型

记住一个原则，越小的列会越快。如果一个表只会有几列罢了（比如说字典表，配置表），那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多。当然，你也需要留够足够的扩展空间。

（3）为搜索字段添加索引

索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么最好是为其建立索引，除非你要搜索的字段是大的文本字段，那应该建立全文索引。

(4)避免使用Select * 从数据库里读出越多的数据，那么查询就会变得越慢。

并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。

即使你要查询数据表的所有字段，也尽量不要用*通配符，善用内置提供的字段排除定义也许能给带来更多的便利。

(5)使用 ENUM 而不是 VARCHAR

ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。

例如，性别、民族、部门和状态之类的这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

(6)尽可能的使用 NOT NULL

除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。

NULL其实需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。

当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。

(7)固定长度的表会更快

如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。

例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。

固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。

而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。

并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。