--- Quote Start ---
I tried to understand this code - How is this delayed by 2 clock cycles? I am a conventional C coder. This is for delay of 2 cycles. If i use the same logiccc can I do for > 2 delays? How is this executed? I know that the process if-else is executed sequentially.
--- Quote End ---
Maybe your conventional C programming experience is misleading you.
Please note the the following assignments
write_req_1 <= write_req;
write_req_2 <= write_req_1;
DON'T behave as in conventional C, where you would obtain write_req = write_req_1 = write_req_2 in the end. Actually the <= assignments are performed in parallel once when the process is triggered (in your case on the clock rising edge).
Then the second line will assign to write_req_2 the original status of write_req_1. In other words, you can change the order of the two lines without changing the result.
This code is usually synthesized with 2 chained flip-flops, but in general the implementation of more complex code will depend on the tools you are using, like C code would result in different assembler instructions depending on compiler and optimization level.
Regards