{"id":5262,"date":"2024-03-14T02:35:52","date_gmt":"2024-03-14T02:35:52","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/"},"modified":"2025-08-01T12:49:08","modified_gmt":"2025-08-01T12:49:08","slug":"how-does-mysql-handle-optimistic-locking-and-pessimistic-locking","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/","title":{"rendered":"MySQL Locking: Optimistic vs Pessimistic"},"content":{"rendered":"

There are two ways in MySQL to handle optimistic locking and pessimistic locking.<\/p>\n

    \n
  1. Optimistic Locking: In MySQL, optimistic locking can be achieved by using a version number or timestamp. A version number or timestamp field is added to the database table, and each time data is modified, the value of that field is queried first. When updating the data, the value of that field is incremented by 1 or updated to the current timestamp. Before committing the update operation, the version number or timestamp of the data is checked to see if it matches the value obtained during the previous query. If they match, the update is successful; if not, the update fails.<\/li>\n
  2. Pessimistic Lock: In MySQL, pessimistic locking can be achieved by using the SELECT … FOR UPDATE statement. When this statement is executed, MySQL will lock the selected data, preventing other transactions from modifying it until the current transaction is either committed or rolled back.<\/li>\n<\/ol>\n

    It is important to note that both optimistic locking and pessimistic locking have their own advantages and disadvantages, and the choice of which method to use should be based on specific business scenarios and needs.<\/p>\n","protected":false},"excerpt":{"rendered":"

    There are two ways in MySQL to handle optimistic locking and pessimistic locking. Optimistic Locking: In MySQL, optimistic locking can be achieved by using a version number or timestamp. A version number or timestamp field is added to the database table, and each time data is modified, the value of that field is queried first. […]<\/p>\n","protected":false},"author":9,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[2136,5704,5703,2819,3177],"class_list":["post-5262","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-database-transactions","tag-mysql-concurrency","tag-mysql-locking","tag-optimistic-locking","tag-pessimistic-locking"],"yoast_head":"\nMySQL Locking: Optimistic vs Pessimistic - Blog - Silicon Cloud<\/title>\n<meta name=\"description\" content=\"Learn how MySQL implements optimistic and pessimistic locking with version numbers, timestamps, and transaction control.\" \/>\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.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL Locking: Optimistic vs Pessimistic\" \/>\n<meta property=\"og:description\" content=\"Learn how MySQL implements optimistic and pessimistic locking with version numbers, timestamps, and transaction control.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-14T02:35:52+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-08-01T12:49:08+00:00\" \/>\n<meta name=\"author\" content=\"Ava Mitchell\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ava Mitchell\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/\"},\"author\":{\"name\":\"Ava Mitchell\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/a3e2658c2cb9fb2be95ae0a8861f4a64\"},\"headline\":\"MySQL Locking: Optimistic vs Pessimistic\",\"datePublished\":\"2024-03-14T02:35:52+00:00\",\"dateModified\":\"2025-08-01T12:49:08+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/\"},\"wordCount\":195,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"keywords\":[\"database transactions\",\"MySQL concurrency\",\"MySQL locking\",\"optimistic locking\",\"Pessimistic Locking\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/\",\"name\":\"MySQL Locking: Optimistic vs Pessimistic - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-14T02:35:52+00:00\",\"dateModified\":\"2025-08-01T12:49:08+00:00\",\"description\":\"Learn how MySQL implements optimistic and pessimistic locking with version numbers, timestamps, and transaction control.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL Locking: Optimistic vs Pessimistic\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/a3e2658c2cb9fb2be95ae0a8861f4a64\",\"name\":\"Ava Mitchell\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/15c63cd0564b4a2e07d611bcdffa296f6ea80e8db07c3091f43a84010514899d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/15c63cd0564b4a2e07d611bcdffa296f6ea80e8db07c3091f43a84010514899d?s=96&d=mm&r=g\",\"caption\":\"Ava Mitchell\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/avamitchell\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"MySQL Locking: Optimistic vs Pessimistic - Blog - Silicon Cloud","description":"Learn how MySQL implements optimistic and pessimistic locking with version numbers, timestamps, and transaction control.","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.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/","og_locale":"en_US","og_type":"article","og_title":"MySQL Locking: Optimistic vs Pessimistic","og_description":"Learn how MySQL implements optimistic and pessimistic locking with version numbers, timestamps, and transaction control.","og_url":"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-14T02:35:52+00:00","article_modified_time":"2025-08-01T12:49:08+00:00","author":"Ava Mitchell","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Ava Mitchell","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/"},"author":{"name":"Ava Mitchell","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/a3e2658c2cb9fb2be95ae0a8861f4a64"},"headline":"MySQL Locking: Optimistic vs Pessimistic","datePublished":"2024-03-14T02:35:52+00:00","dateModified":"2025-08-01T12:49:08+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/"},"wordCount":195,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"keywords":["database transactions","MySQL concurrency","MySQL locking","optimistic locking","Pessimistic Locking"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/","url":"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/","name":"MySQL Locking: Optimistic vs Pessimistic - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-14T02:35:52+00:00","dateModified":"2025-08-01T12:49:08+00:00","description":"Learn how MySQL implements optimistic and pessimistic locking with version numbers, timestamps, and transaction control.","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-does-mysql-handle-optimistic-locking-and-pessimistic-locking\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"MySQL Locking: Optimistic vs Pessimistic"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/a3e2658c2cb9fb2be95ae0a8861f4a64","name":"Ava Mitchell","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/15c63cd0564b4a2e07d611bcdffa296f6ea80e8db07c3091f43a84010514899d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/15c63cd0564b4a2e07d611bcdffa296f6ea80e8db07c3091f43a84010514899d?s=96&d=mm&r=g","caption":"Ava Mitchell"},"url":"https:\/\/www.silicloud.com\/blog\/author\/avamitchell\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/5262","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/9"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=5262"}],"version-history":[{"count":2,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/5262\/revisions"}],"predecessor-version":[{"id":150004,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/5262\/revisions\/150004"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=5262"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=5262"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=5262"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}