Lock wait timeout exceeded - MySQL 5.6.15-ndb-7.3.4

[Dev Das]

Hi,

I am developing an application in VB.Net connecting to MySQL using MySQL ADO.Net; MySQL engine: ndbcluster.

I am regularly receiving "Lock wait timeout exceeded; try restarting transaction" OR "A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond". I am able to trace the issue; and it happens when multiple threads are accessing the same row of a table; one thread is trying to update the record and the other thread is trying to delete the same row entry. Both the connections are locking the same row (I am using ndbcluster, and it does row-level locking).

In the VB.Net application I implemented ADO CommandTimeout to 1 sec; this does bring the control back to the application immediately (as Timeout expires), and the application repeats the failed request and it does work. But sometimes the ADO control returns back after a few seconds. Due to the CommandTimeout parameter, the actual error code is masked and I receive "Timeout expired" in the VB.Net application. Whereas I do have a watchdog application running with my VB.Net application, and it captures the error messages mentioned above.

My question:
1. How this needs to be handled gracefully to avoid the error completely.
2. Once the lock is encountered, the execution pauses till the timeout error reverts - remedy for this (if #1 is handled then this would be rectified also).

Here is the VB.Net application log snippet, reflecting the exact timing of two separate threads processing on the same row:

2014-12-08 11:59:49.527 Worker_DB_6 ERROR: [5: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.] DELETE FROM liveusers WHERE Port = '1313' LIMIT 1

2014-12-08 11:59:49.527 Worker_DB_2 ERROR: [5: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.] UPDATE liveusers SET Req2Connect = 0 WHERE Port = '1313' LIMIT 1

The watchdog error log snippet:
MyApp.exe (1560): debug message: "Could not kill query, aborting connection. Exception was Unknown thread id: 76072792"

MyApp.exe (1560): debug message: "Could not kill query, aborting connection. Exception was Lock wait timeout exceeded; try restarting transaction"

MyApp.exe (1560): debug message: "Could not kill query, aborting connection. Exception was A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond"


Thanks in advance for any help!!!