Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Whenever I try to use my addTask() and removeTask() functions I get a very long wait time followed by an error.

Here are the functions:

def addTask():

    name = raw_input("Enter the name of the task you would like to add: ")
    exists = cur.execute("SELECT Name FROM Tasks WHERE Name = '%s';" % name)
    if exists:
        print("Task already exists.")
    else:
        cur.execute("INSERT INTO Tasks(Name, Time) VALUES('%s', 0);" % name)
        print("'%s' has been added to the task list." % name)

def removeTask():

    name = raw_input("Enter name of the task you would like to delete: ")
    exists = cur.execute("SELECT Name FROM Tasks WHERE Name = '%s';" % name)
    if exists:
        cur.execute("DELETE FROM Tasks WHERE Name = '%s';" % name)
        print("'%s' has been removed from the task list." % name)
    else:
        print("Task doesn't exist.")

If I input a name that already exists in the addTask() function it works fine. However, if I try to add a new task with a new name in the addTask() function I get this error:

Traceback (most recent call last):
  File "./timelog3.py", line 137, in <module>
    main()
  File "./timelog3.py", line 23, in main
    addTask()
  File "./timelog3.py", line 44, in addTask
    cur.execute("INSERT INTO Tasks(Name, Time) VALUES('%s', 0);" % name)
  File "/usr/local/lib/python2.7/dist-packages/MySQL_python-1.2.4b4-py2.7-linux-    x86_64.egg/MySQLdb/cursors.py", line 202, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python2.7/dist-packages/MySQL_python-1.2.4b4-py2.7-linux-x86_64.egg/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1205, 'Lock wait timeout exceeded; try restarting     transaction')

Similarly for the remove function if I try with a non existential task name then it works fine. But if I try to remove a task that exists I get the same error.

The thing that really perplexes me is that it was working just fine a few minutes before and I hadn't even changed anything (or I can't recall what I changed).

share|improve this question
 
As a side note, if you make Name a primary key (or just add a unique constraint to it), you don't need the SELECT in the first version; just do the INSERT and the database will give you an error telling you the task already exists. –  abarnert 14 hours ago
 
For that matter, you definitely don't need the SELECT in the second one. Just do the DELETE and the database will tell you whether it deleted 0 rows or 1. –  abarnert 14 hours ago
 
Finally, you should never use string formatting to build dynamic SQL statements; it leaves you open to SQL injection, forces you to deal with quoting and/or escaping (what if your value has a ' in it?), and prevents the database from optimizing your otherwise-equivalent statements, and generally makes Codd cry. Use parameterized statements: cur.execute("INSERT INTO Tasks(Name, Time) VALUES(%s, 0);", [name]). –  abarnert 14 hours ago

1 Answer

up vote 0 down vote

The error you're getting is saying that someone has a lock on the table (or the whole database, or some other subset of the data).

So, either another program is using the database, your program has multiple connections to the database, or your program has a single connection and is trying to do two things on it interleaved (or even at the same time, if you have multiple threads).

One common possibility is that you started the script, put it in the background, and then started the same script again, and they're now fighting with each other. A few minutes ago, you didn't have two copies running, so it all worked fine.

share|improve this answer
 
I triple checked and I only have the one program attempting to access the database. Then I figured that while I tried to figure it out I'd see whether your other advice would help. (The XKCD comic was great by the way) I started by trying to change the Primary Key using ALTER TABLE Tasks DROP PRIMARY KEY, ADD PRIMARY KEY(Name);When I entered that in mysql> it ended up taking a very long time. Way too long considering there are only two rows in my database at the moment. SELECT queries are quick but INSERT/DROP/ALTER queries time out eventually. Perhaps this has something to do with it? –  Ivan Kelber 14 hours ago
 
Whatever reason the mysql command line tool is blocking forever, that's the same reason that your Python script is timing out. If you're using InnoDB tables, try executing SHOW INNODB STATUS, which will give you a list of (among other things) all existing locks. –  abarnert 14 hours ago
 
Also, you may want to read the overview on InnoDB locks in the MySQL docs and the section on the higher-level (all-table-type) table locks to understand what's going on, and why you shouldn't be surprised that (simple) SELECT queries don't have a problem, and so on. –  abarnert 14 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.