SQL Server Lock 架構讀後心得

記得以前在處理專案時,常遇到一個交易封鎖了一堆交易的問題,非常困擾。遇到這種情形,通常 都是踢掉幾個封鎖他人的交易作為解決辦法。但是不是原本的DBA設計就是有問題的呢?為了讓日後的資料架構的設計能夠減少LOCK的狀況,小弟就很努力的 先去了解一下SQL Server的Lock機置。

當我們要開始介紹Lock之前,首先來瞭解一下資源(Reource)的層級。如下表,SQL Server將資源分為11級並且可以進行鎖定,由上而下,越往下代表層次越低。

image

資 料庫引擎進行鎖定時,通常是以同時產生多種層級以上的鎖定來完整的保護資源,這一組的多層級的鎖定,我們稱之為「鎖定層級(lock hierarchy)」。我們在此舉個例子。系統為了要完整保護索引的讀取,資料庫引擎必需同時產生一個Shared Lock(S)在一筆資料列上,以及分別產生一個Intend Lock(IS)在資料頁(PAGE)及資料表(TABLE)上。這個例子可以看出,一個交易針對一個資料表並不是只產生一個鎖定而已,而是產生多種鎖定 來保護他自己所能讀取的資源。

實用英文

小笨霖英語筆記本 一零五: 爬山

一零五: 爬山

         上次有幸去田納西境內的大煙山國家公園 (Great Smoky Mountains National Park) 參加一個學術會議,當時正值楓葉變色的季節,國家公園裏美不勝收。所以我就利用最後一天的下午,偷偷溜去公園裏的彩虹瀑布一遊,順道記錄了沿路走來和老美閒話家常的心得,也算是此行的一項額外收穫。

1. We need to go back down.

我們該下山了。

“We need to go back down." 這句話值得學習之處在於,老美常常在 go 或是 come 之後連放兩個副詞來同時表達「方向」和「目地」。例如在同樣的句子我可能會說,"We need to go back." 或是 “We need to go down." 但我就不會想到把 back 和 down 連用便成了,"We need to go back down." 這樣子用法的好處在於可以同時表達出,「回去」和「下去」這兩個概念。

聽到這句話時讓我想起以前也常聽媽媽在叫她的小孩回來,"Come back over here." 當然你也可以單說,"Come back." (回來),或是 “Come over here." (過來這裏。),但是把 back 和 over here 連用就成了 “Come back over here." 也就是回來這裏的意思。

2. We have folks passing us.

有人要超過我們。

Folks 這個字指的是一群人,跟 people 的意思是差不多的。但是 folks 這個字的好用之處在於,它可以用來指一群跟自己有點關係,但又稱不上是 friends 的人們。最簡單的例子,假設我今天要發一封 e-mail 給系上同學,e-mail 的開頭就可以寫 Hi folks, 當然如果不嫌肉麻的話寫 Hi my friends, 也可以,但是就不能說成 Hi people, 啦!

另外 pass 這個字是當「超過」解釋,所以整句話 “We have folks passing us." 的意思就是「有人要超過我們。」或是在開車時則是指 「有人想要超車。」當時因為爬山時山路狹窄,只能容一個人通過。所以當我想走快一點超過前面的人時,他們當中就有一個人提醒其他的同伴們說,"We have folks passing us." 請大家先靠邊站,把路讓出來給後面的人先行通過。

而今天如果我是要超過別人的那個人,我必須說聲 “Excuse me." 或是 “Excuse us." (兩人或兩人以上),以示禮貌。而且我發現以前在國內時都是除非路完全被別人堵住了我才會說借過一下,但是在美國,個人的感覺是,只要那個人是在你經過的路線伸手可及的範圍內 (Within arm’s reach),老美就會說 “Excuse me." 或是 “Excuse us." 

Enterprise Solution Patterns Using Microsoft .NET

Enterprise Solution Patterns Using Microsoft .NET

Enterprise Solution Patterns Using Microsoft .NET

7 out of 7 rated this helpful Rate this topic

Enterprise Solution Patterns Using Microsoft .NET

patterns & practices Developer Center

Version 1.1.0

Complete List of patterns & practices

 

 

Authors: David Trowbridge, Microsoft Prescriptive Architecture Guidance; Dave Mancini and Dave Quick, Microsoft Core Infrastructure Solutions; Gregor Hohpe and James Newkirk, ThoughtWorks, Inc.; David Lavigne, SBI and Company

 

Microsoft Corporation

 

June 2003

 

Summary: This document provides a brief overview of Enterprise Solution Patterns Using Microsoft .NET, which embraces existing work in the patterns community, contributes new patterns, and shows how to implement these patterns in .NET. Included in the guide are an introduction to patterns and a catalog of 32 architecture, design, and implementation patterns.

Threading in C#

Threading in C# – Free E-book

Introduction and Concepts

C# supports parallel execution of code through multithreading. A thread is an independent execution path, able to run simultaneously with other threads.

A C# client program (Console, WPF, or Windows Forms) starts in a single thread created automatically by the CLR and operating system (the “main” thread), and is made multithreaded by creating additional threads. Here’s a simple example and its output:

All examples assume the following namespaces are imported:

using System;
using System.Threading;

Multithreading Part 2: Understanding the System.Threading.Thread Class

Multithreading Part 2: Understanding the System.Threading.Thread Class

In this article we will study the .NET threading API, how to create threads in C#, start and stop them, define their priorities and states.

Applications written on the .NET platform are naturally threaded. Let us study threading with respect to C# as the programming language. The runtime environment starts execution of the program with the Main () method in one thread. We all know that there is an automatic garbage collection happening in the background. This takes place in another thread. All of this happens so naturally that it goes unnoticed. There are situations when we need to add threads to our application. We have already seen the numerous situations in  Multithreading — part1 of the article, where we might feel the need to add new threads. A few common requirements being when you need to handle user-input, or do a lengthy calculation etc. To put it more generally, any operation that is time-critical, or needs constant attention or is time-consuming should be placed in a thread of its own.

The classes and interfaces in the System.Threading namespace provide the multithreading support in the .NET platform. This namespace consists of a number of classes. We will be discussing the Thread class of this namespace.

System.Threading.Thread is the main class for creating threads and controlling them. The Thread class has a number of methods. A few interesting methods are shown below:

  • Start(): starts the execution of the thread.
  • Suspend(): suspends the thread, if the thread is already suspended, nothing happens.
  • Resume() : resumes a thread that has been suspended.
  • Interrupt(): interrupts a thread that is in the wait, sleep or join stage.
  • Join(): blocks a calling thread until the thread terminates.
  • Sleep(int x) : suspends the thread for specified amount of time (in milliseconds).
  • Abort(): Begins the process of terminating the thread. Once the thread terminates, it cannot be restarted by calling the function Start() again.

You can pause/block a thread by calling Thread.Sleep or Thread.Suspend or Thread.Join. Calling the method Sleep() or Suspend() on a thread means, the thread does not get any processor time. There is a difference between these two ways of pausing a thread. Thread.Sleep causes a thread to stop immediately but the common language runtime waits until the thread has reached some safe point before calling the Suspend() method on the thread. One thread cannot call Sleep() on another thread but one thread can call Suspend() on the other thread and it causes the other thread to pause. Calling Resume() on the suspended thread breaks the thread out of the suspended state and allows it to continue execution. A single call to Resume() is sufficient to activate a thread regardless of the number of times Suspend() was called to block it. A thread that has already been terminated or has not yet started functioning cannot be suspended. Thread.Sleep(int.Infinite) causes a thread to sleep indefinitely. The thread can only wake up when it is interrupted by another thread that calls Thread.Interrupt or is aborted by Thread.Abort. You can use Thread.Interrupt to break a thread out of its blocking state but it throws a ThreadInterupptedException. You can either catch the exception, do whatever you want to do with the thread, or ignore the exception and let the run-time stop the thread. For managed wait, Thread.Interrupt and Thread.Abort both wake up the thread immediately.

It may be desirable at times to terminate a thread from some other thread. In such situations you use the Thread.Abort method to stop a thread permanently and using this function throws a ThreadAbortException. The terminating thread can catch the exception but it is difficult to suppress it. The only way that it can be suppressed is by calling Thread.ResetAbort method but it can only be called if this thread had been the one that had provoked the exception. Since, Thread.Abort is normally called by some thread A on some other thread B, B therefore, cannot invoke the method Thread.ResetAbort to suppress it from terminating. The Thread.Abort method lets the system quietly stop the thread without informing the user. Once aborted, a thread cannot be restarted. As this method does not say that the thread will abort immediately, hence to be sure that the thread has terminated, you can call Thread.Join to wait on the thread. Join is a blocking call that does not return until the thread has actually stopped executing. But remember, a thread may call Thread.Interrupt to interrupt another thread that is waiting on a call to Thread.Join.

As far as possible, you should avoid using Suspend() to block a thread as it could lead to serious problems like deadlocks. Imagine, what would happen if we suspend a thread that holds a resource that another thread would need. Rather, you should give different priorities to the thread based on their importance. You should, as far as possible, use Thread.Priority rather than Thread.Suspend.

This class also has a number of interesting properties as shown below:

  • IsAlive: (if true, signifies that thread has been started and has not yet been terminated or aborted)
  • Name (gets/sets the name of the thread)
  • Priority (gets/sets the scheduling priority of a thread)
  • ThreadState (gets a value containing the state of the current thread).