Sql &quot;Lock Table&quot; via .Net DataProvider

Nigel Salm · Dec 27, 2021

In Cache/Ensemble/IRIS there is a %NOLOCK clause that you can use in an Insert, Update or Delete. But that disables locking in these oerations.

According to the documentation for TSQL the following documentation exists for LOCK TABLE:

Lock Table

Enables the current user to lock a table.

LOCK TABLE tablename IN {SHARE | EXCLUSIVE} MODE [WAIT  numsecs | NOWAIT]

The LOCK TABLE
statement locks all of the records in the specified table. You can lock a table in SHARE MODE or in EXCLUSIVE MODE. The optional WAIT clause specifies the number of seconds to wait in attempting to acquire the table lock. The LOCK TABLE statement immediately releases any prior lock held by the current user on the specified table.

LOCK TABLE
is only meaningful within a transaction. It locks the table for the duration of the current transaction. When not in a transaction, LOCK TABLE performs no operation.

Specify tablename as described in Table References. LOCK TABLE supports locking a single table; it does not support locking multiple tables.

LOCK TABLE supports SHARE and EXCLUSIVE modes; it does not support WRITE mode.

LOCK TABLE does not support the WITH HOLD clause.

WAIT time is specified as an integer number of seconds; LOCK TABLE does not support WAIT time specified as clock time.

The Documentation on Table Names reads a follows:

Table References

InterSystems TSQL supports table references with the InterSystems IRIS® data platform SQL format:

schema.table

The only mandatory table reference component is table. If the schema is omitted, TSQL uses the default schema name.

Other forms of Transact-SQL may use table references with up to four components, separated by dots: server.database.owner.table. Here is how a Transact-SQL table reference is processed:

The server. component, if present, is ignored.
If the database. component is present and the owner. component is omitted, database is mapped to the schema name. Therefore, database..table maps to schema.table. This conversion is not performed if the database name is 'master'.
If the owner. component is present, it is mapped to the schema name.

For the purposes of name translation, a field name has the field suffix removed while translation is performed and then replaced afterwards.

Temporary Tables

InterSystems TSQL supports #tablename temporary tables. A #tablename
temporary table is visible to the current procedure of the current process. It is also visible to any procedure called from the current procedure. #tablename syntax is only supported in TSQL procedures (class methods projected as procedures with language tsql).

A temporary table is defined by using CREATE TABLE with a table name starting with "#". The temporary table is created at runtime. A #tablename
table definition goes out of scope when you exit the procedure. All temporary table definitions go out of scope when the connection is dropped. You can also explicitly delete a temporary table using DROP TABLE.

However, if a temporary table is referenced by an active result set, the temporary table may become invisible to the process, but the data and definition are retained until the result set goes out of scope.

A #tablename
temporary table is visible both to the creating procedure and to any procedures called from that procedure. Temporary tables are visible to nested procedure calls. It is not necessary to declare the temporary table in the called procedure. If the called procedure also creates a temporary table with the same name, InterSystems IRIS uses the most recently created table definition. Because a temporary table is defined using an ObjectScript local variable, the creation, modification, and deletion of these tables are not journaled transaction events; rolling back the transaction has no effect on these operations.

System Tables

System tables exist per InterSystems IRIS namespace.

Systypes

Partially supported.

Nigel

0 0

Stefan Rieger · Dec 28, 2021

Hi Nigel, thanks - but my question was especially HOW a working LOCK TABLE Command with a .Net Provider CacheCommand would look like - i couldn't get that to work..

Locking a table via my Sql Tool (Database.Net) just works fine so obviously there is a way?

0 0

Benjamin De Boe · Jan 3, 2022

Did you set the SQL dialect to MSSQL or Sybase? That LOCK TABLE command is not part of IRIS SQL. I'm not familiar with .NET myself, but found this older code sample (rename to IRISConnection()):

                _tconn = new CacheConnection(connectionString);
                _tconn.SQLDialect = "MSSQL";
                _tconn.Open();

0 0

Stefan Rieger · Jan 3, 2022

did not set any dialect - unfortunately that did not help ;-(

i'm afraid i didn't get your point "not part of IRIS SQL"? - reading the Documentation i thought it is (pls. keep in mind that this command can be invoked via embedded sql - and ahead i also can invoke that succesfully from my little sql-tool.

0 0

Benjamin De Boe · Jan 3, 2022

woops, I got that quite wrong indeed. I got confused by the mentions of TSQL earlier in the thread and mistook it for some other lock-related syntax that's only there for TSQL. you are correct: LOCK TABLE is part of IRIS SQL.

I'm not sure whether the error message you got would have further clues, but likely you're better off filing this with the WRC. Given that it works for embedded SQL, I'm suspicious it may have to do with the pre-parser for .NET, which does an initial parsing of the command on the client side.

0 0

Stefan Rieger · Jan 3, 2022

this is the exception

InterSystems.Data.CacheClient.CacheException (0x80004005): [SQLCODE: <-1>:<Ungültige SQL-Anweisung >]
[Location: <Prepare>]
[%msg: < IN erwartet, <Ende des Ausdrucks> gefunden ^LOCK TABLE Appliance . Setting>]
bei InterSystems.Data.CacheClient.CacheADOConnection.GetServerError(Int32 rc)
bei InterSystems.Data.CacheClient.CacheADOConnection.processError(Int32 error, Int32 allowError)
bei InterSystems.Data.CacheClient.InStream.readHeader(CacheCommand stmt, Int32 stmt_id, Int32 type, Int32 allowError, Boolean requestData)
bei InterSystems.Data.CacheClient.InStream.readHeader(CacheCommand stmt, Int32 type, Int32 allowError)
bei InterSystems.Data.CacheClient.CacheCommand.sendDirectUpdateRequest()
bei InterSystems.Data.CacheClient.CacheCommand.Execute()
bei InterSystems.Data.CacheClient.CacheCommand.ExecuteReaderObject(CommandBehavior behavior, String method)
bei InterSystems.Data.CacheClient.CacheCommand.internalExecuteNonQuery()
bei InterSystems.Data.CacheClient.CacheCommand.ExecuteNonQuery()

0 0

Stefan Rieger · Jan 3, 2022

my guess was that there migth be a little hack like calling stored procedures with brackets (which is not mentioned in any documentation):

"{ call %SYSTEM.SQL_GetROWID()"

doesn't anybody have an idea on that?

0 0

Andreas Schneider · Jan 3, 2022

Maybe you haven't send the complete statement?
This works for me via JDBC, tested with Caché2018.1.0.184 and IRIS 2021.2.0.617

LOCK TABLE dc_data_flights.airports IN EXCLUSIVE MODE

-- OR
LOCK dc_data_flights.airports IN EXCLUSIVE MODE

-- OR
LOCK TABLE dc_data_flights.airports IN SHARE MODE

-- OR
LOCK dc_data_flights.airports IN SHARE MODE

If try this I get the same error like you:

-- this create an error, the "IN EXCLUSIVE MODE" or "IN SHARE MODE" is missing
LOCK TABLE dc_data_flights.airports

Andreas

0 0

Andreas Schneider · Jan 3, 2022

If I check the Locktable in Caché 2018.1.0.184 it looks like this:
(Used "LOCK Aviation.Aircraft IN EXCLUSIVE MODE" in SAMPLES Namespace)

If I check the Locktable in IRIS 2021.2.0.617 it looks like this:
(Used "LOCK TABLE dc_data_flights.airports IN EXCLUSIVE MODE")

A "UNLOCK TABLE dc_data_flights.airports IN EXCLUSIVE MODE" removes the lock

0 0

Stefan Rieger · Jan 4, 2022

have no words.... feel ashame that i ditn't tried that - thanks very much

0 0

Sql "Lock Table" via .Net DataProvider

Table References

Temporary Tables

System Tables

Social networks

InterSystems resources