Best way to lock table entity framework core

Question

I'm currently working on a project to make an online ticket site. I have a ticket table which includes all purchased tickets, linked to the ticket type and event.

The problem I'm facing is that if an event has an allocation of say 500 tickets, and I have multiple people trying to purchase tickets, how do I stop race conditions where I oversell past the capacity.

This is an example scenario:

498 tickets sold

User 1 and User 2 attempt to buy two tickets each at the same time: User 1 - site checks available tickets 498 sold, 2 available User 2 - site checks available tickets 498 sold, 2 available User 1 - adds 2 rows for purchased tickets, 500 sold User 2 - adds 2 rows for purchased tickets, 502 sold

I know I could re-check the count of sold tickets and if it is over 500, take the rows back out.... but that doesn't sound like an elegant solution.

I'm using entity framework core with .net core razor pages to develop the site.

Thanks in advance,

Matt

Jakub Fojtik · Accepted Answer · 2020-03-28 21:50:19Z

The standard solution is to use transactions, which will lock the table under the hood until an operation completes successfully, or roll back any changes made on error.

From the linked page:

using (var context = new BloggingContext()) { using (var transaction = context.Database.BeginTransaction()) { try { context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/dotnet" }); context.SaveChanges(); context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/visualstudio" }); context.SaveChanges(); var blogs = context.Blogs .OrderBy(b => b.Url) .ToList(); // Commit transaction if all commands succeed, transaction will auto-rollback // when disposed if either commands fails transaction.Commit(); } catch (Exception) { // TODO: Handle failure } } }

I believe it is different with the InnoDB engine for MySQL, which by default only locks the row (not the table).

Collectives™ on Stack Overflow

Best way to lock table entity framework core

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related