Locks placed by queries on SQL Server on selected Isolation Levels.

Locks placed by queries on SQL Server on selected Isolation Levels.

When a query executes, SQL Server places locks on resources. In this article I am going to analyze in detail, what are the locks placed, types of locks, number of locks based on the query executed against the database depending on the isolation level.

First I’ll open a new query window against the adventure works database and executes below query. It will show the current locks placed against this database. Please note that at the moment there are no any current activities on this database and the default isolation (READ COMMITTED) level is set.

SELECT resource_type, request_mode, request_type, request_session_id, request_owner_type, request_status

FROM sys.dm_tran_locks

 

Results looks as below

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
DATABASE	S	LOCK	52	SHARED_TRANSACTION_WORKSPACE	GRANT

 

According to the results, this is a database level shared lock. This lock was requested by a shared transaction workplace which is a session. This session was created by the query window I opened to execute above query.

Now I’ll open another query window to execute a query. Before executing the query, I execute the above query again to see the current locks.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
DATABASE	S	LOCK	53	SHARED_TRANSACTION_WORKSPACE	GRANT
DATABASE	S	LOCK	52	SHARED_TRANSACTION_WORKSPACE	GRANT

 

Now we can see two shared database locks with two different session ids. This is because two query windows I have opened. Let’s execute a simple SELECT query and observe how locks are placed.

SELECT COUNT(1) FROM Production.TransactionHistory

 

Even though it is included in a transaction, it seems no locks have been applied. This is because the SELECT statement commits just after it is executed regardless of the transaction. So we can’t see the lock applied by the SELECT query.

Let’s change the transaction Isolation Level to a higher level by issuing following command

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

 

And execute the SELECT query again and see the locks results are as below.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
DATABASE	S	LOCK	53	SHARED_TRANSACTION_WORKSPACE	GRANT
DATABASE	S	LOCK	52	SHARED_TRANSACTION_WORKSPACE	GRANT
OBJECT	S	LOCK	53	TRANSACTION	GRANT

We can see that there is an additional SHARED lock on an object. Actually it is the table in the query and therefore it is a TABLE LOCK. The lock has been requested by a transaction which is the transaction I started with the SELECT query.

Now I will try to execute an INSERT and an UPDATE statement against the same table. Before that I’ll change the query to check the locks to filter out the locks created by sessions which are not important to our context.

SELECT resource_type, request_mode, request_type, request_session_id, request_owner_type, request_status

FROM sys.dm_tran_locks

WHERE request_owner_id <> 0

 

Then I try to execute below UPDATE query.

UPDATE Production.TransactionHistory SET ModifiedDate = ModifiedDate + 1

 

Once this is executed an additional lock can be seen as below

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
OBJECT	S	LOCK	53	TRANSACTION	GRANT
OBJECT	IX	LOCK	54	TRANSACTION	WAIT

 

This is the lock requested by the UPDATE statement and resource cannot be granted because of the SHARED lock already placed. So it is in the WAIT status. So this query has placed an INTENT EXLUSIVE lock since it can’t place to prevent EXCLUSIVE placing exclusive locks by any other requet. Let’s try with an INSERT statement in another window.

INSERT INTO  Production.TransactionHistory(ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate)

VALUES (316, 100000, 1000001, GETDATE(), 'I', 100, 1000, GETDATE())

 

 

Locks looks like below

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
OBJECT	S	LOCK	53	TRANSACTION	GRANT
OBJECT	IX	LOCK	54	TRANSACTION	WAIT

 

Even the INSERT statement is waiting.

Let’s change the SELECT query to access only a set of records as below. I’ll start it with a transaction to hold the locks.

BEGIN TRANSACTION

 

SELECT * FROM Production.TransactionHistory WHERE TransactionID BETWEEN 150001 AND 150010

 

This query returned 10 rows and locks are placed as below.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
PAGE	IS	LOCK	52	TRANSACTION	GRANT
OBJECT	IS	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT

 

This shows that there are 10 SHARED locks on the 10 rows selected and two INTENT SHARED locks on the page and on the table. Now I’ll execute an UPDATE statement which will try to update set of records from the selected 10 records and some additional records.

BEGIN TRANSACTION

UPDATE Production.TransactionHistory SET ModifiedDate = ModifiedDate + 1 WHERE TransactionID BETWEEN 150008 AND 150015

 

Results for the locks applied looks below.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status	resource_description
PAGE	IS	LOCK	52	TRANSACTION	GRANT	1:16779
PAGE	IX	LOCK	54	TRANSACTION	GRANT	1:16779
OBJECT	IS	LOCK	52	TRANSACTION	GRANT
OBJECT	IX	LOCK	54	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT	(4b8cf61b9bed)
KEY	S	LOCK	52	TRANSACTION	GRANT	(3bc698094207)
KEY	S	LOCK	52	TRANSACTION	GRANT	(52f4b303ba5d)
KEY	S	LOCK	52	TRANSACTION	GRANT	(6ad1848ad628)
KEY	S	LOCK	52	TRANSACTION	GRANT	(8ae5aa051294)
KEY	S	LOCK	52	TRANSACTION	GRANT	(b2c09d8c7ee1)
KEY	S	LOCK	52	TRANSACTION	GRANT	(c28af39ea70b)
KEY	S	LOCK	52	TRANSACTION	GRANT	(939def1d3324)
KEY	S	LOCK	52	TRANSACTION	GRANT	(abb8d8945f51)
KEY	S	LOCK	52	TRANSACTION	GRANT	(dbf2b68686bb)
KEY	X	LOCK	54	TRANSACTION	WAIT	(c28af39ea70b)

 

Here I have included an additional column “resourse_description” to see what are the resources acquired by the queries. Other than the locks applied by the SELECT query, there are two INTENT EXCLUSIVE locks applied by the UPDATE query and it is WAITIING for an ECLUSIVE lock on one KEY.  So the UPDATE statement doesn’t intend to apply locks on other KEYs until the lock is granted to the first KEY.

It looks like, it tries to apply the locks on the order of the KEY. If the UPDATE statement range started, below 150001 in WHERE condition, Locks will be placed to the values below 150001 and WAITING to apply lock on the KEY 150001. If the range is totally out of the SELECT statement’s range, it will apply locks to all the KEYS as below. I change the query to …

BEGIN TRANSACTION

UPDATE Production.TransactionHistory SET ModifiedDate = ModifiedDate + 1 WHERE TransactionID BETWEEN 150011 AND 150015

 

… and the locks are as below.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status	resource_description
PAGE	IS	LOCK	52	TRANSACTION	GRANT	1:16779
PAGE	IX	LOCK	54	TRANSACTION	GRANT	1:16779
OBJECT	IS	LOCK	52	TRANSACTION	GRANT
OBJECT	IX	LOCK	54	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT	(8ae5aa051294)
KEY	S	LOCK	52	TRANSACTION	GRANT	(b2c09d8c7ee1)
KEY	S	LOCK	52	TRANSACTION	GRANT	(939def1d3324)
KEY	S	LOCK	52	TRANSACTION	GRANT	(abb8d8945f51)
KEY	S	LOCK	52	TRANSACTION	GRANT	(52f4b303ba5d)
KEY	S	LOCK	52	TRANSACTION	GRANT	(6ad1848ad628)
KEY	S	LOCK	52	TRANSACTION	GRANT	(c28af39ea70b)
KEY	S	LOCK	52	TRANSACTION	GRANT	(4b8cf61b9bed)
KEY	S	LOCK	52	TRANSACTION	GRANT	(3bc698094207)
KEY	S	LOCK	52	TRANSACTION	GRANT	(dbf2b68686bb)
KEY	X	LOCK	54	TRANSACTION	GRANT	(03e3af802e72)
KEY	X	LOCK	54	TRANSACTION	GRANT	(22bedd1163b7)
KEY	X	LOCK	54	TRANSACTION	GRANT	(1a9bea980fc2)
KEY	X	LOCK	54	TRANSACTION	GRANT	(e3d7810feace)
KEY	X	LOCK	54	TRANSACTION	GRANT	(faafc417cb7e)

 

Here we can see that there are additional EXCLUSIVE locks for the five rows that are trying to UPDATE.

 

For the above same queries, locks are different if the TRANSACTION_ISOLATION_LEVEL is SERIALIZABLE. Then the locks are as below.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status	resource_description
PAGE	IS	LOCK	52	TRANSACTION	GRANT	1:16779
PAGE	IX	LOCK	54	TRANSACTION	GRANT	1:16779
OBJECT	IS	LOCK	52	TRANSACTION	GRANT
OBJECT	IX	LOCK	54	TRANSACTION	GRANT
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(939def1d3324)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(abb8d8945f51)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(dbf2b68686bb)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(52f4b303ba5d)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(6ad1848ad628)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(22bedd1163b7)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(8ae5aa051294)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(b2c09d8c7ee1)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(c28af39ea70b)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(4b8cf61b9bed)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(3bc698094207)
KEY	X	LOCK	54	TRANSACTION	WAIT	(22bedd1163b7)

 

According to MSDN “A key range lock protects this requirement by preventing other transactions from inserting new rows whose keys would fall in the range of keys read by the serializable transaction”. But it is not only INSERT but also UPDATES are prevented.

In this analysis, we can clearly understand how locks applied differently in different isolation levels.

 

Monitoring Processor, Memory and Pagefile Usage in windows server

Below two links explain very important counters to be monitored on performance

http://www.winserverhelp.com/2011/01/monitoring-windows-server-system-memory-and-pagefile-usage/
http://www.winserverhelp.com/2011/01/monitoring-processor-usage-in-windows-server/

Cassandra - Can not start on Ubuntu

When i try to start cassandra on ubuntu with the command
"./Cassandra" i got an error saying "Segmentation fault (core dumped)"

This is caused by open JDK 1.6. This could be solved by changing the default stack size in the "conf/cassandra-env.sh" configuration file.
In the file changing the line
"JVM_OPTS="$JVM_OPTS -Xss180k"

to
"JVM_OPTS="$JVM_OPTS -Xss260k" it will start successfully.

How data exceeding 8 KB stores in a row

Maximum allowed bytes per row is 8060 bytes in SQL Server. There are two ways to store data rows that exceed this limit.

• LOB data
• ROW_OVERFLOW_DATA

Let’s see how this works in SQL server with a simple example.

• Create the below table

CREATE TABLE DataTest (ID int,

                                  Location varchar(MAX),

                                         FirstName varchar(5000),

                                         LastName varchar(4000),

                                         Region varchar(4500))

•      Then insert a record to the table with the below query

INSERT INTO DataTest

VALUES (1,'', REPLICATE('A',5000), REPLICATE('B',4000), REPLICATE('B',4500))

According to the sizes of the above columns neither of two columns can be fit in to a single data page. So only one column will fit into the row and other columns will be saved in different pages. This is called ROW_OVERFLOW_DATA

 This can be observed with the below query

SELECT alloc_unit_type_desc

,index_depth

,index_level

,record_count

,page_count

,avg_record_size_in_bytes

FROM sys.dm_db_index_physical_stats(DB_ID('MyTestDB'),OBJECT_ID('DataTest'),NULL,NULL,'DETAILED')

Results are …

In the column “page_count”, there is one page with IN_ROW_DATA and two pages with ROW_OVERFLOW_DATA. As I explained above, data the exceeds 8 KB, will be stored in different pages as ROW_OVERFLOW_DATA.

•        Now let’s insert another record that contains a value for varchar(MAX) column with below query

INSERT INTO DataTest

VALUES (2,REPLICATE('A',8000),'','','')

Now the data pages looks as below

Now we can see that the page_count of IN_ROW_DATA has been increased by one. This means that the new row has been inserted as a IN_ROW_DATA since the record fits into the limit of 8 KB.

    
       Let’s insert another record which exceeds 8 KB with the below query

INSERT INTO DataTest

VALUES (2,REPLICATE('A',8000),REPLICATE('A',5000),'','')

Now the result looks like,

An additional page has been added with the allocation unit type “LOB_DATA”.

Controlling read phenomena in SQL

Dirty reads, Non-repeatable reads and phantoms are three read phenomena that can be controlled with different transaction isolation levels.

Dirty reads
When a data is changed by a transaction, another transaction will read the same data before committing the previous transaction. So the data read by second transaction are inconsistent and are called dirty reads.

Non-repeatable reads
Within a transaction, when same data read by two separate reads, different values will be returned if the data has been updated between two reads. This is called Non-repeatable reads. In another words, if a set of records read, have been updated between two or more reads, then it is Non-repeatable.

Phantom reads
Between several reads, if new records have been inserted that matches the criteria of the select statement, it is a phantom situation.

All the above situations can be controlled (allowed or prevented) by the transaction isolation levels. All the above reads are allowed with Read Uncommitted transaction isolation level.
With the Read Committed isolation level, Dirty reads are prevented and others are allowed. Non-repeatable reads are prevented with Repeatable Read isolation level and all the above are prevented with Serializable isolation level.

So the Read Uncommitted is the least level of isolation and Serializable gives the maximum isolation. However, maximum level of locks applied with the Serializable isolation level and no locks are applied with Read Uncommited isolation level.

When the maximum isolation, locks applied also maximum which will lead to blocking. To prevent this a new isolation level, Snapshot Isolation was introduced with SQL Server 2005 and later versions. This is based on keeping versions of records which gives the maximum isolation with minimum blocking.

Locking vs Blocking

Locking
This occurs when a session takes ownership of an object by acquiring a lock on the resource

Blocking
This happens when a session is trying to acquire a lock on a resource which is already locked by an another session. Since the resource is already locked, the session is blocked to acquire the resource until the previous session releases the lock.

If the above blocking process is also blocked by another process and that process is also blocked by above blocked process, It will lead to a dead-lock situation where once process need to be terminated by force.