{"id":27955,"date":"2023-09-27T21:06:32","date_gmt":"2023-09-27T19:06:32","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=27955"},"modified":"2023-09-27T21:08:46","modified_gmt":"2023-09-27T19:08:46","slug":"oracle-lock-free-reservations","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-lock-free-reservations\/","title":{"rendered":"Oracle Lock Free Reservations"},"content":{"rendered":"\n<p>The <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/23\/cncpt\/tables-and-table-clusters.html#GUID-7C6A8E8A-F634-4D0D-877A-F948D6101066\">Oracle 23c new feature Lock Free Reservations<\/a> allows to break with the traditional locking mechanism.<\/p>\n\n\n\n<p>Now multiple concurrent sessions can update the same row without be blocked !!! Let&#8217;s see how it works.<\/p>\n\n\n\n<p>In the traditional locking mechanism, when a first session modifies a row, oracle will hold 2 locks :<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Row Exclusive Lock to prevent other transactions which try to update the same row<\/li>\n\n\n\n<li>Table Share Lock that prevent DDL operation on the row being modified<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; update products set price = price+10 where product= 'A1';\n\n1 row updated.<\/code><\/pre>\n\n\n\n<p>If a second session tries to update the same row, oracle will request :<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Row Exclusive Lock<\/li>\n<\/ul>\n\n\n\n<p>and hold:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Table Share Lock<\/li>\n<\/ul>\n\n\n\n<p>The session 2 will wait until the resource locked by session 1 is released.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; update products set price = price+20 where product= 'A1';\n--session 2 is waiting...<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<pre class=\"wp-block-code\"><code>select a.sid,a.type,a.id1,b.object_name,a.lmode,a.request,a.ctime,a.block from v$lock a, dba_objects b where a.id1 = b.object_id(+) and sid in ( 67, 237) and type in ('TX','TM');\n\nSID TYPE  ID1  OBJECT_NAME    LMODE    REQUEST      CTIME  BLOCK\n-----------------------------------------------------------------------------\n67  TM      81133    PRODUCTS 3               0       7          0\n237 TM     81133     PRODUCTS 3               0       2           0\n237 TX      262157            0               6       2           0\n67  TX      262157            6               0       7            1<\/code><\/pre>\n\n\n\n<p>The session 1 hold a ROW EXCLUSIVE LOCK on the resource 262157.<\/p>\n\n\n\n<p>The session 2 requested a ROW EXCLUSIVE LOCK on the resource 262157 and wait.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<p>With Lock Free Reservations features, this behaviour changes and you can have concurrent transactions locked the same resource!!!<\/p>\n\n\n\n<p>First, you have to modify your column in RESERVABLE, new columns indicating if column is reservable or if table contains reservable columns has been added into the oracle catalog :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; alter table if exists products modify (price reservable);\nTable altered.\n<div><span style=\"background-color: initial;font-family: inherit;font-size: inherit;color: initial\">SQL&gt; select has_reservable_column from user_tables where table_name = 'PRODUCTS';<\/span><\/div>\nHAS_RESERVABLE_COLUM\n----------------------------\n<div>YES<\/div>\n\n<div>SQL&gt; select column_name,reservable_column from user_tab_cols where table_name = 'PRODUCTS' and column_name = 'PRICE';<\/div>\nCOLUMN_NAME RESERVABLE_COLUMN\n----------------------------------\n<div>PRICE        YES<\/div>\n<\/code><\/pre>\n\n\n\n<p>Now 2 concurrent updates are possible :<\/p>\n\n\n\n<p>Session 1 :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL;\n\nSYS_CONTEXT('USERENV','SID')\n-----------------------------------------------------------------------------\n271\n\nSQL&gt; update products set price = price+10 where product= 'A1';\n\n1 row updated.<\/code><\/pre>\n\n\n\n<p>Session 2 :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL;\n\nSYS_CONTEXT('USERENV','SID')\n-----------------------------------------------------------------------------\n44\n\nSQL&gt; update products set price = price+20 where product= 'A1';\n\n1 row updated.\n--Session 2 is no more waiting...<\/code><\/pre>\n\n\n\n<p>If we have a look the locks in v$lock, we see that the Table Share Lock is always hold by the both sessions and also the Row Exclusive Lock but now on difference resource.<\/p>\n\n\n\n<p>Moreover a new Table Share Lock has been hold on new system table SYS_RESERVJRNL_81133 created by Oracle when we have altered the column in RESERVABLE:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select a.sid,a.type,a.id1,b.object_name,a.lmode,a.request,a.ctime,a.block from v$lock a, dba_objects b where a.id1 = b.object_id(+) and sid in (\t271,\t    44) and type in ('TX','TM')\n\nSID TYPE ID1 OBJECT_NAME LMODE\t            REQUEST      CTIME\tBLOCK\n---------- -- ---------- ----------------------------------------------------\n44  TM   81133 PRODUCTS               3\t    0           1201\t    0\n271 TM\t 81133 PRODUCTS               3\t    0           1205\t    0\n44  TM\t 87804 SYS_RESERVJRNL_81133   3\t    0           1201\t    0\n271 TM\t 87804 SYS_RESERVJRNL_81133   3\t    0           1205\t    0\n271 TX\t 393235                       6\t    0           1205\t    0\n44  TX\t 262176                       6     0           1201\t    0<\/code><\/pre>\n\n\n\n<p>So, how to ensure the consistency of the database ? The answer is into the table SYS_RESERVJRNL_81133 which is persistent only at the session and contains the updated value only for the session:<\/p>\n\n\n\n<p>Session 1:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; select * from SYS_RESERVJRNL_81133;\n\nORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_STMT_TYPE$ PRODUCT PRICE_O PRICE_RESERVED\n-------------------------------- ---------------- ------------ --------------\n060013005B1D0000 ACTIVE       UPDATE\t\tA1\t   +\t10\n\nSQL&gt;<\/code><\/pre>\n\n\n\n<p>Session 2:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; select * from SYS_RESERVJRNL_81133;\n\nORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_STMT_TYPE$ PRODUCT    PRICE_O PRICE_RESERVED\n-------------------------------- ---------------- ------------ --------------\n04002000EE190000 ACTIVE       UPDATE\t\tA1\t   +\t\t       20\n\nSQL&gt;<\/code><\/pre>\n\n\n\n<p>After the commit or rollback is done, the target table is updated or rollbacked and the system reservation journal table is empty:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>--Session 1\nSQL&gt; SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL;\n\nSYS_CONTEXT('USERENV','SID')\n-----------------------------------------------------------------------------\n271\nSQL&gt; commit;\n\nCommit complete.\n\nSQL&gt; select * from SYS_RESERVJRNL_81133;\n\nno rows selected\n\nSQL&gt;\n--Session 2\nSQL&gt; SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL;\n\nSYS_CONTEXT('USERENV','SID')\n-----------------------------------------------------------------------------\n44\nSQL&gt; commit;\n\nCommit complete.\n\nSQL&gt; select * from SYS_RESERVJRNL_81133;\n\nno rows selected\n\nSQL&gt;<\/code><\/pre>\n\n\n\n<p>Very nice feature but with some limitations where the most important are:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Only numeric columns can be a reservable column<\/li>\n\n\n\n<li>Reservable columns cannot be indexed<\/li>\n\n\n\n<li>Update on reservable column must reference the reservable column (no hard value) and must use the operand &#8216;+&#8217; or &#8216;-&#8216;.<\/li>\n<\/ul>\n\n\n\n<p>The guidelines and restrictions for Reservable Columns is <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/23\/adfns\/using-lock-free-reservation.html#GUID-B2C0C556-64D0-47B6-B8AE-C97AD56A0F96\">here<\/a>.<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>The Oracle 23c new feature Lock Free Reservations allows to break with the traditional locking mechanism. Now multiple concurrent sessions can update the same row without be blocked !!! Let&#8217;s see how it works. In the traditional locking mechanism, when a first session modifies a row, oracle will hold 2 locks : If a second [&hellip;]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,368,59],"tags":[2833,481,96,644],"type_dbi":[],"class_list":["post-27955","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-development-performance","category-oracle","tag-23c","tag-lock","tag-oracle","tag-performance-tuning"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Oracle Lock Free Reservations - 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\/oracle-lock-free-reservations\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle Lock Free Reservations\" \/>\n<meta property=\"og:description\" content=\"The Oracle 23c new feature Lock Free Reservations allows to break with the traditional locking mechanism. Now multiple concurrent sessions can update the same row without be blocked !!! Let&#8217;s see how it works. In the traditional locking mechanism, when a first session modifies a row, oracle will hold 2 locks : If a second [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-lock-free-reservations\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-09-27T19:06:32+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-09-27T19:08:46+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=\"2 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\\\/oracle-lock-free-reservations\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-lock-free-reservations\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle Lock Free Reservations\",\"datePublished\":\"2023-09-27T19:06:32+00:00\",\"dateModified\":\"2023-09-27T19:08:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-lock-free-reservations\\\/\"},\"wordCount\":360,\"commentCount\":0,\"keywords\":[\"23c\",\"lock\",\"Oracle\",\"Performance Tuning\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\",\"Development &amp; Performance\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-lock-free-reservations\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-lock-free-reservations\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-lock-free-reservations\\\/\",\"name\":\"Oracle Lock Free Reservations - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2023-09-27T19:06:32+00:00\",\"dateModified\":\"2023-09-27T19:08:46+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-lock-free-reservations\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-lock-free-reservations\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-lock-free-reservations\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle Lock Free Reservations\"}]},{\"@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":"Oracle Lock Free Reservations - 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\/oracle-lock-free-reservations\/","og_locale":"en_US","og_type":"article","og_title":"Oracle Lock Free Reservations","og_description":"The Oracle 23c new feature Lock Free Reservations allows to break with the traditional locking mechanism. Now multiple concurrent sessions can update the same row without be blocked !!! Let&#8217;s see how it works. In the traditional locking mechanism, when a first session modifies a row, oracle will hold 2 locks : If a second [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-lock-free-reservations\/","og_site_name":"dbi Blog","article_published_time":"2023-09-27T19:06:32+00:00","article_modified_time":"2023-09-27T19:08:46+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-lock-free-reservations\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-lock-free-reservations\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle Lock Free Reservations","datePublished":"2023-09-27T19:06:32+00:00","dateModified":"2023-09-27T19:08:46+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-lock-free-reservations\/"},"wordCount":360,"commentCount":0,"keywords":["23c","lock","Oracle","Performance Tuning"],"articleSection":["Database Administration &amp; Monitoring","Database management","Development &amp; Performance","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-lock-free-reservations\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-lock-free-reservations\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-lock-free-reservations\/","name":"Oracle Lock Free Reservations - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2023-09-27T19:06:32+00:00","dateModified":"2023-09-27T19:08:46+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-lock-free-reservations\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-lock-free-reservations\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-lock-free-reservations\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle Lock Free Reservations"}]},{"@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\/27955","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=27955"}],"version-history":[{"count":16,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/27955\/revisions"}],"predecessor-version":[{"id":28370,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/27955\/revisions\/28370"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=27955"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=27955"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=27955"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=27955"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}