SELECT en SQLite

[jorge.posadas]

Grupo,

Estoy trabajando con SQLITE, y estoy haciendo este tipo de pruebas:

1. Estoy insertando 10000 registros en la tabla CUSTOMER
2. Al mismo tiempo estoy leyendo la tabla CUSTOMER, al mismo tiempo que se están insertando los 10000 regustros.

PROBLEMA: Cuando se ejecuta el SELEC Cusmter_id, Customer_Name from CUSTOMER me manda un error diciendome que "DATABASE IS LOCKED", y según he leído SQLITE la pueden leer varios usuario, PERO solo UNO la actualiza y es precisamente lo que estoy tratando de simular con mi ejemplo, sin embargo y a pesar de que solo un usuario esta insertando y otro leyendo, me da el error al tratar de leer la tabla
PREGUNTA: ¿Alguien ha trabajado con SQLITE?,

Por favor si alguien lo ha hecho, mande un correo para indicarme qué estoy haciendo mal,


De antemano agradezco la ayuda

[mustafa]

Hola jorge.posadas
El amigo Rathinagiri es el experto en temas de Sqlite
te mando estas entradas haber si te pueden servir

http://hmgforum.com/viewtopic.php?f=15& ... lit=sqlite
http://hmgforum.com/viewtopic.php?f=9&t ... ite#p44206
http://hmgforum.com/viewtopic.php?f=37& ... ite#p28208
http://hmgforum.com/viewtopic.php?f=6&t ... ite#p12091
http://hmgforum.com/viewtopic.php?f=5&t ... ite#p32642
http://hmgforum.com/viewtopic.php?f=40& ... ite#p40397

saludos
Mustafa

[Rathinagiri]

Hi,

How do you do your insert?

Within a 'begin' and 'end' transaction?

Further, SQLite is a file based database. We can do multiple select at the same time. But write only once even though we may want to write in different tables. It doesn't have any table level or record level locks. It is a database level lock. So, when you are writing on the database, you can not read.

If you really want to have multi-user system with high concurrency, you can opt for MariaDB/MySQL anyway.

For your kind information, this is an extract from the SQLite documentation.

Situations Where A Client/Server RDBMS May Work Better

Client/Server Applications

If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.

A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network.

High-volume Websites

SQLite will normally work fine as the database backend to a website. But if the website is write-intensive or is so busy that it requires multiple servers, then consider using an enterprise-class client/server database engine instead of SQLite.

Very large datasets

An SQLite database is limited in size to 140 terabytes (247 bytes, 128 tibibytes). And even if it could handle larger databases, SQLite stores the entire database in a single disk file and many filesystems limit the maximum size of files to something less than this. So if you are contemplating databases of this magnitude, you would do well to consider using a client/server database engine that spreads its content across multiple disk files, and perhaps across multiple volumes.

High Concurrency

SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writer queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

[jorge.posadas]

Mustafa y Rathinagiri

Agradezco su respuestas y con eso me ha quedado claro que NO se puede usar un SELECT al mismo tiempo que un INSERT, UPDATE, DELETE.

Por lo que ya estoy trabajando en :
1.- Prohibir conflictos al intentar manipular la base de datos tanto para INSERT, DELETE, UPDATE, SELECT
2.- Implementar que no se pueda actualizar al mismo registro.

Estoy consciente y extremadamente consciente que SQLITE no es multiusuario sin embargo con algunas pruebas he logrado inhibir accesos simultáneos a la base de datos, en el evento de INSERT (probaré con UPDATE Y DELETE), después de eso estaré trabajando en evitar que se edite el mismo registro.

Estoy haciendo esto ya que hace mucho tiempo deje usar DBF y por otro lado varios clientes (como suele suceder) no quieren o no tiene la capacidad económica como para invertir en la compra de un servidor, ni tampoco en licencias, etc. etc.

Y por otro lado SQLITE no necesita de mantenimiento como lo requiere un SQL SERVER o MySQL o MariaDB u otras bases de datos similares.

Espero tener este sistema para compartirlo con ustedes.

Y Agradezco la ayuda y sugerencias

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TRADUCCION DIRECTA CON GOOGLE TRANSLATOR:
I appreciate your answers and that it's clear that you can not use a SELECT while an INSERT, UPDATE, DELETE.

So I'm working on:
1. Prohibit conflicts when trying to manipulate the database for both INSERT, DELETE, UPDATE, SELECT
2. Implement that it can not update the same record.

I am aware and acutely aware that SQLITE is not multiuser however some tests have achieved inhibit simultaneous access to the database in the event INSERT (I try to UPDATE and DELETE), after that I will be working to prevent edit the same record.

I'm doing this as long stop using DBF and on the other hand several clients (as often happens) do not want or do not have the financial capacity to invest in buying a server, or in licensing, etc. etc.

And on the other side SQLITE not need maintenance as required by a SQL Server or MySQL or MariaDB or other similar databases.

I hope to have this system to share with you.

And I appreciate the help and suggestions

[dragancesu]

SQLite is an easy way to move to SQL not perfect
If possible send the code to a real problem, like our theorizing that (like you) without much success