WITH LOCK in Join [CORE940]

[firebird-automations]

Submitted by: Fabio Maulo (fabiomaulo)

I'm not secure if this is exactly a bug or an unsupported future. I need some more information to modify the FireBird deriver for NHibernate. The query work: SELECT http://CUSTOMERS.ID, CUSTOMERS.CORPORATENAME, http://CONTRACTS.ID, CONTRACTS.BEGINNING FROM CUSTOMERS INNER JOIN CONTRACTS ON (http://CUSTOMERS.ID = CONTRACTS.CUSTOMERID) FOR UPDATE

This other query SELECT http://CUSTOMERS.ID, CUSTOMERS.CORPORATENAME, http://CONTRACTS.ID, CONTRACTS.BEGINNING FROM CUSTOMERS INNER JOIN CONTRACTS ON (http://CUSTOMERS.ID = CONTRACTS.CUSTOMERID) WITH LOCK

Cause Invalid token. Dynamic SQL Error. SQL error code = -104. Token unknown. WITH LOCK.

[firebird-automations]

Commented by: Ann Harrison (awharrison)

The syntax for the WITH LOCK clause is

SELECT <select list> FROM <table expression> WHERE <condition> [FOR UPDATE [WITH LOCK]]

In plain words, you need to write FOR UPDATE before WITH LOCK. Firebird supported a FOR UPDATE clause before locking was introduced. The clause did little or nothing, but it was accepted. When we added record level locking, we didn't want to change the semantics of the (useless) FOR UPDATE clause, so we added a second subordinate clause to indicate that not only were we going to update the rows, we wanted them locked.

[firebird-automations]

Commented by: @hvlad

Read the documentation

http://firebird.cvs.sourceforge.net/firebird/firebird2/doc/sql.extensions/README.explicit_locks?view=markup

Restrictions: - You cannot specify this clause with the following other constructs: the DISTINCT operator, group_by_clause, or aggregate functions. - from_clause of SELECT statement must specify single database table (views and external tables are not allowed)

[firebird-automations]

Modified by: @hvlad

status: Open \[ 1 \] =\> Resolved \[ 5 \] resolution: Won't Fix \[ 2 \]

[firebird-automations]

Commented by: Fabio Maulo (fabiomaulo)

Thanks Vlad. Before post this issue i read this http://www.firebirdsql.org/rlsnotes20/dml-select-syntax.html

Your link is more explicit. Thanks again.

[firebird-automations]

Modified by: @pcisar

status: Resolved \[ 5 \] =\> Closed \[ 6 \]

[firebird-automations]

Commented by: gsmcq (gsmcq)

Taken from Firebird_v15.108_ReleaseNotes.pdf: The SELECT... WITH LOCK construct is available in DSQL and PSQL. It can succeed only in a top-level, single-table SELECT statement. It is not available in a subquery specification, nor for joined sets. It cannot be specified with the DISTINCT operator, a GROUP BY clause or any other aggregating operation. It cannot be used in or with a view, nor with an external table, nor with the output of a selectable stored procedure.

You might also read the whole section regarding WITH LOCK construct.

[firebird-automations]

Modified by: @pcisar

Workflow: jira \[ 11221 \] =\> Firebird \[ 15012 \]