0

I have a spring boot application where I'm running a native query.

UPDATE users 
   SET status = "A"
     , user_id = '1234567' 
 WHERE card_id = ( SELECT card_id FROM ( SELECT * FROM users d WHERE d.status = "U" LIMIT 1 ) d1) AND status = "U";

In user table,

card_id is primary key,

user_id is unique key.

The table contains lots of cards with different statuses(maximum of 4 statuses) I need to pick a row with status "U" and assign a user to that row.

Looks like the table is getting locked at some place.

I've around 5 million records. And when running around 40 concurrent users, single calls is taking around 40 seconds.

Thanks.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
1729i
  • 57
  • 1
  • 5
  • Which isolation level are you using? – crizzis Apr 07 '21 at 20:25
  • @crizzis Not giving any explicit isolation level – 1729i Apr 07 '21 at 20:32
  • 40 concurrent users and how many concurrent database connections has your connection pool? The default has 10... Also your query looks overly complex with the double sub-select, that could be reduced to a single select. Also when doing this with multiple concurrent users what do you have inplace to prevent the same user to select the same row? – M. Deinum Apr 08 '21 at 09:29
  • The double sub query is because I'm referring the same table in the query. No explicit lock is written for the query. Hope this itself is good for the locks. And that's the issue, all the requests are queuing. And can't see any option for SKIP LOCKED in MySQL 5.6. – 1729i Apr 08 '21 at 10:35

0 Answers0