Oracle11g提高了DML修改父表时锁定子表的级别

近日处理了一个电信计费系统的性能故障,因为外键上没有索引,导致出现enq: TM 锁争用,以及不时出现Deadlock问题,处理完后整理资料,发现这篇blog叙述比较清晰,遂用Google Translate翻译如下:

Oracle 11g(自11.1.0.6以来)引入了一个微妙但可能有重大的变化,关于在监管外键约束方面保持锁的方式。 以下内容已在11.2.0.1和11.2.0.2上进行了测试。

为了设置场景并复制我们在工作中遇到的问题,我将创建一个小表(ALBUMS),它有2个FK约束,指向两个父表(ARTISTS和FORMATS)并用几行填充它们。

SQL> CREATE TABLE artists (id NUMBER PRIMARY KEY, artist_name VARCHAR2(30));

Table created.

SQL> CREATE TABLE formats (id NUMBER PRIMARY KEY, format_name varchar2(30));

Table created.

SQL> CREATE TABLE albums (id NUMBER, album_name VARCHAR2(30), artist_id NUMBER CONSTRAINT artist_fk REFERENCES artists(id), format_id number

CONSTRAINT format_fk REFERENCES formats(id));

Table created.

SQL> INSERT INTO artists VALUES (1, 'DAVID BOWIE');

1 row created.

SQL> INSERT INTO artists VALUES (2, 'PINK FLOYD');

1 row created.

SQL> INSERT INTO formats VALUES (1, 'CD');

1 row created.

SQL> INSERT INTO formats VALUES (2, 'DVD');

1 row created.

SQL> INSERT INTO albums VALUES (1, 'LOW', 1, 1);

1 row created.

SQL> INSERT INTO albums VALUES (2, 'DIAMOND DOGS', 1, 1);

1 row created.

SQL> COMMIT;

Commit complete.

好的,在10.2.0.3中的ARTISTS表上运行以下insert语句时:

SQL> insert into artists values (3, 'MUSE');

1 row created.

由于这些表之间的FK关系,检查v$lock视图将显示该事务在子ALBUMS表上的行级-共享(SS)Mode 2 TM(DML Enqueue)锁定。

 

如果另一个会话要么说删除一行或从另一个父FORMATS表更新PK:

SQL> update formats set id = 2 where id = 2;

1 row updated.

当它暂时需要在ALBUMS表上进行TM共享(S)模式4锁定时,它将成功没有问题,它可以成功获取它,因为并发SS锁定不会阻止这种情况发生。 它需要访问此模式4共享锁定以确保当前没有影响ALBUMS表的事务可能违反父FORMATS表上的DML操作之后的约束。
但是,在Oracle 11g中重复相同的练习,我们遇到了细微的差别。 在ARTISTS表中再次运行insert语句时:

SQL> insert into artists values (3, 'MUSE');

1 row created.

现在,检查v$lock视图将显示该事务在子ALBUMS表上的行X(SX)LMODE 3中保持TM(DML Enqueue)锁定,而不是像在10g中那样在LMODE 2 SS级别锁定。 这是一个“更高”级别的锁定模式,对于现在尝试删除或更新FORMATS表中的PK的其他会话具有以下结果:

SQL> update formats set id = 2 where id = 2
;

 

 

会话现在挂起,因为它必须等待另一个会话释放DML Enqueue LMODE 3 SX锁,然后它才能获得所请求的TM模式4共享表锁。 这正是我们用一个写得不好的应用程序试图在两个不同的会话中执行类似于上述系列更新的问题所遇到的问题。
Oracle引入了此更改,以消除在重建引用PK的关联FK索引时从具有PK的表中删除行时可能发生的ORA-600问题。
然而,以这种方式引入更严格的锁定级别具有增加遇到诸如此类的新锁定问题的可能性的副作用,增加了遇到死锁情况的可能性(如Charles Hooper先前所讨论的)并因此可能 降低应用程序的整体并发能力。
在这种情况下,“修复”只需在formats_id FK列上创建一个索引(在这种情况下应该要求这样做,以防止更新父FORMAT表时子表上的锁定问题):

SQL> CREATE INDEX albums_format_i on albums(format_id);

Index created.

SQL> insert into artists values (3, 'MUSE');

1 row created.

在这种情况下,ALBUMS表上不再需要表共享锁(因为Oracle现在可以使用关联的索引在父表上执行此类操作后有效地监视子表的完整性)并且语句不再挂起其他会话(session):

 

SQL> update formats set id = 2 where id = 2;

1 row updated.

如果您可能具有没有关联索引的FK约束,那么在迁移到Oracle 11g时,必须注意监管FK约束的锁定行为的这种变化。

附两条查找外键上没建索引的SQL:
sql 1(这个是Tom Kyte书上提供的):

select table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from all_cons_columns where owner='&schema_name') a,
all_constraints b
where a.constraint_name = b.constraint_name
and b.owner='&schema_name'
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from all_ind_columns i
where i.table_name = cons.table_name
and i.table_owner='&schema_name'
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)

sql 2(来自Donald Burlson):

select
case
when b.table_name is null then
'unindexed'
else
'indexed'
end as status,
a.table_name as table_name,
a.constraint_name as fk_name,
a.fk_columns as fk_columns,
b.index_name as index_name,
b.index_columns as index_columns
from
(
select
a.table_name,
a.constraint_name,
listagg(a.column_name, ',') within
group (order by a.position) fk_columns
from
dba_cons_columns a,
dba_constraints b
where
a.constraint_name = b.constraint_name
and
b.constraint_type = 'R'
and
a.owner = '&&schema_owner'
and
a.owner = b.owner
group by
a.table_name,
a.constraint_name
) a
,(
select
table_name,
index_name,
listagg(c.column_name, ',') within
group (order by c.column_position) index_columns
from
dba_ind_columns c
where
c.index_owner = '&&schema_owner'
group by
table_name,
index_name
) b
where
a.table_name = b.table_name(+)
and
b.index_columns(+) like a.fk_columns || '%'
order by
1 desc, 2;

298 Comments

  1. Pingback: nike kyrie 4 dark obsidianblack 943806 401

  2. Pingback: air jordan hydro 6 tumblr.com

  3. Pingback: 2019

  4. Pingback: nike auto flight black infrared

  5. Pingback: converse chuck taylor 2 thermo boot uk trainers

  6. Pingback: top 5 iphone 7 plus cases twitter.com

  7. Pingback: retro 7 olympic clothing

  8. Pingback: air jordan tech challenge 5.2

  9. Pingback: nike air veer lovers khaki light uk trainers

  10. Pingback: nba shoes why not zer01

  11. Pingback: off white ss17 twitter.com

  12. Pingback: cost of christian louboutin shoes

  13. Pingback: nike air huaraches gold uk trainers

  14. Pingback: aa3172 500 nike zoom fly sp indigo

  15. Pingback: 褋懈薪懈泄 芯褉邪薪卸械胁褘泄 nike air custom sup

  16. Pingback: nike free flyknit nike free flyknit 40 womens

  17. Pingback: uk trainers nike free rn cmtr 2018 grey

  18. Pingback: nike hypervenom phantom 2018 nike hypervenomx proximo ii df

  19. Pingback: black trainers for toddlers tumblr.com

  20. Pingback: cleantalkorg2.ru

  21. Pingback: #macron #Lassalle

  22. Pingback: uk trainers womens air jordan 12 lemon yellow

  23. Pingback: a2019-2020

  24. Pingback: facebook

  25. Pingback: facebook1

  26. Pingback: uk trainers womens under armour ignite red black

  27. Pingback: adidas adidas yeezy boost 350

  28. Pingback: 斜械卸械胁褘泄 泻褉邪褋薪褘泄 nike dunk hi premium qs

  29. Pingback: jordan 7 for sale crystal glitter

  30. Pingback: adidas nmd online shop tumblr.com

  31. Pingback: 卸械谢褌褘泄 褔械褉薪褘泄 nike air max invigor print

  32. Pingback: javsearch.mobi

  33. Pingback: gr酶n gr酶n nike free flyknit

  34. Pingback: uk trainers nike air jordan 14 black toe white black varsity red metallic silver

  35. Pingback: uk trainers air jordan 5 3lab5 gs customize

  36. Pingback: brun lyser酶d nike air vapor max

  37. Pingback: nike dunk high ac premium qs bronze black

  38. Pingback: hypervenom 2 bronze co.uk

  39. Pingback: nike air max 180 moon light co.uk

  40. Pingback: swarovski blinged out glitter crystal nike eric koston

  41. Pingback: puma evopower vigor 1 fg ultra yellow peacoat orange clown fish soccer shoes

  42. Pingback: sporting puma game shorts boys

  43. Pingback: womens detroit jersey

  44. Pingback: facepalm nikedown jacket sale quick buy nike sportswear hooded down jacket

  45. Pingback: nike roshe run nike roshe run womens blue

  46. Pingback: strong stretch nike air max hyperposite sneakers men

  47. Pingback: white man pretty sure he needs to weigh on this conversation

  48. Pingback: rojo nike shox turbo

  49. Pingback: mens tampa bay buccaneers nfl pro line by fanatics branded heathered gray true classics full

  50. Pingback: nike mens air max sequent 3 running greyred cool grey

  51. Pingback: nike air jordan 6 noir gris blanc 2012 pas cher

  52. Pingback: 365830 01 puma suede platform

  53. Pingback: readit cold shoulder dresses floral printed dress shirt dress women off shoulder 2018 long sleeve

  54. Pingback: nike air huarache run ultra junior entrenador a5d09

  55. Pingback: vans vault deconstructs iconic slip on sk8 hi with its capsuletwo colourways shoes feature suede leather canvas

  56. Pingback: la vie lancome twitter.com

  57. Pingback: air foamposite one concord black white game royal 314996 005 mens hardaway shoes

  58. Pingback: hugo boss navy pacas l jumper

  59. Pingback: 泻褉邪褋薪褘泄 褋械褉褘泄 lebron 14 low

  60. Pingback: philadelphia eagles new era super bowl lii champions 59fifty fitted hat black

  61. Pingback: white basket heart tumblr.com

  62. Pingback: nike mens bombax

  63. Pingback: don c sneaker with

  64. Pingback: white leather boots

  65. Pingback: womens pink vapormax plus

  66. Pingback: fitted performance hat pts65 pts65 customize

  67. Pingback: bobblection

  68. Pingback: adidas ultra boost 4.0 5th anniversary

  69. Pingback: lateraline

  70. Pingback: women panties bragas thong lace lingerie g string womens under black sexy lace under women briefs

  71. Pingback: a body strap back cap fleecewestern fittingi keep a feeling comfortable with dri fit technology leather strap i

  72. Pingback: sock crafts for kids

  73. Pingback: tentplezier

  74. Pingback: nfl mens buffalo bills concepts sport royal bleacher robe

  75. Pingback: akissilindir

  76. Pingback: michaelclear

  77. Pingback: stopdenguenow

  78. Pingback: glamlustlife

  79. Pingback: metinmilli

  80. Pingback: dolcemialuxe

  81. Pingback: givenkface

  82. Pingback: pistonari

  83. Pingback: iseejanemary

  84. Pingback: threeoaksmi

  85. Pingback: long basic tops asos

  86. Pingback: womens cheap elegant wedding dresses

  87. Pingback: mikeorlandoni

  88. Pingback: emssal

  89. Pingback: air jordan retro vii olympic

  90. Pingback: dvrordie

  91. Pingback: gladatassen

  92. Pingback: cheap mens tracksuit bottoms

  93. Pingback: supreme terry knit camp caps

  94. Pingback: seminarioeam

  95. Pingback: offgridac

  96. Pingback: rentalloka

  97. Pingback: houstonujc

  98. Pingback: bermudabrands

  99. Pingback: cool running blue light sneakers puma

  100. Pingback: buymyhost

  101. Pingback: air jordan black and red bred 11 twitter.com

  102. Pingback: clcohelp

  103. Pingback: pink off shoulder cross back bow waist homecoming dress

  104. Pingback: construction sun hats coolibar

  105. Pingback: new balance 890v4 shoes

  106. Pingback: gbwpvqyg

  107. Pingback: shaunasbags

  108. Pingback: cynshq

  109. Pingback: arron afflalo t shirt yellow

  110. Pingback: resekipman

  111. Pingback: riches le coq sportif femme

  112. Pingback: billknorr

  113. Pingback: churchatsea

  114. Pingback: nicekicks pictures crocs modi mens sport flip flops

  115. Pingback: bootsoffprice

  116. Pingback: nike kd 7 elite sample that may release

  117. Pingback: red rare prints wayfarer subway sunglasses wayfarer eyeglasses wayfarer eyewear unisex

  118. Pingback: linguatravels

  119. Pingback: illustrateh

  120. Pingback: dark blue midi dress

  121. Pingback: mykariera

  122. Pingback: skechers urban track forward mens trainers sage men

  123. Pingback: linsion 925 sterling silver claw hook lion king keyring mens keychain 8j032

  124. Pingback: air jordan son of mars mint green black

  125. Pingback: sethba

  126. Pingback: weisseditions

  127. Pingback: womens mj dunk contest

  128. Pingback: nike mercurial vapor superfly iii fg safari

  129. Pingback: nike hyperdunk 2017 low team nike bordeaux da basket

  130. Pingback: nvusforex

  131. Pingback: ouaias

  132. Pingback: free baby bootees pattern download from womans weekly

  133. Pingback: rectangular blocking pad training equipment volleyball training tool

  134. Pingback: nike benassi jdi slide womens sunset glow desert camo wolf grey

  135. Pingback: asics gel sendai 2 svart

  136. Pingback: aviation cessna skyhawk square keychain

  137. Pingback: pharmacypartd

  138. Pingback: shanamorgan

  139. Pingback: q14rzxj1

  140. Pingback: bobaday

  141. Pingback: 褋械褉褘泄 褋械褉褘泄 nike air force 1 shoe

  142. Pingback: in the temples and less tapered temple tips to fit a wider face and flatter lenses to help prevent the frames from resting on the cheeks

  143. Pingback: cell first back cover for iball andi 4 h tiger black

  144. Pingback: what do you buy for someone that has everything

  145. Pingback: gold grau nike lunar elite sky hi city pack

  146. Pingback: womens fenty sandals

  147. Pingback: womens mens stylish nike hyperdunk 2017 low team university red white metallic silver shoe

  148. Pingback: ripstop army cap ripstop army cap

  149. Pingback: nike blazer hi suede vntg 344344 717

  150. Pingback: jennifer lopez kim kardashian 10 more stars channelling their inner rapunzel with insanely long hair extensions

  151. Pingback: lubimochka

  152. Pingback: new york mets jh design mlb men s wool reversible leather sleeve jacket

  153. Pingback: ksjfifgmif

  154. Pingback: kalimias

  155. Pingback: bringribben

  156. Pingback: intoloo

  157. Pingback: polo hombres zip up sweater

  158. Pingback: sevenkarma

  159. Pingback: rayban aviator large silver metal framed sunglasses silver

  160. Pingback: kbowendesign

  161. Pingback: altharmony

  162. Pingback: nike free run 2 noir blanc uk

  163. Pingback: medsydobrasil

  164. Pingback: docelarsjc

  165. Pingback: nrastogi

  166. Pingback: susanlippold

  167. Pingback: nike free rn 2017 tdv grey volt toddler boys shoes size 5

  168. Pingback: womens kd iv trey

  169. Pingback: linamauri

  170. Pingback: bidmani

  171. Pingback: healtobfree

  172. Pingback: kyrie 2 white and purple

  173. Pingback: cosmicupcake

  174. Pingback: womens blue velvet knee high boots

  175. Pingback: mr wim went all out by customizing a pair of shoes and a board

  176. Pingback: hvactampafl

  177. Pingback: ogstuff

  178. Pingback: new arrivals womens nike sb skor check solarsoft cool grey white

  179. Pingback: riddercorp

  180. Pingback: dianaandwill

  181. Pingback: air jordan 31 n7 for sale virginia

  182. Pingback: phaqtory

  183. Pingback: polo ralph lauren rouge polo shirt

  184. Pingback: checkvins

  185. Pingback: yourctt

  186. Pingback: eventyrklub

  187. Pingback: arcadetogo

  188. Pingback: synexiemobile

  189. Pingback: myflipsstore

  190. Pingback: long sleeve off shoulder white dress coffee

  191. Pingback: hamshrd

  192. Pingback: adidas performance ts entry kn oh tracksuit black

  193. Pingback: trouva the north face black 1990 mountain q jacket

  194. Pingback: ipartybooths

  195. Pingback: bukafalar

  196. Pingback: ycbrowsbeauty

  197. Pingback: nike los angeles lakers nike sleeveless nba top blackblack womens nike tops tshirts sale online womens

  198. Pingback: jordan 12 air max white black red

  199. Pingback: nike lunar speed

  200. Pingback: jessiebridges

  201. Pingback: votuongluan

  202. Pingback: planetmolly

  203. Pingback: ray ban aviator flash lenses 62mm

  204. Pingback: georgerudder

  205. Pingback: zx flux chaussures adidas noir

  206. Pingback: jeep flag hoodie jeep hoodie unisex usa flag jeep etsy

  207. Pingback: nike air max 2017 women blue green 849560 402 the best prices

  208. Pingback: air jordan 11 us silver anniversary white silver new release

  209. Pingback: thehodostore

  210. Pingback: farmsbd

  211. Pingback: air jordan 2017 restock

  212. Pingback: magneticduo

  213. Pingback: hbshbuick

  214. Pingback: hepseninle

  215. Pingback: instabitgram

  216. Pingback: automotoracing

  217. Pingback: theblases

  218. Pingback: mens nike air max fury white cool grey infrared

  219. Pingback: hangrysquad

  220. Pingback: latinphonics

  221. Pingback: mycqhair

  222. Pingback: nike zoom pegasus 31 verde rosso

  223. Pingback: olafhuth

  224. Pingback: hat beanie mottled light grey

  225. Pingback: bitcashgram

  226. Pingback: adidas alpha bounce instinct cc black sport db2735 size 39 44 robert

  227. Pingback: ahlyons

  228. Pingback: herren ralph lauren jackets blau orange

  229. Pingback: gorgeous jordan retro 6 ring high heels boots black pink for

  230. Pingback: kendalfewster

  231. Pingback: adidas leather turf soccer shoes

  232. Pingback: seansinjin

  233. Pingback: blue yellow mens nike hypervenom shoes

  234. Pingback: instanalo

  235. Pingback: wakunga

  236. Pingback: nipiservices

  237. Pingback: nike roshe run femmes floral bleu noir blanc

  238. Pingback: lingatext

  239. Pingback: deploygis

  240. Pingback: divaadiamante

  241. Pingback: sale nike womens zoom rival m 8 running spikes ho17 446

  242. Pingback: tlclease

  243. Pingback: cohenhypnosis

  244. Pingback: burayagidin

  245. Pingback: kvinners nike sb stefan janoski max bl氓 gull

  246. Pingback: drjennieward

  247. Pingback: rmmassage

  248. Pingback: vbchopshop

  249. Pingback: nike air max bleu color

  250. Pingback: intdag

  251. Pingback: elite julius thomas jersey denver broncos 80 home orange

  252. Pingback: imannequins

  253. Pingback: achievehacks

  254. Pingback: zanascuisine

  255. Pingback: tikihutkit

  256. Pingback: alahedsports

  257. Pingback: chaussures sport mode air rift ninja nike blanche nouveau magnifique

  258. Pingback: womens puscifer hoodie

  259. Pingback: mississippi state baseball shirt

  260. Pingback: source ducks equipment size 9

  261. Pingback: air jordan true flight infrarosso 23 uk

  262. Pingback: nike tanjun mujeres athletic zapatos negro

  263. Pingback: gushandyman

  264. Pingback: warrnamboolag

  265. Pingback: sousoushop

  266. Pingback: checkmyprices

  267. Pingback: georginabauer

  268. Pingback: hmttour

  269. Pingback: nike air run ultra crystals etsy.com

  270. Pingback: naturabolt

  271. Pingback: nike air huarache nm mens grey

  272. Pingback: converse platform shoes where to buy

  273. Pingback: tjharkins

  274. Pingback: air jordan son of mars bl氓 mintgr酶n

  275. Pingback: easytanklevel

  276. Pingback: new balance 993 mens white orange

  277. Pingback: air jordan 6 sort infrar酶d

  278. Pingback: tiaracara

  279. Pingback: jautobodyshop

  280. Pingback: mens and womens converse all star blue red whiteconverse shoes grey converse off white official uk

  281. Pingback: smartiesland

  282. Pingback: viptarimas

  283. Pingback: genevacollins

  284. Pingback: ray ban rb4101 58 jackie ohh

  285. Pingback: pickwickcrc

  286. Pingback: size 7 air jordan 11 low ie ghost green

  287. Pingback: ceeditsesame

  288. Pingback: colorado avalanche baseball cap

  289. Pingback: broadcastgg

  290. Pingback: marvel womens black panther 2018 warrior trio black scoop neck t shirt

  291. Pingback: zx flux oro and negro

  292. Pingback: amitsrl

  293. Pingback: womens next underwear trunks

  294. Pingback: dvmengineers

  295. Pingback: smsteachers

  296. Pingback: oldhalliwell

  297. Pingback: sevenguard

  298. Pingback: nike zoom structure 18 kvinders sort gr酶n

Leave Comment