• Welcome, Guest. Please login.
 
October 25, 2021, 09:26:06 PM

News:

Welcome to the SQLitening support forums!


Locked database during a SELECT *

Started by Fim, February 14, 2011, 02:44:18 PM

Previous topic - Next topic

Fim

February 14, 2011, 02:44:18 PM Last Edit: February 14, 2011, 03:32:07 PM by Fim
As far as I can see, the database-file is locked for INSERT and UPDATE during the time another process is doing a
Fim W

Fred Meier

Yes, but works differently in local vs remote mode.

Local Mode
The firt slGetRow after the slSel will set the lock.  No updates from
another process are allowed until the set is closed.  Updates from same
process are allowed. 

Remote Mode. 
The slSel statement is sent to the server where all of the rows are
retrieved and cached and the set is closed before returning to the client. 
So updates are allowed while the client are getting the rows from cache. 
This is required so that a server shared database will not be locked while
a client is "slowly" processing a set. 

In either mode, no updates can occur while the set is being retrieved
from the database. 

Fim

February 17, 2011, 03:51:58 PM #2 Last Edit: February 18, 2011, 03:59:28 AM by Fim
Remote Mode only.
Ok, suppose I have a program that will do some computation for each part in the table
Fim W

Rolf Brandt

Fred may correct me if I say something wrong here.

In your scenario you would call the server 4,000 times with slSel. If I understand things right when the server gets a slSel the result is written to a cache file. This is done extremely fast, even with 100,000 rows. slGetRow then fetches the data from the cache. Like this the database itself is locked only for a very short time. 4,000 slSel's would create lock/unlock sequences, more network traffic, and lower performance.

I could be wrong of course, I did not look into the source code of the server. It's just how I understand it.
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Fim

"Like this the database itself is locked only for a very short time."
Short time in my aplication must be <0.5 second.
I tested SELECT * FROM PARTS;  with 53,000 rows, output to a file, about 10 seconds.
So, if I change the LIMIT to 1000, the lock time will be less then 0.5 second and I will call the server with slsel about 50 times. How about that?
Fim W

Rolf Brandt

You are right, Fim. The 10 sec are a normal figure in a LAN. But most of this time is consumed by the slGetRow loop transporting the data via the network and putting them into a grid (listview or so). I am petty sure the execution of the slSel is less than 0.5 sec.
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Fim

The test with SELECT * FROM PARTS was done local, not over the lan. It was done from the command prompt with SQLITE3.EXE.  And data was just put into a file, noting more. But the table PARTS has 135 columns so 10 second is very god I think. Therefore I think the execution time of the slSel is 10 seconds.
I will do some test with my method, doing slsel with limit 1000 and try to do updates from another program and see if there are any locking problems. I will report the result.
Fim W

Rolf Brandt

I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Fred Meier

February 18, 2011, 01:20:52 PM #8 Last Edit: February 18, 2011, 01:25:20 PM by Fred Meier
Understand Locking (aka IsBusy)
Getting IsBusy returned from SQLite is like catching a cold, if you hang
around long enough it will happen.  Yes, you can design and program to
minimize but you probably can't eliminate.  For complete understanding of
SQLite locking see http://www.sqlite.org/lockingv3.html#shared_lock
IsBusy is not returned as soon as a conflict is detected.  SQLite will
continue to retry until Busy TimeOut is reached.  This value is defaulted
to 10 seconds but can be changed with slSetProcessMods.  When IsBusy is
returned SQLitening will tell the user that the database is busy and would
they like to retry.  You can tell SQLitening to not handle IsBusy with
slSetProcessMods. 

Timings
I increased the Sample.Db3 database to 100K records and did following:
   Select * from Parts returned all 100K rows in .16 seconds.
   Select Price from Parts returned all 100K rows in .08 seconds.
This was done in local mode but since the SQLite engine runs local on the
server, the times are valid.  The Parts table has only nine columns but
notice by asking for only one column cut the time in half.  If is VERY
important to only ask for the columns you must have. 

Summary
Your attempt to break a 100K query into parts has merit but there may be problems:
1. Your multi part query may not be homeogenous if updates occur between queries.
2.  I'm not sure your "Break Into Parts" plan will ALWAYS return you all
the records.  SQL engines have very, very, very complex query planners.  See
http://www.sqlite.org/optoverview.html.  You tell SQL what you want and it
determines how to get it.  You can influence SQL but you can not tell it
how.  Quote from SQLite doc about Select. 
QuoteThe SELECT statement is the most complicated command in the SQL language. 
To make the description easier to follow, some of the passages below
describe the way the data returned by a SELECT statement is determined as
a series of steps.  It is important to keep in mind that this is purely
illustrative - in practice neither SQLite nor any other SQL engine is
required to follow this or any other specific process.
See http://www.sqlite.org/lang_select.html for more about SQLite Select.

Suggestions
Start by selecting all records requesting the minimum number of columns so
that the query will run very fast on server.  If IsBusy's become a problem
then seek a better solution.  In other words don't try to fix what may not
be broken. 

Someting else to consider: Breakig a long query into mulit short queries
will always cause the database to be "locked" for a longer total time and
consume more server resources. 

Rolf Brandt

Thanks for the clarification, Fred. Could you measure the time for - let's say - 1,000, 20,000, and 50,000 records? Would be interesting see how much time slSel needs for that.

Thanks in advance.
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Fred Meier

Select * from Parts
    10,000 = .013132270 Sec
    20,000 = .025596829 Sec
    40,000 = .051212560 Sec
    50,000 = .080564387 Sec
   100,000 = .163080897 Sec


Rolf Brandt

Thanks for those figures, Fred. Very impressive. Shows how fast the database operations on the server side are.
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Bern Ertl

Quote from: Fim on February 17, 2011, 03:51:58 PM...
Ok, suppose I have a program that will do some computation for each part in the table