A Safari through Oracle's Dense Transactional Jungle

there we go all right thanks uh so hello everyone I'm Kishi um I work at Oracle and I am I'm a Consulting member of technical staff in the Oracle database transactions team and I lead a small group of engineers in the transactions engine um so today we'll be going over you know a safari through articles dense transactional jungle okay that's my goal to take you guys on a journey through Oracle you know transactions and you know I want this presentation to be more I guess uh to be you know like two-sided okay not one-sided uh

so in case you have any questions please ask okay uh feel free to shopping and but yeah we will keep a time check as well okay so a few things about Oracle first and Oracle database and then we can move on to more technical stuff the world it relies on Oracle and it runs on Oracle right now whether it's Oracle Cloud infrastructure or historical database or or any other article Technologies out there right that most of these companies right rely on Oracle whether it's ride sharing whether it's the infrastructure we are using right now for

video conferencing um or whether it's a bank right or you know a GPU company right all these companies they use Oracle so now let's look into that why would all these companies use Oracle I have a silly example for you guys uh which is about cheetah Safari it's a tour operator in in Tanzania so let's say if a company like cheetah Safari which is a tour operator it starts to operate so it starts off with the ticketing website a basic ticketing website for that it needs transactions right it wants to it wants to create and

issue tickets to its customers but later as the company grows you know it also wants to publicize all the pictures that that we took in the tour for marketing right on their website they want to put a lot of pictures so for that they need document processing right they want to um I'm stored like uh you know jpegs PDFs for that they need document processing then if they grow to multiple locations within Africa and for that they have a moderately complex website so now they realize the need for rest and Json to simplify their application

development and for that they need rest support and Json support from a database they also have queries to run right as they expand that which month recorded the highest tour sales for any location they want these insights from the database or from the application um so that they can operate better you know and make better business decisions for that they need Analytics and once they become the largest tour operator in Tanzania they have a huge application right they have large application modules and they want to switch to microservices and finally they prepare for an expression

in in the entire continent of Africa and for that they need Geo distribution all these capabilities are must-haves for a modern oldp database so oltp stands for online transaction processing um but but in the modern landscape right transaction processing it's not just running transactions there's more to it you have analytics there is autonomous in the management of the database you have to process documents it should work at Cloud scale we should have Micro Services you know like event processing all of these use cases have to be handled in the OTP database and transaction processing has

to address all of those cases and Oracle transactions does exactly that so Oracle transactions you know it's a leader in many capabilities of transactions so here we are looking at you know like the regular transaction processing Oracle is at you know oracle is a leader and in fact it's a leader in many other transaction processing categories right whether it's augmented transaction processing which is basically you know the analytics um plus oltp combined inside a database so Oracle is a reader in that and other fields like stream and event processing so you can think of it

like iot workloads where you have like devices which are producing massive amounts of data in a very short amount of time then you have operational intelligence which is again a form of analytics so it's used for uh you know it's used for real-time business decisions so even in that category Oracle is at the top so enough about Oracle you know like and about Oracle transactions right let's talk about the database architecture first and then right my plan is to walk you guys through the the design and implementation to some to to some aspect of Oracle

transactions right but let's look at the architecture of the database first so we have a concept uh so okay first let's go over the concepts okay in the basics right so okay what is a shared disk architecture so so a shared disk architecture is an architecture where you have a where you have a database server and you have storage which let's say it lives on a different server so the storage is responsible for hosting the data of the database and the database server it does all the you know the compute or the processing of queries

of the database so so shared this system is you know it's also known as data shipping system where it brings the data close to the server that actually runs the SQL so if you have a data page which is in the storage that data page is brought to the database server for processing so it's bringing the data close to the database server right and that's what a shared this system or a or a data shipping system is so the other concept which is shared nothing architecture so in a share nothing architecture it's also known as

function shipping where where you have multiple nodes in a database and every node in the database has its own compute and Storage or access to storage and if the sequel has to be run um on the data right the SQL is shipped between servers so that the SQL runs locally uh to the data uh which is hosted by the server right so let's take an example to understand this real quick so the client sends a SQL that accesses data XYZ you know and the data over here let me um uh turn on my pointer so

the data over here you know in this small example it's a b c and XYZ so the client SQL accesses the data XYZ over here so the so the first database server it shifts that SQL to the remote server right because the data lives over there so the computation happens on the second server over here and after the computation is done this server sends a response back to the first server and the response is sent back to the client after that so in this the functionality or the compute is being shipped to the other database

server which is actually which which has the data so these two database architectures are good they have their own trade-offs but what does oracle do Oracle does the best of both the worlds it does data shipping and function shipping both okay and that's what makes the Oracle database you know architecture special so Oracle database you know it has a rack architecture RAC stands for real application clusters you know it's another term for having multiple you know like instances of the database in a cluster okay so here we see we have multiple instances of the database

server which have their own memory and processes for doing computation we also have storage servers which host the database files but the important thing is the fusion buffer cache or the cash Fusion technology that we have so we have a distributed cache which which lives on multiple database servers and it maintains the coherency of the data which is in the buffer cache so the data is moved between various uh between different database servers so if the data lives on this server right on database server 2 and it's accessed by database server one we would bring

the data which is on the other database server quickly using a fast interconnect right like infinite band or rocky you know RDMA or you know over converged internet so we use those fast interconnects right to bring the data which is on this server to the other server okay quickly foreign and we'll see the benefits of this in the next few slides um and all these all these servers right the database servers and the storage server they're all connected by this fast interconnect and this achieves best of both the worlds which is data and function shipping

so let's look at next how it's achieved so first let's look at uh Oracle racks you know like scale out architectures right function shipping aspect foreign so function shipping is good for analytics and batch queries okay so analytic query is a long running query essentially um which needs to process massive amount of data and so does the batch sequence so for function shipping right because they process large volumes of data so for them they need parallel execution on multiple uh you know on multiple instances of the database so here nothing architectures right are typically good

for Analytics right so what Oracle does it breaks the sequel right for the analytics into multiple SQL fragments which are executed in parallel across multiple database instances so we distribute and we uh we do this Dynamic distribution of data between the rack instances or the database instances so that the execution of these analytics queries they happen in parallel which is similar to what shared nothing architectures do so the rack instances um that we have over here they are not confined to a subset of data right because the rack instances can read from can read any

data from the storage servers right because they can all access the storage servers uh right and it's shared between all the database service so we can achieve uh we can achieve the dynamic distribution of data with this and also avoid the large skus if one of the storage uh uh you know if one of the compute instances is pretty slow right we can avoid those uh um those large skus in performance right because we can make those decisions dynamically right to spread the load on these servers so this is the function as function shipping aspect

of the Oracle database architecture so the next thing is the data shipping which is good for oltp so oltp queries generally they have uh they would modify small amount of rows you know small number of rows and have very um and expect you know the quicker response times so with function shipping it's very slow and expensive to send an RPC call to a remote instance right so the example that we looked at for function shipping earlier we were shipping the SQL to the other node so imagine doing that for for a query or for a

DML that modifies the row in another instance right you have to like send an RPC call right which is expensive and also right if we end up modifying like different rows across these database instances then you would need the expensive right distributed transaction and two-phase commit protocols where we will see next right that we don't need this in our current architecture and no shared nothing architecture today runs complex oltp applications so like Erp or CRM or xcm okay um so how is data shipping good for oltp so we do local execution of this oltp queries

so the short running oltp queries they can directly run on the instance which receives the application request so if the application request comes on you know like comes on the database server over here on the first instance then it executes a ltp request okay right over here it does not have to ship it to the author distance right and that is because all the data right can be accessed by this by this database server from the storage server directly by reading from the storage server and caching it in the buffer cache or if it's already

in the buffer cache then great right we have in-memory access right or if the data was cached by this server and the oltp request now comes on the other server which accesses the same data then we ping the data from the first instance to the second instance using you know smart buffer cache over here right so we keep the data coherent and by using the fast integrate we ping the data to the other block and it makes that data transfer faster and if the same query uh you know if an old TP query is accessing

the same data over and over then we also leverage uh the buffer cache temporal locality so it makes it faster right it makes a world TP faster the very first time you may end up doing an i o but all the subsequent accesses would be would be of memory so it's much faster so let me you know like stop and ask a question here yes yes I was going to stop yeah go ahead yeah so in the previous slide so earlier you said two-phase commit is expensive because you need to have distributed transactions a coordination

between different nodes but then in the next slide you say for cash coherence between different nodes but in a sense you also need coordination and if multiple know the right to the same data for example only one node can own the data and there there will also be coordination uh but it feels to me that these two kinds of coordination incur similar costs um so the cost of you know of moving the data right in the buffer cache you know like based on our you know based on our performance experiments uh right because you're leveraging

this fast interconnect right that is one of the keys right for uh of moving this data faster across these instances right so in terms of the coordination yes you're right uh that will have to like move the data but but if you have to commit the data right so for example if a transaction right wants to commit right then at that time we don't need the two-phase commits because if the transaction you know in database server one right it locks let's say 100 rows right all that data all the hundred rows are actually brought to

this database server we don't ship it to the other database server so at the time of the commit of the transaction we don't have to incur the two phase commits for example because may I argue that since your network is so fast the 250 mail is also relatively cheap um yes yes it can be cheap but at the same time right you're incurring the overhead of network right but as a as I corrected myself later right that all the blocks right you know are pinned all the data is paying to database instance one for example

over here and at the time of commit you don't have to do you know like any network uh call at all you just commit the data locally okay thanks yeah see and we'll see also right uh um you know like later right that how Oracle leverages mvcc so that if you have like reads you know like coming on the other database server right so we have like consistent reads in the Oracle database where the data is right to the other database servers right but at the same time if someone has locked the data you know

on one of the database instances you know it just lives over there right the reads don't block the rights so we don't oh we don't have the overhead of two phase commits for example right and that's you know like a big win right for this architecture yeah yes go ahead foreign but suppose say I want to do a join where the data is distributed among multiple nodes uh so the probe table is distributed along multiple nodes then in that case wouldn't you also have to shift the data across nodes um so we do ship the

data um but in our case right so imagine your data set right uh you know like if you're scanning let's say a table okay and the table is you know is let's say like one gigabyte so what Oracle does right we actually distribute the data evenly right so that the data is also mutually exclusive so that we don't have to um we don't have to like coordinate between the you know like data processing so in this example right you would split that 500 megabyte and 500 megabyte in those different servers so that those two servers

are doing the scans and you know like as much processing as possible on those two servers okay so so in that way right we ship the functionality to those two servers but yes later right the coordinator of the parallel execution right you know it will have to like process the data from both the servers at the same time so so at that time all the data is brought to a single instance and it does uh you know any processing that has to be done serially but most of the shipping is done uh but most of

the processing is done in public so in your example of of the hash table right so if the joints uh right I'm not sure about the architecture of the hash join over here that how it splits up the data uh whether it's a distributed hash table or you know or first we do as much filtering as possible you know on the individual database servers okay and then we join it um so I'm not sure on you know on that aspect um but most of the processing right happens on the two database servers right in this

example and later we bring the data together okay thank you yeah and also feel free to reach out to me later you know or I can you know reach out to you guys as well in case my answers did not make sense um but some of you know like my expertise May lie more on the transactional side um so yes uh feel free to reach out to me later or I can just you know answer back to you as well so let's discuss uh you know a few more Concepts before we dive into the transactions

land um so I guess uh that this group May mostly be familiar with some of these Concepts so it may be a repeat or I guess it may be you know like uh it will not be interesting so I would quickly cover some of these Concepts okay so right ahead logging okay it basically means that before you modify the data you would log the changes you know in a lock okay so all the forward going changes which is called redo and the reverse changes which is undo right to to roll back those changes to the

data you know are written to a sequential log file or it's called the redo log okay and it's used for atomicity and durability of transactions right so atomicity is achieved with undo so so all the changes must be persisted to the redo log before the data is changed okay as I had mentioned and we'll see how we achieve atomicity in Oracle right later on in the slides also durability all the transactions changes right or the redo log changes right must be persisted to the on disk redo log before it can commit but the data changes

can happen later right so all the redo changes have to be flushed but data changes can happen later right which is good for performance and also we use right ahead logging for database recovery you know in the event of cache so let's look into another concept known as the data Block in Oracle right and I and I'm explaining all these Concepts because later on we'll be looking more into the details how we use these data structures okay so the data block it contains in a bunch of rows and these rows fill you know in the

data block like from bottom up for like Space Management so Row one row two Row three and row four they all fill up in the data block so these row this is the data section right where we keep the rows or if you have an index we keep the key values and you also have you know like metadata about the data itself for the sake of Simplicity I'm just showing you the important thing I mean for me which is the interested transaction list okay but we have other we have other metadata as well like we

have row directory um but but for our for our uh for my presentation I'm mostly interested in the you know interested transaction list which keeps um track of all the transactions which have modified data in this data block [Music] so why do we have an IDL in the oracle um in the data block itself so it's to identify which transaction has locked up so for example if transaction 2 which is active over here if it's modifying Row 1 then Row 1 has a pointer to transaction two which signifies that the row is locked and also

in like multiple rows right so if if the same transaction transaction 2 it modifies root 3 as well then root 3 also points to it right so so within the data block right multiple uh right rows can point to a single itl entry right and multiple rows can be locked by a buyer transaction okay so it's good for Space Management as well but the row locking aspect is something that will look next so Oracle has fine-grade concurrency control using Roblox so we looked at this thing where Row one gets locked if a transaction is active

and Row 2 can also get locked by another transaction at the same time so we achieve fine-grade concurrency control because multiple transactions can run at the same time because of these rolocks and these roll locks they are persistent so the fact that they are persisted right it's great for scalability of the number of changes or for long running changes for long running transactions so for transaction makes a lot of changes to the data right you have to maintain those rolocks and if you have those Roblox in memory you would be limited to the size of

that memory right so the Roblox are persisted in Oracle for that same reason but we have a few few other interesting reasons like the database can be opened right without waiting for the transaction recovery to complete so in the event of a crash of the database right let's say you had a few transactions that were acted right these two transactions are active and if everything was shut down all the memory vaporized then in that case after you start up the database right then in some databases you have to wait for the undo application Phase to

open the database but in Oracle right you don't have to wait for that because the Roblox are you know are persistent so if some transaction wants to access the same data that was locked right it unlocks that row okay using cleanouts which we'll discuss next you know how it's done but your transaction recovery you know essentially it happens asynchronously right and we use a background process called the S mon process right for transaction recovery so so the database opens much faster and why is the interested transaction list and the Roblox kept in the data block

right because the Roblox and the transaction information it can easily transfer across rack instances right so if you have uh so if you have a transaction which had locked a row and now if there's another transaction which wants to you know lock you know like Row 2 on another instance we ping the block over the fast interconnect and and all the information about the lock that hey the transaction that there was a transaction that locked a row on the first instance that's easily migrated to the other rack instance right over over the buffer cache we

also have a system change number in Oracle which is oracle's uh which is oracle's internal clock so it's a so it is a monotonically increasing logical timestamp okay but why do we have that clock in the Oracle it's to order the events in the database right so every time we modify the data a redo record is generated right and every redo record is stacked with an scn so that we can order the changes and at the time of a transaction commit right we also have uh you know every transaction commits as of an scient ist

at a particular SEO so that the transactions can be ordered uh using those Associates and it's also used for database recovery right so we need to figure out that from where in the redo log right should we start uh should we start writing uh should we start recovering the database and where should we stop so it so we use scns for that purpose so every so every statement every transaction or commit it has an scn so when a statement runs you know it runs as of that scn okay and I'll talk about consistent reads for

these statements you know later on and how we use the scn for consistent reads so quickly just going over the concept of undo right for some of you who may not be aware um so so so it may be evident by the way but it's a but it's really interesting to see that in the database right for every forward going change right let's say you inserted a row or updated updated and deleted for every change you have a complementary change which which goes back right which takes your uh takes your transaction back for an insert

right the complete the the complementary operation is a delete for an update it's an update for a delete it's an insert foreign the database but why do we have undo compared to the row versions in the Oracle database some databases right distort the row versions itself okay um we don't have to do uh this complex garbage collection for the stale reversions so as transactions move forward and make changes right and if they roll back they would lead to you know like stale row versions but in Oracle right we don't have to do that complex you

know like garbage you know the collection over here so the undo is recycled automatically using you know an undo segment which I'll discuss next so we have undo segments in Oracle okay and feel free to stop me by the way uh for anything okay so we have I mean we looked at undo so let's look at undo segments so an undo segment you know like in at a very high level it's a persistent circular buffer for storing undo okay so the delete goes uh goes in the down to segment so does the update and the

update and the insert in a circular fashion so in this undo segment right as these undo records right they become obsolete right they are recycled so so we overwrite the the expired you know like unto so in this case if this transaction commits by the way so all these you know all these changes which is the delete update update and insert they would get overwritten by a future transaction so the undo segment you know it composes of undo blocks and and undo blocks have undo records so the undo blocks you can think right practically right

you know it is like you know a data block itself you know a data block holds rows but an undo block holds undo records which is which is a sparse version of the rule it just contains the changes to that rule and it's important to understand one more thing that these undo blocks right they're also cached in our buffer cache so if you have to roll back data we just read the undo blocks from deeper like from the buffer cache right we're just coming next I think uh yes so so let's discuss about the Sandhu

segment right and why do we need this data structure but first right let's argue right that why we why we can't read from the redo log itself right because the undo it also goes into the redo log itself so the redo goes in the redo log but the undo also is protected by the redo log for recovery but why not just read the undo from the redo log because you have to read backwards in that large redo log file okay and it's very inefficient so imagine you have like thousands of transactions which have their footprint

in the large redo log file and you have to roll back just one transaction right so you have to like just read the whole redo log file and find out which undo is which undo belongs to your transaction which you want to roll back and that's very inefficient okay and why do we have an undo segment because undo blocks as I mentioned earlier they're cached in the buffer cache for faster rollback and it's efficient to transfer undo blocks right between those rack instances right with our cash Fusion technology and these and this undo is also

used for consistent reads and transaction recovery so I'll be discussing consistent reads in the next few slides but also an important reason of keeping the undoin dandu segment and not in the redo log is because if your transaction was active and if your undo was only in the redo log so in Oracle right we have this concept of a log switch okay because you do logs are also recycled right we keep like two redo log files okay and transactions right to one uh right we have a log writer process which writes all the log records

to to one of the files and once that file is full we move to the next file so we switch to the next you do log file and we fill that and then we switch back so that uh the other redo log file right you know like it's not being used uh so so now you can switch back to the redo log file okay it's used for you know like archiving you know like purposes right the other redo log file it can be used for archiving so we have this concept of a redo log switch

in Oracle okay so now imagine if your transaction is active and you have to do a redo log switch if you switch to another log file you do log file then you cannot uh read the undo which was in the previous log file right because you have switched already to log file so that is why right we we use an undo segment so that this transaction is kept active right otherwise it would block the redo log switch so the undo segment site they don't uh um they are they are just like data essentially to us

okay so now that we have discussed some Concepts so let's more let's talk more about transactions and I can see I'm running uh you know like late on time right so I'll try to speed it up as well um yes yes go ahead so are these server um no in the shared uh you know storage you know in a separate storage area so so I redo logs right so I redo logs yes yes they're shared between all the servers all the database servers but all the database servers they have um they write their own redo

log right so we have this concept of rack instances as I had mentioned so every rack instance right it writes its own redo logs okay but later these redo logs you know like are merged essentially uh you know in the storage right does that answer your question yeah great thank you so so what is an oracle transaction right you know and I'll take the you know like same silly example of the cheetah Safari app right so let's say if it has to create a ticket right to create a ticket like what it does it creates

a new customer and it adds that customer to the customer tours okay and it commits those changes this is what a transaction is because you want to atomically you know like make these changes and make sure that all these changes are durable on a crash right so the first statement in Oracle is the first DML statement it begins the transaction and then you have data modifications which are these inserts and then you commit so we look into the life cycle of an oracle transaction next okay so let's look into the beginning of a transaction how

do we begin the transaction we saw the undo segment uh you know aspect right so a database session right when it has to begin a transaction it does something called as under segment binding okay and here I'm you know like I'm getting into the guts of the Oracle transaction that how we do it so it's more of like design and implementation aspect um which not many of us get to see you know like in you know in college so let's you know like look at that right so the database session uh you know like first

it needs to bind to another segment so it binds to the recently accessed undo segment you know for load balancing after you bind to an undo segment we'll allocate a slot in a in a data structure which is called a transaction table so the transaction table you know it lives in the undo segment right into the undo segment header block and this transaction table is nothing but it has a bunch of slots in it and this transaction essentially right you know it takes one of those slots right so an incoming transaction over here it took

the slot number two right and this is important okay like this this transaction table data structure you know it achieves a few things for us right which we'll look at next and this transaction table essentially you know it looks like this you know it has you know bunch of slots it's not just five slots it just seems like compacted you know like I mean for the slide but it has a bunch of slots right based on the size of the data block we have the state of the transaction whether they are committed or active right

we have sequence numbers and the scns okay which are the start and commit times of the transactions so committed slots in Oracle right they get reused by incoming transactions okay so if you have you know transaction which is incoming so what it does right so in this example right you know slot number four got reused okay and the sequence number right because it was views it got bumped up right you know it went to 12. so this step right where we allocate a slot in the transaction table right is important because it generates a transaction

ID for us right and the transaction ID is the undo segment number right the slot number and the sequence number okay and this transaction IDE is what we keep you know in the metadata you know in the itl or the interested transaction list that I had shown you guys okay so we'll see how we leverage this aspect you know for achieving atomicity in Oracle okay so the sequence number is also incremented and the undo to the transaction table itself is recorded the undo to the transaction table is used for rolling back of the transaction table

in case we have to look up the outcome of the transactions which um whose slot has been reused so in this case right we had a previous transaction with with the sequence number 11 which we knew it committed at this scn right so if somebody wants to look up the outcome of that we'll have to roll back the transaction table so the final thing in the beginning of the transaction is writing the undo itself so simply we write the undo in the undo segment itself okay for the data change right because it's the first DML

right which Begins the transaction but it but at the same time it's a DML so it's modifying data we have to write the data for that insert which is the delete in the undo segment itself and let's look at the data modifications next so to make the data changes so let's assume that you have a transaction one which wants to update Row 1 in this data block or buffer in the buffer cache it stores the metadata in this data block right it says that hey right transaction one is active and here the transaction one which

is active this is actually right the xid or the transaction ID and we have a state that hey this is active so it makes a change to this row and it takes a row log and we write the undo to the undo segment and at the same time right this transaction it only because it's an ID it's pointing to the transaction table slot okay in this uh in the transaction table so we look into how we use this aspect right for achieving atomicity in order right but let's first discuss about these steps right so what

is commit now if that same transaction commits it simply flips it does the right to the transaction table and it changes the state of the transaction from active to committed and it also stores the scn of course right the time at which this transaction uh you know commits and what is rollback so if this transaction wants to roll back all the changes made by this transaction what it does is that the transaction table you know it saves a pointer to the undo chain so so I didn't mention onto chain earlier but we keep a chain

of these undo records in the undo segment itself so in the previous example where I had shown you guys we have a delete and an update update insert all those changes are chained in the undo segment and the pointer to that chain or linked list is there in the you know is there in the transaction table and it's all persistent so if I have to roll back the changes we just apply the changes from the undo segment to the data block to restore the version of that uh to restore the old data and we mark

this transaction as committed so another aspect right even during rollback the transaction commits because the database always moves forward even the rollback right the change that's applied that's also a change to the data okay so transactions in Oracle always commit because even the undo is a change let's quickly uh do a time check okay so yeah we have we have time so let's look into how we achieve in acid in Oracle okay so far we looked at the life cycle of an oracle transaction we looked at the database architecture but let's fit in all of

those things into how we achieve you know acid an oracle okay the same example right for the transaction okay so we have acid right I won't go into the details of what asset is you know I assume that many of you know about it but asset is basically atomicity consistency isolation and durability right and we'll look into this next okay how we achieve those suspects so atomicity means all your data changes should be atomically you know uh changed and should be atomically visible to all the readers in the system so imagine the same transaction which

I had like transaction number one it modified like multiple rows Row one I wrote 21 11 31 right it modified all these rows on different data blocks right so how do you atomically change those data blocks now and that's using this data structure called the transaction table where this transaction was active and all these data blocks because they have the itl or the interested transaction list entry in the data block which points to the transaction table if I flip the outcome of this this transaction in the transaction table to commit it right the fact that

everybody is pointing to this record so all the data changes you know are Atomic then but you may ask that hey that I have this footprint of transaction in the data in the data block where I had saved some metadata which says that this transaction is active right I've not changed that active to committed that is marked lazily you know in Oracle so the data blocks you know like active to committed change you know it happens lazily and it's it's using a thing called clean out of data blocks in Oracle okay so remember the atomic

commit where we had flipped the outcome of this transaction to commit it but the data block still says that this transaction is active right and the row is locked so the next query or the DML it does a clean out the next reader essentially or the writer you know like if that is reading this data block right it notices the row is locked it finds that this transaction is active then it consults the transaction status in the transaction table and it finds that this transaction is committed so then it marks the transaction as committed and

we also release the rolock so this clean out itself happens lazily and it's a log change it goes into the redo log itself and how do we achieve faster data Block cleanouts in Oracle um so let's first look at the problem right with cleanouts so the problem with cleanouts over here is that we have to read the transaction table right this transaction table itself it lives in the undo segment in the header block of the undo segment so you have to read that header block right which is an i o penalty okay you have to

do an i o to read the transaction table so the clean outs they need an optimization right and to avoid that we have a thing called the commit cache on our exit data engineered systems so exit data essentially right it's essentially you know like a database Appliance right which has you know like lots of CPUs lots of cores you know like a lot of storage okay so it has storage servers it has database servers you know like and it has networking which is built into uh into the appliance itself okay that is exit data and

on accelerator we have a commit cache it is in memory commit cache so this commit cache you know it has a mapping essentially right of transactions to their commit scns and it's maintained on every database instance right and it can be consulted during cleanouts so it speeds up the cleanout process and it helps in increasing the transaction throughput of the database and these commit cache entries they can be read using RDMA on exit Data Systems so RDMA is you know is remote direct memory access okay so I won't be covering the detail of that um

right and that's how we achieve you know like atomicity you know of data changes in Oracle and let's quickly look at you know the the the aspect of consistency now so a data block let's say right it has multiple rows okay and it and it has a column called tourists okay so you have two tourists in one group and three tourists in another group okay and the sum of these two is for um for that Safari essentially you know it's less than 10. okay you know it must be 10 right that's the constraint which is

there on this data and if I issue a single update which updates both of these rows you know in a single statement and it increases the number of tourists in those two groups by three so what happens the first row is modified and we generate the undo for that we keep the older version of that row but then as soon as we modify the second row because it's going to violate the constraint it's going to be 6 plus 2 plus 3 which is 11 which is greater than 10. then it violates the constraint so Oracle

would apply the undo to roll back that statement okay and it keeps the Integrity right the data integrity you know in place so that the Integrity constraints are never violated and it uses undo the undo mechanism of transactions for that okay so let's talk about isolation aspect you know of acid in Oracle we have the isolation Mode called consistent reads okay so in the same example right if transaction one was active uh right and a transaction two it wants to read a row right Row 1 over here right but it happens to be a row

that is already Modified by this transaction one okay and it's already been changed and locked then in Oracle right we don't allow transactions to read uncommitted changes so what does transaction 2 do right it finds out that transaction one is active from the transaction table right because I had mentioned about the clean out aspect so if the cleanout is not done it's essentially like checking for that and then it copies the data buffer in the buffer cache and it applies undue to that so that we restore the version of the data right to its older

version which is which is committed so in Oracle right transactions always read you know like committed changes and they're also you know consistent as of a you know point in time okay I'm not sure if I'll have time to cover that today but uh but let's see so that was rewrite conflicts okay but what about right right conflicts if transaction one was active and transaction 2 also wants to modify the same data but the rolock is already held so the transaction finds you know like transaction two finds transaction one in the transaction table as active

then transaction two just Waits on the true log right so we don't have like uh like multiple versions of Rights in Oracle okay um we want to achieve like statement level serializability and for that we would block this transaction so let's talk about the durability aspect of transactions now okay so all the changes made by this transaction means by made by any transaction they have to be persisted on Commit okay and that's what durability is okay how do we ensure it so in Oracle we have redo log buffers which are in memory and also we

have a redo log stream which is on disk at the time of commit all of your changes all the redo log changes right they have to be flushed on Commit right before we return back to the client and also right the database recovery right it uses redo which is flushed on this to roll forward the database and it uses the undo right to roll back the database right and again right the redo and undo both are protected by the redo itself right and that's pretty much it right towards uh you know as we are at

the top of the r as well okay you know I kept it short right I wanted to discuss more on the aspects of consistent reads and RDMA next but I realized right that that I have a lot of content for you guys okay so that is it from my side right and what we discussed today right we discussed the asset implementation of Oracle we discuss the life cycle of a transaction in you know in Oracle and we discuss how it fits overall into the overall architecture of the databases of Oracle database right so so so

that's pretty much it right and in Oracle transactions right for those of you who are interested you know in knowing that what is are you know a track you know or our Tech stack we mainly work on distributed systems right databases storage systems operating systems you know data structure algorithms so as you can see over here right all the specs of databases right you know in storage systems right are being you know like handled by our team okay and thank you to everyone right you can reach me you know at my email address okay and

if you guys have any questions right please feel free to ask me okay uh and we can discuss it now or you can just email me as well but yeah uh thanks everyone again thank you very much so any questions guys so I have one question a lot of databases are moving to the cloud these days do you think that texture will change for in what aspect when you move to the cloud or maybe you're not interested in moving to the club but I think you are because I expected that question right I expected that

question right from somebody so so I would answer that in two aspects okay so the first aspect is that that a lot of databases are moving to a cloud-based architecture right uh but Oracle database is already deployed on the cloud today we have a lot of cloud services today which use Oracle okay we have the autonomous database cloud services which deploy our multi-tenant model uh of our database where we have pluggable databases within a container database you know it's already deployed out there okay but in terms of scalability of transactions you may ask right or

you know the whole database right so the exer data system uh sorry uh the Oracle rack scale out architecture that we have right you can add multiple nodes you know you know or you know processing nodes right in that architecture itself and we would connect them over the fast interconnect you know and the buffer cache technology right it scales well on the cloud as well as of today and if the customer wants to you know like scale to you know like more in the cloud right we have accelerator engineered systems essentially where you can you

know like purchase different racks right and you can scale you know like the compute and Storage you know at the same time so we scale to like multiple racks on the cloud okay and that is what is deployed on our Cloud today right but that being said right we already have a scale out architecture which which works very well you know like on our Cloud applications today but at the same time right we're also you know looking into you know like cloud-based architectures today you know I can't talk much about that uh you know I

can only talk about the things we have done and you know and what we have deployed out there but yes we are looking into you know like other aspects of the cloud and seeing how you know like different architectures you know or different uh you know aspects of the cloud right that we have to consider for our database right that should be like you know a tweaker architecture a bit or more to achieve scalability for cloud right that is what we are looking into right now so I hope that answered your question yeah thanks it's

great but yes hopefully uh you know in the next few years right we'll be talking about Cloud scalability as well so any other uh questions or otherwise yes yes that's all thank you Kishi thank you yeah thank you all right thank you so much everyone um so I did not have like time to cover the slides for like consistent reads you know RDMA and other aspects of scalability of Oracle transactions right so maybe like that's for later uh but yes right thank you again to everyone you know for asking you know like all your questions

you know and hosting me okay thanks again you know like to Professor like you know like shy thing right for hosting me uh you know like it's it's great that we are connecting you know like you know uh we are in touch right and we connect the Academia you know and the industry and we meet uh you know to discuss various ideas you know I really like it and thanks again right for hosting great looking forward to your future collaborations yeah yes thank you so much thank you thank you see you see you guys thank

you