I am using mysqldb in python.

I need to do the following for a table.

1) Lock
2) Read
3) Truncate the table
4) Unlock

When I run the below code, I get the below error. So, I am rather unsure on how to lock a table for reading it, then truncating the table. I need to be sure that no other connection reads the data.

asin_list = [] conn = MySQLdb.connect(host=parms['database']['operations']['host'],user=parms['database']['operations']['username'],passwd=parms['database']['operations']['password'],db=parms['database']['operations']['database'])cursor = conn.cursor()query = "LOCK TABLES asin_one_time_only READ"cursor.execute(query)print 'fu1'query = """select asin FROM asin_one_time_only""" cursor.execute(query)rows = cursor.fetchall()for row in rows:asin_list.append(row[0]) print asin_listprint 'fu2'query = "UNLOCK TABLES;"cursor.execute(query)conn.commit()print 'fu3'query = "LOCK TABLES asin_one_time_only WRITE"cursor.execute(query)query = """truncate table amz_one_time_only""" cursor.execute(query)conn.commit()print 'fu3'query = "UNLOCK TABLES;"cursor.execute(query)conn.commit()cursor.close()conn.close() Traceback (most recent call last):File "/home/ubuntu/workspace/Amazon-Products-Crawler-1/threaded_crawl.py", line 1086, in <module>onetime = getOneTimeOnlyAsins(parms)File "/home/ubuntu/workspace/Amazon-Products-Crawler-1/threaded_crawl.py", line 109, in getOneTimeOnlyAsinscursor.execute(query)File "/usr/lib/pymodules/python2.7/MySQLdb/cursors.py", line 166, in executeself.errorhandler(self, exc, value)File "/usr/lib/pymodules/python2.7/MySQLdb/connections.py", line 35, in defaulterrorhandlerraise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1192, "Can't execute the given command because you have active locked tables or an active transaction")

You can't truncate a table that is locked for writing. This is because "truncate" means "destroy the table, and recreate a new one with the same schema."

You can however, empty the table. Instead of TRUNCATE TABLE asin_one_time_only use DELETE FROM asin_one_time_only. Note that this will not reset the autoincrement numbering. If you want to reset it as well, use ALTER TABLE asin_one_time_only auto_increment=1

I suggest doing this:

LOCK TABLES asin_one_time_only READ;
SELECT asin FROM asin_one_time_only;
-- minimize the possibility of someone writing to the table in-between
-- an "UNLOCK TABLES" and a "LOCK TABLES" by just issuing a new LOCK TABLES
-- I am not 100% sure that MySQL will do this atomically, so there is a
-- possibility that you may delete a row that was not read.
-- If this is unacceptable, then use a "LOCK TABLES asin_one_time_only WRITE"
-- from the very beginning.
LOCK TABLES asin_one_time_only WRITE;
DELETE FROM asin_one_time_only;
ALTER TABLE asin_one_time_only auto_increment=1;
UNLOCK TABLES;