当前位置: 代码迷 >> SQL >> SQL SERVER之事宜
  详细解决方案

SQL SERVER之事宜

热度:109   发布时间:2016-05-05 11:25:49.0
SQL SERVER之事务


  在实际对数据库的使用中,会出现多个用户同时对某一张表进行操作,当多个用户在同一时间对同一张数据表进行读取或者修改操作时,若处理不当就有可能发生冲突问题。为了解决这样的问题,就需要使用事务的控制和管理机制。

 

事务

   单个逻辑工作单元执行操作的集合,也可以看作是多条语句封装的结果。通过事务可以保证数据表中数据的一致性。

事务的特性

 

 

原子性

   是指事务中所有的执行操作,要么全部成功,要么不执行。如在商场购物中,管理员同时对用户进行充值操作。

  1. 修改账户A中的现金数。

  2. 修改账户A中的现金数

    如果在执行第一个SQL语句之后,第二个语句之前,突然断电了该如何办?

     

一致性

所谓的事务的一致性,是指一个事务操作执行完周,数据库中的数据必须处于合法一致的状态。例如如果账户A给账户B转账1000元后,那么账户A应该减少1000,账户B应该增加1000,但是两人的钱数总和还是一致的,应该处于合法的状态中。

 隔离性

    就是事务看到的数据库中的数据要么是这个事务被修改之前的状态,要么是被修改之后的状态。

 

 持久性

   如果一个事务被成功地修改,其结果在数据库中不会因为软件,硬件等故障而改变,数据会永久的保留下来

 

 

控制事务的流程

START TRANSACTION(开始事务)COMMIT(提交)ROLLBACK(事务回滚)

 

显示开始一个事务

 

使用START TRANSACTION或者BEGIN语句可以显示开始一个新的事务

 

语法规则:

START TRANSACTION{事务名}

     

隐式开始一个事务

 

SQL语句中的第一条语句开始就表示隐式开始了一个新的事务

 

提交事务

 

显示提交:COMMIT[事务名]

start transaction   --开始事务insert into accounttable values('A',5000)update accounttable set cashvalue=cashvalue+1000where accountuser ='A'commit   --提交事务

(这里把两条语句封装到了一起,一个是插入语句,一个是更新语句)

 

隐式提交

 

是指通过使用SQL语句就可以完成事务的提交,如果执行了CREATE TABLE,DROP TABLE

 

操作就会自动提交事务。

 

 

自动提交

 自动提交指通过设置AUTOCOMMIT命令完成事务的提交。

 

自动提交语句

SET AUTOCOMMIT=1

SET AUTOCOMMIT ON

 

关闭自动提交方式

SET AUTOCOMMIT=0

SET AUTOCOMMIT OFF

 

 

回滚事务

  是表示当事务执行失败时,数据库恢复到该事务操作之前的那一个合法状态中,并撤销对该表的一些操作。同时在数据库中还可以设置保存点,可以当发生意外时,回滚到保存点状态。

begin trybegin transaction--使用try语句进行捕捉错误insert into accounttable values(5000,'A')save tran a1insert into accounttable values(5000,'C')commitend trybegin catch--当发生错误时,进行回滚rollback tran a1end catch

 

(这段SQL语句,分别设置了2个保存点,分别是A1,A2,并使用rollback回滚机制)

 

并发事务的工作流程

  1. 用户user1使用SELECT语句查询到accounttable账户中有5000元,但是由于某些原因,用户user1并没有提取现金

  2. 用户2也通过SELECT语句查询到accounttable账户中有5000元,此时他开始事务,从账户中提取出了1000元,但是并没有提交事务。此时用户user1查到的还是5000

  3. 在用户user1查询到账户余额为5000后,希望全部提取出来,但是由于user2的事务并没有提交,所以用户1并不能提取

  4. 用户2提取1000后,提交了事务,此时剩余4000

  5. 用户1终于可以执行提取5000元操作了,可是此时只有4000元,操作将被撤销,回滚到他之前的操作状态

  6. 如果用户1还想全部取出的话,就必须开始一个新的事务

     

通过以上的叙述我们发现事物并发处理中存在的问题

 

  1. 读脏数据

    是指那些已经更改但还是没有被提交的数据。如用户2取走1000后,用户1提取5000时,会发现账户的余额不足。

  2. 不能重复读

    同一个事物中在多次执行时,由于其他事务对其做的修改或者删除等更新操作时,使得每次查询时返回的数据结果都不相同。如上例中,用户2已经取出1000元,而用户1查询到的仍然是5000

     

     

  3. 幻想读

    是指读取了其他事务中执行完插入或者更新操作后的错误数据。

     

    ------------------------------------------------------------------------

 

 

事务的隔离级别

1.READUNCOMMITTED:未提交读。正如上面叙述的那样,由于用户2没有提交,所以用户1还是读出了5000元。在READ UNCOMMITED隔离级别下,会隔离UPDATE语句,但不隔离SELECT语句。它的隔离级别最低。

 

 

 2.READ COMMITTED:提交读。给隔离级别在读取数据时对其加共享锁,可以避免读脏数据,但是在READ COMMITTED隔离级别下,事务在结束前更改可以更改数据,因此不能避免不能重复读或者幻想读。

 

3.REPEATABLE READ:可重复读。在该隔离级别中会将查询中使用的所有数据锁定,防止其他用户对改数据进行操作,可以避免产生不能重复读。

4.SERIALLZABLE :可串行化。该隔离级别在事务提交之前,会锁定整个数据表,防止其他用户对数据进行增加、删除和修改等更新操作。

 

下面是有关四种隔离级别允许不同的类型的行为

 

隔离级别

脏读

不可重复读取

幻象

未提交读

提交读

可重复读

可串行读

 

 

牛刀小试

下面的例子均以下表为例

                                     Table

Money

Int

Name

Char(4)

 

 

 

脏读操作

 由上面的表格可知,脏读操作发生在未提交数据时,正如我们的例子中,用户1查到了5000的情况。如下操作

第一个连接语句

<span style="font-family:SimSun;font-size:18px;">begin tranupdate table set money=103 where name='A'waitfor delay '00:00:10' --等待10秒update table set money=104 where name='A'commit tran</span>

接着马上执行第二个连接语句

<span style="font-family:SimSun;font-size:18px;">set transaction isolation level read uncommittedbegin transelect money from table where name='A'commit tran</span>

最终结果是103,而不是104,这就是脏数据,可知如果我们把第二个连接中的事务隔离级别设置为 READ MOMMITEDREPEATABLE READ或者SERIALLZABLE就可以避免这种情况发生。

 

非重复读操作

第一个连接语句

<span style="font-family:SimSun;font-size:18px;">set transaction isolation level read committed--或者是set transaction isolation level read uncommittedbegin transelect money from table where name='A'waitfor delay '00:00:10'  --等待10秒select money from table where name='A'commit tran</span>

接着马上执行第二个连接语句

 

<span style="font-family:SimSun;font-size:18px;">begin tranupdate table set money=10 where name='A'commit tran</span>

我们发现第一个连接中两次返回账号的余额不一样,第一次是100,第二次是10,这就是典型的“非重复读”的问题

根据上表所示,如果把事务的隔离级别设置为REPEATABLE READ或者SERIALLZABLE可以防止此类问题

 

幻象读

根据上表所示,当事务的隔离级别为READ COMMITTEDREAD UNCOMMITTEDREPEATABLE READ时就会发生幻象

 

先看下面的例子(账户余额为100

第一个连接语句

<span style="font-family:SimSun;font-size:18px;">begin transelect * from tablewaitfor delay '00:00:10'--等待10秒select * from tablecommit tran</span>

接着马上执行第二个连接语句

 

<span style="font-family:SimSun;font-size:18px;">begin traninsert into table values(300,'a')commit tran</span>

我们发现两次查询的结果不一样,这就是典型的幻象读问题,可知解决方法为把隔离级别设置为SERIALLZABLE即可。

 

 

小结:在实际应用的时候,采用何种隔离级别应视具体情况而定。

3楼u010926964昨天 14:13
学习了
2楼lfmilaoshi前天 19:45
这篇博客的内容有些多。最后,来个结尾是不是更温暖捏
Re: ZHOUCHAOQIANG昨天 12:38
回复lfmilaoshin知道了,以后肯定离不开与数据库打交道,所以先看一下这方面的内容
1楼u010539352前天 18:01
数据库这方面了解的很深刻啊
  相关解决方案