| Bug #26134 | DEADLOCK while inserting data on (AUTO_INCREMENT) | ||
|---|---|---|---|
| Submitted: | 7 Feb 2007 5:51 | Modified: | 15 Mar 2007 17:22 |
| Reporter: | Prakash Sinha | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
| Version: | 5.X | OS: | MacOS (MAC 10.X) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | deadlock, innodb, lock | ||
[7 Feb 2007 10:58]
Valeriy Kravchuk
Thank you for a problem report. Please, send the results of SHOW CREATE TABLE for both tables involved. Send also some test data for a table you are selecting from. Please, specify the exact version of MySQL server used, 5.x.y. Do you have any triggers defined on any of these tables?
[7 Feb 2007 12:09]
Heikki Tuuri
Prakash, has transaction (2) in table recipe_ingredient done an INSERT ... SELECT recipe_ingredient? That would set S-locks on records in recipe_ingredient. Transaction (1) wants to insert to recipe_ingredient, but it cannot because there is an S-lock on the page supremum. Regards, Heikki
[7 Feb 2007 15:36]
Prakash Sinha
Output of SHOW CREATE TABLE:
CREATE TABLE `recipe_ingredient` (
`recipe_ingredient_id` int(11) NOT NULL auto_increment,
`recipe_ingredient_order` tinyint(4) default NULL,
`recipe_ingredient_qty` varchar(5) default NULL,
`recipe_ingredient_unit` varchar(75) default NULL,
`recipe_ingredient_name` varchar(255) NOT NULL,
PRIMARY KEY (`recipe_ingredient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
0) Stored Procedure accepts some delimited string "1|1|large|cucumber::2|1|pinch|salt". | is column delimiter and :: is a row delimiter.
1) Code in Stored Procedure Splits this data and puts temporarily into the memory table SplitValue.
2) Split Value data is then inserted into recipe_ingredient as you might have seen in the Error.
********Chunk of code********
CALL recipe_split_string(recipe_ingredients,"::","|",
"recipe_ingredient");
INSERT INTO recipe_ingredient (
recipe_ingredient_order,
recipe_ingredient_qty,
recipe_ingredient_unit,
recipe_ingredient_name)
SELECT VALUE_1,VALUE_2,VALUE_3,VALUE_4 FROM SplitValues;
-- Get the max and min of ID which can be used later to update lookup
SELECT MAX(SplitValues.id), MIN(SplitValues.id)
INTO @MAX_ING_ID, @MIN_ING_ID
FROM SplitValues;
-- Insert data into the ingredient_lookup
INSERT INTO recipe_ingredient_lookup (recipe_ingredient_id)
SELECT ID FROM SplitValues;
-- SELECT recipe_ingredient_lookup.recipe_id INTO status_code
-- FROM recipe_ingredient_lookup FOR UPDATE;
UPDATE recipe_ingredient_lookup
SET recipe_ingredient_lookup.recipe_id = recipe_id
WHERE recipe_ingredient_lookup.recipe_ingredient_id
BETWEEN @MIN_ING_ID AND @MAX_ING_ID;
3) It is transaction based on commit (auto commit is false), so what I am missing is how the second transaction gets fired unless the first was released (commit or rollback)
4) Now I have 5 tables that I am inserting data based on the same way and this is the only one that is showing problem.
I really really appreciated your response, could not believe it was so fast (still reeling :-))
Thanx,
P
[8 Feb 2007 13:30]
Heikki Tuuri
Prakash, do you have FOREIGN KEY constraints referencing recipe_ingredient? Foreign key checks do set S-locks. Please use innodb_lock_monitor to find out what statement sets the S-lock on a record in recipe_ingredient. http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html Regards, Heikki
[14 Feb 2007 23:43]
Prakash Sinha
Yes I do have a foreign key constraint on recipe_ingredient_lookup. I am doing update to recipe_ingredient_lookup which has a recipe_id as foreign key.
Let me ask your suggestion, is there any optimal way I can do Update by using some clause.
Excerpt:
INSERT INTO recipe_ingredient_lookup (recipe_ingredient_id)
SELECT ID FROM SplitValues;
UPDATE recipe_ingredient_lookup
SET recipe_ingredient_lookup.recipe_id = recipe_id
WHERE recipe_ingredient_lookup.recipe_ingredient_id
BETWEEN @MIN_ING_ID AND @MAX_ING_ID;
MySQL thread id 6, query id 16268910 localhost root updatingDELETE FROM recipe_master WHERE recipe_master.recipe_id = recipe_idForeign key constraint fails for table `recipe/recipe_ingredient_lookup`:, CONSTRAINT `Refrecipe_master112` FOREIGN KEY (`recipe_id`) REFERENCES `recipe_master` (`recipe_id`)Trying to delete or update in parent table, in index `PRIMARY` tuple:DATA TUPLE: 13 fields; 0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000015013a; asc :;; 2: len 7; hex 00000003bd183b; asc ;;; 3: len 1; hex 9e; asc ;; 4: len 147; hex 54686520646966666572656e6365206265747765656e20746865736520626565662d616e642d736175736167652d73747566666564207065707065727320616e64207468652062656c6c20766172696574792069732073697a652d2d636865727279207065707065727320617265207065726665637420666f7220706f7070696e6720617320616e206170706574697a65722e; asc The difference between these beef-and-sausage-stuffed peppers and the bell variety is size--cherry peppers are perfect for popping as an appetizer.;; 5: len 22; hex 53747566666564204368657272792050657070657273; asc Stuffed Cherry Peppers;; 6: len 4; hex 800039fa; asc 9 ;; 7: len 1; hex 8f; asc ;; 8: len 1; hex 99; asc ;; 9: len 1; hex ad; asc ;; 10: len 1; hex 81; asc ;; 11: SQL NULL; 12: len 6; hex 706965636573; asc pieces;;
[15 Feb 2007 17:22]
Heikki Tuuri
Prakash, if it is a foreign key check that references recipe_ingredient and sets the S-lock on the page 'supremum' record, then that would suggest that the foreign key check actually fails. If it would succeed, then the S-lock should be on an existing record. Do you have foreign key checks that fail because there is no parent row? Regards, Heikki
[16 Mar 2007 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: I am running into a deadlock situation when inserting data on a table which has auto_increment on a column. I have attached the "Status Of InnoDB" and in that status report the 1st transaction tries to set X lock and the second transaction tries to set a S lock. What is baffling to me is How can Insert set the S lock? Mind you this happens only when I run the insert in a loop thus hammering MYSQL with connections!!!! TABLE STRUCTURE************** recipe_ingredient_id | int(11) | NO | PRI | NULL | auto_increment | recipe_ingredient_order | tinyint(4) | YES | | NULL | | recipe_ingredient_qty | varchar(5) | YES | | NULL | | recipe_ingredient_unit | varchar(75) | YES | | NULL | | recipe_ingredient_name | varchar(255) | NO | | | | INNODB STATUS**************** ------------------------ LATEST DETECTED DEADLOCK ------------------------ 070207 0:22:22 *** (1) TRANSACTION: TRANSACTION 0 246231, ACTIVE 0 sec, OS thread id 101273088 inserting mysql tables in use 1, locked 1 LOCK WAIT 7 lock struct(s), heap size 1024, undo log entries 1 MySQL thread id 3933, query id 14348990 localhost root Sending data INSERT INTO recipe_ingredient ( recipe_ingredient_order, recipe_ingredient_qty, recipe_ingredient_unit, recipe_ingredient_name) SELECT VALUE_1,VALUE_2,VALUE_3,VALUE_4 FROM SplitValues *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 2821 n bits 112 index `PRIMARY` of table `recipe/recipe_ingredient` trx id 0 246231 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) TRANSACTION: TRANSACTION 0 246234, ACTIVE 0 sec, OS thread id 63676928 setting auto-inc lock mysql tables in use 1, locked 1 4 lock struct(s), heap size 320, undo log entries 1 MySQL thread id 3934, query id 14349151 localhost root Sending data INSERT INTO recipe_ingredient ( recipe_ingredient_order, recipe_ingredient_qty, recipe_ingredient_unit, recipe_ingredient_name) SELECT VALUE_1,VALUE_2,VALUE_3,VALUE_4 FROM SplitValues *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 2821 n bits 112 index `PRIMARY` of table `recipe/recipe_ingredient` trx id 0 246234 lock mode S Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Any help is highly appreciated! How to repeat: Just trying inserting multiple rows and it fails.