多程序同时操作mysql_关于多个程序同时操作⼀个表发⽣死锁
的问题
锁的概述  　⼀. 为什么要引⼊锁
多个⽤户同时对数据库的并发操作时会带来以下数据不⼀致的问题：
丢失更新
A，B两个⽤户读同⼀数据并进⾏修改，其中⼀个⽤户的修改结果破坏了另⼀个修改的结果，⽐如订票系统
脏读
A⽤户修改了数据，随后B⽤户⼜读出该数据，但A⽤户因为某些原因取消了对数据的修改，数据恢复原值，此时B得到的数据就与数据库内的数据产⽣了不⼀致
不可重复读
A⽤户读取数据，随后B⽤户读出该数据并修改，此时A⽤户再读取数据时发现前后两次的值不⼀致
并发控制的主要⽅法是封锁，锁就是在⼀段时间内禁⽌⽤户做某些操作以避免产⽣数据不⼀致
⼆ 锁的分类
锁的类别有两种分法：
1. 从数据库系统的⾓度来看：分为独占锁(即排它锁)，共享锁和更新锁
MS SQL Server 使⽤以下资源锁模式。
锁模式 描述
共享 (S) ⽤于不更改或不更新数据的操作(只读操作)，如 SELECT 语句。
更新 (U) ⽤于可更新的资源中。防⽌当多个会话在读取、锁定以及随后可能进⾏的资源更新时发⽣常见形式的死锁。
排它 (X) ⽤于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同⼀资源进⾏多重更新。
意向锁 ⽤于建⽴锁的层次结构。意向锁的类型为：意向共享 (IS)、意向排它 (IX) 以及与意向排它共享
(SIX)。
架构锁 在执⾏依赖于表架构的操作时使⽤。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。
⼤容量更新 (BU) 向表中⼤容量复制数据并指定了 TABLOCK 提⽰时使⽤。
共享锁
共享 (S) 锁允许并发事务读取 (SELECT) ⼀个资源。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。⼀旦已经读取数据，便⽴即释放资源上的共享 (S) 锁，除⾮将事务隔离级别设置为可重复读或更⾼级别，或者在事务⽣存周期内⽤锁定提⽰保留共享 (S) 锁。
更新锁
更新 (U) 锁可以防⽌通常形式的死锁。⼀般更新模式由⼀个事务组成，此事务读取记录，获取资源(页或⾏)的共享 (S) 锁，然后修改⾏，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则⼀个事务尝试将锁转换为排它 (X)锁。共享模式到排它锁的转换必须等待⼀段时间，因为⼀个事务的排它锁与其它事务的共享模式锁不兼容；发⽣锁等待。第⼆个事务试图获取排它 (X) 锁以进⾏更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另⼀
个事务释放共享模式锁，因此发⽣死锁。
若要避免这种潜在的死锁问题，请使⽤更新 (U) 锁。⼀次只有⼀个事务可以获得资源的更新 (U) 锁。如果事务修改资源，则更新 (U) 锁转换为排它 (X) 锁。否则，锁转换为共享锁。
排它锁
排它 (X) 锁可以防⽌并发事务对资源进⾏访问。其它事务不能读取或修改排它 (X) 锁锁定的数据。
意向锁
意向锁表⽰ SQL Server 需要在层次结构中的某些底层资源上获取共享 (S) 锁或排它 (X) 锁。例如，放置在表级的共享意向锁表⽰事务打算在表中的页或⾏上放置共享 (S) 锁。在表级设置意向锁可防⽌另⼀个事务随后在包含那⼀页的表上获取排它 (X) 锁。意向锁可以提⾼性能，因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。⽽⽆须检查表中的每⾏或每页上的锁以确定事务是否可以锁定整个表。
意向锁包括意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。
锁模式
描述
意向共享 (IS) 通过在各资源上放置 S 锁，表明事务的意向是读取层次结构中的部分(⽽不是全部)底层资源。
意向排它 (IX) 通过在各资源上放置 X 锁，表明事务的意向是修改层次结构中的部分(⽽不是全部)底层资源。IX 是 IS 的超集。与意向排它共享 (SIX) 通过在各资源上放置 IX 锁，表明事务的意向是读取层次结构中的全部底层资源并修改部分(⽽不是全部)底层资源。允许顶层资源上的并发 IS 锁。例如，表的 SIX 锁在表上放置⼀个 SIX 锁(允许并发 IS 锁)，在当前所修改页上放置 IX 锁(在已修改⾏上放置 X 锁)。虽然每个资源在⼀段时间内只能有⼀个 SIX 锁，以防⽌其它事务对资源进⾏更新，但是其它事务可以通过获取表级的 IS 锁来读取层次结构中的底层资源。
独占锁：只允许进⾏锁定操作的程序使⽤，其他任何对他的操作均不会被接受。执⾏数据更新命令时，SQL Server会⾃动使⽤独占锁。当对象上有其他锁存在时，⽆法对其加独占锁。
共享锁：共享锁锁定的资源可以被其他⽤户读取，但其他⽤户⽆法修改它，在执⾏Select时，SQL Server会对对象加共享锁。
更新锁：当SQL Server准备更新数据时，它⾸先对数据对象作更新锁锁定，这样数据将不能被修改，
但可以读取。等到SQL Server确定要进⾏更新数据操作时，他会⾃动将更新锁换为独占锁，当对象上有其他锁存在时，⽆法对其加更新锁。
2. 从程序员的⾓度看：分为乐观锁和悲观锁。
乐观锁：完全依靠数据库来管理锁的⼯作。
悲观锁：程序员⾃⼰管理数据或对象上的锁处理。
MS SQL Server 使⽤锁在多个同时在数据库内执⾏修改的⽤户间实现悲观并发控制
三 锁的粒度
锁粒度是被封锁⽬标的⼤⼩，封锁粒度⼩则并发性⾼，但开销⼤，封锁粒度⼤则并发性低但开销⼩
SQL Server⽀持的锁粒度可以分为为⾏、页、键、键范围、索引、表或数据库获取锁
资源描述
RID ⾏标识符。⽤于单独锁定表中的⼀⾏。
键 索引中的⾏锁。⽤于保护可串⾏事务中的键范围。
页 8 千字节 (KB) 的数据页或索引页。
扩展盘区 相邻的⼋个数据页或索引页构成的⼀组。
表 包括所有数据和索引在内的整个表。
DB 数据库。
四 锁定时间的长短
锁保持的时间长度为保护所请求级别上的资源所需的时间长度。
⽤于保护读取操作的共享锁的保持时间取决于事务隔离级别。采⽤ READ COMMITTED 的默认事务隔离级别时，只在读取页的期间内控制共享锁。在扫描中，直到在扫描内的下⼀页上获取锁时才释放锁。如果指定 HOLDLOCK 提⽰或者将事务隔离级别设置为 REPEATABLE READ 或 SERIALIZABLE，则直到事务结束才释放锁。
根据为游标设置的并发选项，游标可以获取共享模式的滚动锁以保护提取。当需要滚动锁时，直到下⼀次提取或关闭游标(以先发⽣者为准)时才释放滚动锁。但是，如果指定 HOLDLOCK，则直到事务结束才释放滚动锁。
⽤于保护更新的排它锁将直到事务结束才释放。
如果⼀个连接试图获取⼀个锁，⽽该锁与另⼀个连接所控制的锁冲突，则试图获取锁的连接将⼀直阻塞到：
将冲突锁释放⽽且连接获取了所请求的锁。
连接的超时间隔已到期。默认情况下没有超时间隔，但是⼀些应⽤程序设置超时间隔以防⽌⽆限期等待
五 SQL Server 中锁的⾃定义
1 处理死锁和设置死锁优先级
死锁就是多个⽤户申请不同封锁，由于申请者均拥有⼀部分封锁权⽽⼜等待其他⽤户拥有的部分封锁⽽引起的⽆休⽌的等待可以使⽤SET DEADLOCK_PRIORITY控制在发⽣死锁情况时会话的反应⽅式。如果两个进程都锁定数据，并且直到其它进程释放⾃⼰的锁时，每个进程才能释放⾃⼰的锁，即发⽣死锁情况。
2 处理超时和设置锁超时持续时间。
@@LOCK_TIMEOUT 返回当前会话的当前锁超时设置，单位为毫秒
SET LOCK_TIMEOUT 设置允许应⽤程序设置语句等待阻塞资源的最长时间。当语句等待的时间⼤于 LOCK_TIMEOUT 设置时，系统将⾃动取消阻塞的语句，并给应⽤程序返回"已超过了锁请求超时时段"的 1222 号错误信息
⽰例
下例将锁超时期限设置为 1，800 毫秒。
SET LOCK_TIMEOUT 1800
3) 设置事务隔离级别。
4 ) 对 SELECT、INSERT、UPDATE 和 DELETE 语句使⽤表级锁定提⽰。
5) 配置索引的锁定粒度
可以使⽤ sp_indexoption 系统存储过程来设置⽤于索引的锁定粒度
六 查看锁的信息  　1 执⾏ EXEC SP_LOCK 报告有关锁的信息
2 查询分析器中按Ctrl+2可以看到锁的信息
七 使⽤注意事项
如何避免死锁
1 使⽤事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；
2 设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，⾃动放弃本次操作，避免进程悬挂；
3 优化程序，检查并避免死锁现象出现；
4 .对所有的脚本和SP都要仔细测试，在正是版本之前。
5 所有的SP都要有错误处理(通过@error)
6 ⼀般不要修改SQL Server事务的默认级别。不推荐强⾏加锁
⼋ ⼏个有关锁的问题
1 如何锁⼀个表的某⼀⾏
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT * FROM table ROWLOCK WHERE id =
1
2 锁定数据库的⼀个表
SELECT * FROM table WITH (HOLDLOCK)
加锁语句：
sybase：update 表 set col1=col1 where 1=0 ;MS SQL：select col1 from 表 (tablockx) where 1=0 ;oracle：LOCK TABLE 表IN EXCLUSIVE MODE ；
加锁后其它⼈不可操作，直到加锁⽤户解锁，⽤commit或rollback解锁
⼏个例⼦帮助⼤家加深印象，设table1(A，B，C)
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3
1)排它锁
新建两个连接，在第⼀个连接中执⾏以下语句
begin tranupdate table1set A='aa'where B='b2'waitfor delay '00：00：30'
--等待30秒commit tran
在第⼆个连接中执⾏以下语句
begin transelect * from table1where B='b2'commit tran
若同时执⾏上述两个语句，则select查询必须等待update执⾏完毕才能执⾏即要等待30秒
2)共享锁
在第⼀个连接中执⾏以下语句
begin transelect * from table1 holdlock -holdlock⼈为加锁where B='b2'waitfor delay '00：00：30'
--等待30秒commit tran
在第⼆个连接中执⾏以下语句
begin transelect A，C from table1where B='b2'update table1set A='aa'where B='b2'commit tran
若同时执⾏上述两个语句，则第⼆个连接中的select查询可以执⾏，⽽update必须等待第⼀个事务释放共享锁转为排它锁后才能执⾏ 即要等待30秒
3)死锁
增设table2(D，E)
D E
d1 e1
d2 e2
在第⼀个连接中执⾏以下语句
begin tranupdate table1set A='aa'where B='b2'waitfor delay '00：00：30'update table2set D='d5'where E='e1'commit tran
在第⼆个连接中执⾏以下语句
begin tranupdate table2set D='d5'where E='e1'waitfor delay '00：00：10'update table1set A='aa'where B='b2'commit tran
同时执⾏，系统会检测出死锁，并中⽌进程
补充⼀点：
SQL Server 2000⽀持的表级锁定提⽰
HOLDLOCK 持有共享锁，直到整个事务完成，应该在被锁对象不需要时⽴即释放，等于SERIALIZABLE事务隔离级别
NOLOCK 语句执⾏时不发出共享锁，允许脏读 ，等于 READ UNCOMMITTED事务隔离级别
PAGLOCK 在使⽤⼀个表锁的地⽅⽤多个页锁
READPAST 让SQL Server跳过任何锁定⾏，执⾏事务，适⽤于READ UNCOMMITTED事务隔离级别只跳过RID锁，不跳过页，区域和表锁
ROWLOCK 强制使⽤⾏锁
TABLOCKX 强制使⽤独占表级锁，这个锁在事务期间阻⽌任何其他事务使⽤这个表mysql删除重复的数据保留一条
UPLOCK 强制在读表时使⽤更新⽽不⽤共享锁
应⽤程序锁：
应⽤程序锁就是客户端代码⽣成的锁，⽽不是SQL Server本⾝⽣成的锁
处理应⽤程序锁的两个过程
sp_getapplock 锁定应⽤程序资源
sp_releaseapplock 为应⽤程序资源解锁
注意： 锁定数据库的⼀个表的区别
SELECT * FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除
SELECT * FROM table WITH (TABLOCKX) 其他事务不能读取表，更新和删除

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。