Lock Tables Error
Posted: Fri 16 May 2014 15:22
Hi Devart Team,
I am developing a multi-user app and in one instance, I am getting the next ID from a master id table. In that routine, I am locking the table as I have the table component on the screen. I am also calling a function within that routine to ensure the next id is not already in use, by doing a SELECT query on another table where it will be used. However, in doing the select query, I have no locking in place - it is simply a read SELECT statement. When I add the "LOCK TABLES tablename READ" line to the sql, and later "UNLOCK TABLES tablename", I get an error that says "Table is READ locked. Unable to do update".
My first question is why do I nead a read lock to do a simple select statement?
Here is my code example:
I used similar code in another routine and it worked previously, but now it does not work either.
Any help would be greatly appreciated.
Thanks
Barry
I am developing a multi-user app and in one instance, I am getting the next ID from a master id table. In that routine, I am locking the table as I have the table component on the screen. I am also calling a function within that routine to ensure the next id is not already in use, by doing a SELECT query on another table where it will be used. However, in doing the select query, I have no locking in place - it is simply a read SELECT statement. When I add the "LOCK TABLES tablename READ" line to the sql, and later "UNLOCK TABLES tablename", I get an error that says "Table is READ locked. Unable to do update".
My first question is why do I nead a read lock to do a simple select statement?
Here is my code example:
Code: Select all
function TForm1.GetNextID: Integer;
var NSID: Integer;
begin
//Set NSID to a 0.
NSID := 0;
//Open the table directly to get the next id value.
try
//Open the table.
IDTbl.Open;
//Lock the table so no one else can read or write for the moment.
IDTbl.LockTable(ltWrite);
//Get the current value - there is only 1 record and increment it by one.
NSID := IDTbl.FieldByName('id').AsInteger+1;
//Check to ensure the user has not previously specified the next id.
while NOT CheckIDAvail(IntToStr(NSID)) do Inc(NSID);
//Put the table in edit mode.
IDTbl.Edit;
//Put the value back into the table.
IDTbl.FieldByName('id').AsInteger := NSID;
//Post the value.
IDTbl.Post;
//Unlock the table.
IDTbl.UnLockTable;
//Close the table.
IDTbl.Close;
except on Exception do begin
//Close the table.
IDTbl.Close;
end;
end;
//Return NSID.
Result := NSID;
end;
// Here us the function that is called to check the availability of the ID.
function CheckIDAvail(SID: String): Boolean;
var SIDQ: TMyQuery;
RV: Boolean;
begin
//This function is designed to query the table to see if
//the SID is available.
//Set RV to True.
RV := True;
//Create the query component.
SIDQ := TMyQuery.Create(nil);
SIDQ.Connection := userConn;
//Close, clear and populate the query.
SIDQ.Close;
SIDQ.SQL.Clear;
//This next line causes grief!
// SIDQ.SQL.Add('LOCK TABLES tablename READ;');
SIDQ.SQL.Add('SELECT prodid '
+'FROM tablename '
+'WHERE id='+SID+';');
//Open the query.
SIDQ.Open;
//Check to see if there are any records - if so, set RV to False.
if SIDQ.RecordCount > 0 then RV := False;
//Unlock the table.
//These lines do not execute.
// SIDQ.Close;
// SIDQ.SQL.Clear;
// SIDQ.SQL.Add('UNLOCK TABLES tablename;');
// SIDQ.Open;
//Close the query and free the component.
SIDQ.Close;
SIDQ.Free;
//Return RV.
Result := RV;
end;
Any help would be greatly appreciated.
Thanks
Barry