Hunting Deadlocks - SQL Server 2000

One of a series of posts from andraszek.net posted originally between 2006 and 2010. 

A typical scenario for a deadlock is described in Books Online: two procedures which try to use two tables in a different order, block each other after aquiring a lock to the first table.

I will describe here a different scenario: one procedure using one table. How can a deadlock occurr in such a case? Let's see.

It all starts with SQL Error 1205: Your transaction (process ID #99) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

First, let's enable SQL Server Trace 1204 flag. You must be in sysadmin server role to execute the following statement:

DBCC TRACEON (1204)

The trace, by default is written to this file:
C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG

Here we find details of our deadlock:

Deadlock encountered .... Printing deadlock information
2004-10-15 19:14:04.64 spid4
2004-10-15 19:14:04.64 spid4 Wait-for graph
2004-10-15 19:14:04.64 spid4
2004-10-15 19:14:04.64 spid4 Node:1
2004-10-15 19:14:04.64 spid4 KEY: 10:1266375687:3 (dd0025eb9a53) CleanCnt:1 
Mode: S Flags: 0x0
2004-10-15 19:14:04.64 spid4 Grant List 1::
2004-10-15 19:14:04.64 spid4 Owner:0x655ac660 Mode: S Flg:0x0 Ref:1 
Life:00000000 SPID:75 ECID:0
2004-10-15 19:14:04.64 spid4 SPID: 75 ECID: 0 Statement Type: SELECT Line #: 29
2004-10-15 19:14:04.64 spid4 Input Buf: RPC Event: InvoiceUpdate;1
2004-10-15 19:14:04.64 spid4 Requested By:
2004-10-15 19:14:04.64 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:64 
ECID:0 Ec:(0x63B3F580) Value:0x57648ee0 Cost:(0/3C)
2004-10-15 19:14:04.64 spid4
2004-10-15 19:14:04.64 spid4 Node:2
2004-10-15 19:14:04.64 spid4 KEY: 16:1153035489:1 (d1003ffe1113) CleanCnt:1 
Mode: X Flags: 0x0
2004-10-15 19:14:04.64 spid4 Grant List 0::
2004-10-15 19:14:04.64 spid4 Owner:0x42bc2140 Mode: X Flg:0x0 Ref:0 
Life:02000000 SPID:64 ECID:0
2004-10-15 19:14:04.64 spid4 SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 
679
2004-10-15 19:14:04.64 spid4 Input Buf: RPC Event: InvoiceUpdate;1
2004-10-15 19:14:04.64 spid4 Requested By:
2004-10-15 19:14:04.64 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:75 
ECID:0 Ec:(0x61D09580) Value:0x655ad200 Cost:(0/0)
2004-10-15 19:14:04.64 spid4 Victim Resource Owner:
2004-10-15 19:14:04.64 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:75 
ECID:0 Ec:(0x61D09580) Value:0x655ad200 Cost:(0/0)
			

You can notice that both nodes executed procedure InvoiceUpdate. From the KEY you can decipher the object they fought for:
10 is database id,
1266375687 is table id,
and 1 or 3 are index ids.

Find name of the database:

SELECT [name] FROM master.dbo.sysdatabases WHERE dbid = 10

Find name of the table:

SELECT OBJECT_NAME('1266375687')

This may be a temporary object, for example a cursor, which will not exist when we check later. In this case find name of the table from the stored procedure: just look for line # provided by trace.

There is a little trap here: if your procedure calls another procedure, then the deadlock may actually occurr in the subprocedure and the line number applies to the subprocedure.

Find ids and names of the indexes:

SELECT indid, [name] FROM sysindexes WHERE id = OBJECT_ID('Invoice')

Find keys of the indexes:

sp_helpindex 'Invoice'

Both instances aquire a shared lock to a range of rows, and then one of them tries to escalate that lock to exclusive to update a row, but cannot, because the other keeps it and also tries to escalate to exclusive to update another row.

The problem is that the initial shared lock was too wide. The index used was not optimal for the SELECT statement and too many rows were locked.

The solution is to create another index which matches exactly the WHERE clause used in the SELECT. This of course does not solve the problem if there are two or more processes that try to execute this procedure with the same parameters. The application has to be designed in a way that processes operate on different sets of data.

Static Methods and Their Variables - .NET 1.1

One of a series of posts from andraszek.net posted originally between 2006 and 2010. 

Have you ever wondered about local variables of static methods? Are they static too? Or are they call-specific? What happens with the variables when a second thread calls the method while the first has not finished executing it yet? The code below allows examining this.

// You may need to run this C# .NET console application 
// a few times before you get interesting results
// Have fun!
using System;
using System.Threading;

namespace Andraszek.net
{
  class StaticMethodExample
  {
    public static void ShowXTwice ()
    {
      // Uncomment the line below to see what happens if only one thread at 
      // a time is allowed to use type StaticMethodExample
      // Observe the average number of ticks go up
      //lock (typeof(StaticMethodExample))
      {
      Random r = new Random();
      // Although the ShowXTwice method is static, each thread has its own instance of x
      // otherwise, all threads would use the same variable
      // and as a result sometimes x would have a different value when checked for the 
      // second time.
      // For example: x checked for the first time by thread 1 (x=123), then thread 2 
      // assigns a new value to x (x=456), and now thread 1 checks x for the second time
      // 
      int x = r.Next(1, 10000); 
      Console.WriteLine(Thread.CurrentThread.Name 
        + " Checking x for the first time:  " + x.ToString());
      Console.WriteLine(Thread.CurrentThread.Name 
        + " Checking x for the second time:  " + x.ToString());
      }
    }
  }

  class TestStaticMethodExample 
  {
    public static void Main() 
    {
      int maxThreads = 100;
      Thread[] threads = new Thread[maxThreads];
      for (int i = 0; i < maxThreads; i++) 
      {
        Thread t = new Thread(
                       new ThreadStart(StaticMethodExample.ShowXTwice));
        t.Name = "Thread " + i.ToString();
        threads[i] = t;
      }
      long startTicks = DateTime.Now.Ticks;
      for (int i = 0; i < maxThreads; i++) 
      {
        threads[i].Start();
      }

      // wait for all threads to finish
      for (int i = 0; i < maxThreads; i++) 
      {
        threads[i].Join();
      }
      long endTicks = DateTime.Now.Ticks;

      Console.Write("Number of ticks from start to end: ");
      Console.WriteLine(endTicks - startTicks);
      Console.WriteLine("Press Enter");
      Console.ReadLine();
    }
  }
}

		

Here is the output:

Unintuitive syntax - ABAP 4

One of a series of posts from andraszek.net posted originally between 2006 and 2010. 


Here is an example of a twisted "colon and comma" logic from SAP ABAP 4 programming language:

UPDATE contacts SET: CITY  = 'WARSZAWA', 
                    PHONE = '+48 22 1234567' 
              WHERE ID = '000899888'. 

This statement will update CITY for all rows and PHONE for the row with ID = '000899888'. The reasoning behind this is that by putting a colon we actually start defining statement chains: statements separated by commas. The whole thing ends with a full stop, which marks the end of every statement in ABAP 4.

This concept will be very weird to all SQL programmers.

Custom SQL Server 2005 Aggregates

One of a series of posts from andraszek.net posted originally between 2006 and 2010. 

Custom aggregates are as fast as built in T-SQL aggregates like MAX(), SUM(), etc..
Here is the C# source code for an aggregate that concatenates short strings:

using System;
using System.Text;
using System.IO;

using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(
	Format.UserDefined, //use clr serialization to serialize the intermediate result
	IsInvariantToNulls = true, //optimizer property
	IsInvariantToDuplicates = false, //optimizer property
	IsInvariantToOrder = false, //optimizer property
	MaxByteSize = 8000) //maximum size in bytes of persisted value
]

public struct Concatenate : IBinarySerialize
{
  private StringBuilder _IntermediateResult;
  // 8000 - 2 control bytes - 4 bytes for 2 UTF-16 characters = 7994
  private const int _MaxSize = 7994; 

  public void Init()
  {
    _IntermediateResult = new StringBuilder();
  }

  public void Accumulate(SqlString value)
  {

    if (!value.IsNull 
      && (_IntermediateResult.Length + value.GetUnicodeBytes().Length 
          < _MaxSize))
        {
          _IntermediateResult.Append(value.Value).Append(", ");
        }
  }

  public void Merge(Concatenate group)
  {
    if ((_IntermediateResult.Length + group._IntermediateResult.Length) 
      < _MaxSize)
    {
      _IntermediateResult.Append(group._IntermediateResult);
    }
  }

  public SqlString Terminate()
  {
    string output = String.Empty;
    // Delete the trailing comma and space, if any
    if (_IntermediateResult != null && _IntermediateResult.Length > 1)
    {
      output = _IntermediateResult.ToString(0, _IntermediateResult.Length - 2);
    }
    return new SqlString(output);
  }

  public void Read(BinaryReader reader)
  {
    _IntermediateResult = new StringBuilder(reader.ReadString());
  }

  public void Write(BinaryWriter writer)
  {
    writer.Write(_IntermediateResult.ToString());
  }
}

And here it is in action:

CREATE AGGREGATE Concatenate (@input nvarchar(4000)) RETURNS nvarchar(max)
	EXTERNAL NAME SqlClr.Concatenate
GO

SELECT Title, dbo.Concatenate(FirstName) AS [First Names] 
	FROM Person.Contact 
	GROUP BY Title


Title    First Names
-------- ---------------------------------------------
Sr.      José, Jésus, Anibal, José, Luis, Gustavo, Ciro, Humberto, Alvaro, Adrian, Ramón
Sra.     Janeth, Pilar, Janaina Barreiro Gambaro
[...]

The law is a poorly written program/application/system

Originally published 2005-12-08, on myspace.com.

It struck me today that the written law is very similar to a poorly written program.

All these "the aforementioned party" and "the other party" and "with the exception of article 9, 5 and 6", and the sentences half page long, and references to other papers cropped all over ...

Do you remember BASIC with the GOTO statement or the assembler language? Now obfuscate it: remove all comments from that code. Replace all subroutine names with numbers, put extra GOTOs, and voila: you have the tax code.

The difference is: the user of the BASIC code - someone who plays a computer game for example - cannot be put to jail for not knowing and understanding every line of the computer code. The tax code user - can.

There are hundreds of thousands of lines of tax code, and they, as a system, change all the time. Why on Earth, we - the users - are obliged to know, understand and keep track of that? Give it to machines instead of us, lawyers, and judges. Give it to the Common Language Runtime. Let it analyze all references. Let it break trying to compile it. Let it freeze before displaying a verdict...
 

Old web sites

About a month ago, I've cancelled my old 1and1 domains and web hosting. The following web sites are gone: andraszek.net, tom-ash.net, whilenotnull.net.

I will re-post some old material here.

Introducing Membricks

Membricks - A spaced repetition game.

Spaced repetition is a key to effective learning. To learn thousands of facts in limited time you have to be efficient. You have to minimise time spent on repetitions. The idea is simple: a program calculates optimal repetition interval for every question and answer pair based on how difficult that pair is for you and how many repetitions you already had. The optimal interval is the longest one in which you still remember the answer. If you still can recall the answer, the next interval will be longer. If you can't, the next interval will be shorter and it will take you a while to get back on track.

I became a believer in spaced repetition about 15 years ago when Supermemo, a precursor of Anki, Mnemosyne, and other spaced repetition programs, as if by magic, made me remember hundreds of difficult Russian words for my Russian course at university. Since then, I tried to use spaced repetition programs to learn new things, but I was always abandoning my attempts quickly. Without an immediate need, it was difficult to keep the regime of daily sessions. Spaced repetition programs are quite boring and until a few years ago you usually ran them on a desktop or laptop computer. That meant that often when you had some spare time you couldn't learn, and when you could learn you didn't want to.

Membricks is my attempt at making a spaced repetition program more attractive and easily accessible. How? By making it an iOS game. My iPhone is with me all the time, so I will be able to use any spare minute to play, ahem... I mean learn. Membricks will be a competitive game. I will try to keep my score high against other players. My score will be going down every day I don't play. Scores will be kept on membricks.com for everyone to see, just like in the old-fashioned video games.

I started developing Membricks in July this year inspired by an old book by Len Walsh for learning Japanese. That great little book describes the meaning of 300 most common Kanji characters by showing how they evolved from a drawing of a thing or concept to the current character. My thought was: this can be animated, and it can be made into a game. I have two little helpers: one is creating animations of these Kanji characters, and the other is working on sets of questions and answers for the first 3 decks: Kanji/English, English/Polish, and French/English. I am working on the code.

To be continued...