15.sql server数据库事务和数据库锁、触发器、游标、视图、自定义函数、字段（by-朝夕）

目标

1 数据库事务和数据库锁
2 触发器、游标、视图、自定义函数、
3 字段类型、字段可空、统计字段、逻辑删除

环境准备：

Microsoft SQL Server Management Studio 18 2012以上；

一.事务

数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,
要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作
组成

1. 一条sql语句，执行的时候，要么都成功，要么都失败；----是一个事务
2. 多条Sql语句当做一个整体去执行，要么都成功，要么都失败；不可分割的逻辑单元；
   案例：下订单，如果下单成功，订单要生成数据，商品必然要减去库存；
   开启事务

A.自动提交事务

一条Sql语句

B.显式事务

use CustomerDB
--select * from Company
--delete Company where id>3
---开启事务
 begin tran
 --错误扑捉机制，看好啦，这里也有的。并且可以嵌套。
 begin try
 --语句正确
 insert into [Company] (Name,CreateTime,CreatorId) values ('朝夕004','2019-03-20',3)
 --加入保存点
 --save tran SavePoint
 --CreatorId为int类型，出错
 --insert into [Company] (Name,CreateTime,CreatorId) values ('朝夕002','2019-03-20','测试')
 --语句正确
 insert into [Company] (Name,CreateTime,CreatorId) values ('朝夕005','2019-03-20','test')
save tran SavePoint
 end try
 begin catch
 select Error_number() as ErrorNumber, --错误代码
 Error_severity() as ErrorSeverity, --错误严重级别，级别小于10 try catch 捕获不到
 Error_state() as ErrorState , --错误状态码
 Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。
 Error_line() as ErrorLine, --发生错误的行号
 Error_message() as ErrorMessage --错误的具体信息
 if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
 rollback tran ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
 end catch
 if(@@trancount>0)
 rollback tran SavePoint --如果成功Company表中，将会有3条数据。
 --表本身为空表，ID ,Numb为int 类型，其它为nvarchar类型
 select * from [Company]

C.隐式事务

use CustomerDB
GO
SET IMPLICIT_TRANSACTIONS ON
 insert into [Company] (Name,CreateTime,CreatorId) values ('朝夕001---隐式事务','2019-03-20',3)
GO
UPDATE [Company] SET Name='朝夕00ccc',CreatorId='bbbbbb' WHERE id=1
GO
UPDATE [Company] SET [Name]='朝夕00xx' WHERE id=2
COMMIT
SET IMPLICIT_TRANSACTIONS OFF
--delete Company where Id>3
select * from Company

二.ACID

Atomicity(原子性):要么都成功 要么都失败
Consistency(一致性)：事务执行完，数据都是正确
Isolation(隔离性): 两个事务同时操作一张表，B事务要么是在A事务前完成，要么在A事务完成后执行(锁
表)
Durability (持久性):数据提交后 就固化下来

三.锁

如果没有锁：

1. 修改丢失
2. 不可重复读
3. 脏读/幻读

锁的意义：

1. 避免以上情况
2. 解决并发问题

锁的问题

可能会发生死锁

乐观锁：

–不是使用数据锁机制，通过程序设计角度来谈的
1.认为没有并发，读取数据—修改—保存（没有锁），通过数据判断
2.更新的时候做一个判断-----需要一个判断维度（时间戳–Timestamp—long数字），就判断这个时间戳
先查询–再更新，查询出来以后，得到时间戳，在更新的时候，加个1（规则统一），判断加1 之后的数
据是否大于之前的时间戳，大于则正常更新，否则这个数据被修改过，那就不能操作-----失败了；
3.Version/跟时间戳套路一样 -----时间戳建议大家使用long----bigint
4.规则需要统一，所以其实还有漏洞；
高性能；

悲观锁：

基于数据库锁机制完成
会影响性能；
1.认为任何时候都可能多线程并发，认为我们在操作某一条数据的时候，刚好别人在操作；
2.共享锁 S锁 读锁，允许别的事务来读，但是不允许修改；读完就释放，锁定数据页；(除非holdlock就
一直锁定)
3.排他锁 X锁 写锁，准备写数据，不允许读也不允许写；—Insert
4.更新锁 U锁 先查询再更新----update
5.行锁 where id=123
6.表锁 where 1

锁的使用：

锁定提示 描述
HOLDLOCK 将共享锁保留到事务完成，而不是在相应的表、行或数据页不再需要时就立即释放锁。
HOLDLOCK 等同于 SERIALIZABLE。
NOLOCK 不要发出共享锁，并且不要提供排它锁。当此选项生效时，可能会读取未提交的事务或一
组在读取中间回滚的页面。有可能发生脏读。仅应用于SELECT语句。
PAGLOCK 在通常使用单个表锁的地方采用页锁。
READCOMMITTED 用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下，SQL
Server 2000 在此隔离级别上操作。
READPAST 跳过锁定行。此选项导致事务跳过由其它事务锁定的行（这些行平常会显示在结果集
内），而不是阻塞该事务，使其等待其它事务释放在这些行上的锁。READPAST 锁提示仅适用于运
行在提交读隔离级别的事务，并且只在行级锁之后读取。仅适用于 SELECT 语句。
READUNCOMMITTED 等同于 NOLOCK。
REPEATABLEREAD 用与运行在可重复读隔离级别的事务相同的锁语义执行扫描。
ROWLOCK 使用行级锁，而不使用粒度更粗的页级锁和表级锁。 (SELECT,UPDATE和DELETE)
SERIALIZABLE 用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于
HOLDLOCK。 TABLOCK 使用表锁代替粒度更细的行级锁或页级锁。在语句结束前，SQL
Server 一直持有该锁。但是，如果同时指定 HOLDLOCK，那么在事务结束之前，锁将被一直持
有。
TABLOCKX 使用表的排它锁。该锁可以防止其它事务读取或更新表，并在语句或事务结束前一直持
有。 UPDLOCK 读取表时使用更新锁，而不使用共享锁，并将锁一直保留到语句或事务的结
束。UPDLOCK 的优点是允许您读取数据（不阻塞其它事务）并在以后更新数据，同时确保自从上
次读取数据后数据没有被更改。 XLOCK 使用排它锁并一直保持到由语句处理的所有数据上的事
务结束时。可以使用 PAGLOCK 或 TABLOCK 指定该锁，这种情况下排它锁适用于适当
级别的粒度

四.如何避免死锁

死锁其实完全避免不可能；更多的是降低死锁的概率

1. 不用锁就不会死锁，建议大家使用乐观锁—高性能
2. 统一操作顺序–先A后B再C。。。。在系统中所有的操作都需要统一顺序
3. 最小单元锁，锁里面操作尽量减少操作
4. 避免事务中等待用户输入，避免在事务中等待时间过久
5. 减少数据库并发----微服务
6. 分库分表表分区：
7. 降低事务级别(遗留项)
8. 设置死锁时间 set lock_timeout（锁超时时间）

五.测试锁语句

• 在第一个窗口中执行如下语句

BEGIN TRAN
	SELECT * FROM [User] WITH (TABLOCKX)	--锁，禁止一切增删改语句执行
	waitfor delay '0:0:10.01'				--延时10秒
Rollback Tran;

• 在另一个窗口中执行如下语句，此时就会看到死锁，因为上面语句不允许执行。

BEGIN TRAN
	SELECT * FROM [User] WITH (TABLOCKX)
ROLLBACK TRAN;

下面sql引用原文地址

--锁表（其它事务不能读、更新、删除）
BEGIN TRAN
SELECT * FROM <表名> WITH(TABLOCKX);
WAITFOR delay '00:00:20'
COMMIT TRAN


--锁表（其它事务只能读，不能更新、删除）
BEGIN TRAN
SELECT * FROM <表名> WITH(HOLDLOCK);
WAITFOR delay '00:00:20'
COMMIT TRAN

--锁部分行
BEGIN TRAN
SELECT * FROM <表名>  WITH(XLOCK) WHERE ID IN ('81A2EDF9-D1FD-4037-A17B-1369FD3B169B');
WAITFOR delay '00:01:20'
COMMIT TRAN

--查看被锁表
select   request_session_id   锁表进程,OBJECT_NAME(resource_associated_entity_id) 被锁表名  
from   sys.dm_tran_locks where resource_type='OBJECT';

--解锁
declare @spid  int
Set @spid  = 55 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid  as varchar)
exec(@sql)

六.存储过程

存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指
定存储过程的名称并给出参数来执行。可以独立的来完成业务逻辑计算-----就类似于我们在程序中的某一
个方法；----单纯从数据库角度来说； 就有点像在数据库中定义的用来处理业务逻辑的Api;
存储过程的优势：
1.存储过程允许标准组件式编程，每一个业务逻辑的处理都可以独立定义成一个存储过程；面对一些业
务逻辑的更新，其实只需要修改数据库存储过程即可；
2.存储过程能够实现较快的执行速度
3.存储过程减轻网络流量
4.存储过程可被作为一种安全机制来充分利用
系统存储过程：

exec sp_databases; --查看数据库
exec sp_tables;       --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename student, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;exec sp_configure--例：--表重命名
exec sp_rename 'stu', 'stud';
select * from stud;
--列重命名
exec sp_rename 'stud.name', 'sName', 'column';
exec sp_help 'stud';
分页存储过程Show

--重命名索引
exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
exec sp_help 'student';
--查询所有存储过程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';