{"id":8687,"date":"2016-07-30T22:17:26","date_gmt":"2016-07-30T20:17:26","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/"},"modified":"2016-07-30T22:17:26","modified_gmt":"2016-07-30T20:17:26","slug":"oracle-serializable-is-not-serializable","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/","title":{"rendered":"Oracle serializable is not serializable"},"content":{"rendered":"
\n

By Franck Pachot<\/h2>\n

.
\nDid you know that when you set isolation level to SERIALIZABLE, it is not serializable but SNAPSHOT? This isolation level is lower than serializable. I’ve never thought about it until I read Markus Winand slides about transactions<\/a>. I recommend every developer or DBA to read those slides. This post is there to illustrate write skew in Oracle.
\n
\nLet’s show an example on SCOTT.EMP table. Let’s say there’s a HR directive to increase one of department 10 employees salary so that total salaries for the department is 9000.
\nNow let’s imagine that two HR users received the directive at the same time.<\/p>\n

User A checks the salaries:
\n
\n23:18:33 SID=365> select ename,sal from EMP where deptno=10;
\n 
\nENAME SAL
\n---------- ----------
\nCLARK 2450
\nKING 5000
\nMILLER 1300
\n<\/code>
\nThe sum is 8750 so User A decides to increase MILLER’s salary with additional 250.<\/p>\n

However, to be sure that he is the only one to do that, he starts a transaction in SERIALIZABLE isolation level, checks the sum again, and do the update:
\n
\n23:18:40 SID=365> set transaction isolation level serializable;
\nTransaction set.
\n 
\n23:18:41 SID=365> select sum(sal) from EMP where deptno=10;
\n 
\n SUM(SAL)
\n----------
\n 8750
\n 
\n23:18:44 SID=365> update EMP set sal=sal+250 where ename='MILLER';
\n1 row updated.
\n<\/code><\/p>\n

Now at the same time, User B is doing the same but chose to increase CLARK’s salary:<\/p>\n


\n23:18:30 SID=12> set transaction isolation level serializable;
\nTransaction set.
\n 
\n23:18:51 SID=12> select sum(sal) from EMP where deptno=10;
\n 
\n SUM(SAL)
\n----------
\n 8750
\n 
\n23:18:53 SID=12> update EMP set sal=sal+250 where ename='CLARK';
\n1 row updated.
\n<\/code><\/p>\n

Note that there is no “ORA-08177: can’t serialize access for this transaction” there because the updates occurs on different rows.<\/p>\n

The User A checks again the sum and then commits his transaction:<\/p>\n


\n23:18:46 SID=365> select sum(sal) from EMP where deptno=10;
\n 
\n SUM(SAL)
\n----------
\n 9000
\n 
\n23:19:04 SID=365> commit;
\nCommit complete.
\n<\/code><\/p>\n

And so does the User B:<\/p>\n


\n23:18:55 SID=12> select sum(sal) from EMP where deptno=10;
\n 
\n SUM(SAL)
\n----------
\n 9000
\n 
\n23:19:08 SID=12> commit;
\nCommit complete.
\n<\/code><\/p>\n

However, once you commit, the result is different:<\/p>\n


\n23:19:09 SID=12> select sum(sal) from EMP where deptno=10;
\n 
\n SUM(SAL)
\n----------
\n 9250
\n<\/code><\/p>\n

Actually, what Oracle calls SERIALIZABLE here is only SNAPSHOT isolation level. You see data without the concurrent changes that have been commited after the beginning of your transaction. And you cannot modify a row that has been modified by another session. However, nothing prevents that what you have read is modified by another session. You don’t see those modification, but they can be commited.<\/p>\n

The definition of serializability requires that the result is the same when transactions occurs one after the other. Here, if User A had commited before the start of User B transaction, the latter would have seen that the total were already at 9000. <\/p>\n

In this example, if you want to prevent write skew you need to lock the table in Share mode. Locking the rows (with select for update) is sufficient to prevent concurrent updates, but then another user can insert a new employee which brings the total salary higher. In addition to that, row locks are exclusive and you don’t want readers to block readers. Locking a range (DEPTNO=10) is not possible in Oracle. So the solution is to lock the table.<\/p>\n

It seems that only PostgreSQL (version >= 9.1) is able to guarantee true serializability without locking.<\/p>\n","protected":false},"excerpt":{"rendered":"

By Franck Pachot . Did you know that when you set isolation level to SERIALIZABLE, it is not serializable but SNAPSHOT? This isolation level is lower than serializable. I’ve never thought about it until I read Markus Winand slides about transactions. I recommend every developer or DBA to read those slides. This post is there […]<\/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],"tags":[643,96,98],"type_dbi":[],"class_list":["post-8687","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-locks","tag-oracle","tag-sql"],"acf":[],"yoast_head":"\nOracle serializable is not serializable - 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-serializable-is-not-serializable\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle serializable is not serializable\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . Did you know that when you set isolation level to SERIALIZABLE, it is not serializable but SNAPSHOT? This isolation level is lower than serializable. I’ve never thought about it until I read Markus Winand slides about transactions. I recommend every developer or DBA to read those slides. This post is there […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-07-30T20:17:26+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=\"3 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-serializable-is-not-serializable\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle serializable is not serializable\",\"datePublished\":\"2016-07-30T20:17:26+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/\"},\"wordCount\":429,\"commentCount\":0,\"keywords\":[\"Locks\",\"Oracle\",\"SQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/\",\"name\":\"Oracle serializable is not serializable - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-07-30T20:17:26+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle serializable is not serializable\"}]},{\"@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 serializable is not serializable - 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-serializable-is-not-serializable\/","og_locale":"en_US","og_type":"article","og_title":"Oracle serializable is not serializable","og_description":"By Franck Pachot . Did you know that when you set isolation level to SERIALIZABLE, it is not serializable but SNAPSHOT? This isolation level is lower than serializable. I’ve never thought about it until I read Markus Winand slides about transactions. I recommend every developer or DBA to read those slides. This post is there […]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/","og_site_name":"dbi Blog","article_published_time":"2016-07-30T20:17:26+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle serializable is not serializable","datePublished":"2016-07-30T20:17:26+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/"},"wordCount":429,"commentCount":0,"keywords":["Locks","Oracle","SQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/","name":"Oracle serializable is not serializable - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-07-30T20:17:26+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-serializable-is-not-serializable\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle serializable is not serializable"}]},{"@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\/8687","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=8687"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8687\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=8687"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=8687"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=8687"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=8687"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}