• Welcome, Guest. Please login.
 
May 07, 2021, 09:54:08 PM

News:

Welcome to the SQLitening support forums!


Are SELECT statements SHARE LOCKED?

Started by cj, April 13, 2014, 06:57:10 AM

Previous topic - Next topic

cj

April 13, 2014, 06:57:10 AM Last Edit: April 15, 2014, 11:59:22 AM by cj
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")     

D. Wilson

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.

cj

April 15, 2014, 12:12:01 AM #2 Last Edit: April 15, 2014, 11:58:53 AM by cj
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.


cj

April 15, 2014, 11:56:35 AM #3 Last Edit: April 15, 2014, 12:01:05 PM by cj
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.