Board index » delphi » How does one Lock a row in a table? HELP

How does one Lock a row in a table? HELP

Any one know how to place a lock on a row?  This is on database engines
that can cope with this.  I am running an Informix Online 5 engine and need
to place a lock on
a row - this is the pratice on multi user application is it not?
--
Dennis JR Harding
(d...@icon.co.za)

 

Re:How does one Lock a row in a table? HELP


Pessimistic locking methods
by Bryan Valencia
08/05/96 3:30 PM

Back in the early days of database programming, before the days of
Client/Server systems, Paradox for DOS offered us a multiuser concurrent
database that would only allow one user to edit a table row at a time. =

Another user could be editing the very next record in the table, but let
him try to touch the locked record, and KABAAM! he was looking up the
ErrorUser function in the manuals.  Now that we have unleashed the
wonders of Client/Server technology upon ourselves and our customers, we
find that row level locking =

The long awaited transaction management routines are here.  I would like
to emphasize that this information is complete and perfect, lacking
nothing, and not subect to change.   However, it isn=92t.  It has yet to
be subjected to multi-user testing, and to be tried on a master-detail
form.

The overall format of this code is that we open the transaction on an
Edit or Insert, and close it on a post or cancel.  There is a lock
placed on a record while it is in edit mode, which remains until the
user closes the transaction.
Informix suggests that we place timers on our code, and do auto-commits
when there are are locks floating around (like the user hits edit and
goes to lunch). I do not plan to do the timer code at this time, because
it is not absolutely necessary for this release.
We also need to do some work to clean up our messes when we encounter
fatal exceptions.  We ran an experiment where we put a table in edit
mode and turned off the computer.  The lock remained on the database,
and had to be released by the DBA.  For this reason we need to find a
way to detect open transactions, in our error management routines we
need to attempt to commit them or roll them back.  I am still figuring
out how to do this.

The code that follows shows how to place a record in edit mode with a
lock, cancel changes, and commit changes.  Keep in mind that this method
should only be used where concurrency is an issue (like editing loans,
borrowers, income, etc.).

Entering Edit mode
procedure TDemo1.Edit1Click(Sender: TObject);
var
        sOldVal,s : string;
begin
        try                                                             {Start an exception block, because the edit mode may fail
due to other user locks}
                ValueDB.startTransaction;       {This is the transaction to hold the lock
while we are in edit mode}
                CreditSource.Edit;      {Place the credit_source table in edit mode}
                cbName.SetFocus;        {Move to the first tab stop}
        except
          on E:eDatabaseError do        {If we get an error...}
          begin
                showMessage(E.message); {Do not do this: Use the Value error reporting
facility}
                ValueDB.rollback;       {This will close out the open transaction in the
case of an error}
                CreditSource.cancel;    {This places our screen object back into browse
mode}
                exit;                                           {Do I really need to do this?}
          end; {on e:EDatabaseError}
        end;     {try}
end; { Edit1Click }

Committing User Edits
procedure TDemo1.btnPostClick(Sender: TObject);
var
        s:string;
begin
        with ValueDB do begin
                try
                        CreditSource.Post;      Try to post all the changes}
                        Commit;         {Try to commit these changes}
                except
                        on E:EDatabaseError do begin    {Display the Message}
                                s:=3DE.Message;
                                messagedlg('DataBase Error, '+S+' rolling back your changes',
mterror,[mbOK],0 );
                                CreditSource.Cancel;    {Cancels the screen edit mode}
                                CreditSource.Refresh;   {Rereads committed data (this will refresh the
screen with real data.)}
                                rollback;       {Closes any open transactions}
                        end; {on EDatabase Error}
                end; {try}
        end;  {with ValueDB}
end; {TDemo1.btnPostClick}

The Add Key
procedure TDemo1.New1Click(Sender: TObject);
begin
        ValueDB.StartTransaction;       {Open a transaction for the 'post' button to
close}
        CreditSource.Edit;
        CreditSource.Insert;
        MaxNum.close; MaxNum.Open;      {This is my SQL object that fetches the last
key}
        CreditSourceCode.AsInteger:=3D 10;      {This code should default to 10}
        CreditSourceCredit_bureau_code.AsInteger :=3D
                1+MaxNum.Fields[0].AsInteger;   {OK. I wrote my own key field
generator.}
end;

Cancelling edits
procedure TDemo1.btnCancelClick(Sender: TObject);
begin
        CreditSource.Cancel;    {cancel the screen data (and updates the screen)}
        ValueDB.rollback;       {cancel the open transaction.}
end;

Checking for locks before placing the user in edit mode.
{This is the code to place in the BeforeEdit of your TTable object.  I
assume you have placed a SQL object on the form called CheckLock.}
procedure TDemo1.CreditSourceBeforeEdit(DataSet: TDataset);
var
        s,Key,Code: string;
begin
        Code:=3DCreditSourceCode.asString;      {every table has a code field: get th=
e
current data}
        Key:=3DCreditSourcecredit_bureau_code.asString; {If there is more than 1=

key field use Key1, Key2, Key3}

        with CheckLock do begin {Checklock is my SQL object, connected to my
Database object}
                SQL.Clear;
                SQL.Add('update credit_source');
                SQL.Add('set code =3D '+Code);
                SQL.Add('where credit_bureau_code=3D'+Key);

                try                                                     {update the record to itself, and if it works, you are
allowed to edit.}
                        ExecSQL;        { do the update }
                except                          {Should there be an error, send it to the caller.}
                        raise EDataBaseError.Create('Cannot enter edit mode: this record is
locked by another user.');
                end; {try}
        end;     {with}         {note that I left the transaction{*word*154}.}
end;

-- =

-----------------------------------------
Software Services - Making Windows Scream
http://www.invsn.com/softserv/
bry...@thevision.net
-----------------------------------------

Other Threads