0

I am using the mySql database to store the user details in table User during registration.

User table has column ID autoincrement primary key.

I understand if two Users register at the same time, two requests are queued at the database. Puts a lock on the table when one inserting.

How do I handle when 15000 users register at the same time? all those requests are queued at the database? If yes it slows downs the web page performance right? How can I scale it?

Hien Nguyen
  • 24,551
  • 7
  • 52
  • 62
Pradeep Charan
  • 653
  • 2
  • 7
  • 28
  • 2
    Yes, they will all take it in turns. But is that actually very likely? It is difficult enough getting 2 people to arrive for a meeting at the allotted time – RiggsFolly Apr 24 '19 at 18:09
  • Dont worry about that, its the database job. Your page/software will probably be hanging a few seconds/minutes and that's all. Anyway such a situation wont happen frequently as Riggs said – Thomas G Apr 24 '19 at 18:16
  • MySQL is quite capable of handling near-simultaneous inserts involving autoincrement values; if you're wondering how to get the value generated, [last_insert_id](https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id) is connection specific. – Uueerdo Apr 24 '19 at 18:17

1 Answers1

1

The key is to understand what is meant by "at the same time."

Even if 15,000 people are using their browsers to register during the same minute, the chances are very low that more than a few of these are executing INSERT in MySQL concurrently.

I manage some pretty large and busy MySQL instances at my company. I watch the processlist but I seldom see more the 20 threads executing concurrently. Even on production databases that process millions of transactions per hour.

Most of the time, a thread in MySQL is idle, waiting for the app that is connected to the database to run code on its side, in between the SQL queries. It's common to see hundreds of rows in the processlist that say "Sleep" while there are 6-12 threads actually executing an SQL query.

Given that, it's not difficult for MySQL to manage to allocate auto-increment values as fast as it is requested to do so.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828