[yunqa.de] Re: How to correctly avoid 'database is locked' in multi user environment
- From: H M <docshotmail2@xxxxxxxxxxx>
- To: DISQLite3 mailing list <yunqa@xxxxxxxxxxxxx>
- Date: Fri, 11 Jan 2013 13:31:08 +0000
Sorry Ralf, The real thing is far too big but here are two small test projects
I made with the same code snippets that show the same behaviour. (sorry if the
layout is poor, the code indentation seems to get lost when I post here)
Project LongLock sets a lock for a time entered by the user by performing lots
of updates inside a transaction.
The other project, TimeoutWrite, sets a timeout entered by the user and then
tries to write a single record to the same table.
I then ran them both simultaneouly.
If the timeout period in project TimeoutWrite is more than the lock period in
project LongLock the 'New comment saved' message is displayed and the record is
saved.
If the timeout period in TimeoutWrite is less than the lock period in LongLock
the 'New comment saved' message is still displayed (at the end of the timeout
period) but the record is not saved.
I was testing with a busy timeout of 3000ms in project TimeoutWrite and a lock
time in project LongLock of between 1 and 10 seconds (by entering between 10
and 100 in the edit box)
Howard
This is Project LongLock that sets the lock (one form and a datamodule)
=========================================
The Main form
***********
unit MainFrmLongLock;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls;
type
TForm1 = class(TForm)
Edit1: TEdit;
Button1: TButton;
Label1: TLabel;
Label2: TLabel;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
uses U_DataModLongLock;
{$R *.dfm}
procedure TForm1.Button1Click(Sender: TObject);
var i : integer;
begin
Label2.Caption := ('Database locked');application.ProcessMessages ;
DataModLongLock.DISQLite3Database1.open;
DataModLongLock.DISQLite3Database1.Execute('begin transaction;');
for i := 1 to strToInt(edit1.Text) do
begin
DataModLongLock.DISQLite3Database1.Execute('UPDATE Comments SET
CommentDeptID = 8 WHERE CommentDeptID = 8;');
sleep(100);
end;
DataModLongLock.DISQLite3Database1.Execute('commit;');
DataModLongLock.DISQLite3Database1.close;
Label2.Caption:=('Database free');
end;
end.
The Data Module
***********
unit U_DataModLongLock;
interface
uses
SysUtils, Classes, DISQLite3Database;
type
TDataModLongLock = class(TDataModule)
DISQLite3Database1: TDISQLite3Database;
private
{ Private declarations }
public
{ Public declarations }
end;
var
DataModLongLock: TDataModLongLock;
implementation
{$R *.dfm}
end.
...........................................................................................................
This is Project TimeoutWrite that tries to do the write (one form and a
datamodule)
=========================================
The Main form
***********
unit U_MainTimeoutWrite;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls;
type
TFrmTimeoutWrite = class(TForm)
Edit1: TEdit;
BtnWriteRecord: TButton;
BtnSetTimeoutPeriod: TButton;
procedure BtnWriteRecordClick(Sender: TObject);
procedure BtnSetTimeoutPeriodClick(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
FrmTimeoutWrite: TFrmTimeoutWrite;
implementation
uses U_DataModTimeoutWrite, DISQLite3API;
{$R *.dfm}
procedure TFrmTimeoutWrite.BtnSetTimeoutPeriodClick(Sender: TObject);
begin
DataModTimeoutWrite.TimeoutValue := StrToInt(edit1.Text); //store timeout in
data module
end;
procedure TFrmTimeoutWrite.BtnWriteRecordClick(Sender: TObject);
var
SQLiteReturnValue : integer;
sql : string;
begin
sql :='insert into Comments (CommentText,CommentDeptID ) values (''Dummy
text'', 8);' ;
SQLiteReturnValue := DataModTimeoutWrite.ExecuteSQLWithLockedDBTimeOut(sql);
//try to write record
if SQLiteReturnValue = SQLITE_OK then //SQLITE_OK defined in
DISQLite3API
showmessage('New comment saved')
else
case SQLiteReturnValue of
SQLITE_BUSY : showmessage ('Database is busy') ;
SQLITE_LOCKED : Showmessage ('Database is locked');
Else showmessage ('Could not save. Error code: '+
inttostr(SQLiteReturnValue));
end;//case
end;
end.
The Data Module
***********
unit U_DataModTimeoutWrite;
interface
uses
SysUtils, Classes, DISQLite3Database;
type
TDataModTimeoutWrite = class(TDataModule)
DISQLite3Database1: TDISQLite3Database;
private
{ Private declarations }
public
{ Public declarations }
function ExecuteSQLWithLockedDBTimeOut(sql: string): integer;
var TimeoutValue : integer; //I know its bad to do this but its just a test
program!!
end;
var
DataModTimeoutWrite: TDataModTimeoutWrite;
implementation
uses
strUtils, //for RightStr()
DISQLite3API; //for sqlite3_busy_timeout
{$R *.dfm}
{ TDataModTimeoutWrite }
function TDataModTimeoutWrite.ExecuteSQLWithLockedDBTimeOut( sql: string):
integer;
var
FullSQL : string;
begin
//Clean sql and make sure it ends with a semicolon
sql := trim(sql); //remove spaces before and after
if rightstr(sql,1) <> ';' then // no semicolon, need to add one
sql := sql + ';';
FullSQL := 'Begin transaction; ' + sql + ' Commit;' ;
try
try
DISQLite3Database1.Open;
sqlite3_busy_timeout( DISQLite3Database1.Handle, TimeoutValue); //set
busy timeout
DISQLite3Database1.Execute(FullSQL); //try to run the sql until timeout
Result := SQLITE_OK; // SQLITE_OK defined in DISqlite3API
except
on e: ESQLite3 do // If the DISQLite3 database engine raised this error,
return the specific error code
begin
Result := e.ErrorCode;
DISQLite3Database1.Rollback; //undo all the bits we were able to do
end;
else //for any other error simply re- raise it so it gets handled
elsewhere
raise
end //try - except
finally
DISQLite3Database1.Close;
end; //try - finally
end; //Function
end.
...........................................................................................................
The test database contains just one table with the same structure as the one in
my real application. The DLL to make the table is
CREATE TABLE `Comments`
(
SEFRowID INTEGER,
CommentDateTime TEXT,
CommentAuthor TEXT,
CommentDeptID INTEGER,
CommentText TEXT,
CommentGrade INTEGER,
KeyCriteriaPhrases Text,
PRIMARY KEY(SEFRowID,CommentDateTime)
);
CREATE INDEX indxSefRowID
ON Comments (SEFRowID);
Other related posts:
