{"id":10381,"date":"2019-03-12T20:14:35","date_gmt":"2019-03-12T20:14:35","guid":{"rendered":"https:\/\/dbtut.com\/?p=10381"},"modified":"2019-03-12T20:14:36","modified_gmt":"2019-03-12T20:14:36","slug":"enq-tx-row-lock-contention-wait-event","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/","title":{"rendered":"enq: TX \u2013 row lock contention wait event"},"content":{"rendered":"

enq: TX – row lock contention<\/strong> can be regarded as a row lock conflict wait event in operations.<\/p>\n

This event indicates that a session is waiting for another session’s row lock. If the waiting time associated with this wait event is very high, it may be responsible for performance issues in the application.<\/p>\n

TX enqueue is acquired privately when a transaction initiates the first change, and is held in the process until COMMIT or ROLLBACK is executed.<\/p>\n

TX enqueue has several states:<\/h3>\n

TX Enqueue in Mode 6:<\/strong>
\nThis happens when a session is waiting for a row-level lock held by another session. This occurs when a user deletes or updates a row, while another user wants to do the same.<\/p>\n

TX Enqueue in Mode 4:<\/strong><\/p>\n

This happens if the unique index is waiting for a session because of potential copies. If two sessions try to insert the same key value, the second session must wait to see if an ORA-0001 error should occur.<\/p>\n

In order to resolve the waiting in both cases, COMMIT or ROLLBACK must be performed in the first session that holds the lock.<\/p>\n

You can see which SQL clause is waiting with the following queries:<\/strong><\/p>\n

SELECT sid, sql_text\nFROM v$session s, v$sql q\nWHERE sid IN (SELECT sid\nFROM v$session\nWHERE state IN ('WAITING')\nAND wait_class != 'Idle'\nAND event = 'enq: TX - row lock contention'\nAND (q.sql_id = s.sql_id OR q.sql_id = s.prev_sql_id));<\/pre>\n
or<\/p>\n
SELECT sid, sql_text\nFROM v$session s, v$sql q\nWHERE sid IN (SELECT sid\nFROM v$session\nWHERE state IN ('WAITING')\nAND wait_class != 'Idle'\nAND event = 'enq: TX - row lock contention'\nAND (q.sql_id = s.sql_id OR q.sql_id = s.prev_sql_id));<\/pre>\n
You can see which session blocks to which sessions, using the following query:<\/strong><\/p>\n
SELECT blocking_session,\nsid,\nserial#,\nwait_class,\nseconds_in_wait\nFROM v$session\nWHERE blocking_session IS NOT NULL\nORDER BY blocking_session;<\/pre>\n
This wait event can be resolved by the application-side modification. The information identified by the above queries can be shared to direct the application developer.<\/p>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
enq: TX – row lock contention can be regarded as a row lock conflict wait event in operations. This event indicates that a session is waiting for another session’s row lock. If the waiting time associated with this wait event is very high, it may be responsible for performance issues in the application. TX enqueue …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[4],"tags":[2499,2500,2501,2497,2494,2496,2495,2498],"class_list":["post-10381","post","type-post","status-publish","format-standard","","category-oracle","tag-find-the-locking-queries-in-oracle","tag-find-the-locking-sessions-in-oracle","tag-locking-problem-in-oracle","tag-oracle-lock-problem","tag-tx-enqueue","tag-tx-enqueue-in-mode-4","tag-tx-enqueue-in-mode-6","tag-which-session-blocks-to-which-sessions"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":1164,"today_views":0},"yoast_head":"\nenq: TX \u2013 row lock contention wait event - Database Tutorials<\/title>\n<meta name=\"description\" content=\"enq: TX \u2013 row lock contention wait event\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"enq: TX \u2013 row lock contention wait event - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"enq: TX \u2013 row lock contention wait event\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-03-12T20:14:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-03-12T20:14:36+00:00\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"enq: TX \u2013 row lock contention wait event\",\"datePublished\":\"2019-03-12T20:14:35+00:00\",\"dateModified\":\"2019-03-12T20:14:36+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/\"},\"wordCount\":245,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"keywords\":[\"Find the locking queries in Oracle\",\"Find the locking sessions in Oracle\",\"Locking Problem in Oracle\",\"Oracle Lock Problem\",\"TX Enqueue\",\"TX Enqueue in Mode 4\",\"TX Enqueue in Mode 6\",\"which session blocks to which sessions\"],\"articleSection\":[\"ORACLE\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/\",\"name\":\"enq: TX \u2013 row lock contention wait event - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"datePublished\":\"2019-03-12T20:14:35+00:00\",\"dateModified\":\"2019-03-12T20:14:36+00:00\",\"description\":\"enq: TX \u2013 row lock contention wait event\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"enq: TX \u2013 row lock contention wait event\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"enq: TX \u2013 row lock contention wait event - Database Tutorials","description":"enq: TX \u2013 row lock contention wait event","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:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/","og_locale":"en_US","og_type":"article","og_title":"enq: TX \u2013 row lock contention wait event - Database Tutorials","og_description":"enq: TX \u2013 row lock contention wait event","og_url":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/","og_site_name":"Database Tutorials","article_published_time":"2019-03-12T20:14:35+00:00","article_modified_time":"2019-03-12T20:14:36+00:00","author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"enq: TX \u2013 row lock contention wait event","datePublished":"2019-03-12T20:14:35+00:00","dateModified":"2019-03-12T20:14:36+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/"},"wordCount":245,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"keywords":["Find the locking queries in Oracle","Find the locking sessions in Oracle","Locking Problem in Oracle","Oracle Lock Problem","TX Enqueue","TX Enqueue in Mode 4","TX Enqueue in Mode 6","which session blocks to which sessions"],"articleSection":["ORACLE"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/","url":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/","name":"enq: TX \u2013 row lock contention wait event - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"datePublished":"2019-03-12T20:14:35+00:00","dateModified":"2019-03-12T20:14:36+00:00","description":"enq: TX \u2013 row lock contention wait event","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/enq-tx-row-lock-contention-wait-event\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"enq: TX \u2013 row lock contention wait event"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/10381","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=10381"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/10381\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=10381"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=10381"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=10381"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}