----Oracle Transaction.
1. User requests a connection to oracle
2. A new dedicated server process is started for the user
3. User executes a statement to insert data in to a table
4. Oracle checks the users privileges, it first checks the library cache (cache hit) for the information and if not found retrieve it from disk.
5. Check to see if the SQL statement has been parsed before (library cache) if it has then this is called a soft parse, otherwise the code has to be compiled a hard parse.
6. Oracle creates a private SQL area in the users session's PGA
7. Oracle checks to see if the data is in the buffer cache, otherwise perform a read from the data file
8. Oracle will then apply row-level locks where needed to prevent others changing the row (select statements are still allowed on the row)
9. Oracle then writes the change vectors to the redo log buffer
10. Oracle then modifies the row in the data buffer cache
11. The user commits the transaction making it permanent, the row-level locks are released
12. The log writer process immediately writes out the changed data in the redo log buffers to the online redo log files, in other words the change is now recoverable.
13. Oracle informs the user process that the transaction was completed successfully
14. It may be sometime before the data buffer cache writes out the change to the data files.
Note: if the users transaction was an update then the before update row would have been written to the undo buffer cache, this would be used if the user rollsback the change of if another user run's a select on that data before the new update was committed.
1. User requests a connection to oracle
2. A new dedicated server process is started for the user
3. User executes a statement to insert data in to a table
4. Oracle checks the users privileges, it first checks the library cache (cache hit) for the information and if not found retrieve it from disk.
5. Check to see if the SQL statement has been parsed before (library cache) if it has then this is called a soft parse, otherwise the code has to be compiled a hard parse.
6. Oracle creates a private SQL area in the users session's PGA
7. Oracle checks to see if the data is in the buffer cache, otherwise perform a read from the data file
8. Oracle will then apply row-level locks where needed to prevent others changing the row (select statements are still allowed on the row)
9. Oracle then writes the change vectors to the redo log buffer
10. Oracle then modifies the row in the data buffer cache
11. The user commits the transaction making it permanent, the row-level locks are released
12. The log writer process immediately writes out the changed data in the redo log buffers to the online redo log files, in other words the change is now recoverable.
13. Oracle informs the user process that the transaction was completed successfully
14. It may be sometime before the data buffer cache writes out the change to the data files.
Note: if the users transaction was an update then the before update row would have been written to the undo buffer cache, this would be used if the user rollsback the change of if another user run's a select on that data before the new update was committed.
No comments:
Post a Comment