Just like you, I spent some hours searching the docs before I posted the question to their discussion forums:
How to start a transaction in sql
Hi, I’ve been using valentina for a while now, and the documentations is somewhat not good, both organisation and language. But it works.
About PDF, there is a PDF for the whole xojo.API and I’ve spent quite some time in that document. Also the examples from the installer is a nice addition… I mainly use the online Wiki for SQL stuff, and other hints, but for main programming I use the pdf.
Also, I’m lazy, I do the construction of the db in Valentina studio, and not schemas via code. 
Sorry for the delay and my bad English, it is difficult for me in this language.
Yes of course. With SQLITE , a write transaction blocks all other write transactions on the entire database. With Valentina DB, only the records concerned will be blocked. This is a significant difference . For example, with Valentina, at the same time, a user can create a customer, another user can modify a customer record , another user to save an invoice, etc.
it seems there is a problem in handling the timeout with Xojo SQLITEWith SQLITE, this is not possible, each write transaction must wait until the previous one is complete. And do not forget: need to wait the full transaction to complete, not only the write request itself. I really like SQLITE, but this is a big limitation. Please note also , .
[quote=253321:@Chay Wesley]Just like you, I spent some hours searching the docs before I posted the question to their discussion forums:
How to start a transaction in sql[/quote]
That certainly limits the kind of projects I can use this for. At a certain point it simply does not make sense to use it at all because the effort necessary to use several different types of database systems is not worth the marginal benefits one provides over another.
I still stand by Valentina Studio as being something I love to use with other database servers.
[quote=253323:@olivier vidal]
With SQLITE, this is not possible, each write transaction must wait until the previous one is complete. And do not forget: need to wait the full transaction to complete, not only the write request itself. I really like SQLITE, but this is a big limitation.[/quote]
Anyone writing an application where high levels of writing concurrency are required really needs to think long & hard about the underlying engine.
SQLite says right up front on their web site that this is NOT a suitable use for SQLite.
It’s a great little DB but trying to push it into high write concurrency usages will run into obstacles.
It’s not what its designed for.
Hello @Chay Wesley, @all,
You could create a Feature Request in Mantis (or even in the offical ValentinaDB Forum), and they would look into this.
I asked (shortly before the V6 Version of Valentina came out) about a Raspberry Pi 2 (ARMv7) Version of the Valentina Server and now there is one nearly usable here. They only have to finish Packaging the Server for install.
[quote=253312:@Chay Wesley]@olivier vidal Again, the difference is huge. The possible number of simultaneous users is multiplied by xxx. Accordingly, the speed of transactions is also much faster, since the database rarely expects an unlocking.
Sorry, I don’t know what that means?[/quote]
With SQLITE, a write transaction locked the entire database. No other writing is possible during this transaction. SQLITE is serialized. Write transactions are written one after the other. Therefore, even if theoretically SQLITE is fast. In practice, with multiple simultaneous users, slowdowns are important. Of course, everything depends on needs. With Valentina , multiple threads can simultaneously write to the database .
[quote=253327:@Norman Palardy]Anyone writing an application where high levels of writing concurrency are required really needs to think long & hard about the underlying engine.
SQLite says right up front on their web site that this is NOT a suitable use for SQLite.
It’s a great little DB but trying to push it into high write concurrency usages will run into obstacles.
It’s not what its designed for.[/quote]
Yes, I really like SQLITE. It is even much more powerful that many think, but actually, it is limited.
[quote=253335:@olivier vidal]
Yes, I really like SQLITE. It is even much more powerful that many think, but actually, it is limited.[/quote]
Right - its not meant to be used as a high write database (which is your complaint)
Comparing Valentina to SQLite for such uses is like comparing a Hummer to a Yaris for offroading
Sqlites “recommended uses” page specifically says “yeah if you try to use it here you wont be happy” more or less
But on an iPad, iPhone, Android etc it IS very capable
OS X uses sqlite as the back end for a number of system services
Maybe I’ll say some nonsense, Ruslan will respond better than me. It’s been long that I haven’t used Valentina DB.
But if I remember correctly, there’s no transaction, but a system of locking. Whenever you create a cursor (SELECT…), you can specify an isolation level. With the readWrite isolation level, the cursor behaves as a kind of temporary table isolated and protected. All records in this cursor are locked. But all other lines of the tables concerned, and all other tables are not.
If I remember correctly, for a long time Valentina was a local database, without server. Kinda like SQLITE. But it was so fast that some people use it with multi-user applications. We opened / closed the connection for each user/group of requests (If I remember correctly, Valentina remains in memory , so the connection / disconnection is very fast, a little like starting a new transaction). Therefore operations are isolated and serialized. It worked well, with the API, but roughly the same limitations as SQLITE. All this may seem weird, but it’s very nice to use.
I’m not complaining. I really like SQLITE, I use it a lot. But I like also Valentina. And I am disappointed with the way that Xojo handles SQLITE/SQL. I was answering to Phillip on the “5 Valentina DB connections vs 10 Sqlite DB connections” comparison (for the Valentina Server).
This is not my opinion. SQLITE and Valentina (with server or not) can be both well suited for use in small business. Both are well suited for handling 5, 10, or 20 users for example. Both can be an embedded database. Both can be used with a server (although it is not native for SQLITE ) .
Since the WAL mode, SQLITE has much more capacity. Of course, this isn’t PostgreSQL, but it can now be used in small business, especially with web applications, and therefore legitimately be compared to any other database for this type of use.
This isn’t because SQLITE is used in an Ipad and Valentina can handle dozens of concurrent writes that they do not also have a common playground.
[quote=253362:@olivier vidal]If I remember correctly, for a long time Valentina was a local database, without server. Kinda like SQLITE. But it was so fast that some people use it with multi-user applications.
[/quote]
Haven’t used it in more than 10 years
Only once on a client project and they switched to sqlite
[quote=253362:@olivier vidal]I’m not complaining.
[/quote]
Criticism / concern
The thing your’re trying to point out
call it what you want
I have no idea what this means ?
Nor is it on topic for this thread
When the authors of sqlite say “we would not recommend using it this way” I tend to follow her advice
Plus I like PostgreSQL much more anyways (there kem!)
???
I had Valentina Pro version 5 (Omega bundle, a few years ago)
After your message i decided to renew my license.
Payment is accepted, but I haven’t received a license key.
I assume the version 5 license doesn’t work on this version of Valentina Pro.
Hello @Paul Sondervan,
you could look under “Account” -> “My Orders” on Valentina DB if the Order is Processed you should find your Licence Code under Your Order. The Only thing you alwas get vie mail is the Valentina Server Licence File.
Ok my order is created, I received an email and the order is created in My orders.
Apparently the order needs to be processed/confimed first before I receive a license.
Patience… 
I can’t edit my last reply although it is the last reply.
After a few hours I received the license key.
All is functioning now.
To be more precise, and if I remember correctly:
-There is no system of transaction itself, but Valentina locking system is interesting. Let’s take an example:
-
A user creates an invoice. He asks several cursors (SQL) to retrieve data (customer, articles, stocks, the last invoice number, etc.). All these cursors are requested with an exclusive lock. If these lock requests are all accepted, the data is consistent: other users can not edit/delete these lines as long as these cursors are opened (ie they cannot obtain an exclusive lock on these lines). But everyone can read them. Other users can simultaneously edit/delete/add other clients, articles, invoices, etc.
-
From the cursors (which are kind of protected temporary tables, containing only the data we need), we create the new invoice, the new lines of the piece, we update the stocks, the last invoice number, etc. All these operations are made in memory cache. If there is no error, we know that all requests (update, create…) are accepted. We can then save all to disk, in a single request, with the “flush” request. That means that if there is a crash before the flush, or after the flush, the data will be always consistent: all operations (the equivalent of a transaction) will be saved, or nothing.
-
We close cursors. Rows are unlocked.
This is not as secure as a real serialized transaction I think, but it seems an acceptable compromise for most. I hope that I did not say nonsense, it’s been long that I have not used it.
Unfortunately you can’t use the API (NoSQL) in the same way.
I think this is also why many people use the embedded Valentina version, and not the server version. This allows to use the API (NoSQL, classes and Xojo methods) and it’s very nice. As there is a connection/disconnection between each group of operations, it is totally ACID and serialized, there’s not need transactions. But then, the number of users should be small, I think generally less than Sqlite with Wal mode (all depends on the needs and if they are spread in time, like Sqlite), and if there is a hard crash in the same time as the flush ? The database can be corrupted? I don’t know.
How many new posts here … and I did not get email notifications on some reason.
I will try to read asap all to see if answers are needed.
Valentina DB have journal like SQLite, what allow recover db to good state in case of failures.
Valentina DB do NOT have transactions support. We do not want make it on db-level as did SQLite.
We have start develop transactions based on versioning. This task is tightly related to multi-threading.
So far we was not in rush, and I think this was good, because now or quite soon, we will get into hands C++11 compilers where added very good support of MT. Mac and Linux okay now, we need upgrade yet to Visual 2015 to get the same level of c++11 language on 3 platforms.
Meanwhile we preparing some underline classes and algs, which should help us do it in way as we see it.
And yes, we will fight yes for transactions in near future.
- On question: “how SQL db can live without transactions?” I can try answer in such ways
-
how did live mySQL ISAM engine? Which is Not ACID-compliant and non-transactional … 20 years at least on web tasks. Right?
-
how live a lots of NON-SQL db engines? Only last time and only some of them claim they provide transaction support.
-
I have asked somehow many web developers (they did use mySQL even with InnoDB engine, do you use transactions? Answer was no mostly).
-
for VServer we promise atomic execution of SQL command. You can separate few by ; You can use Stored procedure to do batch of commands at once, without interruption by other users. Rollback - not works.
I would like to see that benches
It could be:
a) in Valentina project was with enabled DebugLevel, so a lots of logs to disk
b) SQL was without binding.
If you should use SQL binding in real life projects?
Yes of course. This is one of the best advice for most SQL DBs.
Point is - even with pure standard SQL Valentina should beat it.
And this is what we hear usually.
Chay, If you will have Wish, Time and Ability to send us more info, it will be great. This is always interesting 