• Welcome, Guest. Please login.
 

Transaction processing using SQLITENING in client server environment

Started by John Lever, September 18, 2009, 07:56:27 AM

Previous topic - Next topic

Matt Humphreys

Hey Paul thanks for that... I get where it's going tho I kinda like the optimistic lock scenario for the particular app I want to upgrade.
So now I'm being greedy....Fred is there a way to have a similar function to slLockSel (or perhaps a flag included in the slSel function) so that one could send "BEGIN IMMEDIATE" and if successful, optionally execute the "SELECT..." statement, saving a trip to the server?

Fred Meier

QuoteIs there a way to have a similar function to slLockSel (or perhaps a flag included in the slSel function) so that one could send "BEGIN IMMEDIATE" and if successful, optionally execute the "SELECT..." statement, saving a trip to the server?

Sounds like a good idea.  I will try to include within the slLockSel rut. 
I will keep you informed as to progress. 

Paul Squires

Maybe I am missing the point but why not just send all three statements in one shot to the server via slSel like I indicated in my previous post (concatenating the BEGIN/SELECT/END statements into one string). If the BEGIN IMMEDIATE fails then the server will report back the error that it could not get exclusive access and therefore will not execute the SELECT anyway.



Fred Meier

slSel will process only one SQL statement.  It does only the SQLite
Prepare.  The SQLite Step and Finalize are done in slGetRow.  slExe does
the SQLite Exe which does a Prepare, Step, and Finalize for each SQL
statement. 

If you send slSel more than one SQL statement, only the first is Prepared
and then it is not finalized. 

So it is not possible to do more than one SQL statement using slSel and
although you can do a Select statement using slExe, you will not be able
to obtain the results. 

I have considered adding code to insure that the ucase$ first seven bytes
in a slSel statement must be "SELECT ".   Or at least I will change the doc
to explain the restriction.    What are your thoughts?

So to do a Begin and Select in one trip to server will require using
something like slLockSel. 

Paul Squires

Ahhhhhhh.... yes, that makes sense for sure. I should have realized that. Duh!  :)

I think that a simple change in the docs would be sufficient.

I think the idea for the new slLockSel is a good one and will be a nice addition to the library.


Matt Humphreys

Thanks Fred you're a champion...agree with Paul on the new slLockSel function...having both would be invaluable.
and including something akin to your comments below above in the docs would, I think be sufficient.

John Lever

Dear Paul, Fred and Mark,

The possible update to SQLITENING looks as though we will end up with something better than the commercial databases out there.

Thanks for all the feedback so far - I have understood most of it - but a bit unclear on some things.

I will try and outline what I understand and then what I do not (excuse the simplistic approach) :

1. No locking is best way to do a design if possible.

2. A new function say called slLOCK  will allow several database functions to be performed within some named bracketing functions for example SLLOCKBegin and slLOCKEnd - the lock function will normally lock out any other access to a row  in the database (or if required to the database).

3. An exaomple could be :
slLOCK
slLOCKBegin
Use slExe or slGetRow to read a row from a database
Update fields in the row (PB 9 code)
Use some function eg slBuildInsertOrUpdate or slExe to write the row back to the database
slLOCKEnd

Everything beween the Begin and End will cause any other client using the slLOCK function at the same time on this row to return an error saying 'busy'. This means that the functions within the begin and end can be regarded as a single atomic function (run to dompletion and none divisable by anything else except a system crash ).

4. The new functionality will survive system crash with no setup required by the user on restart ie restart can always happen with no user intervention to sort out the database.

What I do not understand is the 3 way discussion between Fred, Paul and Mark involving SELECT - where Paul says AHHH I understand on a reply from Fred - I did not get it at all - any possibilty of an example to make me understand ?

Best Regards,
John Lever

Fred Meier

John, the slLock concept is very fluid at this time while I am designing
and developing it, so until it firms up I am not going to address all your
concerns.  When slLock is done I will respond to your questions.

QuoteWhat I do not understand is the 3 way discussion between Fred, Paul and
Matt involving SELECT - where Paul says AHHH I understand on a reply from
Fred - I did not get it at all - any possibilty of an example to make me
understand ? 
Paul thought is was possible to include more than one SQL statement,
separated by semicolons, in a slSel command.  It is not possible.  Only
one SQL statement can be processed by slSel and that statement should
always be the Select or Pragma statement.  They are the statements that
return row(s) of data.  Multiple statements can be include in the slExe command.

Matt Humphreys

In moving the forum to sqlitening.com , my reply #28 has gone missing which may have thrown you a curve ball John. Just some conceptual code on optimistic locking.

It obviously didn't make the jump to 'Lite speed! ;D

Matt Humphreys

another thought Fred...
In looking at optimistic locking where I am only looking at a single record to edit, it strikes me that using slSelAry would be better than

slSel
slGetRow
for each column do slF

because it is only 1 trip to the server.

In the context then of sending a "BEGIN IMMEDIATE and the SELECT statement in one trip to server,  a function along the lines of slLockSel say called slLockSelAry would also appear beneficial.

(I've completely ignored slSelAry in the past. I guess it was a case of starting to learn with slSel and it worked fine so why look further.)


Fred Meier

slSelAry does not result in fewer trips to the server than slSel.  The main use/purpose for slSelAry is when you need to work randomly within a set of returned rows.  Most of the time both slSel and slSelAry result in only one trip to the server. 

See http://www.sqlitening.com/support/index.php?topic=3128.0

QuoteIn the context then of sending a "BEGIN IMMEDIATE and the SELECT statement in one trip to server,  a function along the lines of slLockSel say called slLockSelAry would also appear beneficial.
I will try to allow both slSel and slSelAry to set database locks like Begin Immediate in one server trip in the next release. 


Matt Humphreys

thanks for tutorial Fred.
That and looking at 'BuildRowDataChunk' in source, it's clearer now.
I see the first RDC is 250k anyway.
I wouldn't mind having %gkMaxChunkSize in the config file to play with but not mission critical.

You've put a lot of thought into this....appreciated.

Fred Meier

Good catch.  I forgot about the first RDC be smaller.  I changed the post.

If you want to play with different size RDC now just recompile SQLiteningServer.

Rick Kelly

This looks very good to me, so much so that I immediately stopped designing my own routines. Two frequent uses I have is to set a "psuedo-lock" with no SELECT statements that I use to serialize access to certain portions of my applications and, I don't rely on generated rowids for foreign keys, I have a "next sequence" table with each row tied to a particular table that I retrieve, add 1, update and then use that as my distinct row key.

Since I like to know if a row had been updated by another user since it's retrieval for editing, it seems the best way is to have an integer that gets updated when changing the row and I can retrieve it and then compare before proceeding and decide on a best course of action.

Looking forward to the Nov release and some fun testing.

Rick Kelly ;D