数据库 http://lib.csdn.net/base/mysql 大并发操作要考虑死锁和锁的性能问题. 看到网上大多语焉不详 (尤其更新锁), 所以这里做个简明解释, 为下面描述方便, 这里用 T1 代表一个数据库执行请求, T2 代表另一个请求, 也可以理解为 T1 为一个线程, T2 为另一个线程. T3,T4 以此类推. 下面以 SQL Server(2005) 为例.
2 锁的种类
共享锁(Shared lock).
例 1:
- ----------------------------------------
- T1: select * from table (请想象它需要执行 1 个小时之久, 后面的 sql 语句请都这么想象)
- T2: update table set column1='hello'
过程:
T1 运行 (加共享锁)
10. T2 运行
11. If T1 还没执行完
12. T2 等......
13. else
14. 锁被释放
15. T2 执行
16. endif
17.
18. T2 之所以要等, 是因为 T2 在执行 update 前, 试图对 table 表加一个排他锁,
19. 而数据库规定同一资源上不能同时共存共享锁和排他锁. 所以 T2 必须等 T1
20. 执行完, 释放了共享锁, 才能加上排他锁, 然后才能开始执行 update 语句.
21.
22. 例 2:
- ----------------------------------------
- T1: select * from table
- T2: select * from table
27. 这里 T2 不用等待 T1 执行完, 而是可以马上执行.
28.
29. 分析:
30. T1 运行, 则 table 被加锁, 比如叫 lockA
31. T2 运行, 再对 table 加一个共享锁, 比如叫 lockB.
32.
33. 两个锁是可以同时存在于同一资源上的(比如同一个表上). 这被称为共
34. 享锁与共享锁兼容. 这意味着共享锁不阻止其它 session 同时读资源, 但阻
35. 止其它 session update
36.
37. 例 3:
- ----------------------------------------
- T1: select * from table
- T2: select * from table
- T3: update table set column1='hello'
43. 这次, T2 不用等 T1 运行完就能运行, T3 却要等 T1 和 T2 都运行完才能运行.
44. 因为 T3 必须等 T1 和 T2 的共享锁全部释放才能进行加排他锁然后执行 update
45. 操作.
46.
47. 例 4:(死锁的发生)
- ----------------------------------------
- T1:
- begin tran
- select * from table (holdlock) (holdlock 意思是加共享锁, 直到事物结束才释放)
- update table set column1='hello'
- T2:
- begin tran
- select * from table(holdlock)
- update table set column1='world'
59. 假设 T1 和 T2 同时达到 select,T1 对 table 加共享锁, T2 也对加共享锁, 当
60. T1 的 select 执行完, 准备执行 update 时, 根据锁机制, T1 的共享锁需要升
61. 级到排他锁才能执行接下来的 update. 在升级排他锁前, 必须等 table 上的
62. 其它共享锁释放, 但因为 holdlock 这样的共享锁只有等事务结束后才释放,
63. 所以因为 T2 的共享锁不释放而导致 T1 等(等 T2 释放共享锁, 自己好升级成排
64. 他锁), 同理, 也因为 T1 的共享锁不释放而导致 T2 等. 死锁产生了.
65.
66. 例 5:
- ----------------------------------------
- T1:
- begin tran
- update table set column1='hello' where id=10
- T2:
- begin tran
- update table set column1='world' where id=20
76. 这种语句虽然最为常见, 很多人觉得它有机会产生死锁, 但实际上要看情
77. 况, 如果 id 是主键上面有索引, 那么 T1 会一下子找到该条记录(id=10 的记
78. 录), 然后对该条记录加排他锁, T2, 同样, 一下子通过索引定位到记录,
79. 然后对 id=20 的记录加排他锁, 这样 T1 和 T2 各更新各的, 互不影响. T2 也不
80. 需要等.
81.
82. 但如果 id 是普通的一列, 没有索引. 那么当 T1 对 id=10 这一行加排他锁后,
83. T2 为了找到 id=20, 需要对全表扫描, 那么就会预先对表加上共享锁或更新
84. 锁或排他锁(依赖于数据库执行策略和方式, 比如第一次执行和第二次执行
85. 数据库执行策略就会不同). 但因为 T1 已经为一条记录加了排他锁, 导致
86. T2 的全表扫描进行不下去, 就导致 T2 等待.
87.
88. 死锁怎么解决呢? 一种办法是, 如下:
89. 例 6:
- ----------------------------------------
- T1:
- begin tran
- select * from table(xlock) (xlock 意思是直接对表加排他锁)
- update table set column1='hello'
- T2:
- begin tran
- select * from table(xlock)
- update table set column1='world'
101. 这样, 当 T1 的 select 执行时, 直接对表加上了排他锁, T2 在执行 select 时, 就需要等 T1 事物完全执行完才能执行. 排除了死锁发生.
102. 但当第三个 user 过来想执行一个查询语句时, 也因为排他锁的存在而不得不等待, 第四个, 第五个 user 也会因此而等待. 在大并发
103. 情况下, 让大家等待显得性能就太友好了, 所以, 这里引入了更新锁.
104. 更新锁(Update lock)
105. 为解决死锁, 引入更新锁.
106.
107. 例 7:
- ----------------------------------------
- T1:
- begin tran
- select * from table(updlock) (加更新锁)
- update table set column1='hello'
- T2:
- begin tran
- select * from table(updlock)
- update table set column1='world'
118. 更新锁的意思是:" 我现在只想读, 你们别人也可以读, 但我将来可能会做更新操作, 我已经获取了从共享锁 (用来读) 到排他锁
119. (用来更新)的资格 ". 一个事物只能有一个更新锁获此资格.
120.
121. T1 执行 select, 加更新锁.
122. T2 运行, 准备加更新锁, 但发现已经有一个更新锁在那儿了, 只好等.
123.
124. 当后来有 user3,user4... 需要查询 table 表中的数据时, 并不会因为 T1 的 select 在执行就被阻塞, 照样能查询, 相比起例 6, 这提高
125. 了效率.
126.
127. 例 8:
- ----------------------------------------
- T1: select * from table(updlock) (加更新锁)
- T2: select * from table(updlock) (等待, 直到 T1 释放更新锁, 因为同一时间不能在同一资源上有两个更新锁)
- T3: select * from table (加共享锁, 但不用等 updlock 释放, 就可以读)
133. 这个例子是说明: 共享锁和更新锁可以同时在同一个资源上. 这被称为共享锁和更新锁是兼容的.
134.
135. 例 9:
- ----------------------------------------
- T1:
- begin
- select * from table(updlock) (加更新锁)
- update table set column1='hello' (重点: 这里 T1 做 update 时, 不需要等 T2 释放什么, 而是直接把更新锁升级为排他锁, 然后执行 update)
- T2:
- begin
- select * from table (T1 加的更新锁不影响 T2 读取)
- update table set column1='world' (T2 的 update 需要等 T1 的 update 做完才能执行)
146. 我们以这个例子来加深更新锁的理解,
147.
148. 第一种情况: T1 先达, T2 紧接到达; 在这种情况中, T1 先对表加更新锁, T2 对表加共享锁, 假设 T2 的 select 先执行完, 准备执行 update,
149. 发现已有更新锁存在, T2 等. T1 执行这时才执行完 select, 准备执行 update, 更新锁升级为排他锁, 然后执行 update, 执行完成, 事务
150. 结束, 释放锁, T2 才轮到执行 update.
151.
152. 第二种情况: T2 先达, T1 紧接达; 在这种情况, T2 先对表加共享锁, T1 达后, T1 对表加更新锁, 假设 T2 select 先结束, 准备
153. update, 发现已有更新锁, 则等待, 后面步骤就跟第一种情况一样了.
154.
155. 这个例子是说明: 排他锁与更新锁是不兼容的, 它们不能同时加在同一子资源上.
156.
157. 排他锁(独占锁, Exclusive Locks)
158. 这个简单, 即其它事务既不能读, 又不能改排他锁锁定的资源.
159. 例 10
- T1: update table set column1='hello' where id<1000
- T2: update table set column1='world' where id>1000
163. 假设 T1 先达, T2 随后至, 这个过程中 T1 会对 id<1000 的记录施加排他锁. 但不会阻塞 T2 的 update.
164.
165. 例 11 (假设 id 都是自增长且连续的)
- T1: update table set column1='hello' where id<1000
- T2: update table set column1='world' where id>900
169. 如同例 10,T1 先达, T2 立刻也到, T1 加的排他锁会阻塞 T2 的 update.
170. 意向锁(Intent Locks)
171. 意向锁就是说在屋 (比如代表一个表) 门口设置一个标识, 说明屋子里有人 (比如代表某些记录) 被锁住了. 另一个人想知道屋子
172. 里是否有人被锁, 不用进屋子里一个一个的去查, 直接看门口标识就行了.
173.
174. 当一个表中的某一行被加上排他锁后, 该表就不能再被加表锁. 数据库程序如何知道该表不能被加表锁? 一种方式是逐条的判断该
175. 表的每一条记录是否已经有排他锁, 另一种方式是直接在表这一层级检查表本身是否有意向锁, 不需要逐条判断. 显然后者效率高.
176.
177. 例 12:
- ----------------------------------------
- T1: begin tran
180. select * from table (xlock) where id=10 -- 意思是对 id=10 这一行强加排他锁
181. T2: begin tran
182. select * from table (tablock) -- 意思是要加表级锁
183.
184. 假设 T1 先执行, T2 后执行, T2 执行时, 欲加表锁, 为判断是否可以加表锁, 数据库系统要逐条判断 table 表每行记录是否已有排他锁,
185. 如果发现其中一行已经有排他锁了, 就不允许再加表锁了. 只是这样逐条判断效率太低了.
186.
187. 实际上, 数据库系统不是这样工作的. 当 T1 的 select 执行时, 系统对表 table 的 id=10 的这一行加了排他锁, 还同时悄悄的对整个表
188. 加了意向排他锁(IX), 当 T2 执行表锁时, 只需要看到这个表已经有意向排他锁存在, 就直接等待, 而不需要逐条检查资源了.
189.
190. 例 13:
- ----------------------------------------
- T1: begin tran
- update table set column1='hello' where id=1
- T2: begin tran
- update table set column1='world' where id=1
197. 这个例子和上面的例子实际效果相同, T1 执行, 系统对 table 同时对行家排他锁, 对页加意向排他锁, 对表加意向排他锁.
198. 计划锁(Schema Locks)
199. 例 14:
200. ----------------------------------------
201. alter table .... (加 schema locks, 称之为 Schema modification (Sch-M) locks
202.
203. DDL 语句都会加 Sch-M 锁
204. 该锁不允许任何其它 session 连接该表. 连都连不了这个表了, 当然更不用说想对该表执行什么 sql 语句了.
205.
206. 例 15:
207. ----------------------------------------
208. 用 jdbc 向数据库发送了一条新的 sql 语句, 数据库要先对之进行编译, 在编译期间, 也会加锁, 称之为: Schema stability (Sch-S) locks
- select * from tableA
212. 编译这条语句过程中, 其它 session 可以对表 tableA 做任何操作 (update,delete, 加排他锁等等), 但不能做 DDL(比如 alter table) 操作.
213. Bulk Update Locks 主要在批量导数据时用(比如用类似于 oracle 中的 imp/exp 的 bcp 命令). 不难理解, 程序员往往也不需要关心, 不赘述了.
3 何时加锁?
如何加锁, 何时加锁, 加什么锁, 你可以通过 hint 手工强行指定, 但大多是数据库系统自动决定的. 这就是为什么我们可以不懂锁也可
以高高兴兴的写 SQL.
例 15:
- ----------------------------------------
- T1: begin tran
- update table set column1='hello' where id=1
T2: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- 事物隔离级别为允许脏读
- go
- select * from table where id=1
这里, T2 的 select 可以查出结果. 如果事物隔离级别不设为脏读, 则 T2 会等 T1 事物执行完才能读出结果.
- ----------------------------------------
- T1: begin tran
- update table set column1='hello' where id=1
- ----------------------------------------
- T1: select * from table (paglock)
- T2: update table set column1='hello' where id>10
- ----------------------------------------
- T1: select * from table (rowlock)
- T2: update table set column1='hello' where id=10
- ----------------------------------------
- T1: select * from table (tablock)
- T2: update table set column1='hello' where id = 10
- ----------------------------------------
- T1: GO
- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
- GO
- BEGIN TRANSACTION
- SELECT * FROM table (NOLOCK)
- GO
- T2: update table set column1='hello' where id=10
- ----------------------------------------
- T1:
- begin tran
- select * from table (tablock)
- T2:
- begin tran
- update table set column1='hello' where id = 10
- ----------------------------------------
- T1:
- begin tran
- select * from table (holdlock)
- T2:
- begin tran
- update table set column1='hello' where id = 10
- ----------------------------------------
- T1: select * from table(tablockx) (强行加排他锁)
- ----------------------------------------
- T1: begin tran
- select * from table(tablockx)
- T1: begin tran
- udpate table set column1='hello' where id = 10
- T2: set lock_timeout 4000
- select * from table wehre id = 10
- | Requested mode | IS | S | U | IX | SIX | X |
- | Intent shared (IS) | Yes | Yes | Yes | Yes | Yes | No |
- | Shared (S) | Yes | Yes | Yes | No | No | No |
- | Update (U) | Yes | Yes | No | No | No | No |
- | Intent exclusive (IX) | Yes | No | No | Yes | No | No |
- | Shared with intent exclusive (SIX) | Yes | No | No | No | No | No |
- | Exclusive (X) | No | No | No | No | No | No |
来源: http://www.bubuko.com/infodetail-3062879.html