sql之事务和并发

1、Transaction(事务)是什么:

 事务是作为单一工作单元而执行的一系列操作。包括增删查改。

2、事务的种类:

 事务分为显示事务和隐式事务:

隐式事务:就是平常我们使用每一条sql 语句就是一个事务,只不过他们执行完成之后事务就跟着结束了。

显示事务:就是需要我们来手写了,这个时候就可以进行控制事务的开始和结束了。

 1 --显式事务(对事物可以进行控制)
 2 
 3 --开始事务
 4 begin transaction;
 5 update [Sales.Shippers]
 6 set companyname='顺丰' where shipperid=5;
 7 
 8 select * from [Sales.Shippers];
 9 
10 --结束事务:
11 --第一种:事务的回滚
12 rollback;
13 
14 --第二种:事务的提交
15 commit;

 

3、事务很重要的四个属性: 

 1、原子性:事务必须是原子工作单位。——在事务中修改数据,要么全都执行,要么全都不执行。在事务执行完成之前(调提交指令写入到sql的事务日志之前),出现问题或重启,sql server 会回滚所有的修改事务。 但是也有例外的错误不会回滚事务————例如:主键冲突和锁超时等。  错误日志会 捕获这些错误的指令,并记录日志里面,然后执行一些操作(例如:回滚事务)

 

2、一致性:发生在同一进程的事物里面的 修改和 查询是不会产生冲突的。保持访问的数据的一致性。

 

3、隔离性:控制数据访问的机制; 说明: 一个事务正在对一个表的数据正在修改, 还没有执行完成;;这时另一个事务,想要查询里面的数据,是不能查到的,必须等到 修改的事务执行完成。:sql server 采用的 “锁”的机制,将正在修改的事务 处理的表的数据 锁定。这样是为了保证数据同步,数据的一致性。

 

4、持久性:  当一个事务的指令 已经提交到 事务日志里面,即使磁盘上的数据还没有修改,这个时候数据库的服务停止,在服务重启的时候还会将事务日志里的指令执行(进行回复处理)。保证数据的持久性。

 

上面将基本的事务介绍了一下,下面开始介绍并发。所以必须要介绍就是事务的“锁”。

4、事务中的锁

 事务中都含有什么锁呢?

最常用的锁:排它锁(独占锁)和共享锁,还有其他的锁,这里就不做介绍了,比如:更新锁、架构锁、意向锁等。

 

5、排它锁和共享锁

 排它锁:

当一个事务执行更新修改操作的时候会申请排它锁,主要是在写操作里面使用。需要注意的两点:1、一个事务含有排它锁,就不能含有其他任何锁。2、一条数据只能被一个排它锁锁住,就不能再被其他排他锁锁定。

共享锁:

 主要是在读操作中使用,并且多个事务可以同时对一条数据使用共享锁。

 

排它锁和共享锁最重要的区别:排它锁是不能被控制他的处理方式和时间,但是共享锁是可以控制其隔离级别来控制其处理的时间。

1 begin transaction;
2 update [Sales.Shippers] set companyname='顺丰' where shipperid=5;
3 --事务还没有查询完成,为这条数据 加上一个 排它锁。这时这条数据就不能被其他进程 访问到

 

事务还没有执行完成,再开一个线程,执行查询操作

1 select * from [Sales.Shippers] where shipperid=5

 

因为读操作默认使用的共享锁,但是这个时候这条数据已经被其他线程的排它锁锁住,所以会造成阻塞,直到排它锁释放。

 

6、隔离级别 

 首先要先明白三点:

1、用于控制并发用户如何读写数据的操做。

2、读操作默认使用共享锁;写操作需要使用排它锁。

3、读操作能够控制他的处理的方式,写操作不能控制它的处理方式

 

隔离级别分为六种:

read uncommited(读取未提交数据),read commited(读取已提交数据)读取的默认方式,repeatable read(可重复读),serializable(可序列化),snapshot(快照),read commited snapshot(已经提交读隔离)(后两个是sql server 2005 里面 引入的)。隔离的强度依次递增。

 

 1、read uncommitted:

1 select * from [Sales.Shippers] where shipperid=3;

 

 查询结果:

在本线程内执行:

1 begin transaction;
2 update [Sales.Shippers] set companyname='圆通' where shipperid=3;

 

在另外一个线程内 使用 read uncommitted 隔离级别 查询数据:

1 --设置读操作的隔离级别
2 set transaction isolation level read uncommitted;
3 select * from [Sales.Shippers] where shipperid=3;

 查询结果:

如果这个时候将那个事务回滚,那么这个时候  查询到的数据就是“脏数据”。

总结:

read uncommitted:最低的隔离级别:查询的时候不会请求共享锁,所以不会和排它锁产生冲突(不会等待排它锁执行完),查询效率非常高,速度飞快。但是缺点:会查到“脏数据”(排它锁的事务已经将数据修改,还没提交,这个时候查询到的数据 是已经更改过的。如果事务回滚,就是“脏数据”)

优点:查询效率非常高,速度非常快。

缺点:会产生“脏数据”

适用性:

适用于 像聊天软件的 聊天记录,会是软件的运行速度非常快。 但是不适用于 商务软件。尤其是银行

 

2、read committed

 读取的默认隔离级别就是read committed 和上面正好相反。如果上面情况,采用read committed 隔离级别查询的话查到的就是还没有更改之前的数据。

 所以在这里就不再演示。

3、repeatable read:

 查询的时候会加上共享锁,但是查询完成之后,共享锁就会被撤销。比如一些购票系统,如果查到票了,当买的时候就没有,这是不行的。所以要在查询到数据之后做一些延迟共享锁,进而阻塞排它锁来修改。(如果查询的事务没有提交,不会释放共享锁,这个时候独占锁就不能访问这条数据

注意:1、repeatable 只会锁定查询的数据 ,而 其他行数据还可以进行 修改(更新、删除)(下面那条语句共享锁只会锁定 shipperid为4 的行)

       2、其他进行插入数据,并且插入的数据满足第一次开始事务时的 查询的筛选条件的时候;第二次查询的时候就会将新插入的数据 查询出来。这就叫做“幻读”(解决幻读,需要更高级别的隔离,就是下面的serializable)

       

在查询线程里面执行sql语句:

1 set transaction isolation level repeatable read;
2 begin transaction;
3 select * from [Sales.Shippers] where shipperid=4;

 

然后在 另外一个线程内执行修改语句:

update [Sales.Shippers] set companyname='shit' where shipperid=4;

 

这个时候会将更改的线程阻塞掉:

 

幻读演示:

在线程1 内 输入:

1 set transaction isolation level serializable;
2 begin transaction;
3 select * from [Sales.Shippers] where companyname='顺丰'

 

结果:

 

在线程2 内 输入 插入语句:

1 insert into [Sales.Shippers] (companyname,phone) values('顺丰',112321211)

 

结果:

 

在  用 共享锁 执行查询语句:

1 select * from [Sales.Shippers] where companyname='顺丰'

 

结果:

 

4、serializable(可序列化)

 更高级的 隔离。用户解决“幻读”(上面提到的)。就是使用上面的(repeatable read)  加上共享锁 并不撤销,如果锁定的 一行数据,那么 其他的进程 还可以对 其他的数据进行操作,也可以 进行新增和删除的操作。   所以如果想要在查询的时候,不能对整张表进行任何操作,那么就要 将表的结构也 锁定    (就需要使用 更强的 锁定)

在查询线程执行sql语句:

1 set transaction isolation level serializable;
2 begin transaction;
3 select * from [Sales.Shippers] where companyname='顺丰'

 

 

那么在另外一个线程执行下面两个语句,不论那一条语句都会阻塞住:

1 insert into [Sales.Shippers] (companyname,phone) values('顺丰',112321211)  --因为插入的数据满足 查询的筛选的 条件条件了

 

 结果:

总结:

可序列话 隔离读操作:用户 解决 幻影数据(将标的数据和表的结构都锁定),是并发降低...隔离级别越高,并发越低,但是效率越低,所以不是要确定使用  最好不要使用

 

下面两种隔离级别是在 sql server 2005才出现的,隔离级别更高:

5、snapshot(快照)

 为数据产生一个临时数据库,当sql server 数据更新之前将当前数据库复制到 tempdb数据库里面,查询就是从tempdb数据库中查询,但是不能再 使用 snapshot 线程的事务内执行 修改操作,因为不能修改 旧版本数据库(tempdb),会报错。

snapshot隔离级别,读操作 不适用 共享锁,使用的是“行版本控制”,所以读数据的性能效率很高,但是修改操作性能就降低的很多。

因为是将 数据库  中的数据 复制到 tempdb 数据库中,所以不会产生 幻读。

--设置数据库支持快照隔离级别:
alter database ssdemo set allow_snapshot_isolation on;--这个时候会产生一个临时数据库(写操作的排它锁锁定的是 现实存在的数据库,,读操作的读取的是 临时数据库)

 

 在一个线程中执行 更新操作,用排它锁锁定当前数据

begin transaction;
--使用 排它锁(独占锁)X,锁定 下面的那条数据
update [Sales.Shippers] set companyname='飞凤' where shipperid=3;

 

这个时候在在另外一个线程中查询这条数据(默认的隔离级别),就会将当前线程阻塞。

如果使用 snapshot 隔离级别查询就不会阻塞。

1 set transaction isolation level snapshot;
2 --下面的就可以  从临时数据库中查询到数据
3 begin transaction;
4 --没有使用 共享锁 ,使用的行版本控制,所以不管数据是否加了 独占锁,都可以到 tempdb中 读取数据(如果数据修改了才到tempdb中读取数据)
5 select * from [Sales.Shippers] where shipperid=3;--查询到的 是还没有完成更新之前的数据

 

但是同时也会带来两个问题:

1、当 另外一个事务  已经提交,但是这边的查询到数据还是没有修改。因为 每次查询到的快照是针对于 本次回话对应的那个 transaction 的,因为在这个事务里面是没有修改的,所以查询到的数据是没有修改的。

2、(更新问题)因为 那边的数据已经是 飞凤公司了,但是这里还是   联邦,所以,在这个事务里面是不能对表进行修改,因为访问的是临时数据库,想要对 数据库修改是不可能的(sql server 就会报错,阻止修改) 

 

针对于上面两个问题,所以下面 更高的隔离级别出现了 read committed snapshot:

6、read committed snapshot

 首先开启数据库的 read committed snapshot 隔离级别:

1 --设置 数据库 为 读取已经提交的快照 开启
2 alter database ssdemo set read_committed_snapshot on;

 

在一个线程中执行:

begin transaction;
update [Sales.Shippers] set companyname='联邦' where shipperid=3;

 

在另外一个线程中:

1 --不用显示声明使用  read committed snapshot 隔离级别,因为设置完 read_committed_snapshot 隔离级别启动,默认就是 read commited snapshot 隔离级别
2 begin transaction;
3 select * from [Sales.Shippers] where shipperid=3;--查询到是 已经提交之后的数据
4 
5 update [Sales.Shippers] set companyname='xiaoxiao' where shipperid=3;

 

这个时候查询到的数据是还没有更改之前的,如果将 前面的那个回话提交,那么在查询 查询到的数据是 提交修改之后的数据。所以解决了上面的问题1.

如果在修改的话。也是在第一个 更新线程中的事务更新之后的数据进行执行修改的操作,不会报错。

 

 

 

 

posted @ 2013-12-09 07:10  小小gogo  Views(8504)  Comments(11Edit  收藏  举报