My First SQLite Project

You can share your experience with HMG. Share with some screenshots/project details so that others will also be benefited.

Moderator: Rathinagiri

ClaudioGalera
Posts: 47
Joined: Tue Jul 14, 2009 1:14 pm
Location: Mar del Plata, Argentina

Re: My First SQLite Project

Post by ClaudioGalera »

Hi !!! :)

Some time ago I read this about File Locking And Concurrency In SQLite, maybe help with something

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

and this

(in spanish)
http://dbnaut.com/sqlite/nota-muy-simpl ... -sqlite-3/

(google translate English) http://translate.google.com.ar/translat ... ry_state0=

Claudio
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: My First SQLite Project

Post by mol »

Thanks for your comment. But - too much theory and any example. All of this text is about file locking, not record locking.
User avatar
apais
Posts: 440
Joined: Fri Aug 01, 2008 6:03 pm
DBs Used: DBF
Location: uruguay
Contact:

Re: My First SQLite Project

Post by apais »

PMFJI but...

An sql engine is a set oriented one. You work with many rows at a time and snapshots of the actual data.
You cannot access to data diretly and locking scheme is managed by the engine itself.
So you should never worry about record locks.
You hace to rethink your data retrieve - data update - data saving sequences on your program.

Regards
Angel
Angel Pais
Web Apps consultant/architect/developer.
HW_apache (webserver modules) co-developer.
HbTron (Html GUI for harbour desktop hybrid apps) co-developer.
https://www.hbtron.com
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: My First SQLite Project

Post by mol »

I'm not worry about record locks - rather i think about inconsistent data in multiuser environment.

In my humble opinion, we can use sqlite for single user programs.

I've looked at oracle and mysql:
SELECT command has record locking - via SELECT .... [ FOR UPDATE | LOCK IN SHARE MODE ]

http://dev.mysql.com/doc/refman/5.0/en/ ... reads.html
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: My First SQLite Project

Post by Rathinagiri »

I have a doubt.

Table lock instead of record lock would provide more consistency than a set of records. Isn't it?

Consider this situation: (This is my imagination and understanding about record locks. Don't hesitate to point out errors)

In a super market there are 15 PoSTs. All of them are entering invoices. Last entered invoice number is say 3545. The next invoice number (Primary Key) should be 3546. At the time of saving everybody is having record lock and not table lock. Therefore everybody can save their record as a new one. If 3 of them approach the database at a time, how would the situation be handled since everybody is having record lock.

In the case of table lock. The table will be locked for writing for the first come first served basis. Nobody is having any lock until they start the transaction to save the invoice data. Once the invoice data is saved, autoincrement function would give the invoice number as 3546 and his lock is released immediately to give room for the next person.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: My First SQLite Project

Post by mol »

Hi, Rathi!
Bad example, in my opinion!

Imagine th situation, when one person must correct data about VAT %tax of product - hi gets information about product on the screen and edits it. And the second operator issues an invoice - he gets wrong % of VAT - without halting an getting record from table!

In my opinion, adding new data to the table does not cause problems. Problems appear it the time, when tow or more people want to work with the same record - without lock they all get old data from table, and you don't know which one will save record first and which will save as last - and what is placed in the database.

I think people from Oracle and Mysql saw that problem and add phrase "FOR UPDATE | LOCK IN SHARE MODE"
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: My First SQLite Project

Post by Rathinagiri »

See, in this case, we can use exclusive lock, so that nobody can even read until you change the VAT %.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: My First SQLite Project

Post by mol »

OK, but exclusive lock of the table causes, that nobody can't do anything - operator can lock database for not known time - maybe phone call, maybe WC :)

best regards, Marek
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: My First SQLite Project

Post by Rathinagiri »

Here is where the difference arises. The operator can't hold the lock for a long time. The lock is allowed only between Start and End transaction. You need to start the transaction only after the decision to change had undergone (may be pressing save button).

However, I understand the situation well and the advantages of Record Locking. In the 'Record Lock' thing, only the particular record is locked and other records in the table are given access to read/write. :)

This article had thrown some light for me in this area.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: My First SQLite Project

Post by mol »

Thanks Rathi, I've read this text and I'm waiting for new version ofsqlite.
Now, I'll try move my project to MySQL database.
Post Reply