技术控

    今日:47| 主题:49507
收藏本版 (1)
最新软件应用技术尽在掌握

[其他] The Transaction Log, Delayed Durability, and considerations for its use with In-

[复制链接]
失夜Sakitama 发表于 2016-10-5 09:10:43
103 0

立即注册CoLaBug.com会员,免费获得投稿人的专业资料,享用更多功能,玩转个人品牌!

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

The Transaction Log, Delayed Durability, and considerations for its use with In--1 (understand,performed,efficient,important,operation)
   If Warhol did transaction Logging…
    In this mini-series of posts, we will discuss how the mechanics of SQL Server’s transaction logging work to provide transactional durability. We will look at how Delayed Durability changes the logging landscape and then we will specifically see how In-Memory OLTP logging builds upon the on-disk logging mechanism. Finally, we will pose the question “should we use Delayed Durability with In-Memory or not” and discuss this scenario in detail. But in order to understand how delayed durability works, it is first important for us to understand how the transaction log works -so we shall start there…
  Caching caching everywhere nor any drop to drink!
  SQL Server is a highly efficient transaction processing platform and nearly every single operation performed by it, is usually first performed within memory. When operations are performed within memory, the need to touch physical resources (such as physical disk IOPS) are also reduced, and reducing the need to touch physical resources means those physical boundaries (and their limitations) have less impact to the overall system performance. Cool right?!
  A little bit about the Buffer Cache
  All data access such as insertion, deletion or updates are all first made in-memory, and if you are a Data Administrator, you will (or should!) already be familiar with the Buffer Cache. So when data pages are needed to fulfill updates, deletions or inserts (and assuming they are not already present in the Buffer Cache), the Storage Engine first reads those data pages into the Buffer Cache before performing any requested operations on those pages in memory. Those dirty pages will ultimately be persisted directly to disk (i.e. to the physical data file\s) through automatic checkpointing and these pages will contain changes performed by either committed or uncommitted transactions. In the latter case of uncommitted transactions, the presence of those dirty pages in the data file\s is why UNDO is a necessary operation that is required upon recovery -and those changes must be rolled back using the transaction log records to do so. Any dirty pages that are a result of committed transaction changes but have not yet been hardened to the physical data file\s through a CHECKPOINT operation would require the REDO portion of the transaction log (assuming that the SQL Server failed) to roll those changes forward. I will refer back to this again when we move on to talk about In-Memory OLTP.
  It’s all about the log, about the log, no trouble!

The Transaction Log, Delayed Durability, and considerations for its use with In--2 (understand,performed,efficient,important,operation)
But it is not the hardening of data to data files that we are focusing on here (since there is little relevance to what we are focusing on), we are more concerned about transactional durability in SQL Server. There is a common misunderstanding with SQL Server DBAs that when transactions are making changes, the transactional changes are written immediately to the logfile. The durability claims of SQL Server make this misunderstanding easy to understand, but requiring a physical IO operation to occur upon every single transactional data modification would clearly be a huge potential bottleneck (and dependency) upon the transaction log disk performance. The SQL Server architects recognised this challenge, so in a similar way that data page modifications are first cached, so are the transactional changes. In the case of the transaction log, the “cached” area is provided by in-memory constructs known as log buffers that can store up to 60 Kilobytes of transaction log records.
   Now that you know that log buffers are there to delay transaction logging to maximize the efficiency of a single physical IOP to disk, we must now consider when these structures must absolutely be flushed to physical disk and stored within the transaction log in order to still provide the Durability property of ACID that SQL Server adheres to. There are two main situations:
  
       
  • On transactional COMMIT. When a client application receives control back after issuing a successful COMMIT statement, SQL Server must provide durability guarantees about the changes that have occurred within the transaction. All transactional operations must have been written and persisted to the transaction log, therefore the log buffer containing those changes must be flushed on COMMIT to provide this guarantee.   
  • On log buffer full. Strictly speaking, as long as the first rule is adhered to, there is no logical requirement that SQL Server should flush the log buffer to disk when it becomes full. However, if you consider that it is paramount for SQL Server to never run out of available log buffers, then it is obvious that the best way for it to avoid this situation is to hold onto the log buffer only as long as it needs to. If a log buffer is full, then it serves no further purpose in the buffering story and furthermore, given that the log buffer will contain a maximum of 60Kilobytes of changes, there is good alignment to the physical write to the transaction log. When the log buffer is full it makes sense to flush it to disk.  

12下一页
友荐云推荐




上一篇:【技术分享】Dlink DWR-932B路由器被爆多个安全漏洞
下一篇:[译] 机器人可以如何通过共享经历学习新技能
酷辣虫提示酷辣虫禁止发表任何与中华人民共和国法律有抵触的内容!所有内容由用户发布,并不代表酷辣虫的观点,酷辣虫无法对用户发布内容真实性提供任何的保证,请自行验证并承担风险与后果。如您有版权、违规等问题,请通过"联系我们"或"违规举报"告知我们处理。

*滑动验证:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

我要投稿

推荐阅读

扫码访问 @iTTTTT瑞翔 的微博
回页顶回复上一篇下一篇回列表手机版
手机版/CoLaBug.com ( 粤ICP备05003221号 | 文网文[2010]257号 )|网站地图 酷辣虫

© 2001-2016 Comsenz Inc. Design: Dean. DiscuzFans.

返回顶部 返回列表