My First SQLite Project
Moderator: Rathinagiri
-
- Posts: 47
- Joined: Tue Jul 14, 2009 1:14 pm
- Location: Mar del Plata, Argentina
Re: My First SQLite Project
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
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
Re: My First SQLite Project
Thanks for your comment. But - too much theory and any example. All of this text is about file locking, not record locking.
Re: My First SQLite Project
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
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.
Web Apps consultant/architect/developer.
Re: My First SQLite Project
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
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
- 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
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.
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.
South or North HMG is worth.
...the possibilities are endless.
Re: My First SQLite Project
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"
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
- 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
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.
South or North HMG is worth.
...the possibilities are endless.
Re: My First SQLite Project
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
best regards, Marek
- 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
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.
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.
South or North HMG is worth.
...the possibilities are endless.
Re: My First SQLite Project
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.
Now, I'll try move my project to MySQL database.