My First SQLite Project

[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

[mol]

Thanks for your comment. But - too much theory and any example. All of this text is about file locking, not record locking.

[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

[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

[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.

[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"

[Rathinagiri]

See, in this case, we can use exclusive lock, so that nobody can even read until you change the VAT %.

[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

[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.

[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.