I'm wondering what logic I should employ to prevent mutual exclusion
occurring.
I'm using SQLite to record an audit trail from a web application - I count
the number of edits, searches and the maximum number of concurrent users.
Each user opens a connection to the database for the duration of their
session.
The audit logic is as follows;
begin transaction
select total from useractivity where event= .....
If lRows = 0 Then
insert into useractivity ......
Else
update useractivity .....
End If
commit transaction
What is happening is that the insertion/update in thread A locks the
database. Before thread A gets the chance to commit the transaction, thread
B also attempts an update/insertion but fails because the database is
locked. However, this causes thread A to also be locked out from committing
the change. I don't have any recovery code in the logic so we're then in a
situation whereby the database is permanently locked for everyone, or until
all the sessions die and each thread closes it's handle.
This can't be right can it ?
Where should I do my busy checking and recovery logic to prevent this mutual
exclusion?
Steve
