Locking modifer for a MSR

Database
SD
N/A

Locking modifer for a MSR

I am trying to understand the locking modifier. Referring to the following two explains, I am not clear why the explains are the same though the locking modifier is applied differently in both cases; the former query is straightforward, however the latter is a multi-statement request (MSR).

What's the difference in the two, if any?

Thanks!

BTEQ -- Enter your DBC/SQL request or BTEQ command:
explain
locking tt12 for exclusive
ins into tt12 (1, 100, 1000);

explain
locking tt12 for exclusive
ins into tt12 (1, 100, 1000);

*** Help information returned. 7 rows.
*** Total elapsed time was 1 second.

Explanation
---------------------------------------------------------------------------
1) First, we lock a distinct SANCHETD."pseudo table" for exclusive
use on a RowHash to prevent global deadlock for SANCHETD.tt12.
2) Next, we lock SANCHETD.tt12 for exclusive use.
3) We do an INSERT into SANCHETD.tt12.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> No rows are returned to the user as the result of statement 1.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
explain
locking tt12 for exclusive
; ins into tt12 (1, 100, 1000);

explain
locking tt12 for exclusive
; ins into tt12 (1, 100, 1000);

*** Help information returned. 8 rows.
*** Total elapsed time was 1 second.

Explanation
---------------------------------------------------------------------------
1) First, we lock a distinct SANCHETD."pseudo table" for exclusive
use on a RowHash to prevent global deadlock for SANCHETD.tt12.
2) Next, we lock SANCHETD.tt12 for exclusive use.
3) We do an INSERT into SANCHETD.tt12.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> No rows are returned to the user as the result of statement 1.
No rows are returned to the user as the result of statement 2.
4 REPLIES

Re: Locking modifer for a MSR

The two requests are equivalent, so the execution plans are identical as shown by the explains. What difference were you expecting?
SD
N/A

Re: Locking modifer for a MSR

Thanks for your reply, Jim. Can you please elaborate on why the two are equivalent?

I have a user interface that accepts the query for a view definition and optionally a locking modifier statement from the user.

If the two queries are equivalent despite the difference of the ';', I should be able to reuse the method buildMSR() to form the REPLACE VIEW AS ... query. Otherwise, need to write an explicit method for the same.

Actually, I was expecting them to be different semantically, but the EXPLAINs showed no difference at all.

Re: Locking modifer for a MSR

The two statements are equivalent only because BTEQ understands the semicolon with additional syntax behind it to be a multi-statement request AND the statement is a DML command (as opposed to a DDL command). A CREATE VIEW is a DDL command; therefore, it cannot be combined with another statement in a multi-statement request.

In other words, the following is not allowed:

CREATE VIEW xyz AS
LOCKING tt12 FOR EXCLUSIVE
;SELECT * FROM tt12;

whereas the following would work:

CREATE VIEW xyz AS
LOCKING tt12 FOR EXCLUSIVE
SELECT * FROM tt12;

I don't know if this answers your question or not since the reference to "buildMSR" and how it works is not described.

Thanks,
Barry
SD
N/A

Re: Locking modifer for a MSR

Barry, I had not thought of the locking modifier getting used in the DDL. Thanks for pointing this out to me.