2

For mutating SQL statements (insert, update, delete), when does SQL returns response to client?

Is this

  1. after the change has been written to the transaction log but b-tree hasn't been updated, OR
  2. after the change has been written to the transaction log and b-tree index has been updated

I am confused because in order for seek operations to work, the b-tree index has to be up-to-date, so I would imagine #2 is correct.

But transaction log is also used to recover from crash. If SQL crashes in the middle of a mutating request, the client never receives success code anyway and the client can retry.

So in what scenario does the t-log provides disaster recovery, assuming there is no replication involved?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user21479
  • 1,179
  • 2
  • 13
  • 21

1 Answers1

0

It is important to understand the step by step process of SQL Server making changes to the stored data.

SQL Server uses Write-Ahead Logging algorithm to make any changes to the stored data in SQL Server. Following are the steps taken to make sure each statement/transaction maintains the ACID property of SQL Server.

  1. A copy of the page(s) [Rows] that needs updating are loaded into buffer cache.i.e all the related pages of the B-Tree and any non-clustered indexes(if any).
  2. Changes are made to the copy of pages in the buffer cache.
  3. At this point, the page is marked dirty.
  4. An entry is made in the Transaction log for each change made to the page in buffer cache.

  5. Return response to the client about the successful/unsuccessful change.

  6. Finally a checkpoint is issued (this could happen before the user response is sent or after, as this is a separate process), the process scans the buffer cache for dirty pages, write the changes to the disk and remove the dirty page records from the cache (process also knows as flushing the pages). And only at this stage the changes are actually written to the disk.

The checkpoint process is the one that makes sure that no dirty pages ever get flushed. This process guarantees the ACID property of transactions in the SQL Server.

If the page has only been modified in the buffer cache and has not been written to the disk(dirty page) , at this point if SQL Server crashes, after coming back online SQL server will decide whether to roll forward or rollback the transaction but data is always left in the consistent state.

If the above mention process fails anywhere in the middle and cannot complete the whole process for any reason, SQL Server marks the data pages as suspect (database corruption) which does happen sometimes but not very often (at this point you will see your DBA sweating). But you shouldn't worry about this unless you are the DBA :)

M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • A checkpoint is NOT issued for each query. The server returns response to the client without directly relation to the checkpoint. There are several types of checkpoints: automatic (related to recovery interval server configuration and number of outstanding writes), indirect (related to TARGET_RECOVERY_TIME and the server version since the default was changed from 0 to 1 minute in 2016), manual (executing query), and internal (related to tasks like backup database). You can find dirty and clean pages using sys.dm_os_buffer_descriptors – Ronen Ariely Jun 18 '18 at 01:04
  • @RonenAriely never claimed that checkpoint is issued for each transaction, but when the checkpoint is issued then the rest happens. – M.Ali Jun 18 '18 at 01:06
  • You wrote it in step 5, which is not supposed to be there! The server will not wait for the next checkpoint in order to return the result ;-) . You should remove step 5 which is done in separate tasks according to the type of checkpoints which I mentioned above. – Ronen Ariely Jun 18 '18 at 01:14
  • @RonenAriely Yes, Now reading it again, it does look like I am suggesting Response to User is only issues when the checkpoint is issued and page flushed :) , will sort it out now, thanks bud. – M.Ali Jun 18 '18 at 01:18
  • I tried to put your name with sign @ like you did, but for some reason the site clear the name... I have no idea how to add "@M.Ali" in the beginning of the comment – Ronen Ariely Jun 18 '18 at 01:18
  • I think because this is my answer, so you do not have to add my name to the comment, I get notified anyway when someone comments on my answer (same is true for questions too), its only if you were chatting to someone else in the comments you can highlight their username using @ so they get a notification when you are addressing them in the comments. – M.Ali Jun 18 '18 at 01:23
  • Glad I could help :-) and thanks for the explanation regarding using the username in the comment. I don't like this option since there might be several comments from several users and I need to clarify to who I respond (By the way, I am looking for another forum interface where I can help in. I am moderator at MSDN and several other forums, but time to find new place... maybe it will be here... who know... I am checking the interface now) – Ronen Ariely Jun 18 '18 at 01:26
  • @RonenAriely A person like yourself with all your experience will certainly be a valued member of any platform, please give this form a bit more chance, you will enjoy yourself and definitely a lot of people can benefit from your experience and knowledge :) – M.Ali Jun 18 '18 at 01:29
  • @RonenAriely Anything you dont like about this platform and think you have a suggestion that can make improve the user experience please raise it on [`Meta`](https://meta.stackexchange.com). – M.Ali Jun 18 '18 at 01:31
  • Thanks for the nice words and the reporting info ;-) – Ronen Ariely Jun 18 '18 at 01:32
  • @RonenAriely No problem mate :) – M.Ali Jun 18 '18 at 01:34
  • Now I remember why I did not like this interface in the past :-( https://stackoverflow.com/questions/50901457/how-to-know-sql-server-is-in-cloud-or-on-premises Well... back to MSDN to help people there :-) – Ronen Ariely Jun 18 '18 at 02:44