{"id":881,"date":"2020-05-22T00:00:00","date_gmt":"2020-05-22T00:00:00","guid":{"rendered":"https:\/\/en.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/"},"modified":"2024-08-21T06:53:26","modified_gmt":"2024-08-21T13:53:26","slug":"pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load","status":"publish","type":"post","link":"https:\/\/www.pingcap.com\/ko\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/","title":{"rendered":"Pessimistic Locking: Better MySQL Compatibility, Fewer Rollbacks Under High Load"},"content":{"rendered":"<p>It&#8217;s critical for modern distributed databases to provide fully ACID transactions. Distributed transactions require some form of concurrency control to guarantee that transactions are executed serially. The choice of concurrency control algorithm affects transaction restrictions and performance under high contention. That&#8217;s why we did something about it.<\/p>\n\n\n\n<p>Since 2015, we at <a href=\"https:\/\/www.pingcap.com\/ko\/\">PingCAP<\/a> have been building <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\">\ud2f0DB<\/a>, an open-source, MySQL-compatible, distributed SQL database. When MySQL users use TiDB, they don&#8217;t need to modify much application code and can onboard TiDB more easily. It&#8217;s known that MySQL uses pessimistic locking as its concurrency control method to ensure data consistency. <strong><a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/pessimistic-transaction\">TiDB supports pessimistic locking<\/a>, which improves TiDB&#8217;s compatibility with MySQL and reduces transaction rollback rates in high-conflict scenarios.<\/strong> Before TiDB 4.0, pessimistic locking was an experimental feature. Now we&#8217;ve improved its performance, stability, and compatibility with MySQL. Pessimistic locking becomes generally available in TiDB 4.0.<\/p>\n\n\n\n<p>In this post, I&#8217;ll explain what pessimistic locking is, how it behaves, and how it differs from the MySQL version of pessimistic locking.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"What_is_pessimistic_locking\"><\/span>What is pessimistic locking?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>There are two common concurrency control mechanisms in the database field:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong><a href=\"https:\/\/en.wikipedia.org\/wiki\/Optimistic_concurrency_control\">Optimistic concurrency control<\/a> (OCC)<\/strong> allows multiple transactions to modify data without interfering with each other. While a transaction is running, the data that will be edited isn&#8217;t locked. Before a transaction commits, optimistic concurrency control checks whether a conflicting modification exists. If a conflict exists, the committing transaction is rolled back.<\/li>\n\n\n\n<li><strong>Pessimistic concurrency control<\/strong>: when a transaction is modifying data, pessimistic locking applies a lock to the data so other transactions can&#8217;t access the same data. After the transaction commits, the lock is released.<\/li>\n<\/ul>\n\n\n\n<p>Pessimistic concurrency control can solve some of the issues caused by optimistic concurrency control. TiDB now implements both pessimistic and optimistic concurrency control mechanisms, which means:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Transaction commits in TiDB won&#8217;t fail due to locking issues except deadlocks.<\/strong><\/li>\n\n\n\n<li><strong>MySQL users can use TiDB more easily.<\/strong> MySQL supports pessimistic locking by default. Now TiDB also supports pessimistic locking, so MySQL users don&#8217;t need to modify much application code to get started with TiDB.<\/li>\n<\/ul>\n\n\n\n<p>To help you better understand the two locking models, let&#8217;s take online shopping as an analogy.<\/p>\n\n\n\n<p>Assume that there are two websites where you can shop online. To complete an order, you choose an item, click &#8220;Add to Cart&#8221; to add the item to the shopping cart, check out, and place an order. But you have different shopping experiences on the two websites:<\/p>\n\n\n\n<figure class=\"wp-block-table is-style-regular\"><table><tbody><tr><td><strong>Website<\/strong><\/td><td><strong>Add to Cart<\/strong><\/td><td><strong>Place an order<\/strong><\/td><\/tr><tr><td>A<\/td><td>Quick and usually succeeds<\/td><td>When products are out of stock, the order fails<\/td><\/tr><tr><td>B<\/td><td>Slower; if a product is out of stock, the request may fail<\/td><td>Usually succeeds<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>In this case, Website A uses optimistic concurrency control, while Website B uses pessimistic locking.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Optimistic concurrency control in online shopping<\/h3>\n\n\n\n<p>Website A uses optimistic concurrency control. If you try to buy something, you can quickly add items to your shopping cart, but:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>You might fail to place an order.<\/li>\n\n\n\n<li>If other people place an order for the same item before you, the inventory changes. You may encounter a conflict and have to reorder.<\/li>\n\n\n\n<li>In scenarios with severe conflicts and high retry costs, for example, when you want to buy 10,000 items in a single order, you will probably fail to place an order.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Pessimistic locking in online shopping<\/h3>\n\n\n\n<p>Website B uses pessimistic locking. It assumes that other buyers who add the same item before you might also place an order before you. So the inventory you see doesn&#8217;t include items which are already in someone else&#8217;s cart.<\/p>\n\n\n\n<p>If you shop on Website B, you get this kind of experience:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>When you click &#8220;Add to Cart&#8221;, the system responds a little slower.<\/li>\n\n\n\n<li>If you successfully add an item to your shopping cart, you&#8217;ll succeed in placing an order.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"TiDBs_pessimistic_locking_behavior\"><\/span>TiDB&#8217;s pessimistic locking behavior<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>In TiDB, you can enable pessimistic locking in multiple ways. For details, see <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/pessimistic-transaction\">TiDB Pessimistic Transaction Model<\/a>. In this section, I&#8217;ll use three examples to introduce TiDB pessimistic locking&#8217;s behaviors.<\/p>\n\n\n\n<p>Note that in these example:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>We set the session <code>tidb_txn_mode<\/code> = &#8216;pessimistic&#8217;. It will make the following transactions in this session work in the pessimistic mode.<\/li>\n\n\n\n<li>We set the global <code>tidb_txn_mode<\/code> = &#8216;pessimistic&#8217;. You can also set the <code>tidb_txn_mode<\/code> in a global scope. It will affect the following new sessions. All of them will run in the pessimistic mode. We will use this setting for the following sections.<\/li>\n\n\n\n<li>To achieve compatibility with MySQL syntax, we add the comment <code>BEGIN \/ *! 90000 PESSIMISTIC * \/;<\/code>.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Update the same row concurrently<\/h3>\n\n\n\n<p>In the table below, assume that we have set the global <code>tidb_txn_mode<\/code> = &#8216;pessimistic&#8217;, and that Session A and Session B are new sessions. Both sessions use pessimistic locking and update the same row concurrently. From top to bottom, here are Session A&#8217;s and Session B&#8217;s operations in chronological order:<\/p>\n\n\n\n<figure class=\"wp-block-table is-style-stripes\"><table><tbody><tr><td><strong>Session A<\/strong><\/td><td><strong>Session B<\/strong><\/td><\/tr><tr><td>&gt; BEGIN;<\/td><td>&gt; BEGIN;<\/td><\/tr><tr><td>&gt; UPDATE test SET v = v + 1 WHERE k = 1;<\/td><td>&nbsp;<\/td><\/tr><tr><td>&nbsp;<\/td><td>&gt; UPDATE test SET v = v + 1 WHERE k = 1;\n<p>&nbsp;<\/p>\n<p><span style=\"color: red;\">block&#8230;<\/span><\/p>\n<\/td><\/tr><tr><td>&gt; COMMIT;<\/td><td><span style=\"color: red;\">Query OK, 1 row affected (0.00 sec)<\/span><\/td><\/tr><tr><td>&nbsp;<\/td><td>&gt; COMMIT;\n<p>&nbsp;<\/p>\n<p><span style=\"color: red;\">Query OK, 0 row affected (0.00 sec)<\/span><\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>We can see in the pessimistic locking model:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>When Session B tries to execute a data manipulation language (DML) statement, it finds that Session A has locked the same row. After Session A commits its update, Session B executes the DML statement.<\/li>\n\n\n\n<li>When Session B successfully executes the DML statement, the final commit also succeeds.<\/li>\n<\/ul>\n\n\n\n<div style=\"--ub-icon-rotation:rotate(0deg);--ub-icon-size:40px;--ub-icon-justification:center;--ub-icon-border-top:  undefined;--ub-icon-border-right:  undefined;--ub-icon-border-bottom:  undefined;--ub-icon-border-left:  undefined\" class=\"wp-block-ub-icon\" id=\"ub-icon-\"><div class=\"ub_icon\"><div class=\"ub_icon_wrapper\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewbox=\"0 0 24 24\" aria-hidden=\"true\"><path d=\"M6.6 6L5.4 7l4.5 5-4.5 5 1.1 1 5.5-6-5.4-6zm6 0l-1.1 1 4.5 5-4.5 5 1.1 1 5.5-6-5.5-6z\"><\/path><\/svg><\/div><\/div><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">Isolation<\/h3>\n\n\n\n<p>Let&#8217;s look at the following table to see the transaction isolation:<\/p>\n\n\n\n<figure class=\"wp-block-table is-style-stripes\"><table><tbody><tr><td><strong>Session A<\/strong><\/td><td><strong>Session B<\/strong><\/td><\/tr><tr><td>&gt; BEGIN;<\/td><td>&nbsp;<\/td><\/tr><tr><td>&gt; SELECT * FROM test;\n<p>&nbsp;<\/p>\n<p>+&#8212;&#8212;+&#8212;&#8212;+<\/p>\n<p>| k | v |<\/p>\n<p>+&#8212;&#8212;+&#8212;&#8212;+<\/p>\n<p>| 1 | 1 |<\/p>\n<p>+&#8212;&#8212;+&#8212;&#8212;+<\/p>\n<\/td><td>&gt; UPDATE test SET v = v + 1 WHERE k = 1;<\/td><\/tr><tr><td>&gt; SELECT * FROM test;\n<p>&nbsp;<\/p>\n<p>+&#8212;&#8212;+&#8212;&#8212;+<\/p>\n<p>| k | v |<\/p>\n<p>+&#8212;&#8212;+&#8212;&#8212;+<\/p>\n<p>| 1 | 1 |<\/p>\n<p>+&#8212;&#8212;+&#8212;&#8212;+<\/p>\n<\/td><td>&nbsp;<\/td><\/tr><tr><td>&gt; SELECT * FROM test FOR UPDATE; &#8212; get an updated snapshot\n<p>&nbsp;<\/p>\n<p>+&#8212;&#8212;+&#8212;&#8212;+<\/p>\n<p>| k | v |<\/p>\n<p>+&#8212;&#8212;+&#8212;&#8212;+<\/p>\n<p>| 1 | 2 |<\/p>\n<p>+&#8212;&#8212;+&#8212;&#8212;+<\/p>\n<\/td><td>&nbsp;<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>This example shows that the TiDB pessimistic transaction&#8217;s behavior is consistent with MySQL&#8217;s pessimistic transactions:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The isolation level of a non-locking read DML statement is snapshot isolation (SI). When the transaction begins, this statement reads the data.<\/li>\n\n\n\n<li>A normal <code>SELECT<\/code> statement always uses the snapshot at the beginning of the transaction, ensuring repeatable reads.\n<p>As the statement is executed, the <code>SELECT FOR UPDATE<\/code> statement and other DML statements such as <code>INSERT<\/code>, <code>UPDATE<\/code>, <code>DELETE<\/code>, \uadf8\ub9ac\uace0 <code>REPLACE<\/code> get an updated snapshot to read the data and lock it.<\/p>\n<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Deadlock<\/h3>\n\n\n\n<p>Let&#8217;s see what may happen when a deadlock occurs.<\/p>\n\n\n\n<p>Taking shopping online as an example, suppose that both User A and User B want to buy masks and disinfectant. User A has all the disinfectant in his shopping cart, but he doesn&#8217;t have any masks; User B has all the masks in his shopping cart, but he doesn&#8217;t have any disinfectant.<\/p>\n\n\n\n<p>If both Users A and B want to buy masks and disinfectant successfully, they should wait for each other to release some masks or disinfectant. Thus, a deadlock occurs.<\/p>\n\n\n\n<p>Here&#8217;s how a similar case looks in the database:<\/p>\n\n\n\n<figure class=\"wp-block-table is-style-stripes\"><table><tbody><tr><td><strong>Session A<\/strong><\/td><td><strong>Session B<\/strong><\/td><\/tr><tr><td>&gt; BEGIN;<\/td><td>&gt; BEGIN;<\/td><\/tr><tr><td>&gt; UPDATE test SET v = 2 WHERE k = 1;<\/td><td><strong>&gt; <\/strong>UPDATE test SET v = 1 WHERE k = 2;<\/td><\/tr><tr><td>&gt; UPDATE test SET v = 1 WHERE k = 2;<\/td><td>&nbsp;<\/td><\/tr><tr><td>&nbsp;<\/td><td><strong>&gt; UPDATE test SET v = 2 WHERE k = 1;<\/strong>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: red;\">ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction<\/span><\/strong><\/p>\n<\/td><\/tr><tr><td>&gt; COMMIT;<\/td><td>&nbsp;<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>In the example above, Sessions A and B meet a deadlock. In this case, TiDB&#8217;s deadlock manager immediately detects the deadlock and returns an error to the client.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Comparison_with_MySQL_InnoDB\"><\/span>Comparison with MySQL InnoDB<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>As a distributed SQL database, TiDB tries to maintain protocol compatibility with MySQL to benefit the majority of MySQL users. However, TiDB and MySQL differ in implementation. TiDB is not 100% compatible with MySQL in some details. For a complete list of incompatible behaviors between TiDB and MySQL, see <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/pessimistic-transaction#difference-with-mysql-innodb\">Differences with MySQL InnoDB<\/a>.<\/p>\n\n\n\n<p>Here, I&#8217;ll briefly discuss these differences:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>MySQL supports gap locking while TiDB does not.<\/li>\n\n\n\n<li>MySQL and TiDB have different behaviors for embedded <code>SELECT<\/code> statements.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">No gap lock in TiDB<\/h3>\n\n\n\n<p>A <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/innodb-locking.html#innodb-gap-locks\">gap lock<\/a> is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. MySQL supports gap locking while TiDB does not.<\/p>\n\n\n\n<p>When the database can&#8217;t guarantee that the data that matches the filter condition is unique:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>MySQL locks all the rows that the filter condition can cover, including rows that may not exist. It uses range locking or table locking.<\/li>\n\n\n\n<li>TiDB only locks the existing rows it reads.<\/li>\n<\/ul>\n\n\n\n<p>The following table shows a specific comparison. Note that <code>id<\/code> is the primary key.<\/p>\n\n\n\n<figure class=\"wp-block-table is-style-stripes\"><table><tbody><tr><td><strong>Session A<\/strong><\/td><td><strong>Session B (MySQL)<\/strong><\/td><td><strong>Session B (TiDB)<\/strong><\/td><\/tr><tr><td>mysql&gt; BEGIN;\n<p>&nbsp;<\/p>\n<p>Query OK, 0 rows affected (0.00 sec)<\/p>\n<p>mysql&gt; SELECT * FROM t WHERE id&gt;=10 AND id&amp;lt;11 FOR UPDATE;<\/p>\n<p><span style=\"color: red;\">Empty set (0.00 sec)<\/span><\/p>\n<\/td><td>&nbsp;<\/td><td>&nbsp;<\/td><\/tr><tr><td>&nbsp;<\/td><td>&gt;BEGIN;\n<p>&nbsp;<\/p>\n<p>Query OK, 0 rows affected (0.00 sec)<\/p>\n<p>&gt; SELECT * FROM t WHERE id&gt;=10 AND id&amp;lt;11 FOR UPDATE;<\/p>\n<p><span style=\"color: red;\">\/\/ block<\/span><\/p>\n<\/td><td>&gt; BEGIN;\n<p>&nbsp;<\/p>\n<p>Query OK, 0 rows affected (0.00 sec)<\/p>\n<p>&gt; SELECT * FROM t WHERE id&gt;=10 AND id&amp;lt;11 FOR UPDATE;<\/p>\n<p><span style=\"color: red;\">Empty set (0.00 sec)<\/span><\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Different embedded <code>SELECT<\/code> behaviors<\/h3>\n\n\n\n<p>When TiDB executes a DML statement that includes an embedded <code>SELECT<\/code>, TiDB does not lock the data in an embedded <code>SELECT<\/code>. By contrast, MySQL does.<\/p>\n\n\n\n<figure class=\"wp-block-table is-style-stripes\"><table><tbody><tr><td>CREATE TABLE t1 (a INT, b INT DEFAULT 0, PRIMARY KEY (a,b));\n<p>&nbsp;<\/p>\n<p>INSERT INTO t1 (a,b) VALUES (1070109, 99);<\/p>\n<p>CREATE TABLE t2 (b INT, a INT, PRIMARY KEY (b));<\/p>\n<p>INSERT INTO t2 (b,a) VALUES (7,1070109);<\/p>\n<\/td><\/tr><tr><td><strong>Session A<\/strong><\/td><td><strong>Session B<\/strong><\/td><td><strong>MySQL vs. TiDB <\/strong><\/td><\/tr><tr><td>&gt; BEGIN;<\/td><td>SET innodb_lock_wait_timeout = 1;<\/td><td>&nbsp;<\/td><\/tr><tr><td>&gt; SELECT b FROM t2 WHERE b=7 FOR UPDATE;<\/td><td>BEGIN;<\/td><td>&nbsp;<\/td><\/tr><tr><td>&nbsp;<\/td><td>SELECT b FROM t2 WHERE b=7 FOR UPDATE;<\/td><td>Both MySQL and TiDB fail with an error `lock wait timeout`.<\/td><\/tr><tr><td>&nbsp;<\/td><td>INSERT INTO t1 (a) VALUES ((SELECT a FROM t2 WHERE b=7));<\/td><td>TiDB does not lock the data in `(SELECT a FROM t2 WHERE b=7)`, so it succeeds.\n<p>&nbsp;<\/p>\n<p>MySQL tries to lock the data in `(SELECT a FROM t2 WHERE b=7)`, so it fails with an error `lock wait timeout`.<\/p>\n<\/td><\/tr><tr><td>&nbsp;<\/td><td>UPDATE t1 SET a=&#8217;7000000&#8242; WHERE a=(SELECT a FROM t2 WHERE b=7);<\/td><td>TiDB succeeds, while MySQL fails with an error `lock wait timeout`.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Looking_ahead\"><\/span>Looking ahead<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Since it began, TiDB has been known for supporting high-performance distributed transactions. With improvements in stability and functionality in TiDB 4.0, we finally remove the experimental label for pessimistic locking, making it a generally available feature. In our future posts, we&#8217;ll deep dive into TiDB pessimistic locking&#8217;s implementation principles and performance tuning. Stay tuned.<\/p>\n\n\n\n<p>TiDB&#8217;s transaction model continues to improve. If you&#8217;re interested, you can help build this cutting-edge distributed transaction model along with us. You&#8217;re welcome to try our pessimistic locking in the <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/release-4.0.0-rc.2\">TiDB 4.0 release candidate<\/a> and join <a href=\"https:\/\/slack.tidb.io\/invite?team=tikv-wg&amp;channel=transaction-sig&amp;ref=pingcap-blog\">our Transaction Special Interest Group (SIG) on Slack<\/a>, or contact <a href=\"mailto:transaction-group@pingcap.com\">transaction-group@pingcap.com<\/a> to give us your feedback.<\/p>","protected":false},"excerpt":{"rendered":"<p>With improvements in stability and functionality in TiDB 4.0, we finally remove the experimental label for pessimistic locking, making it a generally available feature. See how pessimistic locking behaves in TiDB.<\/p>","protected":false},"author":100,"featured_media":883,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ub_ctt_via":"","footnotes":""},"categories":[13],"tags":[34],"class_list":["post-881","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-product","tag-transaction"],"acf":[],"featured_image_src":"https:\/\/static.pingcap.com\/files\/2020\/05\/pessimistic-locking.jpg","author_info":{"display_name":"Transaction team","author_link":"https:\/\/www.pingcap.com\/ko\/blog\/author\/transaction-team\/"},"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.9 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Pessimistic Locking: Better MySQL Compatibility, Fewer Rollbacks<\/title>\n<meta name=\"description\" content=\"In this post, we will explain what pessimistic locking is, how it behaves, and how it differs from the MySQL version of pessimistic locking.\" \/>\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.pingcap.com\/ko\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/\" \/>\n<meta property=\"og:locale\" content=\"ko_KR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Pessimistic Locking: Better MySQL Compatibility, Fewer Rollbacks\" \/>\n<meta property=\"og:description\" content=\"In this post, we will explain what pessimistic locking is, how it behaves, and how it differs from the MySQL version of pessimistic locking.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pingcap.com\/ko\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/\" \/>\n<meta property=\"og:site_name\" content=\"TiDB\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/facebook.com\/pingcap2015\" \/>\n<meta property=\"article:published_time\" content=\"2020-05-22T00:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-08-21T13:53:26+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/static.pingcap.com\/files\/2020\/05\/pessimistic-locking.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1600\" \/>\n\t<meta property=\"og:image:height\" content=\"534\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Transaction team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@PingCAP\" \/>\n<meta name=\"twitter:site\" content=\"@PingCAP\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Transaction team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"9\ubd84\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/\"},\"author\":{\"name\":\"Transaction team\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/person\/9342f16b4f4020674cbed312130dce8c\"},\"headline\":\"Pessimistic Locking: Better MySQL Compatibility, Fewer Rollbacks Under High Load\",\"datePublished\":\"2020-05-22T00:00:00+00:00\",\"dateModified\":\"2024-08-21T13:53:26+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/\"},\"wordCount\":1809,\"publisher\":{\"@id\":\"https:\/\/www.pingcap.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/static.pingcap.com\/files\/2020\/05\/pessimistic-locking.jpg\",\"keywords\":[\"Transaction\"],\"articleSection\":[\"Product\"],\"inLanguage\":\"ko-KR\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/\",\"url\":\"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/\",\"name\":\"Pessimistic Locking: Better MySQL Compatibility, Fewer Rollbacks\",\"isPartOf\":{\"@id\":\"https:\/\/www.pingcap.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/static.pingcap.com\/files\/2020\/05\/pessimistic-locking.jpg\",\"datePublished\":\"2020-05-22T00:00:00+00:00\",\"dateModified\":\"2024-08-21T13:53:26+00:00\",\"description\":\"In this post, we will explain what pessimistic locking is, how it behaves, and how it differs from the MySQL version of pessimistic locking.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/#breadcrumb\"},\"inLanguage\":\"ko-KR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"ko-KR\",\"@id\":\"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/#primaryimage\",\"url\":\"https:\/\/static.pingcap.com\/files\/2020\/05\/pessimistic-locking.jpg\",\"contentUrl\":\"https:\/\/static.pingcap.com\/files\/2020\/05\/pessimistic-locking.jpg\",\"width\":1600,\"height\":534,\"caption\":\"Pessimistic locking in database\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.pingcap.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Pessimistic Locking: Better MySQL Compatibility, Fewer Rollbacks Under High Load\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.pingcap.com\/#website\",\"url\":\"https:\/\/www.pingcap.com\/\",\"name\":\"TiDB\",\"description\":\"TiDB | SQL at Scale\",\"publisher\":{\"@id\":\"https:\/\/www.pingcap.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.pingcap.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"ko-KR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.pingcap.com\/#organization\",\"name\":\"PingCAP\",\"url\":\"https:\/\/www.pingcap.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"ko-KR\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"contentUrl\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"width\":811,\"height\":232,\"caption\":\"PingCAP\"},\"image\":{\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/facebook.com\/pingcap2015\",\"https:\/\/x.com\/PingCAP\",\"https:\/\/linkedin.com\/company\/pingcap\",\"https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/person\/9342f16b4f4020674cbed312130dce8c\",\"name\":\"Transaction team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"ko-KR\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/static.pingcap.com\/files\/2022\/10\/17234942\/avatar.jpg\",\"contentUrl\":\"https:\/\/static.pingcap.com\/files\/2022\/10\/17234942\/avatar.jpg\",\"caption\":\"Transaction team\"},\"url\":\"https:\/\/www.pingcap.com\/ko\/blog\/author\/transaction-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Pessimistic Locking: Better MySQL Compatibility, Fewer Rollbacks","description":"In this post, we will explain what pessimistic locking is, how it behaves, and how it differs from the MySQL version of pessimistic locking.","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.pingcap.com\/ko\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/","og_locale":"ko_KR","og_type":"article","og_title":"Pessimistic Locking: Better MySQL Compatibility, Fewer Rollbacks","og_description":"In this post, we will explain what pessimistic locking is, how it behaves, and how it differs from the MySQL version of pessimistic locking.","og_url":"https:\/\/www.pingcap.com\/ko\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/","og_site_name":"TiDB","article_publisher":"https:\/\/facebook.com\/pingcap2015","article_published_time":"2020-05-22T00:00:00+00:00","article_modified_time":"2024-08-21T13:53:26+00:00","og_image":[{"width":1600,"height":534,"url":"https:\/\/static.pingcap.com\/files\/2020\/05\/pessimistic-locking.jpg","type":"image\/jpeg"}],"author":"Transaction team","twitter_card":"summary_large_image","twitter_creator":"@PingCAP","twitter_site":"@PingCAP","twitter_misc":{"Written by":"Transaction team","Est. reading time":"9\ubd84"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/#article","isPartOf":{"@id":"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/"},"author":{"name":"Transaction team","@id":"https:\/\/www.pingcap.com\/#\/schema\/person\/9342f16b4f4020674cbed312130dce8c"},"headline":"Pessimistic Locking: Better MySQL Compatibility, Fewer Rollbacks Under High Load","datePublished":"2020-05-22T00:00:00+00:00","dateModified":"2024-08-21T13:53:26+00:00","mainEntityOfPage":{"@id":"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/"},"wordCount":1809,"publisher":{"@id":"https:\/\/www.pingcap.com\/#organization"},"image":{"@id":"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/#primaryimage"},"thumbnailUrl":"https:\/\/static.pingcap.com\/files\/2020\/05\/pessimistic-locking.jpg","keywords":["Transaction"],"articleSection":["Product"],"inLanguage":"ko-KR"},{"@type":"WebPage","@id":"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/","url":"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/","name":"Pessimistic Locking: Better MySQL Compatibility, Fewer Rollbacks","isPartOf":{"@id":"https:\/\/www.pingcap.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/#primaryimage"},"image":{"@id":"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/#primaryimage"},"thumbnailUrl":"https:\/\/static.pingcap.com\/files\/2020\/05\/pessimistic-locking.jpg","datePublished":"2020-05-22T00:00:00+00:00","dateModified":"2024-08-21T13:53:26+00:00","description":"In this post, we will explain what pessimistic locking is, how it behaves, and how it differs from the MySQL version of pessimistic locking.","breadcrumb":{"@id":"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/#breadcrumb"},"inLanguage":"ko-KR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/"]}]},{"@type":"ImageObject","inLanguage":"ko-KR","@id":"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/#primaryimage","url":"https:\/\/static.pingcap.com\/files\/2020\/05\/pessimistic-locking.jpg","contentUrl":"https:\/\/static.pingcap.com\/files\/2020\/05\/pessimistic-locking.jpg","width":1600,"height":534,"caption":"Pessimistic locking in database"},{"@type":"BreadcrumbList","@id":"https:\/\/www.pingcap.com\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pingcap.com\/"},{"@type":"ListItem","position":2,"name":"Pessimistic Locking: Better MySQL Compatibility, Fewer Rollbacks Under High Load"}]},{"@type":"WebSite","@id":"https:\/\/www.pingcap.com\/#website","url":"https:\/\/www.pingcap.com\/","name":"\ud2f0DB","description":"TiDB | SQL at Scale","publisher":{"@id":"https:\/\/www.pingcap.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pingcap.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"ko-KR"},{"@type":"Organization","@id":"https:\/\/www.pingcap.com\/#organization","name":"PingCAP","url":"https:\/\/www.pingcap.com\/","logo":{"@type":"ImageObject","inLanguage":"ko-KR","@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/","url":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","contentUrl":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","width":811,"height":232,"caption":"PingCAP"},"image":{"@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/facebook.com\/pingcap2015","https:\/\/x.com\/PingCAP","https:\/\/linkedin.com\/company\/pingcap","https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA"]},{"@type":"Person","@id":"https:\/\/www.pingcap.com\/#\/schema\/person\/9342f16b4f4020674cbed312130dce8c","name":"Transaction team","image":{"@type":"ImageObject","inLanguage":"ko-KR","@id":"https:\/\/www.pingcap.com\/#\/schema\/person\/image\/","url":"https:\/\/static.pingcap.com\/files\/2022\/10\/17234942\/avatar.jpg","contentUrl":"https:\/\/static.pingcap.com\/files\/2022\/10\/17234942\/avatar.jpg","caption":"Transaction team"},"url":"https:\/\/www.pingcap.com\/ko\/blog\/author\/transaction-team\/"}]}},"grav_blocks":false,"card_markup":"<a class=\"card-resource bg-white\" href=\"https:\/\/www.pingcap.com\/ko\/blog\/pessimistic-locking-better-mysql-compatibility-fewer-rollbacks-under-high-load\/\"><div class=\"card-resource__image-container\"><img class=\"card-resource__image\" alt=\"Pessimistic locking in database\" src=\"https:\/\/static.pingcap.com\/files\/2020\/05\/pessimistic-locking.jpg\" loading=\"lazy\" width=1600 height=534 \/><\/div><div class=\"card-resource__content-container\"><div class=\"card-resource__content-head\"><div class=\"card-resource__category\">Product<\/div><\/div><h5 class=\"card-resource__title\">Pessimistic Locking: Better MySQL Compatibility, Fewer Rollbacks Under High Load<\/h5><\/div><\/a>","_links":{"self":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/posts\/881","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/users\/100"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/comments?post=881"}],"version-history":[{"count":7,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/posts\/881\/revisions"}],"predecessor-version":[{"id":18916,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/posts\/881\/revisions\/18916"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/media\/883"}],"wp:attachment":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/media?parent=881"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/categories?post=881"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/tags?post=881"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}