[yunqa.de] Re: How to correctly avoid 'database is locked' in multi user environment
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Fri, 11 Jan 2013 09:32:29 +0100
On 11.01.2013 08:43, H M wrote:
> In case anyone else is following this, below is my solution (slightly
> adatped from the demo Busy_timeout.dpr)
> My application could generate insert/update/delete sql with a varying
> number of statements. So I replaced all my
>
> DISQLite3Database1.execute(sql);
>
> statements with a call to the following function.
>
> ......................................................................................................
>
> function TDataModSP.ExecuteSQLWithLockedDBTimeOut(sql: string): integer;
> var
>
> FullSQL : string;
>
> begin
> //since we are goint to wrap the SQL inside a transaction, the supplied
> SQL needs to end
> //with a semicolon. Make sure it does
> 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;' ; //generate
> transaction sql
> try
>
> try
>
> DISQLite3Database1.Open;
> sqlite3_busy_timeout( DISQLite3Database1.Handle,
> BUSY_TIMEOUT_TIME); //set busy timeout to const value
>
> DISQLite3Database1.Execute(FullSQL); //try to run the sql
> until timeout
> Result := SQLITE_OK; //if we get to here without exception
> then the data was saved
>
> except
>
> on e: ESQLite3 do // The DISQLite3 database engine raised an
> error, return the specific error code
>
> begin
> Result := e.ErrorCode;
> DISQLite3Database1.Rollback; //undo all the bits of
This DISQLite3Database1.Rollback call will be ineffective since there
was no previous call to DISQLite3Database1.StartTransactios. Reason is
that these methods maintain their own transaction counter to mimic
nested transactions which are yet not currently supported by SQLite.
Instead of modifying your SQL, I suggest this pseudocode on your
unmodified SQL:
DISQLite3Database1.StartTransaction;
try
DISQLite3Database1.Execute(SQL);
DISQLite3Database1.Commit;
except
DISQLite3Database1.Rollback;
end;
Or use SAVEPOINT instead of transaction to allow transaction nesting:
DISQLite3Database1.Execute('SAVEPOINT some_unique_savepoint_name');
try
DISQLite3Database1.Execute(SQL);
DISQLite3Database1.Execute('RELEASE some_unique_savepoint_name');
except
DISQLite3Database1.Execute('ROLLBACK TO some_unique_savepoint_name');
end;
Ralf
> the transaction we did get done in case...
> end;
> //...its important we do it all or none of it
>
> else //for any other error simply re- raise it so it gets
> handled elsewhere
>
> raise
>
> end //try - except
>
> finally
>
> DISQLite3Database1.Close; //close the database again
>
> end; //try - finally
> end; //function
> .......................................................................................
>
> Then in the application the function is called with lines like
>
> SQLiteReturnValue := DataModSP.ExecuteSQLWithLockedDBTimeOut(sql);
> if SQLiteReturnValue = SQLITE_OK then
>
> showmessage('Data saved OK');
>
> else
>
> case SQLiteReturnValue of
>
> SQLITE_BUSY : showmessage ('Data not saved, please try again') ;
> SQLITE_LOCKED : showmessage ('Data not saved, please please try
> again in a a couple of seconds');
>
> Else
>
> showmessage ('Data not saved, error code: '
> inttostr(SQLiteReturnValue ));
>
> end; //case
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
