"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

February 27, 2010

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....

February 22, 2010

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..

February 17, 2010

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.
  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