REPEATABLE READ is fine, but want to let others to read non-locked records

We usually set the REPEATABLE READ Transaction Isolation Level in order to overcome Inconsistent analysis (nonrepeatable read) and Lost Update concurrency problems. This level do not let other users to read records that are read by your transaction, but creates a deadlock situation. Here is a simple example;

   1: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

   2: BEGIN TRAN

   3:     DECLARE @Id int

   4:     SELECT TOP 1 @Id = Id

   5:     FROM dbo.Employee

   6:     WHERE IsUpdated = 0

   7:  

   8:     ......... -- more processing

   9:  

  10:     UPDATE dbo.Employee

  11:         SET Info = 'some text', IsUpdated = 1

  12:     WHERE Id = @Id

  13: COMMIT TRAN 

This code gets the first record available for updating (TOP 1, IsUpdated = 0) and does some processing, finally updates the record. This code does not allow others to modify the record while it is being accessed, avoiding inconsistent analysis and lost updates. But it reduces the concurrency. It does not allow other to get the second available record too, making a deadlock situation if the same is executed by another connection. There is no harm of letting others to access the second available record. How do we do it? This cannot be implemented with REPEATABLE READ isolation level. All we have to do is, use UPDLOCK and READPAST query hints. UPLOCK puts exclusive lock on the object and does not allow others to obtain a lock on the same. READPAST skips all locked records and read the non-locked records. Here is the corrected code;

   1: -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

   2: BEGIN TRAN

   3:     DECLARE @Id int

   4:     SELECT TOP 1 @Id = Id

   5:     FROM dbo.Employee

   6:     WITH (UPDLOCK, READPAST)

   7:     WHERE IsUpdated = 0

   8:  

   9:     ......... -- more processing

  10:  

  11:     UPDATE dbo.Employee

  12:         SET Info = 'some text', IsUpdated = 1

  13:     WHERE Id = @Id

  14: COMMIT TRAN 

This locks the record while it is being accessed but allows to process the next available record for others.

Reporting Services 2008 Error: The underlying connection was closed. An unexpected error occured on a send.

It hit me again. This does not allow you to deploy any reports or open report manager via the browser. This is because of the invalid configuration in reportserver.config file. The default value of the key SecureConnectionLevel has been set as 2, which requires SSL configuration. If you do not use SSL, make it as 0, making service least secure. Refer BOL for more info.

Reporting Services 2008 Error: Invalid namespace

This error comes when we try to configure Reporting Services 2008 with Reporting Services Configuration Manager. The reason for this is funny and identified as a known issue; It checks for wrong instance name. This has been fixed with the Cumulative Update Package 3. If you are unable to download, another method has been given with this which I used. It gives instructions for changing the reportingservices.mof file.

SQL 2008 Launch, Kandy: Few more pohotos

Doing my presentation.
Giving a prize to a winner.
All prize grabbers.
SQL Server crew

Sucess of Community Launch of MS SQL Server 2008, in Kandy

I am proud to announce that the SQL Server 2008 Community Launch, Kandy took place in grand scale. This was organized with some great difficulties, however we managed to make the event as a success event. Targeted audience was 100 and there were 90-odd participants. All participants were delighted and almost all appreciated the event. Following the agenda, Gogula delivered the KeyNote and Introduction to SQL Server 2008. Dinesh Asanka's presentation was the next one which was on developer track. Preethi did the third presentation, again it was on developer track, finally I did a presentation on BI. In addition to that Dinesh Asanka showed a presentation that spoke about red-gate tools. I did not get all photos for the event, once I have, we will publish on SQLServerUniverse.Com. I must thank to following companies, individual for making the event success; Gogula Aryalingam - This was his idea and credit must go to him. Dinesh Asanka - Did a fantastic job, not only delivering job, helping from start to end. Preethi - He was with us since we were planing, just like Dinesh Asanka and did a great job too. Wela (Microsoft Sri Lanka) - Supported us providing financial support, where we were in great difficulty. Rachel (RedGate software) - Supported us sending a package of goodies for delivering. It helped us very well. Akalanka (Peradani University) - Helped us to organized the event at the campus. Shamri (ESoft, Kandy) - Supported us by giving 30% discount vouchers on MS courses for all participants. L. H. Event Management - Supported the event by organizing the lunch. It was a great buffet :). And all participants - We would not be able to make this is as a success event without you guys. We will be doing the Colombo event soon. Though it was scheduled for this month, since we have some financial issues to be sorted out, we had put it off. Here are some pictures; At registration: Everybody got a SQLServerUniverse.Com pen, 30% discount voucher and a SQL Server 2008 file. Part of the audiance - just before starting the event During lunch time Phots of sessions and other activities will be published soon.

Separating batches using a new word, instead of “GO”.

Can we use a different word instead of GO? Yes, it is possible because GO is not a T-SQL statement; it is a command that is recognized as a batch separator by client tools such as Management Studio and sqlcmd. Unfortunately most are unaware of this and consider it is as a T-SQL statement. If you need to use a new word with your management studio, follow these steps; Open Options window (Tools -> Options). Expand and select the node Query Execution -> SQL Server -> General. Find an option called Batch Separator. Set a new value as you want. Done. Now you can use the new word instead of GO. You may face for a problem if you execute the code in a different Management Studio. Because of that, the word GO is preferred and recommended but remember the fact; it is not a T-SQL statement.

Community Launch of MS SQL Server 2008, in Kandy

We, SQL Server MVPs are organizing Community Launch of SQL Server 2008, next week in Kandy and 4th week of January in Colombo with the support of Microsoft Sri Lanka, Red-gate and SQLServerUniverse. SQL Server MVPs (Dinesh Asanka, Gogula and me) and Preethi (One of experienced DBA with vast knowledge) will be delivering; What's new in SQL Server 2008. If you are in Kandy, join with us.

MVP again

Microsoft MVP award team has decided keep my MVP title for another year. Thanks Microsoft award team, Microsoft Sri Lanka, Lead Lilian Quek for re-awarding me.