1 Replies - 475 Views - Last Post: 07 April 2017 - 05:45 AM

#1 [email protected]  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 26-March 12

MySQL Deadlock on Insert

Posted 06 April 2017 - 08:34 PM

I am using a MySQL table InnoDB. Here is the Create statement:
CREATE TABLE `soil` (
  `LabNo` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `Year` year(4) NOT NULL,
  `SoilRefNo` int(9) unsigned NOT NULL,
  `Billing` char(6) DEFAULT NULL,
  `GrowerName` varchar(30) DEFAULT NULL,
  `GrowerAddress1` varchar(30) DEFAULT NULL,
  `GrowerAddress2` varchar(30) DEFAULT NULL,
  `GrowerCity` varchar(25) DEFAULT NULL,
  `GrowerState` char(2) DEFAULT NULL,
  `GrowerZip` varchar(10) DEFAULT NULL,
  `SubmitterName` varchar(30) DEFAULT NULL,
  `SubmitterAddress1` varchar(30) DEFAULT NULL,
  `SubmitterAddress2` varchar(30) DEFAULT NULL,
  `SubmitterCity` varchar(25) DEFAULT NULL,
  `SubmitterState` char(2) DEFAULT NULL,
  `SubmitterZip` varchar(10) DEFAULT NULL,
  `Contact` varchar(30) DEFAULT NULL,
  `DateReceived` date DEFAULT NULL,
  `DateSampled` date DEFAULT NULL,
  `FieldID_Protocol` varchar(30) DEFAULT NULL,
  `County_FieldTestNo` varchar(15) DEFAULT NULL,
  `SampleID_NonGLPDepth` varchar(30) DEFAULT NULL,
  `Township_PO` varchar(30) DEFAULT NULL,
  `Acres` decimal(5,1) unsigned DEFAULT NULL,
  `Section` smallint(4) unsigned DEFAULT NULL,
  `PreviousCrop` tinyint(3) unsigned DEFAULT NULL,
  `Quarter` varchar(15) DEFAULT NULL,
  `DepthString` varchar(8) DEFAULT NULL,
  `PlantRefNo` varchar(12) DEFAULT NULL,
  `StartDepth1` tinyint(2) unsigned DEFAULT NULL,
  `Depth1` tinyint(2) unsigned DEFAULT NULL,
  `startdepth2` tinyint(2) unsigned DEFAULT NULL,
  `Depth2` tinyint(2) unsigned DEFAULT NULL,
  `Depth3` tinyint(2) unsigned DEFAULT NULL,
  `Depth4` tinyint(2) unsigned DEFAULT NULL,
  `ElectronicID` varchar(40) DEFAULT NULL,
  `Route` char(1) DEFAULT NULL,
  `DriedGround` char(1) DEFAULT NULL,
  `LongTermStorage` char(1) DEFAULT NULL,
  `Manured` char(1) DEFAULT NULL,
  `Bulk` char(1) DEFAULT NULL,
  `ForeignSoil` char(1) DEFAULT NULL,
  `Certified` char(1) DEFAULT NULL,
  `Project` char(1) DEFAULT NULL,
  `EntererCode` smallint(3) unsigned DEFAULT NULL,
  `ProoferCode` smallint(3) unsigned DEFAULT NULL,
  `Crop1` tinyint(3) unsigned DEFAULT NULL,
  `Crop2` tinyint(3) unsigned DEFAULT NULL,
  `Crop3` tinyint(3) unsigned DEFAULT NULL,
  `Yield1` smallint(5) unsigned DEFAULT NULL,
  `Yield2` smallint(5) unsigned DEFAULT NULL,
  `Yield3` smallint(5) unsigned DEFAULT NULL,
  `Guides1` tinyint(3) unsigned DEFAULT NULL,
  `Guides2` tinyint(3) unsigned DEFAULT NULL,
  `Guides3` tinyint(3) unsigned DEFAULT NULL,
  `TestOptionNumber` tinyint(3) unsigned DEFAULT NULL,
  `TestOptionString` varchar(200) DEFAULT NULL,
  `DealerDefaultOptionString` varchar(200) DEFAULT NULL,
  `SampleType` tinyint(1) unsigned DEFAULT NULL,
  `OptionType` tinyint(1) unsigned DEFAULT NULL,
  `GridNumber` mediumint(8) unsigned DEFAULT NULL,
  `TotalGridPoints` tinyint(3) unsigned DEFAULT NULL,
  `BoxNumber` smallint(6) unsigned DEFAULT NULL,
  `MiscTests` varchar(25) DEFAULT NULL,
  `Specimen` varchar(25) DEFAULT NULL,
  `FieldName` varchar(40) DEFAULT NULL,
  `UniqueID` varchar(40) DEFAULT NULL,
  `RangeSoil` varchar(30) DEFAULT NULL,
  `Sampler` varchar(25) DEFAULT NULL,
  `RecordTimeStamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`LabNo`,`Year`,`SoilRefNo`),
  KEY `plantrefNo` (`PlantRefNo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


LabNo needs to be unique for each year, so I put a before insert trigger on the table:
CREATE DEFINER=`root`@`%` TRIGGER `agvise`.`soil_BEFORE_INSERT` BEFORE INSERT ON `soil` FOR EACH ROW
BEGIN
   SET NEW.labno=(SELECT IFNULL(MAX(labno),0)+1 FROM soil WHERE year=new.YEAR); 
END


There will be approximately a dozen users entering records into this table at the same time.
Also a program runs that inserts many records one right after the other but only part of the record which is then
updated by the users later.
There have been deadlocks happening.
Here is part of the Show Engine Innodb Status:
LATEST DETECTED DEADLOCK
------------------------
2017-04-06 08:51:04 0xe64
*** (1) TRANSACTION:
TRANSACTION 5955991, ACTIVE 1 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 474 lock struct(s), heap size 106704, 514871 row lock(s), undo log entries 1
MySQL thread id 1347, OS thread handle 2080, query id 340606 avb-007.agvise.com 192.168.168.147 root
Insert into SOIL (Year, SoilRefNo, DealerDefaultOptionString) values('2017', 1858249, '............................................Y...Y.Y...........Y...Y..........................................Y...Y....................................')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 144 page no 20274 n bits 1192 index plantrefNo of table `agvise`.`soil` trx id 5955991 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 791 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: SQL NULL;
1: len 3; hex 002291; asc " ;;
2: len 1; hex 73; asc s;;
3: len 4; hex 01073176; asc 1v;;

*** (2) TRANSACTION:
TRANSACTION 5955990, ACTIVE 1 sec inserting, thread declared inside InnoDB 702
mysql tables in use 2, locked 2
474 lock struct(s), heap size 106704, 514871 row lock(s), undo log entries 1
MySQL thread id 1339, OS thread handle 3684, query id 340602 agvise-off3.agvise.com 192.168.168.56 root
Insert into SOIL (Year, SoilRefNo, Billing, GrowerName, GrowerAddress1, GrowerAddress2, GrowerCity, GrowerState, GrowerZip, SubmitterName, SubmitterAddress1, SubmitterAddress2, SubmitterCity, SubmitterState, SubmitterZip, Contact, DateReceived, DateSampled, FieldID_Protocol, County_FieldTestNo, SampleID_NonGLPDepth, Township_PO, Acres, Section, PreviousCrop, Quarter, DepthString, PlantRefNo, StartDepth1, Depth1, StartDepth2, Depth2, Depth3, Depth4, ElectronicID, Route, DriedGround, LongTermStorage, Manured, Bulk, ForeignSoil, Certified, Project, EntererCode, ProoferCode, Crop1, Crop2, Crop3, Yield1, Yield2, Yield3, Guides1, Guides2, Guides3, TestOptionNumber, TestOptionString, DealerDefaultOptionString, SampleType, OptionType, GridNumber, TotalGridPoints, BoxNumber, MiscTests, Specimen, FieldName, UniqueID, RangeSoil, Sampler) values(2017, 14586930, 'CR2618', 'TOM LISTUL', '', '', 'CLARKFIELD', 'MN', '', '
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 144 page no 20274 n bits 1192 index plantrefNo of table `agvise`.`soil` trx id 5955990 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;;]

Does the before insert trigger lock the whole table?
Would it help to change the before insert trigger to:
CREATE DEFINER=`root`@`%` TRIGGER `agvise`.`soil_BEFORE_INSERT` BEFORE INSERT ON `soil` FOR EACH ROW
BEGIN
   SET NEW.labno=(SELECT LabNo FROM soil WHERE year=new.YEAR order by LabNo Desc Limit 1+1); 
END

Does that trigger only lock one record? But can it possibly create duplicate LabNo?

Here is the VB.NET code to insert the whole record and I use the same but less fields for the program that inserts part of the record:
Try
            Dim nbrrows As Byte
            Using myconnection = New MySqlConnection(myConnstr)
                myconnection.Open()
                Using mycommand As MySqlCommand = myconnection.CreateCommand()
                    With mycommand
                        .CommandText = "Insert into SOIL (Year, SoilRefNo, Billing, GrowerName, " &
                        "GrowerAddress1, GrowerAddress2, GrowerCity, GrowerState, GrowerZip, SubmitterName, " &
                        "SubmitterAddress1, SubmitterAddress2, SubmitterCity, SubmitterState, SubmitterZip, " &
                        "Contact, DateReceived, DateSampled, FieldID_Protocol, County_FieldTestNo, SampleID_NonGLPDepth, " &
                        "Township_PO, Acres, Section, PreviousCrop, Quarter, DepthString, PlantRefNo, StartDepth1, Depth1, " &
                        "StartDepth2, Depth2, Depth3, Depth4, ElectronicID, Route, DriedGround, LongTermStorage, " &
                        "Manured, Bulk, ForeignSoil, Certified, Project, EntererCode, ProoferCode, Crop1, Crop2, " &
                        "Crop3, Yield1, Yield2, Yield3, Guides1, Guides2, Guides3, TestOptionNumber, TestOptionString, " &
                        "DealerDefaultOptionString, SampleType, OptionType, GridNumber, TotalGridPoints, BoxNumber, " &
                        "MiscTests, Specimen, FieldName, UniqueID, RangeSoil, Sampler) " &
                        "values(@Year, @SoilRefNo, @Billing, @GrowerName, @GrowerAddress1, @GrowerAddress2, @GrowerCity, " &
                        "@GrowerState, @GrowerZip, @SubmitterName, @SubmitterAddress1, @SubmitterAddress2, @SubmitterCity, " &
                        "@SubmitterState, @SubmitterZip, @Contact, @DateReceived, @DateSampled, @FieldID_Protocol, @County_FieldTestNo, " &
                        "@SampleID_NonGLPDepth, @Township_PO, @Acres, @Section, @PreviousCrop, @Quarter, @DepthString, @PlantRefNo, " &
                        "@StartDepth1, @Depth1, @StartDepth2, @Depth2, @Depth3, @Depth4, @ElectronicID, @Route, @DriedGround, " &
                        "@LongTermStorage, @Manured, @Bulk, @ForeignSoil, @Certified, @Project, @EntererCode, @ProoferCode, " &
                        "@Crop1, @Crop2, @Crop3, @Yield1, @Yield2, @Yield3, @Guides1, @Guides2, @Guides3, @TestOptionNumber, " &
                        "@TestOptionString, @DealerDefaultOptionString, @SampleType, @OptionType, @GridNumber, @TotalGridPoints, " &
                        "@BoxNumber, @MiscTests, @Specimen, @FieldName, @UniqueID, @RangeSoil, @Sampler) "
                        With .Parameters
                            .AddWithValue("@Year", Year)
                            .AddWithValue("@SoilRefNo", SoilRefNo)
                            .AddWithValue("@Billing", Billing)
                            .AddWithValue("@GrowerName", GrowerName)
                            .AddWithValue("@GrowerAddress1", GrowerAddress(1))
                            .AddWithValue("@GrowerAddress2", GrowerAddress(2))
                            .AddWithValue("@GrowerCity", GrowerCity)
                            .AddWithValue("@GrowerState", GrowerState)
                            .AddWithValue("@GrowerZip", GrowerZip)
                            .AddWithValue("@SubmitterName", SubmitterName)
                            .AddWithValue("@SubmitterAddress1", SubmitterAddress(1))
                            .AddWithValue("@SubmitterAddress2", SubmitterAddress(2))
                            .AddWithValue("@SubmitterCity", SubmitterCity)
                            .AddWithValue("@SubmitterState", SubmitterState)
                            .AddWithValue("@SubmitterZip", SubmitterZip)
                            .AddWithValue("@Contact", Contact)
                            .AddWithValue("@DateReceived", DateReceived)
                            If DateSampled > Nothing Then
                                .AddWithValue("@DateSampled", DateSampled)
                            Else
                                .AddWithValue("@DateSampled", DBNull.Value)
                            End If
                            .AddWithValue("@FieldID_Protocol", FieldID_Protocol)
                            .AddWithValue("@County_FieldTestNo", County_FieldTestNo)
                            .AddWithValue("@SampleID_NonGLPDepth", SampleID_NonGLPDepth)
                            .AddWithValue("@Township_PO", Township_PO)
                            .AddWithValue("@Acres", Acres)
                            .AddWithValue("@Section", Section)
                            .AddWithValue("@PreviousCrop", PreviousCrop)
                            .AddWithValue("@Quarter", Quarter)
                            .AddWithValue("@DepthString", DepthString)
                            .AddWithValue("@PlantRefNo", PlantRefNo)
                            .AddWithValue("@StartDepth1", StartDepth1)
                            .AddWithValue("@Depth1", Depth(1))
                            .AddWithValue("@StartDepth2", StartDepth2)
                            .AddWithValue("@Depth2", Depth(2))
                            .AddWithValue("@Depth3", Depth(3))
                            .AddWithValue("@Depth4", Depth(4))
                            .AddWithValue("@ElectronicID", ElectronicID)
                            .AddWithValue("@Route", Route)
                            .AddWithValue("@DriedGround", DriedGround)
                            .AddWithValue("@LongTermStorage", LongTermStorage)
                            .AddWithValue("@Manured", Manured)
                            .AddWithValue("@Bulk", Bulk)
                            .AddWithValue("@ForeignSoil", ForeignSoil)
                            .AddWithValue("@Certified", Certified)
                            .AddWithValue("@Project", Project)
                            .AddWithValue("@EntererCode", EntererCode)
                            .AddWithValue("@ProoferCode", ProoferCode)
                            .AddWithValue("@Crop1", Crop(1))
                            .AddWithValue("@Crop2", Crop(2))
                            .AddWithValue("@Crop3", Crop(3))
                            .AddWithValue("@Yield1", Yield(1))
                            .AddWithValue("@Yield2", Yield(2))
                            .AddWithValue("@Yield3", Yield(3))
                            .AddWithValue("@Guides1", Guides(1))
                            .AddWithValue("@Guides2", Guides(2))
                            .AddWithValue("@Guides3", Guides(3))
                            .AddWithValue("@TestOptionNumber", TestOptionNumber)
                            .AddWithValue("@TestOptionString", TestOptionString)
                            .AddWithValue("@DealerDefaultOptionString", DealerDefaultOptionString)
                            .AddWithValue("@SampleType", SampleType)
                            .AddWithValue("@OptionType", OptionType)
                            .AddWithValue("@GridNumber", GridNumber)
                            .AddWithValue("@TotalGridPoints", TotalGridPoints)
                            .AddWithValue("@BoxNumber", BoxNumber)
                            .AddWithValue("@MiscTests", MiscTests)
                            .AddWithValue("@Specimen", Specimen)
                            .AddWithValue("@FieldName", FieldName)
                            .AddWithValue("@UniqueID", UniqueID)
                            .AddWithValue("@RangeSoil", RangeSoil)
                            .AddWithValue("@Sampler", Sampler)
                        End With
                        nbrrows = .ExecuteNonQuery()
                        If nbrrows > 0 Then
                            'record inserted
                            SoilErrorFound = False
                        Else
                            MessageBox.Show("Error, record was not saved in SoilTable clsSoil InsertRecord.  Contact IT Dept.")
                            SoilErrorFound = True
                            Exit Sub
                        End If
                    End With
                End Using


Should I use a separate MySQL table with the year and LabNo stored in it? Then add one to it and update it?
Any assistance with this deadlock would be appreciated.
Thank you.

Is This A Good Question/Topic? 0
  • +

Replies To: MySQL Deadlock on Insert

#2 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,356
  • Joined: 03-December 12

Re: MySQL Deadlock on Insert

Posted 07 April 2017 - 05:45 AM

Trigger table locks

Quote

LabNo needs to be unique for each year

Is this for reporting reasons?

Here is my thought, just because the year and increment count combined needs to be unique, doesn't mean that it needs to be in the table like that.
1. You can use a view for reporting, that combines the year and increment id.
2. You can query the same in a regular statement or any other application level view. You just need to reference the real id in the case of update and removal.


I believe the issue comes down to the performance hit when calculating the current dataset. The larger that table gets, the longer it takes to get the total.

For further reading
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1