Suggestions on making acess to global database be thread-safe

New comment
 

Hi!

In order to avoid two or more EAs to end up having the same Magic Number, I developed a global SQLite database using MQL5s own "DatabaseFunctions" where any EA, upon OnInit, registers its own MagicNumber if it is not already in use and erases its record on OnDeinit.

The problem is that different graphs work in different threads and so what  is happening is that whever I load two or more EAs, there is an write or read error with 5605 "database error, database is locked". I guess this implies I need to make the access to my database be "thread-safe".

Well is there something more "native" in the Database system which would allow me to avoid having to use mutexes/semaphores or the GlobalVariables? I couldn't find anything. And if the answer is "no", has MetaQuotes already implemented some native mutex/semaphore or do I still need to use some 3rd party library?

 
As far as I am aware, SQLite supports already thread safety. That's why you are getting the error message. You will need to read about locking tables and transactions.

For semaphores or mutexes you can use global variables and especially the function SetOnCondition.

The rest needs to be done by you.

But since you can only have one EA per chart, why don't you just generate a Magic that is unique to that, having a deterministic algorithm to create the same Magic for the same EA will make it much more reliable, and you do not need to store anything in a database.

Also, global variables are much faster than a database file, so why not use that instead.

And what happens if the EA crashes, who is going to clean up the "left overs" inside your DB?

I would say, your approach seems a little oversized.

The terminal does not support so many EAs in a single instance that it would make sense to maintain a whole DB to manage that.

Just my thoughts.
 

The Magic No. is now of type ulong so from 0 bis 18 446 744 073 709 551 615.

You can use different parts of the long number (19 digits)  to store different information, like the last 3 digits for information you often use (=>magic%1000=615 from above). It can be stored in one function which can be included in any EA for any purpose.

Even more unconventional would be to cast ulong (8 bytes) into the type datetime (also 8 bytes), then one would have this value structured in:

  1. Years 1970-3000
  2. Months 1-12
  3. Days: 1-28 (29-31 are sometimes, sometimes not)
  4. Hours 0-23
  5. Minute 0-59
  6. Seconds 0-59

This way you have 6 different sections of the 20 digits and in total 29 901 312 000 options to store information - enough I guess.

Of course it's no the way datetime was meant but it should work.



 

Dominik Christian Egert #:

As far as I am aware, SQLite supports already thread safety. That's why you are getting the error message. You will need to read about locking tables and transactions.

Yeah, I really lost myself in that one ^.^ Actually what I need is a Qt's function where until the thread-safe code is released, the code is blocked, something like "waitUntilReleased" function.

Dominik Christian Egert #:
For semaphores or mutexes you can use global variables and especially the function SetOnCondition.

The rest needs to be done by you.

Ok! I'll read about that unknown function.

Dominik Christian Egert #:
But since you can only have one EA per chart, why don't you just generate a Magic that is unique to that, having a deterministic algorithm to create the same Magic for the same EA will make it much more reliable, and you do not need to store anything in a database.

Well, my plan is to have a considerable number of EAs running different strategies in different symbols, 50 or more. And I need to be organized relative to which EA is running which strategy in which symbol in which broker in which Terminal in which computer... For that, I keep an Excel table where the Magic Number is the ID of the EA. And with that many EAs, what may happen is that I may mistakenly eventually load the same preset file twice, an error I'ld like to avoid.

Dominik Christian Egert #:
Also, global variables are much faster than a database file, so why not use that instead.

And what happens if the EA crashes, who is going to clean up the "left overs" inside your DB?

I used GV a little bit some time ago, but it felt a little okward :| Specially because when the whole system gets ready (50 EAs or more), that may produce an ugly list of Global Variables put together with other global variables... That looks messy. 

 
Martin Bittencourt #:

Well, my plan is to have a considerable number of EAs running different strategies in different symbols, 50 or more. And I need to be organized relative to which EA is running which strategy in which symbol in which broker in which Terminal in which computer... For that, I keep an Excel table where the Magic Number is the ID of the EA. And with that many EAs, what may happen is that I may mistakenly eventually load the same preset file twice, an error I'ld like to avoid.

Can't you have the magic number formated in such a way that you can calculate it on the fly like Carl Schreiber said?

For example, the first 8 bits to store the symbol ID, the next 8 bits to store the strategy ID, the next 8 to store the broker ID, etc...
Since the magic number is a 64 bit, you could encode a lot of information there and your use case looks simple enough.

 
Carl Schreiber #:

The Magic No. is now of type ulong so from 0 bis 18 446 744 073 709 551 615.

...

I suppose your idea is relative to the previous comment on creating an internal function to generate the Magic Numbers, right? In that case, thanks for the complement :)

 
Alexandre Borela #:

Can't you have the magic number formated in such a way that you can calculate it on the fly like Carl Schreiber said?

For example, the first 8 bits to store the symbol ID, the next 8 bits to store the strategy ID, the next 8 to store the broker ID, etc...
Since the magic number is a 64 bit, you could encode a lot of information there and your use case looks simple enough.

Well, that's possible... And maybe it wouldn't cost too many extra input variables.  I'll think about it :)

EDIT: I thought and think it wouldn' suit me. Ultimately I'll still need a numeric ID for the strategies and some security mechanism I'm not repeating strategy/preset files, which is what Magic Numbers are doing for me right now.
 
Ok. I guess this could solve your request permanently.

Use a CRC-64 to generate a "uID" using following as input.

- the set config
- the Symbol
- the EA name

And what else you need to identify your EAs uniqueness.

You will receive a 8 Byte ulong value.

That should do the job
 
Oh, btw, you will have issues with multiple terminals, since they all have their own, private data store.

Only the common section is interchangeable.

You could also use files named by the magic I'd I suggested, and let your EA write a character to it. As the size of the file is more than 1 you know, you are running a duplicate of an existing version.

You could of course also write a set of bytes, with a predefined length, with human readable information. This way you can also maintain the files manually.

As an idea.
 
Martin Bittencourt #:

Well, that's possible... And maybe it wouldn't cost too many extra input variables.  I'll think about it :)

EDIT: I thought and think it wouldn' suit me. Ultimately I'll still need a numeric ID for the strategies and some security mechanism I'm not repeating strategy/preset files, which is what Magic Numbers are doing for me right now.
I see, an alternative is to NOT try to initialize the EA on the OnInit function but on the OnTick or OnTimer.

For example, you have a global boolean indicating if it was initialized or not, and, during OnTick/OnTimer, try to initialize
the EA (calling the SQLite database) if it is false, this way it'll keep retrying until it get the settings it need from the database
and SQLite will make sure only one of them will have access at a time as it will give you an error if some other EA is accessing it.
 
Alexandre Borela #:
I see, an alternative is to NOT try to initialize the EA on the OnInit function but on the OnTick or OnTimer.

For example, you have a global boolean indicating if it was initialized or not, and, during OnTick/OnTimer, try to initialize
the EA (calling the SQLite database) if it is false, this way it'll keep retrying until it get the settings it need from the database
and SQLite will make sure only one of them will have access at a time as it will give you an error if some other EA is accessing it.

Thanks for the idea, Alexandre! 

I never though about initializing the EA in OnTick/OnTimer; that seems so unorthodox. That being sad, I'm not sure if that approach would be better then the one I have now, which is: still using SQLite database to check if a certain MN has been used, if it tries to read/write the table and is locked by another EA being initialized, it just try again inside a "for" loop. After all, this process is fast and normally in the second attempt the table has already been released.

Dominik Christian Egert #:
Oh, btw, you will have issues with multiple terminals, since they all have their own, private data store.

Only the common section is interchangeable.

You could also use files named by the magic I'd I suggested, and let your EA write a character to it. As the size of the file is more than 1 you know, you are running a duplicate of an existing version.

You could of course also write a set of bytes, with a predefined length, with human readable information. This way you can also maintain the files manually.

As an idea.

Thank Dominik for your replies!

I'm under the impression having a single SQLite database to manage the MN is far more 'elegant' than having a pile of files with 0 or 1 byte and other alternatives. Regarding the CRC-64 idea, I'm not sure it would work in case I open two EAs in the same asset and period in the same terminal with the same presets; whatever checking mechanism would still accept the second configuration and I'll end up with two identical EAs!

New comment