SQL语言的分类

快来打我* 2022-06-05 04:00 227阅读 0赞

二. SQL语言的分类  
  
SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。  
  
1. 数据查询语言DQL  
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE  
子句组成的查询块：  
SELECT <字段名表>  
FROM <表或视图名>  
WHERE <查询条件>  
  
2 .数据操纵语言DML  
数据操纵语言DML主要有三种形式：  
1) 插入：INSERT  
2) 更新：UPDATE  
3) 删除：DELETE  
  
3. 数据定义语言DDL  
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、  
索引、同义词、聚簇等如：  
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER  
| | | | |  
表 视图 索引 同义词 簇

DDL操作是隐性提交的！不能rollback   
  
4. 数据控制语言DCL  
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制  
数据库操纵事务发生的时间及效果，对数据库实行监视等。如：  
1) GRANT：授权。

2) ROLLBACK \[WORK\] TO \[SAVEPOINT\]：回退到某一点。  
回滚---ROLLBACK  
回滚命令使数据库状态回到上次最后提交的状态。其格式为：  
SQL>ROLLBACK;

3) COMMIT \[WORK\]：提交。

在数据库的插入、删除和修改操作时，只有当事务在提交到数据  
库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看  
到所做的事情，别人只有在最后提交完成后才可以看到。  
提交数据有三种类型：显式提交、隐式提交及自动提交。下面分  
别说明这三种类型。

(1) 显式提交  
用COMMIT命令直接完成的提交为显式提交。其格式为：  
SQL>COMMIT；

(2) 隐式提交  
用SQL命令间接完成的提交为隐式提交。这些命令是：  
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，  
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

(3) 自动提交  
若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，  
系统将自动进行提交，这就是自动提交。其格式为：  
SQL>SET AUTOCOMMIT ON；

<table style="width:628px; margin:1px 0px!important; padding:0px!important; background:rgb(238,238,238)!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; font-size:12px!important; min-height:auto!important"> 
 <tbody style="margin:0px!important; padding:0px!important; background:none!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important"> 
  <tr style="margin:0px!important; padding:0px!important; background:none!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important"> 
   <td style="width:600px; margin:0px!important; padding:0px!important; background:none!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; min-height:auto!important"> 
    <div style="margin:0px!important; padding:0px!important; background:none!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:relative!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important"> 
     <div style="margin:0px!important; padding:0px 1em!important; background:none white!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; min-height:auto!important">mysql&gt; show variables </code> 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; min-height:auto!important">like</code> 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; min-height:auto!important">'autocommit'</code> 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; min-height:auto!important">;</code> 
     </div> 
     <div style="margin:0px!important; padding:0px 1em!important; background:none white!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; min-height:auto!important">+</code> 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; min-height:auto!important">---------------+-------+</code> 
     </div> 
     <div style="margin:0px!important; padding:0px 1em!important; background:none white!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; min-height:auto!important">| Variable_name | Value |</code> 
     </div> 
     <div style="margin:0px!important; padding:0px 1em!important; background:none white!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; min-height:auto!important">+</code> 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; min-height:auto!important">---------------+-------+</code> 
     </div> 
     <div style="margin:0px!important; padding:0px 1em!important; background:none white!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; min-height:auto!important">| autocommit&nbsp;&nbsp;&nbsp; | </code> 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; font-weight:bold!important; min-height:auto!important">ON</code>&nbsp;&nbsp;&nbsp; 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; min-height:auto!important">|</code> 
     </div> 
     <div style="margin:0px!important; padding:0px 1em!important; background:none white!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; min-height:auto!important">+</code> 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; min-height:auto!important">---------------+-------+</code> 
     </div> 
     <div style="margin:0px!important; padding:0px 1em!important; background:none white!important; border:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; min-height:auto!important; white-space:pre!important"> 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; min-height:auto!important">1 row </code> 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; min-height:auto!important">in</code> 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; font-weight:bold!important; min-height:auto!important">set</code> 
      <code style="margin:0px 5px; padding:0px 2px; border:1px solid rgb(204,204,204); color:rgb(255,127,80); background:none!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; outline:0px!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas,&quot;Bitstream Vera Sans Mono&quot;,&quot;Courier New&quot;,Courier,monospace!important; min-height:auto!important"> (0.04 sec)</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

从查询结果中，我们发现Value的值是ON，表示autocommit开启。我们可以通过以下SQL语句改变这个模式

值0和OFF都是一样的，当然，1也就表示ON。通过以上设置autocommit=0，则用户将一直处于某个事务中，直到执行一条commit提交或rollback语句才会结束当前事务重新开始一个新的事务。

事务的四大属性ACID即事务的原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability.。

原子性（Atomicity）  
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）  
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

隔离性（Isolation）  
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（Durability）  
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

事务的隔离级别

l       多个线程开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个线程在获取数据时的准确性。

l      如果不考虑隔离性，可能会引发如下问题：

1、  脏读：

•        指一个事务读取了另外一个事务未提交的数据。

这是非常危险的，假设Ａ向Ｂ转帐１００元，对应sql语句如下所示

1.updateaccount set money=money+100 while name=‘b’;

2.updateaccount set money=money-100 while name=‘a’;

当第1条sql执行完，第2条还没执行(A未提交时)，如果此时Ｂ查询自己的帐户，就会发现自己多了100元钱。如果A等B走后再回滚，B就会损失100元。

2、不可重复读：

•        在一个事务内读取表中的某一行数据，多次读取结果不同。

例如银行想查询A帐户余额，第一次查询A帐户为200元，此时A向帐户存了100元并提交了，银行接着又进行了一次查询，此时A帐户为300元了。银行两次查询不一致，可能就会很困惑，不知道哪次查询是准的。

•        和脏读的区别是，脏读是读取前一事务未提交的脏数据，不可重复读是重新读取了前一事务已提交的数据。

•        很多人认为这种情况就对了，无须困惑，当然是后面的为准。我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和写到文件中，结果在一个事务中针对输出的目的地，进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。

3、虚读(幻读)

•        是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

•        如丙存款100元未提交，这时银行做报表统计account表中所有用户的总额为500元，然后丙提交了，这时银行再统计发现帐户为600元了，造成虚读同样会使银行不知所措，到底以哪个为准。

因此，在特定的情况下，为事务设定相应的隔离级别是十分必要的。

事务的隔离级别的设定方法如下：

Serializable：可避免脏读、不可重复读、虚读情况的发生。（串行化）(序列化)

•        Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读）

•        Read committed：可避免脏读情况发生（读已提交）。

•        Read uncommitted：最低级别，以上情况均无法保证。(读未提交)

set   transaction isolation level 设置事务隔离级别

select@@tx\_isolation      查询当前事务隔离级别

正确使用隔离级别，了解事务的四大属性，有助于我们更好的借助事务解决实际问题。

在MySQL数据库中，支持上面四种隔离级别，默认的为Repeatable read (可重复读)；而在Oracle数据库中，只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别，其中默认的为Read committed级别。

在MySQL数据库中查看当前事务的隔离级别：

select @@tx_isolation;

在MySQL数据库中设置事务的隔离 级别：

set  [glogal | session]  transaction isolation level 隔离级别名称;
    
        set tx_isolation=’隔离级别名称;’

例1：查看当前事务的隔离级别：

![787876-20160313202200007-1111796802.png][]

例2：将事务的隔离级别设置为Read uncommitted级别：

![787876-20160313202224241-2101542210.png][]

或：

![787876-20160313202245210-345198166.png][]

记住：设置数据库的隔离级别一定要是在开启事务之前！

如果是使用JDBC对数据库的事务设置隔离级别的话，也应该是在调用Connection对象的setAutoCommit(false)方法之前。调用Connection对象的setTransactionIsolation(level)即可设置当前链接的隔离级别，至于参数level，可以使用Connection对象的字段：

![787876-20160313202333460-377269897.png][]

在JDBC中设置隔离级别的部分代码：

![787876-20160313202355694-2106931487.png][]

后记：隔离级别的设置只对当前链接有效。对于使用MySQL命令窗口而言，一个窗口就相当于一个链接，当前窗口设置的隔离级别只对当前窗口中的事务有效；对于JDBC操作数据库来说，一个Connection对象相当于一个链接，而对于Connection对象设置的隔离级别只对该Connection对象有效，与其他链接Connection对象无关。

Hibernate中事务的隔离级别，如下方法分别为1/2/4/8。

![1085788-20161224143126229-1527379960.png][]

在Hibernate配置文件中设置，设置代码如下

![1085788-20161224143323682-1607746145.png][]

悲观锁(Pessimistic Lock), 顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

乐观锁(Optimistic Lock), 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write\_condition机制的其实都是提供的乐观锁。

两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

## 乐观锁 ##

> 在关系数据库管理系统里，乐观并发控制（又名”乐观锁”，Optimistic Concurrency Control，缩写”OCC”）是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。乐观事务控制最早是由孔祥重（H.T.Kung）教授提出。

### 乐观并发控制的阶段 ###

乐观并发控制的事务包括以下阶段：   
1. 读取：事务将数据读入缓存，这时系统会给事务分派一个时间戳。   
2. 校验：事务执行完毕后，进行提交。这时同步校验所有事务，如果事务所读取的数据在读取之后又被其他事务修改，则产生冲突，事务被中断回滚。   
3. 写入：通过校验阶段后，将更新的数据写入数据库。

乐观并发控制多数用于数据争用不大、冲突较少的环境中，这种环境中，偶尔回滚事务的成本会低于读取数据时锁定数据的成本，因此可以获得比其他并发控制方法更高的吞吐量。

相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。

数据版本,为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

实现数据版本有两种方式，第一种是使用版本号，第二种是使用时间戳。 使用版本号实现乐观锁

使用版本号时，可以在数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行+1操作。并判断当前版本号是不是该数据的最新的版本号。

### 使用版本号实现乐观锁 ###

使用版本号时，可以在数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行+1操作。并判断当前版本号是不是该数据的最新的版本号。

1.查询出商品信息
    select (status,status,version) from t_goods where id=#{id} 2.根据商品信息生成订单 3.修改商品status为2 update t_goods set status=2,version=version+1 where id=#{id} and version=#{version};
     
      
       1
       2
       3
       4
       5
       6
       7

### 优点与不足 ###

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。

## 悲观锁 ##

> 在关系数据库管理系统里，悲观并发控制（又名”悲观锁”，Pessimistic Concurrency Control，缩写”PCC”）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作读某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。

悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

### 使用 ###

MySQL InnoDB中使用悲观锁

要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。`set autocommit=0;`

#0.开始事务
    begin;/begin work;/start transaction; (三者选一就可以)
    #1.查询出商品信息
    select status from t_goods where id=1 for update;
    #2.根据商品信息生成订单
    insert into t_orders (id,goods_id) values (null,1);
    #3.修改商品status为2
    update t_goods set status=2;
    #4.提交事务
    commit;/commit work;
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10

上面的查询语句中，我们使用了select…for update的方式，这样就通过开启排他锁的方式实现了悲观锁。此时在t\_goods表中，id为1的 那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

上面我们提到，使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。

### 优点与不足 ###

悲观并发控制实际上是”先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；另外，在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数

## 总结 ##

乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write\_condition机智的其实都是提供的乐观锁。 相反，如果经常发生冲突，上层应用会不断进行 retry，这样反而降低了性能，所以这种情况下用悲观锁比较合适。

MySQL有两种常用的引擎类型：MyISAM和InnoDB。目前只有InnoDB引擎类型支持外键约束。InnoDB中外键约束定义的语法如下：

ALTER TABLE tbl_name
        ADD [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (index_col_name, ...)
        REFERENCES tbl_name (index_col_name,...)
        [ON DELETE reference_option]
        [ON UPDATE reference_option]

例如：

ALTER TABLE `user_resource` CONSTRAINT `FKEEAF1E02D82D57F9` FOREIGN KEY (`user_Id`) REFERENCES `sys_user` (`Id`)

InnoDB也支持使用ALTER TABLE来删除外键：

ALTER TABLE `user_resource` DROP FOREIGN KEY `FKEEAF1E02D82D57F9`;

CASCADE  
在父表上update/delete记录时，同步update/delete掉子表的匹配记录

SET NULL  
在父表上update/delete记录时，将子表上匹配记录的列设为null (要注意子表的外键列不能为not null)    
  
NO ACTION  
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作    
  
RESTRICT  
同no action, 都是立即检查外键约束  
  
SET NULL  
父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别

`NULL、RESTRICT、NO ACTION`

`删除：从表记录不存在时，主表才可以删除。删除从表，主表不变`

`更新：从表记录不存在时，主表才可以更新。更新从表，主表不变`

`CASCADE`

`删除：删除主表时自动删除从表。删除从表，主表不变`

`更新：更新主表时自动更新从表。更新从表，主表不变`

` `

`SET NULL`

`删除：删除主表时自动更新从表值为NULL。删除从表，主表不变`

`更新：更新主表时自动更新从表值为NULL。更新从表，主表不变`

外键约束属性： RESTRICT | CASCADE | SET NULL | NO ACTION  外键的使用需要满足下列的条件：

1. 两张表必须都是InnoDB表，并且它们没有临时表。

2. 建立外键关系的对应列必须具有相似的InnoDB内部数据类型。

3. 建立外键关系的对应列必须建立了索引。

4. 假如显式的给出了CONSTRAINT symbol，那symbol在数据库中必须是唯一的。假如没有显式的给出，InnoDB会自动的创建。

如果子表试图创建一个在父表中不存在的外键值，InnoDB会拒绝任何INSERT或UPDATE操作。如果父表试图UPDATE或者DELETE任何子 表中存在或匹配的外键值，最终动作取决于外键约束定义中的ON UPDATE和ON DELETE选项。InnoDB支持5种不同的动作，如果没有指定ON DELETE或者ON UPDATE，默认的动作为RESTRICT:

1. CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。

2. SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。

3. NO ACTION: InnoDB拒绝删除或者更新父表。

4. RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。

5. SET DEFAULT: InnoDB目前不支持。

外键约束使用最多的两种情况无外乎：

1）父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败；

2）父表更新时子表也更新，父表删除时子表匹配的项也删除。

前一种情况，在外键定义中，我们使用ON UPDATE CASCADE ON DELETE RESTRICT；后一种情况，可以使用ON UPDATE CASCADE ON DELETE CASCADE。

当执行外键检查之时，InnoDB对它照看着的子或父记录设置共享的行级锁。InnoDB立即检查外键约束，检查不对事务提交延迟。

要使得对有外键关系的表重新载入转储文件变得更容易，mysqldump自动在转储输出中包括一个语句设置FOREIGN\_KEY\_CHECKS为0。这避免在转储被重新装载之时，与不得不被以特别顺序重新装载的表相关的问题。也可以手动设置这个变量：

mysql> SET FOREIGN\_KEY\_CHECKS = 0;

mysql> SOURCE dump\_file\_name;

mysql> SET FOREIGN\_KEY\_CHECKS = 1;

如果转储文件包含对外键是不正确顺序的表，这就以任何顺序导入该表。这样也加快导入操作。设置FOREIGN\_KEY\_CHECKS为0，对于在LOAD DATA和ALTER TABLE操作中忽略外键限制也是非常有用的。

InnoDB不允许你删除一个被FOREIGN KEY表约束引用的表，除非你做设置SET FOREIGN\_KEY\_CHECKS=0。当你移除一个表的时候，在它的创建语句里定义的约束也被移除。

如果你重新创建一个被移除的表，它必须有一个遵从于也引用它的外键约束的定义。它必须有正确的列名和类型，并且如前所述，它必须对被引用的键有索引。如果这些不被满足，MySQL返回错误号1005 并在错误信息字符串中指向errno 150。

[787876-20160313202200007-1111796802.png]: /images/20220605/6d045d7338f24dafba93515bf88be80f.png
[787876-20160313202224241-2101542210.png]: /images/20220605/d09224577af4448285512be2b3659ad7.png
[787876-20160313202245210-345198166.png]: /images/20220605/316a3fb1052646d1b99d4e3f04f5a2ce.png
[787876-20160313202333460-377269897.png]: /images/20220605/4e61090f6d094ec5bc3a9e48ff129a74.png
[787876-20160313202355694-2106931487.png]: /images/20220605/be926131ab704845889bfd14a1a3eb19.png
[1085788-20161224143126229-1527379960.png]: /images/20220605/b116c684e39b41fe905f2bf4a7d44d54.png
[1085788-20161224143323682-1607746145.png]: /images/20220605/81a5e5159bc44f22aa057f6b69584573.png