473,785 Members | 2,283 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

About InnoDB locking mechanism

4 New Member
Hello everyone,
I am a bit confused about Innodb deadlock and the "SELECT…FOR UPDATE” statement.
1. Here is a deadlock example
1) Create actor table
Expand|Select|Wrap|Line Numbers
  1.  
  2.     CREATE TABLE actor(actor_id INT NOT NULL,
  3.                        first_name VARCHAR(10),
  4.                        last_name VARCHAR(10),
  5.                        PRIMARY KEY(actor_id)
  6.                        )ENGINE=INNODB;
2) Add one row in the actor table
Expand|Select|Wrap|Line Numbers
  1.  
  2.     INSERT INTO actor VALUES(1,’Penelope’,’Guiness’);
3) Open two sessions and do as following:
Expand|Select|Wrap|Line Numbers
  1.  
  2.           session 1                            session 2
  3.      -------------------------------------------------------------------------
  4.       SET autocommit=0;             |        SET autocommit=0;
  5.      -------------------------------------------------------------------------
  6.       INSERT INTO actor VALUES      |
  7.           (1,'Peter','Pan');        |
  8.       ERROR 1582(23000):Duplicate   |
  9.       entry '1' for key ‘PRIMARY’   |
  10.      -------------------------------------------------------------------------
  11.                                     |    SELECT * FROM actor
  12.                                     |    WHERE actor_id=1 FOR UPDATE;
  13.                                     |    (Waiting for lock...)
  14.       ------------------------------------------------------------------------
  15.       UPDATE actor SET last_name    |
  16.        ='White' WHERE actor_id=1;   |
  17.       ERROR 1213(40001):Deadlock    |
  18.       Found when trying to get lock;|
  19.       try restarting transaction    |
  20.      -------------------------------------------------------------------------
My question is why session 1 detects deadlock at the last operation? Although the INSERT statement generates a duplicate primary key error, session 1 will still hold a share lock on the record, so that session 2 will wait for the share lock unlocking when it issues the “SELECT…FOR UPDATE” statement. For now, only session 1 holds a share lock, so I think it should update the record without any deadlock appearing. But MySQL really detects a deadlock at this point, why?

2. Does the “SELECT…FOR UPDATE” statement get different locks in different transaction isolation levels? I use the actor table(created previously), open two sessions like this,


Expand|Select|Wrap|Line Numbers
  1.   session 1                                 session2
  2. -----------------------------------------------------------------------------
  3. SET session transaction isolation level  | SET session transaction isolation level
  4.      READ COMMITTED;                     |       READ COMMITTED;
  5. -----------------------------------------------------------------------------
  6.   SET autocommit=0;                      |  SET autocommit=0;
  7. -------------------------------------------------------------------------------
  8.   SELECT * FROM actor WHERE              | SELECT * FROM actor WHERE
  9.      actor_id=100 for update;            |    actor_id=100 for update;
  10.   Empty set(0.00sec)                     |  Empty set(0.00sec)
  11. -----------------------------------------------------------------------------
  12.    INSERT INTO actor VALUES              |
  13.    (100,'Tom','Smith');                  |
  14.   Query ok, 1 row affected(0.00sec)      |
  15. -----------------------------------------------------------------------------
Two sessions both lock on a non-existed record in READ COMMITTED isolation level, it seems that the “SELECT…FOR UPDATE” statement in session 2 do nothing in this case because session 1 can still add the record successfully.
But if I change the transaction isolation level from READ COMMITTED to REPEATABLE READ, the INSERT statement in session 1 will wait session 2 to release the lock. It appears that the “SELECT…FOR UPDATE” statement in session 2 holds a share lock on this record.
Does the “SELECT…FOR UPDATE” statement behave different in different isolation levels?

3. In which circumstance should we use the “SELECT…FOR UPDATE” or “SELECT…LOCK IN SHARE MODE” statement? Why we can not directly use the “INSERT” or “UPDATE” statement instead?

4. The deadlock seems inevitable; can I use the following statements to deal with it?


Expand|Select|Wrap|Line Numbers
  1. DECLARE CONTINUE|EXIT HANDLER FOR SQLSTATE ‘40001’ 
  2.        BEGIN
  3.         handling deadlock statements
  4.       END
Thanks in advance!
Oct 9 '08 #1
0 2703

Sign in to post your reply or Sign up for a free account.

Similar topics

1
3641
by: Marcus | last post by:
Hello, I have 2 questions regarding InnoDB tables: 1. In the MySQL manual, it states that "MySQL begins each client connection with autocommit mode enabled by default. When autocommit is enabled, MySQL does a commit after each SQL statement if that statement did not return an error." Does using BEGIN override this behavior? In other words, with
0
3236
by: Steve McWilliams | last post by:
Hello, I am relatively new to MySql (4.0.14) but I have read through the relevent documentation and am still confused about how row level locking behaves with InnoDB tables. I created a database with a single innodb table which has 2 columns, one of which is indexed. The locking behavior I see when I test against this database is that it uses row level locks if the "SELECT ... FOR UPDATE" involves the indexed column, but uses table...
3
4299
by: Martin Gill | last post by:
I am a novice user of MySQL so please excuse my ignorance. I recently installed MySQL and have a third party tool using it to update data in a database. Recently it started creating erorr messages: table.innoDB missing Looking at the database in phpMyAdmin the table affected is marked as
1
2293
by: Andy | last post by:
I'm using Mysql Max-4.1.4-gamma for Linux. I have a question related to a problem with my database server. If on my db server I have 2 different database, say DB1 & DB2, I create two INNODB tables with the same name (foo), one for each database, how they are stored in the innodb data dictionary ? DB1.foo & DB2.foo or simply foo ? Thank you in advance Andrea
2
2396
by: Morten | last post by:
Hi. Creating the below to tables give errno 150, any idea why? CREATE TABLE categories ( id int(11) unsigned NOT NULL auto_increment, name varchar(64) NOT NULL, PRIMARY KEY (id), UNIQUE KEY name (name) ) TYPE=InnoDB; CREATE TABLE types (
5
12908
by: Eddie | last post by:
I have a MySQL-server running Innodb. We have installed ~ 2GB of memory in the server. In spite of this MySQL keeps crashing due to out-of-memory errors. The server is a dual xeon i686 running kernel Linux 2.4.20-8smp redhat stock. Top shows mysql steadily consuming more resourses over a period of 2 hours, up to 1.2 Gb, before it crashes. Any clues?
3
2723
by: siliconmike | last post by:
We have developed a site where users can post blogs. Now, each blog is stored in a separate row, in a table called blog_table. This single table would be the most active one and would grow to large size in the coming years. What is the most advisable engine for this table - Innodb or MyISAM ?
0
2306
by: shakahshakah | last post by:
Just started investigating InnoDB after having used MyISAM tables in the 4.0.12 version, almost immediately ran into a locking issue with INSERTs, DELETEs, and transactions. Given the following table: CREATE TABLE test1 ( f1 varchar(32) default NULL ) and a steady stream of INSERTs from a command-line shell:
0
1323
by: xpding | last post by:
Hello, I have a class MyEmbededList contains a generic dictionary, the value field is actually the MyEmbededList type as well. There is another class need to access and manipulate a list of MyEmbededList (please refer to the MyTestClass below). I am not sure whether I implements the right locking mechanism here and hope someone can give me some advices. I have provided some codes for these two classes below. My questions are: 1. Am I...
0
9480
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10083
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8968
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7494
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6737
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5379
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5511
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
3645
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2877
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.