Jump to content

How To Lock Execproc and Different Data Records for Each Users


jemmyhatta

Recommended Posts

Hello,

First of all, please pardon my English, I'm Indonesian.

 

I have e-commerce project for request maintenance's order. 

I'm using components stored procedure FireDAC in unidatamodule, unidbgrid, etc.

 

Now I'm in the testing stage to try whether this project is ready to deploy or not, and running some tests on it.

 

The first test is to do some "concurrent data submit".

In same time, multiple testers are inputting and trying to submit the datas. Then, I've got the "Key Violation" error message. Is there any way to lock the session or for buying me some time when I run the 'execproc' to avoid that error? Or, any other way to solve that? Thanks.

 

The second test is to input as many datas as possible in slightly different submit time (not exactly the same, maybe just differ for several seconds). When the datas are successfully saved, each user see differences in unidbgrid. There're different data records for each of them, even when they already run the "Refresh" button (which is already there in unidbgrid); even when I already close and reopen the execproc table.

 

  DataModule.spEditHOrder.Prepare;
  DataModule.spEditHOrder.ExecProc;
  DataModule.spQueryHOrderBlmProses.Active:=False;
  DataModule.spQueryHOrderBlmProses.Active:=True;

 

Example:

Before testing begins, there're 10 records of datas. User1, User2, and User3 are trying to add 3 datas for each of them. The result is that User1 got to see 13 datas, User2 sees 16 datas, and User3 15 datas. In database, the total summary is 19 datas (correct, 10 + 3*3). But, is it normal? For each user just seeing differences in total data records appeareances?

 

After executing the line "DataModule.spEditHOrder.ExecProc;"

Then, what to do next, "DataModule.spEditHOrder.Release;" or "DataModule.spEditHOrder.Refresh;" ?

 

Thank you in advance.

 

 

 

 

 

Link to comment
Share on other sites

"Key violation " si a database message. It has nothing to do with UniGui.

Your tables have primary keys and you are not generating new values for

each new record. If you are using Firebird you need "generators" and a

trigger to generate the new ID for the new record.

If you are in PostgresSQL or MsSQL the same thing is called "sequence"

and you need to define the tables with serial / autoincrement fields ( like ID-s )

Depending on your data access layer you also have to define when you generate the new ID-s.

 

For the second problem it looks like you have to red a little about transactions.

Depending on a thing called "isolation level" of the transaction you can "see" or not see

updates made by other clients.

In very short, if you are using "read commited" isolation level and never commit after adding data,

the others never see the new records. There is also a great chance that you loose the data also.

  • Upvote 1
Link to comment
Share on other sites

"Key violation " si a database message. It has nothing to do with UniGui.

Your tables have primary keys and you are not generating new values for

each new record. If you are using Firebird you need "generators" and a

trigger to generate the new ID for the new record.

If you are in PostgresSQL or MsSQL the same thing is called "sequence"

and you need to define the tables with serial / autoincrement fields ( like ID-s )

Depending on your data access layer you also have to define when you generate the new ID-s.

 

I am using MSSQL Server 2012 and use stored procedure to help me check the last number where I call from my project.

 

The primary key is "nobukti" and type of "nobukti" is varchar.  I am generate new value  from event OnClick because the value of "nobukti" have format.   The format of "nobukti" is   S001-YYYYMMXXX         -->  YYYY= Year,  MM=Month,  XXX=Order Number

 

The codes is :

 

procedure TFTOrder.BSubmitClick(Sender: TObject);

var tamp, info,  :string;

begin

 

  {Check last number}

  DataModule.spLastNobukti.Active:=False;

  DataModule.spLastNobukti.ParamByName('@NOBUKTI').Value:=cust+'-'+FormatDateTime('YYYYMM',Date);

  DataModule.spLastNobukti.Prepared;

  DataModule.spLastNobukti.Active:=True;

  DataModule.spLastNobukti.Last;

 

  if DataModule.spLastNobukti.RecordCount=0 then tamp:=cust+'-'+FormatDateTime('YYYYMM',Date)+'001'

    else tamp:=Cust+'-'+FormatDateTime('YYYYMM',Date)+FormatFloat('000',StrToFloat(Copy(DataModule.spLastNobuktiNOBUKTI.AsString,12,3))+1);

 

  {save data}

  DataModule.spEditHOrder.ParamByName('@ASAL').Value:='SUBMIT';

  DataModule.spEditHOrder.ParamByName('@TRANS').Value:='CUST';

  DataModule.spEditHOrder.ParamByName('@KDCUST').Value:=Cust;

  DataModule.spEditHOrder.ParamByName('@SESSIONID').Value:=UniLogin.SessionID;

  DataModule.spEditHOrder.ParamByName('@USERID').Value:=UniLogin.UserID;

  DataModule.spEditHOrder.ParamByName('@NOBUKTI').Value:=tamp;

  DataModule.spEditHOrder.ParamByName('@TGLBUKTI').Value:=Date;

  DataModule.spEditHOrder.ParamByName('@NOSERI').Value:=trim(ENoSeri.Text);

  DataModule.spEditHOrder.ParamByName('@NOREG').Value:=trim(ENoAsset.Text);

  DataModule.spEditHOrder.ParamByName('@NOBARCODE').Value:=trim(ENoBarcode.Text);

  DataModule.spEditHOrder.ParamByName('@LOKASI').Value:=ELokasi.Text;

  DataModule.spEditHOrder.ParamByName('@UP').Value:=EPerson.Text;

  DataModule.spEditHOrder.ParamByName('@TELP').Value:=ETelp.Text;

  DataModule.spEditHOrder.ParamByName('@MEDIAINFO').Value:=EMedia.Text;

  DataModule.spEditHOrder.ParamByName('@PRODUK').Value:=EJenis.Text;

  DataModule.spEditHOrder.ParamByName('@MERK').Value:=EMerk.Text;

  DataModule.spEditHOrder.ParamByName('@TIPE').Value:=ETipe.Text;

  DataModule.spEditHOrder.ParamByName('@SIZE').Value:=ESize.Text;

  DataModule.spEditHOrder.ParamByName('@KELUHAN').Value:=EKeluhan.Text;

  DataModule.spEditHOrder.Prepare;

  DataModule.spEditHOrder.ExecProc;

  DataModule.spQueryHOrderBlmProses.Active:=False;

  DataModule.spQueryHOrderBlmProses.Active:=True;

 

  ShowMessage('Data Order Sudah Di Submit Dengan Nomor Order '+tamp);

end;

 

 

I have been added exeption in event onError  in FDConnection like this :

 

procedure TDataModule.FDConnectionTransaksiError(ASender: TObject;

  const AInitiator: IFDStanObject; var AException: Exception);

var

  oExc: EFDDBEngineException;

begin

  if AException is EFDDBEngineException then begin

    oExc := EFDDBEngineException(AException);

    if oExc.Kind = ekRecordLocked then

      oExc.Message := 'Mohon Coba Beberapa Saat Lagi. Saat ini Proses Sedang Padat.'

    else if (oExc.Kind = ekUKViolated) and SameText(oExc[0].ObjName, 'UniqueKey_Orders') then

      oExc.Message := 'Mohon Coba Proses Penyimpanannya Sekali Lagi';

  end;

end;

 

 

 

 

After you see the code, you can give me some advice for this codes or in the properties of table in Datamodule?

post-2205-0-51490200-1419045108_thumb.jpg

post-2205-0-88622100-1419045111_thumb.png

Link to comment
Share on other sites

I don't think StrToFloat in that format is thread-safe. Try the other format that uses TFormatSettings which is thread-safe.

The same situation for DateToStr.

 

As a general rule it is better to use ID-s of type longint generated by the server ( autoincrement )

Best retrivel speed is achived on indexed integer fields, not on varchar.

Secondary, you can put in some other field some serial no based on whatever rule you want.

You shall see the differences when the database increases in size.

 

I can't say that I understand everything you put there but the general approach is :

 

1. Create a separat table only with countors ( key, id ). Dont "select max(....) from ..." because it's a tricky business in multi user environments.

2. Before insert a new order , select one record from that table based on the "ORD' key for example ,  incremet the ID , Post it and COMMIT !.

3. Save the new number somwhere in the Datamodule or UniMainModule.

4. Generate your key acc. to your formula using that number.

5. Insert a new record in the orders table with the newly generated key.

 

I hope I was clear enough.

 

Salve

Link to comment
Share on other sites

 

SET XACT_ABORT ON

 

at the start of every stored proc if it uses transactions.

zilav, thank you very much. This command is very useful and important.   :)

 

 

I can't say that I understand everything you put there but the general approach is :

 

1. Create a separat table only with countors ( key, id ). Dont "select max(....) from ..." because it's a tricky business in multi user environments.

2. Before insert a new order , select one record from that table based on the "ORD' key for example ,  incremet the ID , Post it and COMMIT !.

3. Save the new number somwhere in the Datamodule or UniMainModule.

4. Generate your key acc. to your formula using that number.

5. Insert a new record in the orders table with the newly generated key.

 

I hope I was clear enough.

 

Salve

Adragan, thank you for your advice.  :) 

 

Your advise is very helpful when the order number do not have a format of years and months which is must back to number "1" every beginning of the new months and years. If I'm using this approach, then in the every beginning of new months and new years i have to reset back that number to "0". I'm very grateful for your assistance on this matter.

 

The "Key Violation" error doesn't appear anymore after I added "SET XACT_ABORT ON" in stored procedure, and a little addition of code on event on error. And then, the code looks like this below:

 

procedure TDataUmum.spEditHOrderError(ASender: TObject;

  const AInitiator: IFDStanObject; var AException: Exception);

begin

if EFDDBEngineException(AException).Errors[0].Kind = ekUKViolated then

  begin

  AException.Free;

  AException := Exception.Create('Mohon Coba Proses Penyimpanannya Sekali Lagi');

  end;

if EFDDBEngineException(AException).Errors[0].Kind = ekRecordLocked then

  begin

  AException.Free;

  AException := Exception.Create('Mohon Coba Beberapa Saat Lagi. Saat Ini Proses Sedang Padat ');

  end;

end;

 

I've tested that code with several simultaneous users trying to accessed the program together, and the "Key Violation" error not appear. Very Thanks!

 

-Jemmy-

Link to comment
Share on other sites

Please sign in to comment

You will be able to leave a comment after signing in



Sign In Now
×
×
  • Create New...