MySQL and locking mechanism

GuzunNicolae · Post by **GuzunNicolae** » Fri 22 Jun 2007 14:58

Hello

I have a serious problem.

I have an application for Data entry. It takes a field one by one and presents the user to enter data.
I have a manual locking mechanism. The table with data has a boolean field, so when smb wants to edit the record it is set to True thus preventing other users to take it.

The procedure is as follows:
- Start transaction
- Get first matching record
- Set lock field to true
- Commit transaction

If two concurent users try to Get the same first matching record one of them waits until the other one commits transaction so it sees that it is manually locked and skips it.
Everything works fine until some point where application freezes.

Can you tell me where the problem can be? I think this is due to a deadlock somewhere. How MyDAC copes with deadlocks? Is there a mechanism to know that a dead lock appears?

Thanks. Bye

Antaeus · Post by **Antaeus** » Sat 23 Jun 2007 13:41

MyDAC has no mechanisms to recognize deadlocks, and cope with them. Setting the CommandTimeout option may be useful for you to prevent applications from freezing.

Antaeus · Post by **Antaeus** » Sat 23 Jun 2007 13:42

Probably this topic of MySQL Reference Manual will help you to solve the problem.

GuzunNicolae · Post by **GuzunNicolae** » Mon 25 Jun 2007 08:06

I set CommandTimeout to 100 and it still freezez application.
Is this possible?

I thought it will raise an exception.

Antaeus · Post by **Antaeus** » Tue 26 Jun 2007 15:12

The commend timeout property is measured in seconds. Does it mean that your application does not raise an exception after 2 minutes?

GuzunNicolae · Post by **GuzunNicolae** » Wed 27 Jun 2007 13:55

I thought it was measured in milliseconds.
Anyway I put it to 2 and I do not get the exception.

Well I get it when the query really exceeds this time, but I do not get it when my application waits for the lock.

Antaeus · Post by **Antaeus** » Wed 27 Jun 2007 17:03

Thank you for information. We have reproduced this problem and fixed it. This fix will be included in the next MyDAC build.
Please send me at evgeniyd*crlab*com a small test sample to make sure that this fix also solves the problem you have encountered.

GuzunNicolae · Post by **GuzunNicolae** » Thu 28 Jun 2007 06:38

Very good. Thanks

But I want to know, what exactly have you fixed?
The application freezing or the Query to raise exception?

If the first can you please tell me what was the problem?

And when will be the next release of MyDAC? I need to solve this problem as fast as possible. The project already started.

Antaeus · Post by **Antaeus** » Tue 03 Jul 2007 13:49

We performed some additional investigation of this problem. It looks like the best way to solve this problem is to change the value of the innodb_lock_wait_timeout server variable.
Managing lock timeout by MyDAC causes some additional difficulties. There is no simple way to avoid them.

Antaeus · Post by **Antaeus** » Tue 03 Jul 2007 14:00

If this is not an appropriate solution for you, please send me at evgeniyd*crlab*com a complete small application that demonstrated this issue.