Solved

what is causing mySQL Deadlock error

Posted on 2014-09-09
20
966 Views
Last Modified: 2014-09-29
Dear experts,

I just had an issue today when running mysql. I got this error: ERROR 1213 (40001) at line 1: Deadlock found when trying to get lock; try restarting transaction

I want to know what was causing this error? I am simply inserting data into one of my data table.

Thanks.
0
Comment
Question by:Kinderly Wade
  • 7
  • 5
  • 2
  • +3
20 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Means that at the same time you was insert data in the table, another process has a lock in the same table so the engine choose one to be killed. Looks like you was the victim.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
How are you inserting data into the table?

DO you have an application that allows you to update the data or are you using the MySQL equivalent of SQL Server Manager?

mlmcc
0
 

Author Comment

by:Kinderly Wade
Comment Utility
I am inserting the data through a script. I simply copy the data from a .DBF file to mysql DB. When everytime the file changes on .DBF file, I will upload the new data into one of the table in mysql.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
What is the script?

Can you create a NEW table in the database with the same fields and upload to that one?

mlmcc
0
 

Author Comment

by:Kinderly Wade
Comment Utility
Hi mlmcc,

Yes. It will create a table and insert data into this new table.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
There must be some other process running that has the table locked.

Does your application do anything else with the tables before trying to upload?

mlmcc
0
 

Author Comment

by:Kinderly Wade
Comment Utility
Hi mlmcc,

My process will do this:

1. execute the truncate query
2. execute create table query (with index) with engine innodb
3. lock table xxx WRITE query
4. insert query
5. unlock table xxx query

The reason I want to lock the table is because when user need to access the data, if the data is being inserted into table, no one shall have the access to it (I know innodb supports row lock but I want to make sure that entire table is not accessible).

Can this cause issue? If so, what will be my better alternative? Thanks
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Try it without the lock and see what happens.
You could change the table name so it is no the one others would try to use.

How are you locking the table?

mlmcc
0
 

Author Comment

by:Kinderly Wade
Comment Utility
Hi mlmcc,

Since the tables are based on Innodb engine, it supports row lock. I need the table lock function because no one shall be reading the table when it is been updated. I simply use the query

LOCK TABLE test1 WRITE;

then I will need to unlock tables with this command:

UNLOCK TABLES;

I have 4 different scripts that locks different tables as in test1, test2, test3, and test4.
As to when the script will triggers the lock, it depends on file time. If my script detects a change with file time, the script will run. There can be a time when all 4 scripts running at same time or at different time.

Thanks.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Can you show the full script?

mlmcc
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Why would you LOCK TABLES before an INSERT query?  What is the rationale for doing that?
0
 
LVL 26

Assisted Solution

by:dpearson
dpearson earned 500 total points
Comment Utility
After you get the deadlock error, you can enter "SHOW INNODB STATUS" at the command line and it will dump out a mass of data, including a section titled this:

------------------------
LATEST DETECTED DEADLOCK
------------------------

That will help explain where the deadlock occurred and what the 2 transactions were.  Once you see that you may be able to figure out the problem yourself.  If not, post what it says here.  It may shed some light on what's happening.

Doug
0
 

Author Comment

by:Kinderly Wade
Comment Utility
Lately I haven't get a DEADLOCK on my tables. I am not too sure the actual cause of this. But it happens again I will try to post it up here.

To Ray,

Reason that I want to have the LOCK TABLE WRITE is because I will call the instance of the mysql connection and lock that a specific table during the insert. In case there are other users try to access the table with select * from test_tbl, they will have to wait till I release the table lock with UNLOCK TABLES. If I don't do this, other users can access the Innodb table as long as the row is not locked. In case the user access the database while the data is still being inserted, the result will not be complete. The insert process may take up to almost 1 min for those tables with large data.

The deadlock  doesn't always happen but once in a while. The code loop itself by steps as in check file time, if time change copy file over. Lock table and then read the data on file. Once data is obtained, insert into mysql database. Unlock the table.(Repeat).
0
 
LVL 61

Expert Comment

by:gheist
Comment Utility
No - you do not need to lock table at all. Full table lock is needed when e.g. adding column, not when adding one or million rows in a go.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
@gheist: Exactly why I asked the question!  It seemed like there was an assumption of something that does not exist.

@kinderly_wade: Please get an experienced DBA involved in the process to advise you about how to design this application.  It will not cost you very much money and it will give you far better outcomes.  As the great firefighter Red Adair once said, "If you think it's expensive to hire a professional, just wait till you hire an amateur!"
0
 

Author Comment

by:Kinderly Wade
Comment Utility
Hi gheist and Ray,

Locking the table is the only way for me to ensure that no one will be able to access the table during the insert. If the data is inserted half way while the users are able to access the table, the data will not be accurate. This will happen when I don't lock the table: some order haven't completely copied over to mysql database. When user happens to access the database pertaining to certain order, either the data for that order is partially available or the order is not available at all. Unless there is a way to stop user from accessing the table till the data has been inserted into mysql data table has complete besides locking table. This process takes about 1 min(large data). During this min of time the chance of user accessing mysql db is very high. After I implemented the lock, the data is accurate when user obtains the data. Also, the deadlock really happen even right before I implemented the lock. Deadlock happens my script do this:
1. TRUNCATE table xxxx
2. create table xxxx ......

Deadlock happens when I simply create the table. I pulled the log out dated back couple months ago before I implemented the table lock.

Let me rephrase my question:
1. with simple command TRUNCATE and CREATE, what can be the possible cause of deadlock?

2. according to gheist and Ray that LOCK may not be a better choice. What will be a better choice for my scenario if I wish to stop user from accessing the data table (approx 1 min) till the copy of data into mysql completes?

Thanks.
0
 
LVL 26

Accepted Solution

by:
dpearson earned 500 total points
Comment Utility
If you wrap all of the inserts into a transaction, then I think the visibility of the inserts is controlled by the transaction isolation level:
http://dev.mysql.com/doc/refman/5.6/en/dynindex-isolevel.html

There's a particular explanation of how you get "consistent reads" here which is what I think you may want:
http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

However, I'm no DBA so detailed questions on what the meanings are for these different modes is beyond my knowledge.

Doug
0
 

Author Comment

by:Kinderly Wade
Comment Utility
Hi dpearson,

After looking into the reference, I was able to obtain more info in depth and one of the topic resolve my issue. Thanks for the links.

For everyone else, thanks for the great feedback which allows me to learn something new to LOCK from mysql.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now