SQL查询艺术学习笔记--SQL事务处理 隔离 锁 与 并发操作

--事务控制与并发处理
--事务的特点：
--atomic:原子性，事务的全有或全无。事务做为一个处理单元，处理完成扣要么保存
--要么回滚不保留。
--consistent:一致性，事务完成或撤消后，都应该处于一致的状态。数据变化符后数据定义
--规则，数据结束时，数据所有结构必须正确。
--isolated:隔离性，多个事务同时进行，他们之间应该互不干扰。事务处理过程中的暂时不一致
--数据不能被其他事务应用，直到数据再次一致。
--durable：永久性，一旦事务引发了变化，事务提交后，所做的工作就永久的保存下来，
--即使硬件和应用程序发生错吴，这些数据也会可靠一致。


--SQL中与事务有关的语句：
--set transaction:设置下一个要执行的事务的属性
--start transaction:开始事务
--set constraints:设置当前事务中约束模式。约束是否立刻应用于数据或者应用延迟到下一个事务。
--savepoint:在事务中建立保存点。类似断点
--release savepoint:释放保存点
--rollback：终止事务，取消事务进行的任何操作。返回事务开始点或者保存点。
--commit:提交事务


--事务控制的具体实现
--SQL开始事务含以下方式：
--隐式事务：在当前会话中，通过set implicit_transaction on命令设置事务类型
--任何dml语句delete update insert 都会开始一个事务，事务结束也是用commit或rollback.
--显式开始:以begin transaction开始一个事务
--语法：
begin transaction [transaction_name|@transaction_name_variable]
--说明：transaction_name事务名称，限32个字符
--      @transaction_name_variable:用变量来指定事务的名称，变量为char varchar,nchar nvarchar类型。
--自动提交：对用户发出的每一休SQL语句，SQL server都会自动开始一个事务
--缺省自动提交：默认如上一条自动提交。


--set constraints 语句设置约束的延期执行
--set constraints 语法：
set constraints
{constrain_name|all}{deferred|immediate}
--defferred:延期标识的约束
--immediate:立即应用约束


create table myfriends
(
name char(8) not null,
sex char(2) not null,
phone char(11) not null
)
 begin transaction 
 insert into myfriends(name,sex) values ('小米','男')
 update myfriends set phone='11111111111' where name='小米'
 commit
--事务操作不成功 MS-SQL不支持set constraints
 
--事务的终止
--SQL事务的终止有两种方法：commit和rollback commit执行时，事务变化都保存在
--数据库中并终止事务，而执行rollback时，数据库将返回到事务开始时的初始状态并终止事务。
--rollback会回滚取消事务，如需要事务执行中提交需要数据。可以使用保存点(save point)，
--rollback将只会回滚至保存点。取消保存点以下的操作，在这个回滚至保存点过程，事务将不会终止。
 
--在SQL中，设置保存点语法：
save transaction [savepoint_name]|@savepoint_variable]
--savepoint_naem  @savepoint_variable为保存点名称或变量名，变量只能为char varchar nchar nvchar类型
--回滚至保存点语法：
rollback transaction [transaction_name|@tran_name_variable|savepoint_name|@savepoint_variable]]]


create table trans_table
(
row_number int,
destcribe varchar(20)
)
begin transaction
  insert into trans_table values (1,'insert 1')
  insert into trans_table values (2,'insett 2')
  save transaction save1
  delete from trans_table where row_number=2
  insert into trans_table values(3,'insert 3')
  insert into trans_table values(4,'insert 4')
  save transaction save2
  delete from trans_table where row_number=1
  delete from trans_table where row_number=3
  
  rollback transaction save2
  update trans_table  set destcribe='row 1 afert 2'
  delete from trans_table where row_number=4
  commit transaction
--创建表并测试保存点和事务回滚  
  select * from trans_table
--查询数据变更


--数据库并发控制
--数据库DBMS在没有适当处理数据据库并发事务会话有以下四种情况:
--1.数据丢失  2.未提交的数据读取（读取错）3.不一致的读取（查询期间，由于数据变化未保留一个状态，其他用户操作影
--响查询结果）4.幻影读（假读)在数据操作影响正在查询的结果。
--以上问题我们可以用过set transaction语句设置隔离层，以隔离层设置事务与事务之间的隔离性。


--事务的隔离级别
select create_date as '开始时间' from sys.databases where name='tempdb'
--通过查看tempdb数据库查看系统开启时间
--隔离级别是高级的dbms锁定技术，通过使用隔离级别达到多用户环境中保证每个用户都在一人
--专用数据库的目的，同时仍然让尽量多的用户同时访问数据库中的数据。
--事务隔离级别分为以下4种：
--1.read uncommitted:未提交的读取，允许读取已尼补其他用户修改但尚未提交确定的数据。
--这种隔离方式在数据并发控制时所有的4种情况均会发生。
--2.read committed :提交读取，比read uncommitted高一级，通过隐藏未提交的变化解决了会读取到 脏数据的可能。
--这种隔离方式不能防止不可重复的读取和幻读。
--3.repeatable read:可重复读取，比read commited高一级，在这个级别下，使用select命令读取的数据在整个命令
--执行过程中均由dbms实施锁定，不会被更改。
--4.serializable：可串行化，该隔离级别为限制最大级别，将事务以一种顺序连接起来，防止一个事务影响其他事务。


--set transaction设置事务属性
--SQL中隔离级别通过使用set transaction语句来实现，语法如下：
set transaction [read only|read write]
isoltion level level1
diagnostece aize size
--set transaction指定了3种事务模式：访问层   隔离级别  诊断区大小
--访问层：read only 和read write  read only事务中不能有任何修改数据库语句
--含更改数据语句（update）和修改数据库结构语句(create table alter table)
--read write则即可以有访问语句，也可以有修改语句
--隔离级别：level的取值包括：read uncommited read committed,repleatable read,和serializable 4
--种级别。
--诊断区大小：诊断区用于存储执行sql语句时产生的条件，包括警告，异常和其他信息。
--隔离级别存在的数据并发异常
--隔离级别            数据更新丢失 错读不可重复读    假读
--read uncommited      可能 可能  可能    可能
--read commited   防止防止           可能 可能
--repeatableread       防止              防止 防止        可能
--serializable         防止 防止防止 防止




--SQL中并发事务控制
--SQL多用户环境下对资源访问的限制机制是通过锁（lock）来实现，对一个数据源加锁后，此数据源就有一定的
--访问限制，即对此数据进行锁定，隔离级别也是一种高级锁定锁术
--锁对象：
--数据行（row):数据面中的单行数据
--索引行（key):索引中的单行数据，即索引键值
--页（page):sql server存取数据的基本单位，大小为8KB
--盘区(extent):一个盘区由8个连续面组成
--表（table）  ---数据库(database)
--SQL 中，锁有两种分类：数据库系统分类和程序员角度分类


--数据库系统分类锁分为：共享锁（shared lock) 独占锁(exclusive lock)  更新锁(update lock)
--共享锁（ahared lock):锁定资源可以被其他用户读取，但不能修改。在select命令执行时
--SQL会对对象进行共享锁定，一般情况下，加共享锁数据在读取完毕后，其共享锁将会释放。
--独占锁(excluesive lock):独占锁只许允锁定操作的程序使用，对任何其他的操作均不会接受，执行
--数据更新命令（insert update delete）命令时，SQL会自动使用独占锁，但当对象上有其他锁存在时，无法再加独占锁，更新操作完成后，独占锁释放。
--更新锁（update lock)：更新锁是为防止死锁而设定的，在SQL server中准备更新数据时，它会对数据做更新锁定，这样防止数据修改，但可以读取，在SQL Server确定更新数据
--操作时，它会自动将更新锁换为独占锁，但当对象上存在其他锁时，无法使用更新锁。


--程序员角度分为：乐观锁（optimistic lock)和悲观锁(pessimistic lock)
--乐观锁（optimistic lock):在处理数据时，不需要在应用程序代码中做任保事情可以直接在记录上加锁，即完全依靠数据库来管理工作。通常况下，SQL Serve会自动对事务处理范围内更新到的表做锁定）
--悲观锁（pressimistic lock)：不采用数据库秒统的自动管理，由程序员直接管理数据库或对象上的加锁处理，并负责获取共享和放弃正在使用数据上的任何锁。


--SQL表级锁的应用
--在SQL Server中，提供几种表级锁定提示（locking hints)通过这些悲观锁，多用户修改数据可以实现悲观并发控制。对数据库表加锁后，其他人不可操作，直到加锁用户以commit命令或rolbakc命令释放解锁。
--锁定提示------------功能描述----------------
--holdlock    持有共享锁，直到事务完成，应该在被锁对象不需要时立即释放，等于serializable事务隔离级别
--nolock      语句执行时不发出共享锁，允许读取脏数据，等同于read uncommit事务隔离级别
--paglock     在使用一个表锁的地方用多个页锁。
--readpast    跳过任何锁定行执行事务，仅适用于read uncommited事务隔离级别，只跳过行锁，不跳过页锁，盘区锁和表锁，仅能用select语句。
--rowlock     强制使用行锁代替页锁，盘区锁和表锁。
--tablelock   强制使用独占表级锁，将会锁定这个表阻止其他事务使用。
--updlock     强制在读表时使用更新锁而不用共享锁，直到事务结束。


--锁定数据库表的语法：
select * from table [with](holdlock|tablelock....)


create table lctable
( A char(4),B char(4),C char(4))
insert into lctable values('a1','b1','c1')
insert into lctable values('a2','b2','c2')
insert into lctable values('a3','b3','c3')
select * from lctable


--表加锁及更新测试
--用户1操作：
begin transaction
declare @now_time varchar(8)
select * from lctable with (holdlock) where 1=2
--对表加holdlock锁，需事务完成后再执行。
select @now_time=CONVERT(varchar,getdate(),8)
print '用户S1锁定时间为：'+@now_time
waitfor delay '00:00:10'
select @now_time=CONVERT(varchar,GETDATE(),8)
print '用户S1解锁时间为:'+@now_time
commit transaction
--用户2操作：
begin transaction
declare @now1_time varchar(8)
select @now1_time=CONVERT(varchar,getdate(),8)
print '用户S2开始的时间为：'+@now1_time
select * from lctable where B='b2'
select @now1_time=CONVERT(varchar,getdate(),8)
print '执行S2执行select时间为：'+@now1_time
update lctable set A='AA' where B='b2'
select @now1_time=CONVERT(varchar,getdate(),8)
print '用户S2执行update时间为：'+@now1_time
rollback


select * from lctable


--设置隔离级别实现并发控制
--在SQL中指定事务的隔离级别与在select语句中使用锁定选项具有相同效果。设置隔离级别通过select transaction来实现的
--设置隔离级别语法如下：
set transaction isolation level  level --isolation 隔离，隔绝
--其中level从低到高依次可以设轩：read uncommitted. read commited, repeatable read,seralizable 四个选项


--read uncommited 操作实例
--用户1操作：
set transaction isolation level read uncommitted
--设置隔离级别
begin transaction
update lctable set C='c'
print '事务结束前表中数据:'
select * from lctable
waitfor delay '00:00:10'
rollback transaction
print '事务结束后表中数据'
select * from lctable
--用户2操作：
set transaction isolation level read uncommitted
--设置隔离级别
print '读取了脏数据，即S1更新中间数据'
select * from lctable
if @@ROWCOUNT>0
begin 
waitfor delay '00:00:10'
print  '再次读取数据表结果不同，用户1操作已完成'
select * from lctable
end
-- 注：模拟上述操作需要在两个不同执行查询窗口执行，得到我们想要的结果。


--read committed
-- 该级别指定在读取数据时控制独占锁避免读'脏数据',但数据可在事务结束前更改，产生不可重复读取或幻影读。
-- 不可重复读即数据可能会由其他用户修改后，再次无法读取原数据。
-- read committed是MS-SQL默认的隔离级别
--用户1操作：
set transaction isolation level read committed
begin transaction 
print 'lctalbe初始数据，在用户2访问之前的数据'
select * from lctable
waitfor  delay '00:00:10'
print '再次读取数据，用户2更改数据造成了不可重复读取'
select * from lctable
rollback transaction


--用户2操作：
set transaction isolation level read committed
update lctable set C='CQ'
print '更新表后的数据：'
select * from lctable
--注：调试时在不同窗口执行


--更改隔离级别为repeatable read测试
--用户1操作：
set transaction isolation level repeatable read 
begin transaction 
print 'lctalbe初始数据，在用户2访问之前的数据'
select * from lctable
waitfor  delay '00:00:10'
print '再次读取数据，用户2更改数据造成了不可重复读取'
select * from lctable
rollback transaction


--用户2操作：
set transaction isolation level repeatable read
update lctable set C='qc'
print '更新表后的数据：'
select * from lctable
--注：更改后用户1读取数据为正确数据，可重复读取。用户2操作无效


--repeatable read
--该级别将锁定查询中所使用的数据，可以防止其他用户更新，但其他用户可以插入数据。同样也会出现幻影行数据。
--用户1操作：
set transaction isolation level repeatable read
begin transaction
print 'lctalbe 表初始数据，在用户2访问前数据'
select * from lctable
waitfor delay '00:00:10'
print '再次读取lctalbe表数据，用户2插入操作造成幻读'
select * from lctable
rollback transaction


--用户2操作：
set transaction isolation level repeatable read
insert lctable values ('a4','b4','c4')
print '向表中插入数据后lctable表'
select * from lctable
--注：同样在不同窗口执行，因插入数据改变表数据，造成了幻读。


--serializable
--使用serializable隔离放置范围锁，等同于holdlock
--测试
--用户1操作：
set transaction isolation level serializable
begin transaction
print 'lctalbe 表初始数据，在用户2访问前数据'
select * from lctable
waitfor delay '00:00:10'
print '再次读取lctalbe表数据，用户2插入操作造成幻读'
select * from lctable
rollback transaction


--用户2操作：
set transaction isolation level serializable
insert lctable values ('a5','b5','c5')
print '向表中插入数据后lctable表'
select * from lctable
--注：同样在不同窗口执行，因隔离造成数据不可变更，数据读取正确，但数据依然会写入至数据表。。


--关于死锁及预防
--死锁指多用户和多进程时为使用同一资源产生无法解决的争用状态。如果状态持续，这种情将永远继续下去。
create table lc1talbe ( D char(4),E char(4))
insert lc1talbe values ('d1','e1')
--用户1操作：
begin transaction
update lctable set A='AA' where C='qc'
select * from lctable where C='qc'
waitfor delay '00:00:20'
update lc1talbe set D='d5' where E='e1'
select * from lc1talbe where E='e1'
commit transaction


--用户2操作：
begin transaction
update lc1talbe set D='dd' wherer E='e1'
waitfor delay '00:00:05'
update lctable set A='aa' where C='qc'
select * from lctable where C='qc'
commit transaction
select * from lc1talbe
--同样在两个窗口运行，系统将选择结束一个事务让另一个事务得以运行。