MySQL Forums
Forum List  »  InnoDB

Locked tables...
Posted by: Lucio Menci
Date: October 03, 2014 10:55AM

Hi everybody!
I do not understand what is happening.
I have a program that runs two processes. The secondary process, run a select statement like the following when it needs to perform it:

Begin Transaction 
Create Table PippoXXXX As Select * From MyTable Where KeyAAA = 'AAA'; 
Update OtherTables, PippoXXXX Set OtherTables.ColX = Expression(PippoXXXX.*) Where OtherTable.KeyAAA = PippoXXXX.KeyAAA 
Delete PippoXXXX 
Commit Transaction.
Wait 3 seconds on average
Connection.Close
With a connection like this:
Connection TimeOut = 3;CommandTimeOut = 5

It's seems to be nothing of special (except that the update is done under certain statements, and the procedures can decide to do the RollBack instead of the commit, but not in this case).

The main process performs as following:
Delete * From MyTable Where KeyPrimary = XXXX; 
Insert Into MyTables ([...]) Values ​​([...])

With a connection with CommandTimeOut = 60.

If the connection with the main process founds the connection to the secondary process opened, can not perform the insert (the connection in bacground working on all the table MyTable and sometimes even takes a few seconds). But once the secondary connection terminates the job and after three seconds it disconnects, the primary connection does not realize that the table is free, and it could start writing. After 60 seconds the statement Insert (never noted that was the Delete statement, but it can be just for statistics), it raise a timeout error.

If I run
Show Engine Innodb Status
, it returns that the locked table is locked by a reading of the key KeyAAA, then by the secondary connection.

It's fine (no, it isn't) that MySql has this absurd manner, but I have to found a workaround.

The problem: The secondary connection is unique in the whole program, but I have thosands of connections like the main one. Is it possible that the secondary connection performs the
Create Table PippoXXXX As [...]
without marking the Read Lock on the table MyTable?

Thank you

Options: ReplyQuote


Subject
Views
Written By
Posted
Locked tables...
1746
October 03, 2014 10:55AM
943
October 04, 2014 09:33AM
900
October 06, 2014 01:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.