• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

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

Hi Guys
I am using mysql database with my program
my program run on many clients pc which connect with database on the server

the question is : how can I prevent user from deleteing one or many records which is/are opened by another user?
how can I know if the record is used (opened) by another user .

I am waiting your answer......................
  • 4
  • 3
1 Solution
Richard QuadlingSenior Software DeverloperCommented:
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?


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.
Hope it helps u

Richard QuadlingSenior Software DeverloperCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

peruzziAuthor Commented:
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"
peruzziAuthor Commented:

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
Richard QuadlingSenior Software DeverloperCommented:
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.

peruzziAuthor Commented:
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?
Richard QuadlingSenior Software DeverloperCommented:
An SQL databases works by receiving requests to amend the data in some way and processes them in first come first served sequence.

You MAY be able to use some sort of priority control within the DB (non-standard).

You can use transactions to block the work you need to do. When you commit the transaction, it will be an all or nothing. You would only know something is wrong AFTER you commit.

There is normally no state information available (well, not entirely true).

You cannot realistically have 2 users editing the same row unless you are VERY clever about which columns to update (messy).

Normally, the locking that is mentioned is dealing with the sql server during its updates.

You either have to issue lock commands or use some sort of semaphore to tell the DB that the row is locked AND you have to always check that semaphore in all your code when you want to delete things. messy again.

I know with MS SQL I can get lock information but not to row level, only page level (another issue you have is that ROWS tend not to be locked, but pages of rows or tables or DBs).

The real issue is that a SQL server is NOT a DBase or MDB file. You are expected to construct queries which protect themselves. If you need application based row protection, then you need to add application row protection. The SQL server will lock data as it is updated.


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now