update same row from different transactions

[pavanrust]

Hi,
I am trying to understand the following sequelize test case which tries to initiate multiple async requests to update a table and it uses current mysql2 driver.

it('should not deadlock with concurrency duplicate entries and no outer transaction',

mentioned here https://github.com/sequelize/sequelize/blob/main/test/integration/model/create.test.js

• Test Description:
  A function , findOrCreate is called 50times in async fashion . findOrCreate function opens a transaction, does a select call and a row is inserted if row not found.

• Clarification on the exclusive lock behaviour.
  findOrCreate has following sqls to run; start txn, select, insert, commit txn. As per my understanding, for insert sql , an exclusive lock is taken on that row which is released only after running sql "commit txn".
  If multiple async calls to findOrCreate function are initiated , is it possible one of the async request just hangs on insert sql while other async request has not done a commit . when running the test case multiple times, hang however is never seen. It always happens that a second insert won't get called before the first commit .

ex:
START TRANSACTION;
SELECT id, email, companyId, createdAt, updatedAt FROM Users AS User WHERE User.email = 'unique.email.1@sequelizejs.com' AND User.companyId = 2 LIMIT 1;
ebd8da13-7231-40cc-8805-7551c58004c1 INSERT INTO Users (id,email,companyId,createdAt,updatedAt) VALUES (DEFAULT,?,?,?,?);
START TRANSACTION;
START TRANSACTION;
START TRANSACTION;
COMMIT;
START TRANSACTION;
SELECT id, email, companyId, createdAt, updatedAt FROM Users AS User WHERE User.email = 'unique.email.1@sequelizejs.com' AND User.companyId = 2 LIMIT 1;
SELECT id, email, companyId, createdAt, updatedAt FROM Users AS User WHERE User.email = 'unique.email.1@sequelizejs.com' AND User.companyId = 2 LIMIT 1;
SELECT id, email, companyId, createdAt, updatedAt FROM Users AS User WHERE User.email = 'unique.email.1@sequelizejs.com' AND User.companyId = 2 LIMIT 1;
SELECT id, email, companyId, createdAt, updatedAt FROM Users AS User WHERE User.email = 'unique.email.1@sequelizejs.com' AND User.companyId = 2 LIMIT 1;
INSERT INTO Users (id,email,companyId,createdAt,updatedAt) VALUES (DEFAULT,?,?,?,?);
START TRANSACTION;
INSERT INTO Users (id,email,companyId,createdAt,updatedAt) VALUES (DEFAULT,?,?,?,?);
2c906390-17a2-4b0d-82cb-d6011e6fbfba COMMIT;
SELECT id, email, companyId, createdAt, updatedAt FROM Users AS User WHERE User.email = 'unique.email.1@sequelizejs.com' AND User.companyId = 2 LIMIT 1;
COMMIT;

Can someone please clarify how this is ensured in the driver.

[pavanrust]

I think I understood it now.

Since all the sqls are executed in non blocking fashion , it would never result in hang .
async request transaction causing the row lock on server would eventually send commit sql in non blocking fashion and gets data ready event , resulting in releasing the row locks on server.