.forShare() should not require a transaction

[txase]

When adding .forShare() to a .select() query, the query will not return until any external pending transactions on the data are resolved. This does not require the select query to be made as part of a transaction. For example, in mysql the following query is valid and useful outside of a transaction:

SELECT id FROM accounts LOCK IN SHARE MODE

This will return only once all returned rows are free of any external transactions.

In the meantime, knex requires the following workaround of wrapping the simple select query in an unnecessary transaction:

knex.transaction((trx) => {
  return trx 
    .select('id')
    .from('accounts')
    .forShare()
})

[elhigu]

In mysql what does

SELECT id FROM accounts LOCK IN SHARE MODE

without transaction do exactly? I skimmed through http://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html but didn't see any mention of using it outside of transaction.

I suppose mysql could create implicit transaction where it runs that select and frees lock right after or it may lock selected rows until connection is closed...

I'm pretty sure that this problem has not anything to do with knex, since as far as I know knex doesn't do any special waiting for selects with lock statements.

Could you provide complete test code demonstrating the problem you got?

[txase]

I tested it by opening two mysql cli connections to the same database. In the first connection I did something like:

BEGIN;
UPDATE accounts SET status = 'open' WHERE id = 58;

(Note that I did not commit the transaction yet)

In the second connection I did:

SELECT * FROM accounts WHERE id = 58 LOCK IN SHARE MODE;

The second connection did not return until the first connection's transaction was committed.

This is a potential issue with knex because knex won't add the 'LOCK IN SHARE MODE' part to the query if .forShare() is not called within a transaction: https://github.com/tgriesser/knex/blob/67b62d3213aa6ade68bdf84cb95b3410a1fd8053/src/query/compiler.js#L400-L408.

[wubzz]

Fixed by #2475