SQL statement execution process

After the server receives the SQL statement, perform the following steps: Parse (resolution)-> Bind (binding)-> Execute (execution ).

A flowchart clearly shows these processes:

The following describes the specific execution details:

1 Select (query)

2 INSERT/DELETE/UPDATE

These are the statements for modifying the data and draw a flowchart, Which is concise and clear:

This statement is confusing. For example:

If there is such a row, the student id is 100, and the score is 99, you need to change this score to 80 (the row corresponding to this ID has only one row ).

The SQL statement should be update student set score = 80 where stu_id = 100;

Assuming that the data block is already in the buffer cache, you must first obtain two locks: The table's data row exclusive lock and the data row exclusive lock. Next, we need to copy the Redo Entry with 99 records to the log buffer. Then, an undo block will be generated, which is like this ). After that, the modified Redo Entry will also be copied to the log buffer, which is 80. After all this is done, the score will be updated from 99 to 80.

After all this, enter commit.

In this case, the database will do the following:

1 will copy the Redo Entry of commit to the Log Buffer. In this way, there will be three groups in the Log Buffer in chronological order: (, 99), (, 80), commit.

2. Start LGWR. Write the Log Buffer to the Online Logfile in the above sequence.

3. Tell the user that the process is finished.

For a long time, I thought that after commit, data will be written to the data file, that is, Datafile, but this is not the case. After the preceding steps are completed, the Datafile still exists ). The next time you query the data in the buffer zone, you can directly query the data in the buffer zone. If the buffer zone is tight, DBWR starts to work and fl the buffer into the data file.