Flat File Integrity

mac100

Say I have a website with a quantity of 'suppliers' & 'buyers'

...each buyer has a number of tokens in their accounts, giving each of them a 'credit-balance/value' (eg: say: 100)

...which they use to 'buy' from the suppliers.

SO: They choose an item to 'buy' and their account-value is reduced in value by '1-token'
while the suppliers 'account' is increased in value by '1-token'

I'd though of creating flat-files for each of the 'suppliers' & 'buyers'

and then, when there's a transaction, the appropriate accounts flat-files get (debited and credited) written to.

But what would happen if more than one buyer was buying from the same 'suppliers' at the same (or nearly same) time ?

Then: That suppliers ff would need to be accessed several times (at the same time) so that each credit could be added to the current value in that suppliers account !

So what would happen !
How can I make sure that each credit gets reliably added to a supplies account ?

Many thanks

bradgrafelman

Why on earth aren't you using a RDBMS such as MySQL for this?

planetphp

As above!

But if you really needed to you could create a lock file.

Say your file is sup123.dat when accessing this file you could create an empty file sup123.lock your scripts must then check for the existence of this file before doing anything with it.

You can basically do a loop on the lock file as long as it exists don't do anything. When it disappears you can create it again, do what you need to do, and then delete it.

But once again a database is made for this kind of stuff so you would be much better of using one.

bradgrafelman

planetphp;10994606 wrote:
But if you really needed to you could create a lock file.

Although I haven't benchmarked it, I would think you'd be better off performance-wise to simply [man]flock/man the file.

mac100

Many thanks guys for your feedback so far...

Flat-Files are simple and great for some applications, but it does seem that in this case they could create unreliabilty, as nothing Ive read so far seems to guarantee reliabilty ?

If I use MySQL: Will this guarantee reliability ?

Or does that also have its problems ??

Many thanks.

bradgrafelman

mac100;10994628 wrote:
Flat-Files are simple and great for some applications, but it does seem that in this case they could create unreliabilty, as nothing Ive read so far seems to guarantee reliabilty ?

It's not the fact that you're storing data in flat files (hey, even MySQL has to store it's data in files on the disk, too), but more along the lines of it's totally up to your application to handle things like concurrency and data integrity. This isn't always easy to do.

mac100;10994628 wrote:
If I use MySQL: Will this guarantee reliability ?

I don't know if I would say guarantee, but it could significantly improve reliability... assuming it's used correctly.

For example, if data integrity is really a concern here, then I would suggest using MySQL's InnoDB engine since it is ACID-compliant (it provides transactions; MyISAM does not) among other things.

Of course, MySQL itself is only one of many RDBMSs out there; more experienced DB gurus might be able to comment on why they prefer (Oracle, PostgreSQL, etc.) over MySQL for various reasons. I myself am no such guru. :p

EDIT: Just to clarify this:

bradgrafelman wrote:
I don't know if I would say guarantee, ...

I was trying to refrain from using such strong language in a subject that I would label myself an "intermediate" rather than an expert/guru.

For the application you've described above, it's likely that MySQL's InnoDB engine provides all the safety you need in the form of transactions. Search around for a couple of good articles/explanations on what SQL transactions are and how they ensure data consistency even if an application fails mid-transaction for some reason or another.

EDIT2: Okay, I'll stop inundating you with info after this edit, but one more thing...

I just realized I focused on the wrong concerns. As far as concurrency goes (e.g. the case of multiple buyers modifying a single supplier at once), InnoDB also supports row-level locking, so you'd want to LOCK the relevant suppliers and buyers rows before performing the UPDATE query to increment/decrement the relevant counters.

mac100

Hi bradgrafelman,
Thanks for the detailed info, it's greatly appreciated.
Do you know where I can find more info on MySQL's InnoDB & MyISAM ?
Or is it just a case of searching online ?

For example, if data integrity is really a concern here, then I would suggest using MySQL's InnoDB engine since it is ACID-compliant (it provides transactions; MyISAM does not) among other things.

Also please pardon my ignorance...
If MySQL's InnoDB has better data-integrity, then MyISAM, then why would anyone want to use a DB (MyISAM) that has a worse data-integrity ?

Many thanks.

NogDog

MyISAM is optimized for reading (select queries), and can provide better performance in situations that require a lot of reads versus only occasional writes. (And it is possible to define some tables as MyISAM and some as INNODB within the same DB, depending on your needs.)

If read/select speed is critical, you could look into something like Mongo, perhaps.

Weedpacket

[man]SQLite[/man] also provides ACID compliance, without the overhead of having to install and run an entire RDBMS as well. In effect it implements what your original post was inquiring after: the database is stored as a file that your application reads from and writes to (see how SQLite handles atomicity - it's not a trivial problem to solve!)

There are advantages/disadvantages to SQLite over an RDBMS; fortunately, the site summarises those as well.