The Complete Course from ExamCollection industry leading experts to help you prepare and provides the full 360 solution for self prep including 1z0-071: Oracle Database SQL Certification Video Training Course, Practice Test Questions and Answers, Study Guide & Exam Dumps.

TCL - Transaction Control Language

1. TCL – Basics

As we went through the DML statements, the next logical section to learn is the transaction control language. This is needed to either confirm a transaction or roll back a transaction, as we already saw. Commit is used to confirm and close a transaction. Rollback is used to roll back a transaction; fully saved points are used for partial rolling back of a transaction TCL statements do not need any special kind of privileges after the end of your DML statements, and you can run any of the TCL commands as needed without requiring any special privilege. Also, TCL commands are needed to define transactions; let's look at them each in the subsequent lessons.

2. What is a Transaction?

We need to understand what a transaction is. A transaction is defined by our needs. It can be a single DML statement or it can contain multiple DML statements. We have a table called Test Employee with two columns: alumni name and cash. Then my boss says I have given $200 to Tom. Please make a note of it. Then I would run the following command: insert into employeename cash values tom 200 This will insert Tom into the name column and 200 into the cash column. Then I will commit it, indicating the end of this transaction. It's a simple transaction involving a single DML. That's it. End of transaction is easy. One more insert statement insert into testemployee name cache values scott 500 followedby commit in the transaction. And this transaction also needed only one DML statement. Again, after a few more minutes, my boss calls and says that of the $500 that I gave to Scott, I took back $200 and gave the $200 to Tom. Please make a note of it. Now? What should I do? First, I need to update Tom's cash to $400 as he has received $200 from Scott's cash. Then I need to update Scott's cash to $300 as $200 was taken back from him. But can I close the transaction immediately after just updating Tom's cash value? No. Here, the transaction can be brought to a valid conclusion only after modifying both Tom's and Scott's cash values. In other words, this transaction involves two DML statements. So I need to do two update statements to update both the rows. One method is to use update test employee set cash equals 400 where name equals tau to set terms and conditions cash amount to $400. The second is to set Scott's cache value by update test employee" with cache equals 300" where name equals Scott. Then I need to verify themodifications using this select statement only. After that, I should confirm the transaction by committing. In this case, the transaction needed two statements. In a typical business environment, it's usual for a transaction to have hundreds of SQL statements. So a transaction is defined by our needs.

3. Demo of a Transaction

Let me show you a demonstration of transactions. First, I've logged in as the test user test.Then I'm going to create an attribute called "test dot employee created." Now, transaction one, which isinserting Tom's info verified, committed. Transaction One is done. Now on to transaction two. Insert Scott's information; done, verified, and committed. Modify both the values. I should not make a commitment in between. That's the key. Verify and commit. Transaction three is done.

4. DBA Info - Internal handling of Transaction through REDO

Let us see how a transaction is handled inside the database. This is a complex process, but I've tried my best to simplify it. Let's just say I have a table like this. Now I change the value of the bananato-pair error using an update statement. What happens now? Oracle notes that the new value is "paired" in the memory, but it doesn't write it on the actual table yet. What if the server crashes and reboots accidentally? Now the database will come clean during the next startup as nothing was written in the table. Of course, the content in the memory will be wiped out, as it does during every restart of a computer. Let's call that "crash one." After that, we connect again and notice that the value is still banana as it should be. We changed that to pair and commit the transaction this time. What will happen now? Oracle has two sections called "redo" and "undo" stored permanently on the disk. It will set the new value to "redo. Why? In a typical environment, there will be millions of transactions. It won't be efficient to write on the real tables each time by going back and forth. Instead, it uses this redo as a quick scrapbook to note down the information. Then, when it has time, it will go update the various tables. Let me stress this again. Redo and undo information are stored on the disk, and they are permanent. At ten-two, somebody runs a select on this table. This is well after commit, but the data hasn't been written to the table yet. They will read the first row without any issue. When they come to the second row, an internal mechanism indicates that this row has been modified, and the new value can be found at the reduced section. Then it will produce the same result as this by reading from the redo section for the new values. Now at 1003, the host computer crashes again. Let us call this "crash two." Remember that the old data Banana is still in the real table, and we all know that it is an invalid value because it has been changed to pair and confirmed through a commit. After restarting the host, the database is also restarted. When the database is starting up, it checks every record for its validity. An internal mechanism finds out that this banana is an old value and replaces it with the new value from the redo section.

5. DBA Info - Internal handling of Transaction through UNDO

Now at 11:00 a.m., I change the value of orange to plum. Oracle writes down the new value in the redo, but the volt value is still there in the real table. At 2:00 a.m., the oracle actually changes the value to plum in the table. At this time, Oracle puts the word value in the undo area. Why? Every transaction has a timestamp in it, and they may read multiple tables over a period of time. For example, there could be a query reading from these three tables starting at 11:00 a.m. and may take 30 minutes to finish. If the tables are huge, it may get to the second table at 11:15 to read it and to this third table at 11:20. By the time these tables could have been modified, let's assume that somebody has committed their modifications to table two at 11:10 a.m. And at 11:15 a.m. in the third table. Now, will the data be consistent if this query reads these new values? No. It has to get the data as it was when the query started. It has to fetch the data as it was at 11:00 a.m. But these two tables have data that is new. Where will it get the old data from? Data was modified at 11:00 a.m. The data got written into the table at 2:00 a.m. Now, I have a query that has been running since 1050 a.m. after reading multiple tables. Now it is time to read this table. It will read rows one and two without any issue. at row three. Oracle's internal mechanism will tell that query that this value of plum is new and you need the whole value as it was at 11:00 a.m. So it will fetch the whole value from Undo, which is orange. The result will look like this. This is just a simplification. Technically, Oracle writes the data into the realtables even if it is not committed. But those would be confusing as of now and are for advanced-level courses.