how to check if a record is in use by a other user

[mierlp]

hi,

I've readed several threads, looked at the \Lock sample for record locking...but it's not doing what i want.

How can i check if a other user has (the same) record in EDIT mode
and show a warning on the screen and cancel his action...is there
a sample somewhere...what i've read there a more user having
this ''problem'' and looking for a solution.

Greetz and thanks

Peter

[Antaeus]

Before editing try locking record with lrImmediately parameter (see Lock demo) and catching the error with try...except statement. If error happened, then the record is in use by someone.

[swierzbicki]

I tried the Lock Demo (with MyDAC 4.0.0.30).

Locking issue ?
I noticed that their is no differences between the lrImmedialty and lrDelayed locktype : I have opened "Lock.exe" twice , let the record lock set to lrImmedialty.

On the 1rst running Exe I edit the first record ...

On the 2nd running Exe when trying to edit the first record I'm getting an exception only after 60 sec !. Is it not possible to get this exception immediatly ! My customers will kill me if they have to wait such an amount of time before being warned that the record is in use !

Transaction Question
Is it possible to overlap transactions ?.
Here is the way I want to proceed.

I have a customer database.
Each customer own oders.

My program have 3 forms :

Main Form (DBGrid with all customer)
Customer Form (Edit controls + DBGrid will all customer orders)
Orders (Edit controls)

Is it possible to overlap transactions this way ?

When double clicking on my Main DBGRid (to display Customer infos):

Code: Select all

customer.connection.startTransaction;
customer.connection.lock(lrimmedialty);
showModalCustomerForm;
customer.connection.commit;

When double clicking on my Orders DBGRid (to display Orders infos):

Code: Select all

oders.connection.startTransaction;
oders.connection.lock(lrimmedialty);
showModalOrderForm;
order.connection.commit;

[mierlp]

hi,

Just like user swierzbicki i used the lock demo and als noticed there are no differences between IrImmendialty and IrDelayed locktype.

I just start my modified (for my situation) lock demo TWICE on 1 pc,
go to the same record, in the first instance hit the edit button, on
the second instance..hit the edit problem...no problem, no message !!!


It has to be possible to give the second user which hit the edit
button to display a messagen

[Antaeus]

> I'm getting an exception only after 60 sec
To modify wait time for exception try to define option innodb_lock_wait_timeout=XX in my.ini and restart server. Default value of this option is 50.

> als noticed there are no differences between IrImmendialty and IrDelayed locktype.
There is difference between lrImmediately and lrDelayed. You can't lock record two times using lrImmediately and lrDelayed option or lrImmediately and lrImmediately options. The following principle demonstrates the difference in locking options:
lrImmediately is equal to SELECT ... FOR UPDATE
lrDelayed is equal to SELECT ... LOCK IN SHARE MODE

> ...hit the edit button, on the second instance..hit the edit problem...no problem, no message !!!
Check if you use InnoDB tables because only InnoDB tables support row-level locking.
Quote from MySQL Reference:

You can generally code around the need for row-level locking. Some situations really do need it, and InnoDB tables support row-level locking. With MyISAM tables, you can use a flag column in the table...

For detailed information read MySQL Reference Manual.

[AAS71]

Hi, I'm using C++ Builder 6, MySQL 5.0.26 and MyDAC version 4.30.0.11

I'm using the InnoDB engine.
I implemented row locking.
When a second user tries to edit the same record the exception is not caught if I use lrImmedialty. However the exception is caught when I use lrDelayed after the second user tries to post the record.

This reply is just for your information, lrDelayed works fine for me.

[Antaeus]

When a second user tries to edit the same record the exception is not caught if I use lrImmedialty.

We have tested this with MyDAC 4.40.0.21(last build), but could not reproduce the problem. Please describe this problem more detailed or send a complete small sample to evgeniyD*crlab*com to demonstrate it, including script to create and fill table.