How do database repository file work?

[Paul Rice]

I don't know if this is the best place to put this but here goes?

Does a database rewrite it's file everytime new data is stored? Is it able to selectively remove parts of it's file when data is to be deleted?

Thanks,

Paul

[exterminator]

Well, i tried giving you some hints in the C++ forum.. Seems not enough.

You cannot replicate what databases actually do. Why? Because they are smart and hardworkers. They will be working even when there is nothing being done with the database. They will organize files, storage, make checkpoints, logs, etc.

The latest queried for information of write information is not stored in files directly. That means, if you issue an INSERT/UPDATE/DELETE (DML) query, it will not go and directly modify the physical persistent storage. It will keep it in memory. Then depending upon how much load it has - it will opt to instantly write that information into the physical file storage or not. There are more than one instances of DB writers (that actually write the information to files). That memory space is often called buffer cache.

If a user asks for a SELECT that may involve reading the same data - the writers may write to the file or may not. They would read from files and from the memory cache and return you the result depending upon if the previous DML transaction was commited. As leisure or depending upon its logic, it will sometime go and update the files - that would not cause any performance issues because that is happening in another thread.

There is something called dirty read - this is what you get when the DML transaction has not been commited until you throw a COMMIT, this will only happen for a different user and not the user who issued the DML. For the one who issued the DML - the data would be read as I mentioned above from files (physical stoage) as well as the memory cache and hence he will get the full read (not dirty read). You can read up more on this, how databases do actually manage transactions and dirty reads for multiple users.

The point is that database engines are very intelligent. For your case, when you need to update a file in the middle - what you can do is - have a similar logic but it will need to be very extensive. A lot of work. Because, here you would be reading from the file as well as from the data stored in a cache (memory). You would need to identify where that cache held data would go in the file and properly respond to requests for read. Things get complicated when there can be more edits than reads...

You can try using async writes to files - if read is not going to take place immediately. This would require you to work with multiple threads (databases use multi-threading heavily). When a command to write (update/insert text) comes - you begin a thread and let is write the data appropriately but synchronously acquiring locks so that any further reads should wait. This can be done asynchronously as well but there should be a call back registered to release the file lock/mutex when the async write completes.

Moreover, in case of database there is a concept of block storage (typically 8KB) - files are stored in blocks. Take a look at this to gain more info about the Oracle server architecture. It sure is interesting - Oracle Server Architecture

There are very good books available too on database server architectures but I know you are not quite interesting in implementing something like that. You just need a quicker way to update files in middle or something.. or are you?

Does this answer your questions?

[Paul Rice]

Well, i tried giving you some hints in the C++ forum.. Seems not enough.

Sorry, I actually posted here first! I thought it was a more relevant board but in retrospect it seems rather dead around here.

Thanks for the extra info.

Paul