eCommerce items management

Question

I'm creating an app that selling tickets, I have a table for available tickets

id       | serial      | sold
-------------------------------
1        | 000001      | false
2        | 000002      | false
3        | 000003      | true

When a user request a ticket the app select a ticket and mark it as sold.

My question is: how to manage this table and prevent selling the same ticket to a different user knowing that there will be a hundreds of requests at the same time?

I'm thinking about locking but when the row is locked for a transaction, if another transaction request the lock it must wait till the first transaction is finished and update the row as sold, then the second transaction will return that the ticket is been sold. Now how can I redirect the second transaction to a different row if it found the row is locked?

Edit

According to Paul Zahra's comment I created a stored procedure

DECLARE @id varchar(MAX)        
SELECT @id = (
    SELECT top(1) [Id]
    FROM   [dbo].[AvailableItems] WITH (ROWLOCK, UPDLOCK)
    WHERE  ([Sold] = 0) )

UPDATE [dbo].[AvailableItems]
SET [Sold] = 1
WHERE [Id] = @id

SELECT [Id], [Serial], [CreateDate], [AvailableTo]
FROM   [dbo].[AvailableItems]
WHERE  ([Id] = @id)

But I need to be sure what is going to happen to the second transaction if two transactions came at the same time? will it skip this row and move to the next row? or wait the first transaction to be finished then select the same row?

Can you just generate the serial when someone buys a ticket? Check the serial hasn't already been sold and mark it as such. — Paul Zahra, Feb 02 '15 at 09:16
no, because i have to export the table to the entrance system — Medo Elkamaly, Feb 02 '15 at 09:21
Ok then. Why not simply loop the table until you hit the max id value, looking for a sold = false ? Which is a bit inefficient, so why not 'select * where sold = false'? If that fails because of a race condition then loop? — Paul Zahra, Feb 02 '15 at 09:26
that is great but that will delay the request, how about the locking can you help me with the code? — Medo Elkamaly, Feb 02 '15 at 09:30
about the loop select, the list will be out of date since there is multiple request — Medo Elkamaly, Feb 02 '15 at 09:36
Have a read of... NOWAIT here http://stackoverflow.com/questions/3767328/how-to-perform-a-row-lock as for detecting if a row is locked, it's stupidly difficult I'm afraid http://stackoverflow.com/questions/11464317/how-to-know-the-lock-status-for-a-row-without-updating-the-record — Paul Zahra, Feb 02 '15 at 10:11

score 0 · Answer 1 · edited May 23 '17 at 12:28

I would lock the item using a simple object and ensure that it is private/static. This will allow its state to be shared between all instances of the class whilst preventing the object being modified from outside the class.

For error handeling and locks please see

In C# how can I safely exit a lock with a try catch block inside?

in support of the //Log exception lock will be released automaticaly statement.

The code has not been tested but it should give you a good start.

public class NextTicketinSequence
{
    private static int _LastTicketNumber;
    private static object _TicketLock;

    public static NextTicketinSequence()
    {
         NextTicketinSequence._LastTicketNumber = 0;
    }

   public static int GetNextTicketNumber()
   {
     int ticketId = 0;

      try
      {
        lock(_TicketLock)
        {
            NextTicketinSequence.LoadLastTicketSold();
            ticketId = NextTicketinSequence._LastTicketNumber + 1;
        }
      }
      Catch(Exception ex)
      {
            //Log exception lock will be released automaticaly
      }

      return ticketId
   }

  public static void LoadLastTicketSold
  {
      //Code to get the last ticket marked as sold
  }
}

eCommerce items management

1 Answers1

Linked