SQLitening Support Forum

Support Forums => You've got Questions? We've got Answers! => Topic started by: cj on April 13, 2014, 06:57:10 AM

Title: Are SELECT statements SHARE LOCKED?
Post by: cj on April 13, 2014, 06:57:10 AM
Somebody could be updating the database while you are creating a recordset.
Should BEGIN IMMEDIATE be used before all select statements?
result = slSel(SQLStatement,0,"B1")     
Title: Re: Begin immediate needed before SELECT statement?
Post by: D. Wilson on April 15, 2014, 12:01:34 AM
I only use begin/end in transactions. This 'forces' then database engine to lock the database and perform all the writes/updates at one time without opening/closing the database for each write/update. I don't feel there is a need to use begin/end when reading data from the database.  The reads take fractions of a second where as if you are updating 25000 records it could take a second or two. I also feel that sqlitening helps manage multiple users who are trying to access the database at the same time. Personally I never leave an open connection to the server. I open/close the connection when I need to access the database. In my experience this has worked excellent for my needs.
Title: Share locked?
Post by: cj on April 15, 2014, 12:12:01 AM
Thank you.  I do the same thing, but it might be possible for someone to update an account balance
that a recordset already processed and come up with a total that wouldn't be correct.
The account balances would not add up to the grand total.
Fred added a "B" option to the select statements and I wonder if it was for this reason.
The performance doesn't seem to drop because you have exclusive access for a moment.

Title: Share locked appears to be automatic with SELECT
Post by: cj on April 15, 2014, 11:56:35 AM
If I read this correctly a shared lock is performed with SELECT so BEGIN IMMEDIATE should not be used.


https://www.sqlite.org/lockingv3.html

If multiple commands are being executed against the same SQLite database connection at the same time, the autocommit is deferred until the very last command completes. For example, if a SELECT statement is being executed, the execution of the command will pause as each row of the result is returned. During this pause other INSERT, UPDATE, or DELETE commands can be executed against other tables in the database. But none of these changes will commit until the original SELECT statement finishes.