Day 282 of writing every day.
After sending in my daily report yesterday, I was given feedback that I’d reached a rather difficult point in SQL fundamentals. This is due in part to the fact that I’m using a textbook that my coworker has and had recommended to me, so they have an idea of what the content is and probably had similar hardships understanding the content as well.
Today I spent most of my time reading a chapter on transactions in the context of SQL. Transactions are a segment of SQL code that is treated as one block that cannot be broken down any further, like the idea of an atom where the chunk of code is one element arranged a certain way with unique properties. Transactions are important for the role they play in ensuring databases work properly and protecting the integrity of data being accessed by many people at the same time.
When sharing a file for example and giving someone else editing permissions, this means you and that person can fiddle around the same file such as Word or Excel at the same time if made available online. While on such a small scale it might be easy to track who did what and make sure the information is maintained accurately, it becomes much more complicated when a group of ten handles the same file. And then scale that up to millions perhaps using the ATM or shopping online.
Transactions in SQL make it so that a block of code is run only when every step of it has been completed. It’s like a true and false condition where it’s one or the other. If a session ends abruptly, then the incomplete entry gets dumped like it never happened. If there was no such automatic failsafe, a power loss or internet connection disruption could mean something getting only half done and sent out as complete, causing problems.
The example given with the ATM is when the sender transfers money but an error occurs after hitting send and the recipient never gets the money because midway through, the power went out or something. So, the money is deducted but not received making an error where money just got erased and nobody receives it. For something like this, a transaction would ensure that only after the recipient has received the money would the transfer be finalized. If the recipient didn’t get the money with the total in their account updated to reflect incoming money, then the whole procedure would be canceled and the sender would have a chance to start over.
Another feature is to set the mode so that you can lock the data, from the choices of row, table, or database. However, given that people need to be able to access the database and use it, I was informed that the general practice is to limit the use of locks to as small a scope as possible. Locking up entire databases when it’s being accessed also slows it down and prevents it from running as fast as it could.
I’m still learning but it’s starting to come together for me.
Thanks for reading!
One Chance Works 