数据库死锁问题
假设有一个音乐CD管理系统:
- CDs — 每个年代发布一套CD,包含其在的唱片集。
- 唱片集Buckets — 组织单位,由年代划分,比如八十年代 九十年代等,包含很多CD,没有一个唱片集都有一个标签,记录当前其中唱片数量。
- 年代Generation — 唱片集所在的年代时间
- 收藏Collection —收藏某人的CDs 和 bucket
设计两个数据表:
- Buckets唱片集 表
- bucketId (e.g. 1,2,3,4) – PRIMARY KEY CLUSTERED聚集索引
- name (e.g. 80年代音乐, 90年代音乐)
- generation (e.g. 0, 1, 2)
- size (e.g. 4323, 122)
- collectionId (e.g. “Steves 音乐收藏”) – NON-CLUSTERED INDEX非聚集索引
- Cds 表
- cdId (e.g. 1,2,3,4) – PRIMARY KEY CLUSTERED聚集索引
- name (e.g. “月亮代表我的心”, “你不懂我的心”)
- bucketId (e.g. 1,2, etc. Bucket表的外键) – NON-CLUSTERED INDEX非聚集索引
下面开始对CD进行在线挑拣分配,从原来的唱片集中分配到新的唱片集表中,比如你原来唱片集是按照男女歌手分类,后来想按年代分类,那么就要从旧的分类挑片到新分类,假设我们有成千上万的CD,数千唱片集,挑片的逻辑如下:
(I)得到工作清单 Get next chunk of work
(II)遍历每个CD根据其所属的年代 唱片集
(新的唱片集和旧的唱片集的大小不断增长 )
(III)开始数据库事务
(IV)保存分配到的新唱片集Buckets大小
(V) 将CD的外键更新到新的唱片集Buckets
(VI)确认数据库事务
假设这个过程是并发,有很多操作者操作,每加入一个唱片到新唱片集,都要修改新的唱片集大小:
UPDATE Buckets SET size = size + 123 WHERE bucketId = 1.
然后再更新CD的外键bucketId,标志这个CD已经属于新的唱片集了:
UPDATE Cds SET bucketId = 123 WHERE bucketId = 101.
其中 bucketId = 101是旧的唱片集代号, 123是新的唱片集号码。
这两个操作是在同一个数据库事务。
问题出来了,系统暂停了,停止了。输出线程日志:
c:\>jps
1234 BucketRedistributionMain
3456 jps
c:\>jstack 1234 > threaddump.out
得到下面错误:
"container-lowpool-3" daemon prio=2 tid=0x0000000007b0e000 nid=0x4fb0 runnable [0x000000000950e000]
java.lang.Thread.State: RUNNABLE
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(Unknown Source)
...
at net.sourceforge.jtds.jdbc.SharedSocket.readPacket(Unknown)
at net.sourceforge.jtds.jdbc.SharedSocket.getNetPacket(Unknown)
...
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(Unknown)
...
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216)
...
at com.mycompany.BucketRedistributor$Worker.updateBucketSizeDeltas()
...
at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
...
at java.lang.Thread.run(Unknown Source)
一个工作线程updateBucketSizeDeltas正在保存更新唱片集的大小,等待数据库MSSQL完成返回响应时出错。
为什么数据库在这里挂机了呢?
MSSQL提供了数据库工具:
SUSPENDED表示暂停了,53, 54, 60, and 61都在等待56 红色标注的SELECT的,58不是,是管理用的。很奇怪,一个查询操作竟然堵塞了其他更新操作,阻止它们对唱片集大小进行更新,读查询如下:
SELECT c.cdId, c.bucketId FROM Buckets b INNER JOIN Cds c ON b.bucketId = c.bucketId WHERE b.collection = 'Steves 音乐收藏' and b.generation = 23
56 握住了一个 S (shared共享锁) 在key资源 (key资源 = 索引上的行锁) 1349579846,
查询工作Session情况:
select es.session_id, es.host_name, es.status as session_status, sr.blocking_session_id as req_blocked_by,
datediff(ss, es.last_request_start_time, getdate()) as last_req_submit_secs,
st.transaction_id as current_xaction_id,
datediff(ss, dt.transaction_begin_time, getdate()) as xaction_start_secs,
case dt.transaction_type
when 1 then 'read_write'
when 2 then 'read_only'
when 3 then 'system'
when 4 then 'distributed'
else 'unknown'
end as trx_type,
sr.status as current_req_status,
sr.wait_type as current_req_wait,
sr.wait_time as current_req_wait_time, sr.last_wait_type as current_req_last_wait,
sr.wait_resource as current_req_wait_rsc,
es.cpu_time as session_cpu, es.reads as session_reads, es.writes as session_writes,
es.logical_reads as session_logical_reads, es.memory_usage as session_mem_usage,
es.last_request_start_time, es.last_request_end_time, es.transaction_isolation_level,
sc.text as last_cnx_sql, sr.text as current_sql, sr.query_plan as current_plan
from sys.dm_exec_sessions es
left outer join sys.dm_tran_session_transactions st on es.session_id = st.session_id
left outer join sys.dm_tran_active_transactions dt on st.transaction_id = dt.transaction_id
left outer join
(select srr.session_id, srr.start_time, srr.status, srr.blocking_session_id,
srr.wait_type, srr.wait_time, srr.last_wait_type, srr.wait_resource, stt.text, qp.query_plan
from sys.dm_exec_requests srr
cross apply sys.dm_exec_sql_text(srr.sql_handle) as stt
cross apply sys.dm_exec_query_plan(srr.plan_handle) as qp) as sr on es.session_id = sr.session_id
left outer join
(select scc.session_id, sct.text
from sys.dm_exec_connections scc
cross apply sys.dm_exec_sql_text(scc.most_recent_sql_handle) as sct) as sc on sc.session_id = es.session_id
where
es.session_id >= 50
一个线程试图在唱片集buckets表collection索引中查询"Steves 音乐收藏" ,然后再查询年代generation=23,然后进入CD表的bucketId索引,为了完成前面读查询的Where条件,数据库引擎得用聚集索引也就是主键索引和一个非聚集索引。
当joining时并且在 READ COMMITTED isolation level隔离级别读行记录, 引擎实际得到一个锁这样能从一个索引遍历到另外一个索引,这是为了保证一致性读,为了读取在唱片集Bucket表中的generation值,它必须获得一个共享锁。
当另外一个session试图更新唱片集Bucket中同样记录的size字段时,问题来了,它需要一个在行记录上的排他锁X (exclusive) (用红色标注),但是它不能得到,因为读查询拥有一个与之冲突的 S 共享锁(绿色标注)。
为什么共享锁被hold了呢?因为在READ COMMITTED 你通常必须在记录读取时hold住锁(也有例外),一旦值被读取就被释放,如果你在一个SQL语句中读取10行,这个引擎将在读1行时锁1然后释放,读2行时锁2然后释放。
我们注意到前面读查询的等待类型是ASYNC_Network_IO。这是等待I/O在网络上完成的等待。
java多线程
Java同步或锁
Java性能调优