How to update and query an SQL Queue table with multiple consumers

First, create the test table and populate it with some data. The data itself isn’t too important; we just want a table that will be updated while also concurrently be queried and then deleted.

drop table if exists [dbo].[Queue];

create table [dbo].[Queue](
	QueueID int identity( 1, 1 ) primary key,
	SomeAction varchar(100) not null
)

go

insert into [dbo].[Queue]( [SomeAction] )
select top 1000
	ac.[name]
from [master].[sys].[all_columns] ac
where ac.[column_id] between 1 and 100
group by ac.[name]
order by newid();

Now open a second tab and paste this next query. This one will make random changes to the queue table every tenth of a second, while holding a transaction on the modified rows.

again:
	begin tran;
		with numbers as (
			select top 10
				ac.[column_id]
			from [master].[sys].[all_columns] ac
			where ac.[column_id] between 1 and 100
			group by ac.[column_id]
			order by newid()
		)
		update q with(updlock) set
			q.[SomeAction] = convert( varchar(100), newid() )
		from [dbo].[Queue] q
		join numbers n on q.[QueueID] = n.[column_id]

	   waitfor delay '00:00:00.1';
	commit tran;
goto again;

Now, open two more tabs and paste this SQL into each.
Try more tabs if you want to test the behavior.

set nocount on;
declare @queueid int;

waitfor delay '00:00:01';

while exists(select * from [dbo].[Queue] with (updlock, readpast) ) begin;

   begin tran;

	   select top 1 @queueid = q.[QueueID]
	   from [dbo].[Queue] q with(updlock, readpast) 

	   print 'processing queueid # ' + cast(@queueid as varchar) 

	   -- Simulate some slow processing..
	   waitfor delay '00:00:00.1';

	   delete q
	   from [dbo].[Queue] q
	   where q.[QueueID] = @queueid

   commit tran;
end;

Execute all 3+ tabs at once to simulate the load an application might place on the Queue table, along with 2+ queue consumers.

If everything works well, each unique QueueID should only show up once across all running consumer tabs in the results.


How does this work? Here’s my simplified English explanation.

The with(updlock) query hint tells the SQL engine that, “Hey, place some locks on these rows that I’m going to modify inside this transaction! Don’t let anyone else update them!”

And the with(readpast) query hint tells the SQL engine that, “Hey, go ahead and just skip past any rows that are locked. Just show me the ones that are not locked.”

Someone with better wording and the proper terminology (of what the locks are called) can give you a better understanding of what’s going on in each query.

I’m just hoping that these queries (my simplified re-telling) helps you to understand how to modify a queue while also consuming rows from the very same table, but without blocking and without deadlocking.

Any feedback? Questions?
Better practices I’ve glanced over? (Such as setting the isolation level..)

My Main Rules For Life

  • Don’t Lie.
  • Be Kind.
  • Not Yours? Don’t Touch.
  • Always Learn.
  • Leave things better than you found them.

These pretty much cover every action I take (or don’t take).

I know they’re not “Earth Shattering” or “Complex“. But I believe in their simplicity and earnestness, and I hope that by sharing them I can bring some more light to the world.


Don’t Lie – Pretty much self explanatory. I won’t lie unless it is a certain special occasion that has no better course of action. If I determine you don’t deserve access to the truth, then I just won’t tell you anything. If you are one of those people that continually spout lies, I will expend energy exposing you in every feasible way.


Be Kind – Never be cruel or cowardly. If there are two possible courses of action to take, then do the kinder option. There’s no need to spread more hatred or be mean to anyone. You are not better than someone else, so stop acting “Holier Than Thou”.


Not Yours? Don’t Touch – This pretty much means “Don’t Steal”, “Hands off my loved one”, or even “Is that mine or yours?”.

Don’t take an item that does not belong to you.

Found a wallet or a purse? Return it to the owner without stealing any items.

Is this woman married? Leave her alone. (For that matter, if any woman says to leave her alone or stop.. then full STOP. Women, this rule applies to you too. :/ )

Are you married? Don’t be touching other women.


Always Learn – If you make a mistake, admit, learn from it and move on.

Don’t dwell on past mistakes, but do learn from them.


Leave things better than you found them

How to [Smartly] add SQL Braces in C#

If you need a SQL object correctly quoted by the [ and ] braces.

		/// <summary>
		///     Add the left [ and right ] braces if they're not already on the string.
		///     <para>An empty or whitepsace string throws <see cref="ArgumentEmptyException"/>.</para>
		/// </summary>
		/// <param name="self"></param>
		/// <returns></returns>
		/// <exception cref="ArgumentEmptyException"></exception>
		[DebuggerStepThrough]
        [NotNull]
        [Pure]
        public static String SmartBraces( [NotNull] this String? self ) {
            self = self.Trimmed();

            if ( String.IsNullOrEmpty( self ) ) {
	            throw new ArgumentEmptyException( nameof( self ) );
            }

			if ( self.StartsWith( "[", StringComparison.Ordinal ) && self.EndsWith( "]", StringComparison.Ordinal ) ) {
	            self = self[ 1..^1 ]?.Trim();
            }

            if ( String.IsNullOrEmpty( self ) ) {
	            throw new ArgumentEmptyException( nameof( self ) );
			}

			return $"{'['}{self}{']'}";
        }

		/// <summary>Trim the ToString() of the object; returning null if null, empty, or whitespace.</summary>
		/// <param name="self"></param>
		/// <returns></returns>
		[DebuggerStepThrough]
		[CanBeNull]
		[Pure]
		public static String? Trimmed<T>( [CanBeNull] this T self ) =>
			self switch {
				null => default( String? ),
				String s => s.Trim().NullIfEmpty(),
				var _ => self.ToString()?.Trim().NullIfEmpty()
			};


		/// <summary>Returns null if <paramref name="self" /> is <see cref="String.IsNullOrEmpty" />.</summary>
		/// <param name="self"></param>
		/// <returns></returns>
		[CanBeNull]
		[DebuggerStepThrough]
		[Pure]
		public static String? NullIfEmpty( [CanBeNull] this String? self ) => String.IsNullOrEmpty( self ) ? null : self;


//Note: All attributes can be removed.

Boolean Parsing Constants in C#

		/// <summary>
		/// N, 0, no, false, fail, failed, failure, bad
		/// </summary>
		[NotNull]
		[ItemNotNull]
		public static readonly String[] FalseStrings = {
			"N", "0", "no", "false", Boolean.FalseString, "fail", "failed", "failure", "bad"
		};

		/// <summary>
		/// Y, 1
		/// </summary>
		[NotNull]
		public static readonly Char[] TrueChars = {
			'Y', '1'
		};

		/// <summary>
		/// Y, 1, yes, true, Success, good, Go, Positive, Continue
		/// </summary>
		[NotNull]
		[ItemNotNull]
		public static readonly String[] TrueStrings = {
			"Y", "1", "yes", "true", Boolean.TrueString, nameof( Status.Success ), "good", nameof( Status.Go ), nameof( Status.Positive ), nameof( Status.Continue ), nameof(Status.Okay)
		};

How to convert any object to a Boolean value in C#

        /// <summary>
        ///     <para>Returns true if <paramref name="value" /> is a true, 'Y', "yes", "true", "1", or '1'.</para>
        ///     <para>Returns false if <paramref name="value" /> is a false, 'N', "no", "false", or '0'.</para>
        ///     <para>A null will return false.</para>
        /// </summary>
        /// <param name="value"></param>
        [Pure]
        public static Boolean ToBoolean<T>( [CanBeNull] this T value ) {
            switch ( value ) {
                case null: return false;

                case Boolean b: return b;

                case Char c: return c.In( ParsingConstants.TrueChars );

                case Int32 i: return i >= 1;

                case String s when String.IsNullOrWhiteSpace( s ): return false;

                case String s: {
                    var clean = s.Trimmed();

                    if ( clean is null ) {
                        return false;
                    }

                    if ( clean.In( ParsingConstants.TrueStrings ) ) {
                        return true;
                    }

                    if ( Boolean.TryParse( clean, out var result ) ) {
                        return result;
                    }

                    break;
                }
            }

            var t = value.ToString();

            if ( !String.IsNullOrWhiteSpace( t ) ) {
                t = t.Trim();

                if ( t.In( ParsingConstants.TrueStrings ) ) {
                    return true;
                }

                if ( t.In( ParsingConstants.FalseStrings ) ) {
                    return false;
                }

                if ( Boolean.TryParse( t, out var rest ) ) {
                    return rest;
                }
            }

            return false;
        }

        [DebuggerStepThrough]
        [Pure]
        public static Boolean? ToBooleanOrNull<T>( [CanBeNull] this T value ) {
            switch ( value ) {
                case null: return default( Boolean? );

                case Boolean b: return b;

                case Char c: return c.In( ParsingConstants.TrueChars );

                case Int32 i: return i >= 1;

                case String s when String.IsNullOrWhiteSpace( s ): return default( Boolean? );

                case String s: {
                    var trimmed = s.Trimmed();

                    if ( trimmed is null ) {
                        return default( Boolean? );
                    }

                    if ( trimmed.In( ParsingConstants.TrueStrings ) ) {
                        return true;
                    }

                    if ( trimmed.In( ParsingConstants.FalseStrings ) ) {
                        return default( Boolean? );
                    }

                    if ( Boolean.TryParse( trimmed, out var result ) ) {
                        return result;
                    }

                    break;
                }
            }

            var t = value.ToString();

            if ( String.IsNullOrWhiteSpace( t ) ) {
                return default( Boolean? );
            }

            t = t.Trim();

            if ( t.In( ParsingConstants.TrueStrings ) ) {
                return true;
            }

            if ( t.In( ParsingConstants.FalseStrings ) ) {
                return default( Boolean? );
            }

            return Boolean.TryParse( t, out var rest ) ? rest : default( Boolean? );
        }

                public static Boolean ToBooleanOrThrow<T>( [CanBeNull] this T value ) =>
            value.ToBooleanOrNull() ?? throw new FormatException( $"Unable to convert {nameof( value ).SmartQuote()} [{value}] to a boolean value." );

		/// <summary>Trim the ToString() of the object; returning null if null, empty, or whitespace.</summary>
		/// <param name="self"></param>
		/// <returns></returns>
		[DebuggerStepThrough]
		[CanBeNull]
		[Pure]
		public static String? Trimmed<T>( [CanBeNull] this T self ) =>
			self switch {
				null => default( String? ),
				String s => s.Trim().NullIfEmpty(),
				var _ => self.ToString()?.Trim().NullIfEmpty()
			};


		/// <summary>Returns null if <paramref name="self" /> is <see cref="String.IsNullOrEmpty" />.</summary>
		/// <param name="self"></param>
		/// <returns></returns>
		[CanBeNull]
		[DebuggerStepThrough]
		[Pure]
		public static String? NullIfEmpty( [CanBeNull] this String? self ) => String.IsNullOrEmpty( self ) ? null : self;

		/// <summary>
		/// N, 0, no, false, fail, failed, failure, bad
		/// </summary>
		[NotNull]
		[ItemNotNull]
		public static readonly String[] FalseStrings = {
			"N", "0", "false", Boolean.FalseString, "fail", "failed", "stop", nameof( Status.Bad ), nameof( Status.Failure ), nameof( Status.No ),
			nameof( Status.Negative )
		};

		/// <summary>
		/// Y, 1
		/// </summary>
		[NotNull]
		public static readonly Char[] TrueChars = {
			'Y', '1'
		};

		/// <summary>
		/// Y, 1, yes, true, Success, good, Go, Positive, Continue
		/// </summary>
		[NotNull]
		[ItemNotNull]
		public static readonly String[] TrueStrings = {
			"Y", "1", "yes", "true", Boolean.TrueString, nameof( Status.Success ), "good", nameof( Status.Go ), nameof( Status.Positive ), nameof( Status.Continue ), nameof(Status.Okay)
		};


How to get the Description Attribute in C#

		[CanBeNull]
		public static String? GetDescription<T>( [CanBeNull] this T e ) where T : IConvertible {
			if ( e is not Enum ) {
				return default( String? );
			}

			var type = e.GetType();

			foreach ( Int32 val in Enum.GetValues( type ) ) {
				if ( val != e.ToInt32( CultureInfo.InvariantCulture ) ) {
					continue;
				}

				var ename = type.GetEnumName( val );

				if ( ename is null ) {
					continue;
				}

				var memInfo = type.GetMember( ename );

				if ( memInfo[0].GetCustomAttributes( typeof( DescriptionAttribute ), false ).FirstOrDefault() is DescriptionAttribute descriptionAttribute ) {
					return descriptionAttribute.Description;
				}
			}

			return default( String? );
		}

How to check the return Status in C#

	public static class StatusExtensions {

		static StatusExtensions() {
			if ( Status.Good.IsBad() ) {
				throw new InvalidOperationException( "Someone blinked." );
			}

			if ( Status.Failure.IsGood() ) {
				throw new InvalidOperationException( "Someone blinked." );
			}

			if ( Status.Success.IsBad() ) {
				throw new InvalidOperationException( "Someone blinked." );
			}

			if ( !Status.Unknown.IsUnknown() ) {
				throw new InvalidOperationException( "Someone blinked." );
			}
		}

		[Pure]
		[MethodImpl( MethodImplOptions.AggressiveInlining )]
		public static Boolean Failed( this Status status ) => status <= Status.Failure;

		[Pure]
		[MethodImpl( MethodImplOptions.AggressiveInlining )]
		public static Boolean IsBad( this Status status ) => status.Failed();

		[Pure]
		[MethodImpl( MethodImplOptions.AggressiveInlining )]
		public static Boolean IsUnknown( this Status status ) => status == Status.Unknown || !status.IsBad() && !status.IsGood();

		[Pure]
		[MethodImpl( MethodImplOptions.AggressiveInlining )]
		public static Boolean Succeeded( this Status status ) => status >= Status.Success;

		[Pure]
		[MethodImpl( MethodImplOptions.AggressiveInlining )]
		public static Boolean IsGood( this Status status ) => status.Succeeded();

		[Pure]
		[NotNull]
		public static String Symbol( this Status status ) => status.GetDescription() ?? Symbols.Null;

		[Pure]
		[MethodImpl( MethodImplOptions.AggressiveInlining )]
		public static Status ToStatus( this Boolean status ) => status ? Status.Success : Status.Failure;

	}