Question

Consider faculties (users) of a certain college, initiates remote database connections using the college application and saves attendance of their students in the class. Students’ attendance details are saved in the college database server.

Using the above context as an example, draw the diagram and briefly elaborate on the series of actions done in a transaction server process architecture.

Your solution must include the use of user process, server process, log writer, database writer process and checkpoint process in performing the transaction. Solution should also include the shared memory areas (buffer pool, log buffer and query plan cache) and physical structures (data files and log files

Answer 1

1. The users interface with the database through the school application which is built up with the assistance of a Listener and SMON and PMON

2. Upon association foundation, Program Global Area is allotted, the statement is sent to the server and a User Process is made. (relies upon Architecture if it's Dedicated or Shared)

3. Presently the Server procedure looks for the comparable statement in the Shared pool.

a. In the event that comparable statement is found in the Shared pool zone, it is utilized.

b. In the event that not discovered, at that point the statement is parsed and put away in Shared SQL Area.

4. Parsing includes the means

a. Parse (Syntax, semantics, Privileges)

I. Language structure – The essential structure of the statement. For our situation it's a statement to spare the participation in the server.

ii. Semantics – The factors which really characterize the column on which the assignment is to be performed.

iii. Benefits – To check for access and awards required for the undertaking by the client. Just the resources with explicit jobs (Grants and benefits) can play out the assignment.

b. Execution plan which is produced by Optimizer.

c. Execution of the statement

5. Upon fruitful parsing of the statement and execution plan, the server procedure peruses the data from the Data Files (physical) and stores in Database Buffer Cache.

a. In the event that the data is now in the Database Buffer Cache, at that point it doesn't get it again from the Data Files.

6. An exchange ID is created and changes are made to the data hinder in Database Buffer Cache by the server procedure. A lock is applied on the data to keep up the consistency.

a. Value-based procedure must agree to ACID properties (Atomicity, Consistency, Isolation and Durability)

7. The changes/refreshes made on the data squares are written in Redo Log Buffer. (Record of all activities performed on the data, both old and new with essential key subtleties)

8. At the same time Undo data is put away in Rollback Segments. (The old data before alteration/update)

9. These adjustments in Rollback portion is recorded in the Redo log cushion also. (Re-try log cradle logs everything).

10. Presently the server gives a message that Process is finished effectively.

11. On the off chance that the College application has a spare catch which most presumably will provide the submit order to the server to submit the progressions made. Thinking about this case, when the users(Faculties) press the spare catch, the records are commited.

12. On submit,

a. SCN (System Change Number) alongside Transaction ID is connected to the re-try log cradle data and afterward all the data in re-try log cushion is pushed/written to Redo Log Files (Physical) by the LGWR (Log essayist process).

b. Changes made in the Database cushion reserve is made perpetual by connecting this SCN information to the data square and lock which was applied before is evacuated.

c. Until there is a lock, any synchronous client attempts to get similar data will see the old data kept up by the Undo log cushion. Upon submit, the new data is made changeless and lock is evacuated and new data will be seen by different users.

d. This adjusted data with SCN information is known as the DIRTY support now.

e. When there is a checkpoint given by the client or the server procedure, the data in the Database Buffer store is kept in touch with Data Files by Database Writer (DBWn) process.

f. This activity can likewise be trigger when the Database Buffer Cache is practically full or the client executes submit order.

At that point the data is refreshed/altered (Attendance data is put away in the database for the understudy by the workforce) effectively.

Shared Pool Area Database Buffer Cache Redo Log Buffer Data Shared SQL Area (parsing, execution...) Data Lock +SCN (Commit) Data blocks fetched from the Datafiles (Logical Memory) All the redo logs are present here. Library Cache Data Dictionary Cache Undo log buffer SCN + Transaction ID Data CKPT DBWn LGWR Data Files Redo Log Files (Physical Memory) (Physical Memory) Server process 1 User

Thank You Please like this it really means alot ?!!!!!