delphi and mysql :preventing user from delete an opened record by another user

Hi peruzzi,


What you are talking about is a form of locking.

What are you using to connect to the mysql DB? ODBC? or some native component?

Regards,

Richard Quadling.

Generally once the record is in the Edit state the database will throw error something like 'record locked by another user' . Until the user commit or roll back it will be in the record state in the normal case. Don't know mysql will throw this error. If it throws capture that exception and infom the user.


This article helps you to LOCK and UNLOCK mysql table.
http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html
Hope it helps u

sun4sunday

Be careful. Depending upon the technology being used, you may or may not get row locking.

I use to use an ODBC connection for Delphi. Setting the result set to edit made NO difference to the DB.

As far as I know there is no row locking. Only table locking.

To implement row locking you need to use a locking semaphore mechanism where you tell the DB that a row is locked. This creates a lot of work. But if used consistently it will resolve 2 users editing data and saving it.

You WILL get notification AFTER you edit the data, but NOT before you edit it. Not much use.

UPDATE table SET table.semaphore = 1 WHERE table.semaphore <> 1 AND table.rowid = some_id

If the update worked, you set the lock.

If not, someone else has.

You now have the issue of locked data when an app crashes.

So, you would be better off using a datetime for the lock

OK that's good
at first I am using tmycomponent tool to connect with mysql database

and in fact I don't want to lock the record
all what I want is to know if this record is used by another user or not

if it is used by another user2 then my program must prevent user1 from deleteing this record

and show message like"you can not delete this record , it's used by another user ,try later"

I mean by user1 an user2 the users of the program
and not the users of database
I use one database' user for all clients to connect to database

What does "in use" mean?

For me, in use could mean ...

1 - The row in the table is linked to other rows in other tables. Say like a Stock Item is used on many Outstanding Orders. You cannot delete the stock item until all the Outstanding Orders have been processed.
2 - The row is a login row. User 1 has logged into the application, therefore this would stop another physical person logging in as User 1 at a different location.
3 - The row is being edited by User 1 and you do not want another user to edit it.

1 - Use the database to create a trigger for On Delete to see if it is in use in other tables.
2 - Set a "logged_in" flag as they login and clear it when they logout. You would also need a user maintenance app to allow you to log a user off when they crash.
3 - This is what locking is about. Use a semaphore to indicate the row is being edited.

ok this is work
first all users must be able to edit the same record at the same time
trigger may be work
but I don't want to use trigger

is there any other way to know if record is opening by another user?