pessimistic lock hang

[Valerio M.]

Hello, I've a problem with a multiuser application.

MyDac: 6.00.0.1 for Delphi XE
MySQL server version: 5.5.8

this is the scenario:

Pc A:
mytable.Edit;

Pc B:
mytable.Edit;
Here I expected an exception to be thrown,
so I can alert the user he can't editing that record at that time.

But, when the "Pc B" call mytable.Edit, the application hang until the "Pc A"
call post or commit. Also the OnEditError event is not fired.

this happens also into the mydac lock demo in the same way.

ps: Both Pc A and Pc B are connecting to mysql server with the same account and the table engine is InnoDB.

I hope you have some suggestion on this.

[Valerio M.]

No-one have this problem?

[AndreyZ]

Hello,

Such behaviour is correct. If any other connection tries to modify the locked record, it will wait for the time specified by the innodb_lock_wait_timeout server variable, by default 50 seconds. If during this time the record isn't unlocked, an exception raises.

[Valerio M.]

uhm so I can tweak the inno db lock record timeout to fit my needs.

thank you

ps: just another question. there's a way to manually abort the "Pc B" edit command while it is waiting for inno db timeout?

[swierzbicki]

Hi,

just another question. there's a way to manually abort the "Pc B" edit command while it is waiting for inno db timeout?

Don't know if this is possible but before editing just test if the record is locked.

[Valerio M.]

Hi,

just another question. there's a way to manually abort the "Pc B" edit command while it is waiting for inno db timeout?

Don't know if this is possible but before editing just test if the record is locked.

Right, I had already thought this.

But, I can't find any "mytable.TryEdit" or "mytable.isRecordLocked"

[AndreyZ]

MySQL server doesn't allow to check if a record is locked or not. You can decrease the value of the innodb_lock_wait_timeout server variable and see if a record is locked by catching the exception.