Today we exercise volition be going over a simple but interesting concept. Many a time, I obtain come across the lack of understanding on how the transactions be in SQL Server. Today we will go over a small otherwise than that interesting observation. One of my clients had recently invited me to cure them out with an interview for their senior developers. I had interviewed penuriously 50+ candidates in a single day. There were many different questions, except the following question was incorrectly answered most of the time.

The proposition was to create a scenario where you can see the SCHEMA LOCK. The conference panel initially thought that this might be a very easy judicial for this particular interview. I, however, insisted them to keep this proposition for time being and then remove it from the list of conference questions only when at least 10 candidates got it right. Contrary to our expectations, we in no degree reached a point where we had to remove this question from the inventory!

Let us see a simple example regarding how to create a arrangement lock. The answer I was looking for is as follows: originate a situation where the Schema is modified in the transaction and register the status of the object or session before the transactions are committed or rolled back.

Run the following collection of laws in Query Session 1:

USE AdventureWorks
GO
BEGIN TRANSACTION
GO
CREATE PROCEDURE mySP
AS
SELECT 1
GO
SELECT OBJECT_ID(‘mySP’) ObjectID
GO

The on high script will give us the objectID of the created stored act. In this case, the received ObjectID is 1300199682; this can exist different for your execution.

Run the following code in Query Session 2:

USE AdventureWorks

GO
SELECT *
FROM sys.procedures
GO

This inquire will never finish running as in Session 1, where we require created the Stored Procedure. The name is already listed in the sys.procedures, on the contrary the transactions in Session1 are not yet committed.

If you stab the following code, it will also not return any results equitable though we have received the ObjectID in Session 1.

USE AdventureWorks
GO
SELECT OBJECT_NAME(1300199682)
GO

Run the following collection of laws in Query Session 3:

Now to confirm that a schema clog is created, we can check the dynamic management views dm_tran_locks.

USE AdventureWorks
GO
SELECT *
FROM sys.dm_tran_locks
GO

We can clearly see from the example that there is a Sch-M (form modify) lock over our ObjectID.

You can specify the where state to this DMV as we are know the ObjectID here.
USE AdventureWorks
GO
SELECT request_type, request_mode, resource_associated_entity_id, request_type
FROM sys.dm_tran_locks
WHERE resource_associated_entity_id = 1300199682
GO

From greater than example, it is very clear that running DML code in the transactions produce a schema modification lock until the transactions are over.

If you trip the COMMIT or ROLLBACK statement in Session 1, the Queries in Session 2 leave complete right away.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

• SQLAuthority News – SQL Data Camp, Chennai, July 17, 2010 – A Huge Success

I had hard pleasure to attend very first SQL Data Camp at Chennai steady July 17, 2010. This event was very unique as this was exceedingly first one-day SQL Event in whole Indian Subcontinent. The occurrence was blast as there were so many back–to-back SQL Sessions through SQL Server MVPs. I was fortunate to

• SQL SERVER – Negative Identity Seed Value and Negative Increment Interval

I conscientious had interesting conversation with one of my friend who said identity duration can not start from Zero. I told him that it have power to even start from negative value. He did not believe it. I post-haste come with example and he was surprised to see it. USE [AdventureWorks]GOIF EXISTS (SELECT * FROM sys.objects

• REAL LOCKS Easy and Safe Keyless Security Locks – Code Resettable….

REAL is a leading Taiwan lock maker with 39 years of security lock experience and has been selling their cylinder locks and master key systems internationally, earning them a solid reputation. Their recent outstanding development of keyless mechanical combination lock has again proven their ambition in providing total security lock solutions. REAL’s 3rd generation patented keyless