Nov 072013
 

It seems like I am going to write another none SQL stuff again. No. This is a real life scenario. I am writing an ETL for my customer to load data from a Sybase server running on Unix system. The database I am accessing is a standby log-shipped database which is restored 5 minutes. The user account the ETL is using has very limited right on Sybase Server. The Sybase DBA does not have SQL Server driver on the Unix system. This means that I can’t put log restore as part of my ETL and Sybase DBA does not have connectivity to SQL Server side. We need to coordinate those 2 processes that when one is running the other must not run.
Continue reading »

Jun 262012
 

With Snapshot (read committed snapshot and snapshot isolation level) enabled, when data reading on a table takes place, if any records are being modified at the same time, the data will be read from the rows in version store rather than wait until data modification complete. Such behavior lets prgrammers think that reader process will NEVER ever be blocked by writer process. In most of the scenario, this is correct, however, when reader process is indirectly issued and running against a table, the isolation level for that reader process is always read committed lock isolation regardless the transacitoin isolation level of the parent statement.

Continue reading »

Jan 302012
 

Global temp tables can be accessed by one or more sessions. This important feature of SQL Server becomes more important than it was in my programming live since more applications I worked with and am currently working on have parallel processing involved heavily. Very often, amount of data generated from one sessioin are shared to many concurrent sessions. Global temp tables come and play. Creating a global temp table is simple.

create table ##temp(i int)

It will be released when

  1. It’s explicitly removed by any sessions
  2. The creator session closed and there is no other sessions referencing this session.

Continue reading »

Dec 022011
 

One of my friends finds this issue in the systems he is managing. In my locking and blocking series, I mentioned that when a record is being modified it always acquires IX lock on the object, IX lock on the page, and X lock on the record. In rare case, this may also hold IX lock on another object. This is cased by sp_rename.

Continue reading »

Nov 252011
 

Counting semaphores restricts number of concurrent sessions from accessing the same resource. It’s useful when code blocks perform well when number of concurrent users is under a certain number. For instance, SQL Server merge replication implements semaphores to limit number of subscribers to access publication at the same time.

Continue reading »

Nov 212011
 

Building lock based process control is to avoid conflicting processes from running at same time. A very common question DBAs undergo is that Task A can be launched when Task B(s) are not running. Many times I heard that “I will have to disable jobs, wait until they are finished, start another job, wait until it finishes, and then enable the jobs were disabled previously.”. The cycle of this process some time is just very few minutes but mostly it’s half to few hours which is occasionally scheduled in the midnight. The work like this is no longer to be fun to DBAs. It would be nice if something can be implemented in the process which it can detect, wait, and/or safely stop or interrupt running business operations, start its own process after all business processes stopped, and then resume the business processes automatically…This is possible in SQL Server. SQL Server application locks can even bring more value for such matters.

Continue reading »

Nov 162011
 

Semaphore is an abstract for controlling access by multiple threads (sessions) to a common resource. Binary semaphore, it can be called Mutex, or Lock/Unlock, allows only one thread access a resource at a time. Back old days, in C++ programming, critical section object is used for this purpose. In C#, keyword lock(obj){code block} and Monitor class implements binary semaphores. Once a object is locked by one thread, other threads who need to acquire the lock on the object will have to wait until the lock on the object is released by thread previously held. SQL Server is an multi-threading application. It uses semaphores internally to control the access to the record, for instance, while you are modify a record, an exclusive lock is placed on the record and released when modification finishes. This is a binary semaphore. In T-SQL, you can implement binary semaphore by locking row(s) and application locks.

Continue reading »

Nov 112011
 

Deadlock can happen at latch level, memory, MARS, parallel query, etc. In this post, I am only going to talk about deadlocks at lock level. Resource A and B, when Session 1 locks Resource A and tries to get a lock on Resource B which has a incompatible lock held by Session2, Session 1 will wait until Session 2 releases locks on Resource B. This is called live lock. A live lock will be eventually resolved when the blocking session releases the lock or requesting lock gets timeout. If Session 2 requests a incompatible lock on Resource A at this moment, deadlock happens, because Session 1 will be waiting for the released lock on Resource B and Session 2 will be waiting for the released lock on resource A. Both sessions are deadly locked and blocked. I am going to give you few deadlock scenarios and solutions below.

Continue reading »

Nov 072011
 

Lock hints are used to customize the locking behavor of SQL Server from 3 main perspectives, granularity, mode, and duration. Available hints are HoldLock, NoLock, NoWait, PagLock, ReadCommtted, ReadCommittedLock, ReadPast, ReadUncommitted, RepeatableRead, RowLock, Serializable, TabLock, TabLockX, UpdLock, and XLock. A full understanding of them is important before using them.

Let’s assume you are running your SQL within a transaction. I assume all are running under READ COMMITTED transaction isolation mode. If you are interested, you can use the script posted in here to verify what I explained below.

Mode and Duration, Shared and Intent Shared locks are default locks used while performing date reading. While a Shared lock is on the record, there’s always an IS lock on the page the record located, and an IS lock on the object. Shared locks are released when the records and pages are no longer needed, which is not bound to a transaction scope.

  • NoLock, ReadUncommitted: no lock will be putting on the page or row but S lock will be placed on HOBT for HEAP and SCH-S on the object. The duration of such will be at statement level regard less number of statements within the transaction.
  • UpdLock: When this hint specified, an IX lock is placed on the object and IU lock is on the page. SQL Server use U lock to perform search the records. U lock will be released if the row is not the one to be returned regardless the duration of the transactioin. When a record satisfies the predicates and is needed to be returned, the U lock on the record along with IU lock on the page and IX lock on the object will NOT be released until transaction finishes
  • XLock: This hint will place exclusive lock on the record, IX lock on the page, and IX lock on the object. It behaves very differently than S lock in which it locks all the records the SQL Server engine “touched” regardless whether the records are returned back to the client or just for searching. For instance, you have a table with 5 records but does not have any indexes. Your query returns one record in the middle. If you are using XLock hint, all records will be locked, because there is no index on the table, SQL Server has to perform a full table scan by using exclusive lock. As just said, all records touched by SQL Server will have exclusive locks held on it unil transaction complete. In this case, creating proper index is the only way to improve the concurrency
  • ReadCommittedLock: it converts the locking behavor back to old Read Committed transaction isolation mode under read committed snapshot mode
  • ReadCommtted, ReadUncommitted, RepeatableRead, and Serializable: SQL Server support using different lock pattern on different table within the same query. See my last post of this series for details.
  • HoldLock: A synonym of Serializable.

Granularity, Locks can be applied to different granularities, such as, row, page, object, extent, schema, database, file, and partition. You can also apply locks on a string as known as application locks which will be discussed later.

  • RowLock: Putting locks on each record. It’s bit tricky. You will see it’s almost useless. If you are SELECTing records, Shared locks apply anyways. In order to reduce the overhead of requesting and releasing locks, if there is no locks on the page, SQL Server will not use Shared lock (us IS lock on the page instead) to scan the records event though you have a RowLock hint. If you combine UpdLock or XLock with RowLock, SQL Server will use update or exclusive lock to scan the record one by one anyways.
  • PagLock: Place lock on Page.
  • TabLockX: Place exclusive lock on the object (table).

ReadPast, a very useful hint especially when it’s combining with UpdLock, I will talk about it more in my later posts, it skips the records holding conflicting locks. For instance, there are 5 records in a table. Session 1 begins a transaction and updates one records. Before Session1’s transaction complete, session2 is selecting all record using ReadPast combining with ReadCommittedLock, session2 will not be blocked, it will return 4 records instead. In here, I use also ReadCommittedLock, this hint will let SQL Server perform as if no existance of snapshot when read committed snapshot is enabled.

NoWait, I have never used it. It will raises an error message 1222 “Lock request time out period exceeded.” whenever there is an blocking.

you can combine multiple hints for one table. Some of them are conflicting each other, such as NoLock and RowLock. Once it happens, error message 1047 “Conflicting locking hints specified.” will be returned.