一、事务
1.1 事务的ACID
- 原子性:一个事务内的操作,要么全部成功过、要么全部失败
- 一致性:保证事务的一致性状态,事务成功前、失败后数据保持一致
- 隔离性:事务与事务之间互不干扰
- 持久性:事务提交后,数据不会丢失
1.2 事务的控制
1、显式事务
- begin transaction : 显式的开始一个事务
- rollback transaction : 显式的对一个事务进行回滚
- commit transaction : 显式提交一个事务
2、隐式事务
隐式提交需要开启数据库的 implicil_transactions 参数(set implicil_transactions on 开启)。开启隐式事务后,默认第一个语句执行时开启一个事务,需要显式的执行commit、rollback完成事务
3、自动提交事务
每条单独的语句都是一个事务
二、锁
2.1 锁粒度
- 行锁:数据库最小粒度锁,只针对某一行级别进行加锁(RID/KEY)
- 页锁:针对某一数据页进行加锁,在T-SQL语句中,若查询使用了页锁则不会再使用相同类型的行锁;同样若使用行锁,则不会再使用相同类型的页锁(PAGE)
- 表锁:对整个表进行加锁(Obgect)
- 库锁:将数据库设置为只读、或者查询数据时防止别人删库会在数据库级别上添加锁资源(DATABASE)
2.2 锁模式
常见锁模式
- 共享锁(S锁):在查找数据前,获取该记录的共享锁,共享锁之间兼容,提高读读并发。SQL Server默认隔离级别下,共享锁使用后就会释放,不会等到事务提交/回滚才释放。
- 排他锁(X锁):在更新数据前,获取该记录的拍他锁(独占锁),阻止其他事务对该记录进行更新。
- 更新锁(U锁):发生在更新数据的操作中,更新锁用于查找数据,当扫描到数据不是被更新的记录时,与S锁一样,使用后就释放;若扫描到的数据为需要更新的记录时,将U锁升级为X锁,进行更新操作。
- 意向锁(IU锁):意向锁又分为意向共享锁(IS锁)、意向排他锁(IX)、意向更新锁(IU)、共享意向排他锁(SIX)、共享意向更新锁(SIU)、更新意向排他锁(UIX),发生在较低粒度的锁资源获取前。
- 架构锁(Sch-S/M):查询是未防止架构修改需要获取Sch-S锁、执行DDL操作调整架构时获取Sch-M锁
2.3 锁兼容性
- | IS | S | U | IX | SIX | X |
---|---|---|---|---|---|---|
IS | 是 | 是 | 是 | 是 | 是 | 否 |
S | 是 | 是 | 是 | 否 | 否 | 否 |
U | 是 | 是 | 否 | 否 | 否 | 否 |
IX | 是 | 否 | 否 | 是 | 否 | 否 |
SIX | 是 | 否 | 否 | 否 | 否 | 否 |
X | 否 | 否 | 否 | 否 | 否 | 否 |
2.4 锁相关参数
- lock_timeout
该参数默认为 -1 ,表示无锁超时等待限制。单位为毫秒。
-- 查看当前锁超时的全局设置
select @@LOCK_TIMEOUT
-- 会话层面设置锁超时
set LOCK_TIMEOUT 5000 //5秒锁超时
2.5 死锁
1、什么是死锁
死锁就是指两个事务在执行过程中,因争夺资源而造成的一种互相等待的现象,造成这个现象的原因主要是因为两个事务加锁的顺序不一致,导致每个事务互相请求对方持有的资源,从而产生死锁闭环。通常数据库发生死锁会优先回滚掉权重较小的事务。
2、如何避免死锁
- 避免大事务/长事务,事务及时提交
- 有效通过索引减少锁定范围
2.6 锁监控
1、详细版的各会话锁阻塞情况查询
SELECT
der.[session_id],
[request_id],
percent_complete,
estimated_completion_time/1000/60,
con.[client_net_address],
con.local_net_address,
[start_time] AS '开始时间',
[status] AS '状态',
[command] AS '命令',
dest.[text] AS 'sql语句',
DB_NAME([database_id]) AS '数据库名',
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
[wait_type] AS '等待资源类型',
[wait_time] AS '等待时间',
[wait_resource] AS '等待的资源',
[reads] AS '物理读次数',
[writes] AS '写次数',
[logical_reads] AS '逻辑读次数',
[row_count] AS '返回结果行数',
[cpu_time],
percent_complete
FROM sys.[dm_exec_requests] AS der
outer APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
left join sys.dm_exec_connections con
on der.session_id=con.session_id
WHERE der.[session_id]>50 AND
DB_NAME(der.[database_id])='db1'
ORDER BY [reads] DESC
2、简易版的各会话锁阻塞情况查询
SELECT spid,kpid,blocked ,waittime AS 'waitms', lastwaittype, DB_NAME(dbid), waitresource, open_tran,hostname,[program_name],hostprocess,loginame, [status]
FROM sys.sysprocesses WITH(NOLOCK)
WHERE kpid>0 AND [status]<>'sleeping' AND spid>50
ORDER BY waittime DESC
3、极简版锁阻塞情况查询
select blocking_session_id, wait_duration_ms, session_id from
sys.dm_os_waiting_tasks
where blocking_session_id is not null
dbcc INPUTBUFFER(${session_id}) //查看具体会话执行SQL情况
4、锁等待各个会话持有锁资源信息查看
SELECT k.request_session_id,k.resource_type,k.request_status,k.request_mode,k.resource_description,
OBJECT_NAME( p.object_id) as objectName,p.index_id FROM SYS.dm_tran_locks k LEFT JOIN SYS.PARTITIONS p
ON k.resource_associated_entity_id=p.hobt_id
ORDER BY request_session_id,resource_type