{"id":5470,"date":"2015-09-13T18:37:03","date_gmt":"2015-09-13T16:37:03","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/"},"modified":"2015-09-13T18:37:03","modified_gmt":"2015-09-13T16:37:03","slug":"waiting-for-row-lock-but-which-row-is-locked","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/","title":{"rendered":"Waiting for row lock. But which row is locked?"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nWhen you are waiting on a locked row, the locked resource is the transaction that has locked the row. We have no information about which row is locked. Here is how to get it from V$SESSION.
\n
\nIn a first session I lock a row in the SCOTT schema:<\/p>\n

\nSQL> select * from SCOTT.SALGRADE where grade=1 for update ;\n \n     GRADE      LOSAL      HISAL\n---------- ---------- ----------\n         1        700       1200\n<\/code><\/pre>\n
And in another session:<\/p>\n
\nSQL> delete from SCOTT.SALGRADE;\n<\/code><\/pre>\n
Of course it’s waiting. <\/p>\n
Let’s see the wait event:<\/p>\n
\nSQL> select sid,state,event,p1raw,p1text from v$session where event like 'enq: %'\n \n   SID STATE   EVENT                          P1RAW            P1TEXT\n------ ------- ------------------------------ ---------------- ---------\n    54 WAITING enq: TX - row lock contention  0000000054580006 name|mode\n<\/code><\/pre>\n
So I’ve a row lock contention.<\/p>\n
Here is more information from V$SESSION about the row that is locked:<\/p>\n
\nSQL> select row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row# from v$session where event like 'enq: %';\n \nROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#\n------------- -------------- --------------- -------------\n       116760              6             211             0\n<\/code><\/pre>\n
From there you can find which row is locked. It’s straightforward, but I’ve two warnings about it.<\/p>\n
First warning is that the ROW_WAIT_OBJ# is not the OBJECT_ID but the DATA_OBJECT_ID.
\nYou have to join with DBA_OBJECT and get the DATA_OBJECT_ID if you want to get the ROWID.<\/p>\n
\nSQL> select owner,object_name,data_object_id from dba_objects where object_id=116760;\n \nOWNER      OBJECT_NAM DATA_OBJECT_ID\n---------- ---------- --------------\nSCOTT      SALGRADE           116760\n<\/code><\/pre>\n
The ROW_WAIT_OBJ# is the OBJECT_ID but I need the DATA_OBJECT_ID to get the ROWID. Here it is the same, but just try to truncate the table (and insert back the data) and you will see that it is different. <\/p>\n
The second warning is about the ROW_WAIT_FILE# which is the the absolute file number.
\nBut you need the relative file number if you want to get the ROWID. <\/p>\n
We have to go though DBA_DATA_FILES to get it.<\/p>\n
\nSQL> select relative_fno from dba_data_files where file_id=6;\n \nRELATIVE_FNO\n------------\n           6\n<\/code><\/pre>\n
It’s the same number here, but if you have transported datafiles (or have more than 1024 datafiles), then you will see different numbers.<\/p>\n
Remember, the ROWID must change when a table is truncated (in order to be sure that we don’t read old data) and the ROWID must not change when we transport tablespaces (so that we don’t have to update all the blocks in the tablespace).<\/p>\n
So now, I can get the ROWID with dbms_rowid and get the row that is locked:<\/p>\n
\nSQL> select * from SCOTT.SALGRADE where rowid=dbms_rowid.rowid_create(1,116760,6,211,0);\n \n     GRADE      LOSAL      HISAL\n---------- ---------- ----------\n         1        700       1200\n<\/code><\/pre>\n
This is the ideal case. You find the row that is locked.
\nFor most of the mode 6 TX lock (exclusive) you will have that information because the session went to the row in order to see the lock, and that information was registered in V$SESSION.
\nIf you have a mode 4 TX lock (share) that can be different. You find those when the lock is at higher level than the row. For example ITL wait, or unique index entry, or referential integrity (I’m talking about TX mode 4 here, not TM locks). Then the information gotten from V$SESSION is incomplete. There is no row number, it can be an index block, etc.<\/p>\n
More information about locks at DOAG 2015<\/a>.<\/p>\n
As a summary, here is the query to get the ROWID for which sessions are waiting on TX locks:
\n(fixed thanks to comment below.)<\/em><\/p>\n
\nselect s.p1raw,o.owner,o.object_name,\ndbms_rowid.rowid_create(1,o.data_object_id,f.relative_fno,s.row_wait_block#,s.row_wait_row#) row_id\nfrom v$session s \njoin dba_objects o on s.row_wait_obj#=o.object_id \njoin dba_segments m on o.owner=m.owner and o.object_name=m.segment_name\njoin dba_data_files f on s.row_wait_file#=f.file_id and m.tablespace_name=f.tablespace_name\nwhere s.event like 'enq: TX%'\n<\/code><\/pre>\n
From P1RAW, you have the lock type (5458 is TX in ascii) and the lock mode: 54580006 is mode 6 (exclusive) and 54580004 is mode 4 (share).<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . When you are waiting on a locked row, the locked resource is the transaction that has locked the row. We have no information about which row is locked. Here is how to get it from V$SESSION.<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,59],"tags":[481,96],"type_dbi":[],"class_list":["post-5470","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-oracle","tag-lock","tag-oracle"],"acf":[],"yoast_head":"\nWaiting for row lock. But which row is locked? - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Waiting for row lock. But which row is locked?\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . When you are waiting on a locked row, the locked resource is the transaction that has locked the row. We have no information about which row is locked. Here is how to get it from V$SESSION.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-09-13T16:37:03+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Waiting for row lock. But which row is locked?\",\"datePublished\":\"2015-09-13T16:37:03+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/\"},\"wordCount\":490,\"commentCount\":0,\"keywords\":[\"lock\",\"Oracle\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/\",\"name\":\"Waiting for row lock. But which row is locked? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-09-13T16:37:03+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Waiting for row lock. But which row is locked?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Waiting for row lock. But which row is locked? - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/","og_locale":"en_US","og_type":"article","og_title":"Waiting for row lock. But which row is locked?","og_description":"By Franck Pachot . When you are waiting on a locked row, the locked resource is the transaction that has locked the row. We have no information about which row is locked. Here is how to get it from V$SESSION.","og_url":"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/","og_site_name":"dbi Blog","article_published_time":"2015-09-13T16:37:03+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Waiting for row lock. But which row is locked?","datePublished":"2015-09-13T16:37:03+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/"},"wordCount":490,"commentCount":0,"keywords":["lock","Oracle"],"articleSection":["Database Administration & Monitoring","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/","url":"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/","name":"Waiting for row lock. But which row is locked? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-09-13T16:37:03+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/waiting-for-row-lock-but-which-row-is-locked\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Waiting for row lock. But which row is locked?"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5470","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=5470"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5470\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=5470"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=5470"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=5470"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=5470"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}