Concurrent passwordless registrations deadlock on user_account insert (500 System Error)

[devondragon]

Summary

Concurrent passwordless registrations deadlock on the user_account insert. Under load, UserService.registerPasswordlessAccount (and by extension POST /user/registration/passwordless) surfaces a 500 "System Error" instead of completing or retrying.

Observed while running the demo app's step-up E2E (SpringUserFrameworkDemoApp#75) with 4 Playwright workers each registering a passwordless account at the same moment, against MariaDB 12.2, library 5.3.3.

Evidence

WARN  o.m.jdbc.message.server.ErrorPacket : Error: 1213-40001: Deadlock found when trying to get lock; try restarting transaction
ERROR c.d.spring.user.api.UserAPI         : Unexpected error during passwordless registration.
org.springframework.dao.CannotAcquireLockException: could not execute statement
  [(conn=58) Deadlock found when trying to get lock; try restarting transaction]
  [insert into user_account (email,enabled,failed_login_attempts,first_name,last_activity_date,last_name,locked,locked_date,password,provider,registration_date,id) values (?,?,?,?,?,?,?,?,?,?,?,?)]
  at com.digitalsanctuary.spring.user.service.UserService$$SpringCGLIB$$0.registerPasswordlessAccount(<generated>)
  at com.digitalsanctuary.spring.user.api.UserAPI.registerPasswordlessAccount(UserAPI.java:499)
Caused by: org.hibernate.exception.LockAcquisitionException ... 
Caused by: java.sql.SQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

The CannotAcquireLockException is a transient, retryable condition, but the registration path neither retries nor maps it to a clean client response, so it becomes a generic code-5 "System Error!" (HTTP 500).

Impact

• Any application registering accounts concurrently (load, or an E2E suite with parallel workers) sees intermittent 500s on registration.
• The likely mechanism is index/gap-lock contention around the email-uniqueness check plus insert under the default isolation; concurrent inserts of distinct emails still deadlock.

Suggested direction

• Retry on CannotAcquireLockException/LockAcquisitionException (e.g. a small bounded @Retryable, or catch-and-retry once), and/or reduce the lock footprint of the uniqueness check + insert.
• At minimum, map a lock-acquisition failure to a retryable client response rather than a generic System Error.

Found via SpringUserFrameworkDemoApp#75 (step-up demo). Worked around there by running those tests serially.