数据库死锁 [英] Database deadlocks

问题描述

我们有数据库死锁的一个经典原因是两个事务以不同的顺序插入\更新表。

One of the classical reason we have a database deadlock is when two transactions are inserting \updating tables in a different order.

例如。事务A在表A中插入，然后在表B中插入表B

e.g. Transaction A inserts in Table A then Table B

和事务B插入，其后是A

and Transaction B inserts in Table B followed by A

一个场景总是存在数据库死锁的风险（假设您没有使用可序列化的隔离级别）

Such a scenario is always at risk of a database deadlock (assuming you are not using serializable isolation level)

我的问题是

A）您在设计中遵循什么样的模式，以确保所有事务以相同的顺序插入\updating。
我正在阅读的一本书 - 有一个建议，你可以按照表的名称对语句进行排序。
你做了这样或不同的 - 这将强制所有插入\updates是在同一个顺序？

A) What kind of patterns do you follow in your design to make sure that all transactions are inserting\updating in the same order. A book I was reading- had a suggestion that you can sort the statements by the name of the table. Have you done something like this or different - which would enforce that all inserts\updates are in the same order ?

B）删除记录怎么办？
删除需要从子表和更新开始\inserts需要从父表开始
如何确保这不会遇到死锁？

B) What about deleting records ? Delete needs to start from child tables and updates\inserts need to start from parent tables How do you ensure that this would not run into a deadlock ?

推荐答案

1. 所有交易都是
   以相同的顺序插入\updating


2. 识别要在交易外删除
   的记录，然后
   尝试在
   最小可能的交易中删除。
   通过在查找阶段识别的主键或类似的
   查找。


3. 小交易一般


4. 索引和其他成效
   调整到速度交易
   ，并促进索引查询
   tablescans


5. 避免使用hot tables，
   eg一个表增加
   计数器用于其他表主
   键。任何其他'switchboard'类型
   配置是有风险的。


6. 特别是如果不使用Oracle学习
   目标
   RDBMS的查找行为（乐观/
   悲观，隔离级别等）
   确保您不允许行锁锁定
   升级到表锁，因为某些
   RDMS将会。

1. All transactions are inserting\updating in the same order
2. Deletes; identify records to be deleted outside a transaction and then attempt the deletion in the smallest possible transaction, e.g. looking up by primary key or similar identified during lookup stage.
3. Small transactions generally
4. Indexing and other performance tuning to both speed transactions and to promote index lookups over tablescans
5. Avoid 'Hot tables', e.g. one table with incrementing counters for other tables primary keys. Any other 'switchboard' type configuration is risky.
6. Especially if not using Oracle learn the looking behaviour of the target RDBMS in detail (optimistic / pessimistic, isolation levels, etc) Ensure you do not allow row locks to escalate to table locks as some RDMSes will.

这篇关于数据库死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！