1. olive
  2. as SQLite DB Server
  3. 月, 1月 18 2016, 09:48 AM
  4.  メールで購読
Hi all,

I do a test with Valentina Server for SQLITE (licence 5 connexions) and Xojo.
It's a Web app.

With a session, I run an import of one million lines.
During this import, another session tries to import a single line.
Of course, the second can not be imported because the engine is already locked on the first writing.

BUT it seems that the server does not respecte the timeout. This timeout is 10 seconds. Usually SQLITE refuses writing at the end of this timeout. With valentina server, the writing is rejected immediately, without waiting for the timeout.

I forgot something ?

Here the project:
https://dl.dropboxusercontent.com/u/24945071/essaiSqlVALENTINA.xojo_binary_project

- Launch the project
- Run a session in a web browser (http: //127.0.0: 9000)
- Open another session on a web browser (http: //127.0.0: 9000)
- On the first session, click "insert". The import starts and will take several minutes.
- Meanwhile, on the second session,click "go page2" then "Insert 1 line". Writing is denied immediately without waiting for the timeout.

I did the test on Windows 10, Xojo 2015R4. I downloaded the latest version of Valentina Server yesterday. You can view the logs in the Windows event viewer.
コメント
There are no comments made yet.
olive 承諾済みの回答
In fact, if I do a "SELECT 1 LINE" instead of "INSERT 1 LINE" while the big import is not complete, the database is also locked, which is not normal, because I used the "PRAGMA journal_mode=WAL;"

So, it should be possible to *simultaneously* have 1 write and several reads.

I realize that I use Sqlite commands/pragmas, while perhaps I should use the commands from the valentina server. I check in the doc.
コメント
There are no comments made yet.
olive 承諾済みの回答
I did not find in the server doc something that could solve this problem.
コメント
There are no comments made yet.
Ruslan Zasukhin 承諾済みの回答
Hi Oliver,

while Ivan is offline ... in Ukraine snow storm now, troubles with inet and electricity...

Can you point in docs of SQLite this parameter?
to make sure we discuss all the same
コメント
There are no comments made yet.
Ruslan Zasukhin 承諾済みの回答
also, I remember that SQLite allows to control many own params via SQL and pragmas.
This can help?
コメント
There are no comments made yet.
olive 承諾済みの回答
Yes,

For WAL :
https://www.sqlite.org/isolation.html
http://www.sqlite.org/wal.html

Since version 3.7.0, SQLite also supports "WAL mode". In WAL mode, changes are not written to the original database file. Instead, changes go into a separate "write-ahead log" or "WAL" file. Later, after the transaction commits, those changes will be moved from the WAL file back into the original database in an operation called "checkpoint". WAL mode is enabled by running "PRAGMA journal_mode=WAL".


WAL mode permits simultaneous readers and writers. It can do this because changes do not overwrite the original database file, but rather go into the separate write-ahead log file. That means that readers can continue to read the old, original, unaltered content from the original database file at the same time that the writer is appending to the write-ahead log. In WAL mode, SQLite exhibits "snapshot isolation". When a read transaction starts, that reader continues to see an unchanging "snapshot" of the database file as it existed at the moment in time when the read transaction started. Any write transactions that commit while the read transaction is active are still invisible to the read transaction, because the reader is seeing a snapshot of database file from a prior moment in time.

An example: Suppose there are two database connections X and Y. X starts a read transaction using BEGIN followed by one or more SELECT statements. Then Y comes along and runs an UPDATE statement to modify the database. X can subsequently do a SELECT against the records that Y modified but X will see the older unmodified entries because Y's changes are all invisible to X while X is holding a read transaction. If X wants to see the changes that Y made, then X must ends its read transaction and start a new one (by running COMMIT followed by another BEGIN.)

Another example: X starts a read transaction using BEGIN and SELECT, then Y makes a changes to the database using UPDATE. Then X tries to make a change to the database using UPDATE. The attempt by X to escalate its transaction from a read transaction to a write transaction fails with an SQLITE_BUSY_SNAPSHOT error because the snapshot of the database being viewed by X is no longer the latest version of the database. If X were allowed to write, it would fork the history of the database file, which is something SQLite does not support. In order for X to write to the database, it must first release its snapshot (using ROLLBACK for example) then start a new transaction with a subsequent BEGIN.

If X starts a transaction that will initially only read but X knows it will eventually want to write and does not want to be troubled with possible SQLITE_BUSY_SNAPSHOT errors that arise because another connection jumped ahead of it in line, then X can issue BEGIN IMMEDIATE to start its transaction instead of just an ordinary BEGIN. The BEGIN IMMEDIATE command goes ahead and starts a write transaction, and thus blocks all other writers. If the BEGIN IMMEDIATE operation succeeds, then no subsequent operations in that transaction will ever fail with an SQLITE_BUSY error.


Ruslan Zasukhin wrote:

also, I remember that SQLite allows to control many own params via SQL and pragmas.
This can help?


Yes, I used the pragmas in the test project:

http://www.sqlite.org/pragma.html#pragma_journal_mode
http://www.sqlite.org/pragma.html#pragma_busy_timeout

Good luck for the storm!
コメント
There are no comments made yet.
Ruslan Zasukhin 承諾済みの回答
Hi Oliver,

we have learned your example and Ivan have made couple of fix in VServer to satisfy your wish.

1) this will be available in the tomorro's 6.3b2 build

2) You should install and use not regular VServer, but VServer_MT.
VServer_MT is our special build with more threading features enabled.
コメント
There are no comments made yet.
Ruslan Zasukhin 承諾済みの回答
Hi Oliver,

You can get 6.3b2 build here:
http://www.valentina-db.com/download/beta/

* Once again, please use vserver_mt archive.

* Note that exists prefs panel "Valentina Servers", which allows to stop/run Vserver.

** You can stop Vserver installed now, and delete its folder /Library/VServer
Then install new build.

You test project was tested and works as you expect it.
コメント
There are no comments made yet.
olive 承諾済みの回答
Ruslan thank you!

I tried, it works. By cons, there is a problem Xojo side: If the database is already writing, and we ask another writing, **all** sessions of the app are blocked for the timeout (not just the thread/session that requested the lock). But there is also the problem with the SQLite engine included in Xojo, So it's not a problem of Valentina.
コメント
There are no comments made yet.
Ruslan Zasukhin 承諾済みの回答
hi Oliver,

we are glad to hear it works for you now better.

I wonder if scenario you describe - did work better in cubeSQL?
If yes, let us know -- we will think how to reach it.
If cubeSQL also works in same way, then I afraid this is SQLite behavior.
コメント
There are no comments made yet.
  • ページ :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 0 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories
Omegabundle
  1. 0 subcategories