Learning on Blocking

One more learning added to our list while participating in a discussion.
Scenario -

• Read Committed Snapshot Isolation Enabled Database
• Transaction1 - Begin a Transaction for - Truncate Table, Do not Commit it
• Transaction2 - Begin a Transaction for - Select * from Table
• Transaction2 is blocked by Transaction1. We will try the example and see it why.

 Getting Ready with Required Steps and Data for it

• Enable ReadCommitted Snapshot Isolation
• Create a Table
• Insert Records

STEP 1
ALTER DATABASE TestDatabase
SET READ_COMMITTED_SNAPSHOT ON

STEP 2

Verify it is enabled by below query
select is_read_committed_snapshot_on, snapshot_isolation_state, snapshot_isolation_state_desc,
sys.databases.[name] from sys.databases

STEP 3
Create TestTable and Populate Data

CREATE TABLE TestTable

(
Number INT IDENTITY(1,1),
Name VARCHAR(20) NOT NULL
)
STEP 4
Populate Data in the tables

DECLARE @I INT

SET @I = 100
WHILE 1 = 1
BEGIN
    SET @I = @I + 1
    INSERT INTO TestTable(Name)
    VALUES (CONVERT(CHAR(6),@I)+'Test')
    IF @I=10000
    BREAK;
END

STEP 5 (Transaction 1)

Now we are set to experiment now. Open Transaction to trancate table TestTable
BEGIN TRAN
TRUNCATE TABLE TestTable

STEP 6 (Transaction 2)

Open Another Transaction, Transaction2
use TestDatabase
BEGIN TRAN
SELECT * FROM TestTable
 
STEP 7 (Here is Blocking, It has finally Arrived as per the need of this blog post). Detect Blocking with below Query
SELECT * FROM sys.sysprocesses WHERE blocked <> 0
Run Query as provided in link

STEP 8 - Find the locks associated with the transaction
Select * from sys.dm_tran_locks Where request_session_id = 52
Sch-M LOCK GRANT

Select * from sys.dm_tran_locks Where request_session_id = 53

Sch-S LOCK WAIT

For Session 52 It Already Holds Sch-M Lock.

STEP 9 - Reason Why and What is Learning Now for us

A Very Important Idea in MSDN Link

For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock.

Now that we learnt SCH-M Lock is the reason for blocking. Now How do i Fix it.
This is the learning of this Post. Replacing Delete with Truncate Solved the issue.

STEP 10 - Next Step
Transaction1
BEGIN TRAN
DELETE FROM TestTable

Transaction2
BEGIN TRAN
SELECT * FROM TestTable
Since, ReadCommitted Transaction is enabled you will see last committed data.

STEP 11 - Further Analysis

DBCC INPUTBUFFER(52)
--DELETE FROM TestTable
Select * from sys.dm_tran_locks Where request_session_id = 52
It has got X Lock

SELECT object_name(resource_associated_entity_id)

SELECT OBJECT_NAME(2105058535)
--TestTable
DBCC INPUTBUFFER(53)
--SELECT * FROM TestTable
Select * from sys.dm_tran_locks Where request_session_id = 53
It has got S Lock

STEP 12 - Below Query in Link is also good. Its worth to take a look at Lock Compatability Matrix
The schema stability (Sch-S) lock is compatible with all lock modes except the schema modification (Sch-M) lock mode. The Sch-M lock is incompatible with all lock modes. Lock Compatability


More Reads
Why do I get blocking when I use Read Uncommitted isolation level or use NOLOCK hint?


Happy Reading.......Happy Learning.....Thanks Roji and Vaibhav for the Discussion.

Third Biztalk Experiment

Third experiment is for correlation concept. Correlation is a process of associating an incoming message with the appropriate instance of an orchestration (link)

Understanding is – “Correlation is a process of relating an incoming message based on fields promoted and stored for its related messages, u can relate two messages based on some common property” .

There are three correlated messages exchange patterns:

• Traditional handshake
• Sequential convoy
• Parallel convoy

I am going to try simple Traditional handshake scenario.
Step 1. Created a New Biztalk Project
Step 2. Now Input XML Schema is Created

Step 3. Add a child Field Element as mentioned below

Step 4. Add element OfferId and Promote it. Once you promote it promoted schema would be created


Step 5. Create Output XML Schema and promote OfferId Child Field Element
 

Step 6. Create an Empty Biztalk Orchestration and Create two messages based on Input and Output Schema

Step 7. Create Message2 based on Output Schema

Step 8. The steps we will create in orchestration and our objective is listed below 

• ReceiveShape – Get Input based on input XML schema, Set Activate –True. (Message1)
• Send Shape – Send Input to a file share and enable correlation (Message1)
• ReceiveShape – Receive the output file and follow correlation (Message2)
• SendShape – Send the correlated message to destination file share (Message2)

This is all we are going to achieve in next listed steps. Created Receive and Send shape for Input Schema based message. Now we are going to Enable Correlation Type and Define Correlation Set

Step 9.  Create Correlation Type based on the promoted property offerid

Step 10. Create a Correlation Set based on Correlation Type

Step 11. Initialize Correlation Set for the Send Port

Step 12. Now add a Receive port and set follow correlation property

Step 13. Orchestration will look like below

Step 14. Build Application, Signin with Key, Deploy it

Step 15. Bind Ports and Assign Host. I have missed it in the picture

Step 16. Create sample files and drop in the source share with input xml.
Step 17. Create output file in the next receive share. Only matching offerid should reach the final share configured in the last send shape.

I was able to learn it with guidance from my team. Happy Reading....

Second Biztalk Experiment

My second Biztalk Experiment is calling web services from Biztalk. Source is Link . I got everything about it from link. I am trying it out step by step from a beginner perspective.

Step 1 - To get the WSDL for web service Goto Add->Add Generated Items

Step 2 - Select the option Consume WCF Service

Step 3 - Select Option Metadata files (WSDL and XSD)

Step 4 - Create the webservice as per below code

[WebMethod]
public string ConcatName(string firstName, string lastName)
{
     return firstName + " " + lastName;
}

Host the  web service and add the wsdl

Save and Add the WSL

Step 5 - Under Orchestration View create two messages based on SOAP Request and Response

Step 6 - Create Messages as shown below

Message1

 Message2

Step 7 - Add the following shapes

 Step 8 - Add ports to shapes 

Step 9 - Individual Port configurations

Port1(One Way)

Port2 (Static Request-Response Port)

Port3 (One Way)

Step 8 - Build, Signin, Deploy the Application

Step 9 - While configuring the web service in send port, Configure as below

Step 10 - Mistakes and learnings
• Finding Multi-part messages under orchestration view
• In Port2 configuration– It must be sending a request and receiving a response
• Bind the ports – Specify WCF-BasicHttp and provide URL details
• Restart the host instances

Thanks to priya for correcting my mistakes.. Happy reading..

First Biztalk Experiment

I attended Biztalk traning few years back and nothing on top of my head now :). Curious to know biztalk and learn a bit wanted to attempt this example. I created a biztalk project and added orchestration. Dragged few shapes as mentioned in the pic. It looked like this

Dragged a Few Shapes :)

Step 1 - Time for me to get some help and run through this. I took some help from my colleague.
Step 2 - Create a Biztalk Project

Step 3 - Add a Test Schema

Step 4 - Insert Child Element

Step 5 - Validate and generate Instance

Step 6 - Add Orchestration

Step 7 - Add Receive Port. Drag Receive icon from Toolbox and select Activate = true, message = Message_1 from Properties window.

Step 8 - Add Message Schema

Step 9 - Add Port Configuration

Step 10 - Port Configure

Step 11 - Port Binding

Step 12 - Receive Port

Step 13 - Add Expression

Step 14 - Expression Code

Step 15 - Scope it

Step 16 - Add a send Port

Step 17 - Send port config specify Later

Step 18 - Exception Handler for Scope

Step 19 - Specify Exception Type and Exception Name

Step 20 - Add Expression

Step 21 - Add Suspend Shape

Step 22 - Suspend Message

Step 23 - Add Scope and Another Send Port

Step 24 - Add Another Exception for Capturing the exception

Step 25 - Expression Exception

Step 26 - Orchestration

Got the error - use of unconstructed message 'Message_1. I have wrongly put receive shape under scope one. I moved it out of scope1 and corrected picture as below.

Step 27 - Error Corrected

Step 28 - Strong name Key

Step 29 - Application Name Under Deployment

Step 30 - Sign in with Strong key

Deploy the solution. After Deploying You will see it under btsmmc.msc. Biztalk group->Apps->FileCopyExample.

Step 31 - Send Port Create after deploying

Step 32 - Send Port Check XML Transmit

Step 33 - Receive Port - Receive Location properties

Step 34 - Bind Ports and Orcheatration

Step 35 -  I was able to test it successfully for both positive and negative scenarios. For negative scenario,

When first copy folder is Renamed - Error captured in eventlog

Thanks to priya and srinivas for their help. And Now I need to learn a little more deep into biztalk.

Few Good Questions - Difference between XML Transmit and Pass Thru default pipelines  

1. PassThru can be used with almost all types of files.
2. It will not promote properties
3. It basically provides a way to pass the message right into the MessageBox
4. The Xml Transmit is used for processing an Xml message and it will promote a few properties from the Xml message.
5. The XMLTransmit contains the XML Assembler component.

More Reads

WCF-SQL Adapter Stored Procedure


Happy Reading