InterviewSolution
| 1. |
Explain the Write-Ahead Transaction Log (WAL) protocol? |
|
Answer» To understand Row versioning, you should first understand RCSI (Read COMMITTED Snapshot Isolation). RCSI does not hold a lock on the table during the transaction so that the table can be modified in other sessions, eliminate the use of shared locks on read operations. RCSI isolation maintains versioning in Tempdb for old data called Row Versioning. Row versioning increases overall system performance by reducing the resources used to MANAGE locks. When any transaction updates the row in the table, New row version was generated. With each upcoming update, If DB is already having the previous version of this row, the previous version of the row is stored in the version store and the new row version contains a POINTER to the old version of the row in the version store. SQL Server keeps running clean up task to remove old versions which are not in use. Until the transaction is OPEN, all versions of rows that have been modified by that transaction must be kept in the version store. Long-running open transactions and multiple old row versions can cause a huge tempDB issue. |
|