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

Posted on 2006-06-07
Last Modified: 2010-04-05
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......................
Question by:peruzzi
    LVL 40

    Expert Comment

    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.
    LVL 9

    Expert Comment

    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

    LVL 40

    Expert Comment

    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

    Author Comment

    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"

    Author Comment


    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
    LVL 40

    Expert Comment

    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.


    Author Comment

    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?
    LVL 40

    Accepted Solution

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
    In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now