Hi Richard,
I haven't had any response from anyone else so I thought I'd try you direct
Richard.
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(timestamp,datasource,event,total) values
......
Else
update useractivity set total=total+1 where datasource= .....
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. This causes thread A to 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 locked for everyone,
permanently 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
-----Original Message-----
From: Steve O'Hara [mailto:[EMAIL PROTECTED]
Sent: 08 September 2004 01:42
To: [EMAIL PROTECTED]
Subject: [sqlite] Locking in 3.0.6
I'm having difficulty understanding the locking regime in SQLite 3.
I open the same database with 2 processes (sqlite3.exe for instance).
In process 1 I begin a transaction and insert a row.
In process 2 I also begin a transaction and insert a row - this fails on the
insert with an expected "database is locked" error
In process 1 I now do a commit, but this now also fails with "database is
locked" !!
Am I missing something here or is that a little odd ?
I assumed that the first thread to get a WRITE lock would be the controlling
thread and the database would be locked until that thread relinquished the
lock by calling commit or rollback. How can it be that a writer that has a
change pending is blocked from committing that change by another thread that
hasn't actually attempted a change ?
Steve
