[quote=303479:@James Dooley]No not in my book! Simply add a version control field to the record either a time stamp or my preferred way an int field. Then update statement is:
UPDATE … version = version + 1 WHERE … (version = ?)
If rows affected is zero it means someone else updated the record while you were editing.[/quote]
a field in the table does no good if the act of reading the table is what is leading to the issue…
the semaphore says “HANG ON THE TABLE IS BUSY DO NOT EVEN TRY TO ACCESS IT”… it is a form of TABLE LOCK, what you are describing is a RECORD LOCK. Two totally different things.
but then as mentioned SQLite is not the best choice for what the poster is wanting to do, yet he seems bent on making it work.
that being said… I’ve made my suggestions, as have others… lead a horse to water etc.
It wasn’t as painful as I thought to convert to Valentina SQLite Server. I created a method to replace my attach statements. I’m wondering now if I’m going to run into a problem binding everything as text and using databasefield.stringvalue? I don’t use any blobs but expect those would be a problem with this method.
[code]Function PutInDB(extends db as VSQLiteDatabase, Table as String, rs as RecordSet, Delete as Boolean = False, ColNames as String = “”) As string
if rs <> Nil and rs.RecordCount> 0 Then
db.SQLExecute(“BEGIN TRANSACTION”)
if Delete Then db.SQLExecute(“DELETE FROM " + Table)
if db.Error Then
dim msg as String = db.ErrorMessage
db.SQLExecute(“ROLLBACK”)
Return msg
end if
dim Values as String
dim Temp(), Temp1() as String
if ColNames = “” Then
for i as Integer = 1 to rs.FieldCount
temp.Append rs.IdxField(i).Name
temp1.Append “?”
next
ColNames = Join(Temp,”,")
Else
temp1 = Split(ColNames,",")
for i as Integer = 0 to temp1.Ubound
temp1(i) = “?”
next
end if
Values = Join(Temp1,",")
do
dim ps as SqlitePreparedStatement
ps = db.Prepare("INSERT INTO " + Table + "(" + ColNames + ") VALUES(" + Values + ")")
for i as Integer = 1 to rs.FieldCount
ps.BindType(i-1,SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(i-1, rs.IdxField(i).StringValue)
next
ps.SqlExecute
if db.Error Then
dim msg as String = db.ErrorMessage
db.SQLExecute("ROLLBACK")
Return msg
end if
rs.MoveNext
loop until rs.EOF
db.SQLExecute("COMMIT TRANSACTION")
if db.Error Then
Return db.ErrorMessage
else
Return "ok"
end if
Would SQLite still be a bad Idea for concurrent users if they were doing read only access? For example accessing a catalog or product setup information? I’m thinking 2-10 users.
On a single machine with multiple processes each with a separate connection you can do this
But dont share one db file with many writers on some network file systems
It’s why we and sqlite have been saying “dont use it this way”
(see my previous comment)
Thanks Norman. Your previous comment among others is what help me decide switching to a server app was necessary.
I switched this particular project to Valentina SQLite Server and then to PostgreSQL. I have another app that might still be better off with SQLite.
So what causes the problem isn’t so much more than one writer, but rather writers from more than one machine. Thanks for the clarification.
On another note talking about connections. Is it a problem to have several separate threads in the same app writing simultaneously to the same connection? I don’t think I do that anywhere but thought I would ask. In other words would it make a difference if you create a new connection vs. pass the database variable from the main thread.
Because of how SQLite uses file system locks IF the server OS doesn’t do it “right” a single writer can cause issues too IF the db file is on one machine & the “client” is on another that has that servers file system mounted
This is the relevant part of the FAQs SQLite Frequently Asked Questions