This content has been marked as final. Show 3 replies
Do you mean prohibiting DG4ODBC to manipulate SQL Server data? If yes, you could set a gateway parameter HS_TRANSACTION_MODEL=READ_ONLY_AUTOCOMMIT.
No, I mean I want only DG4ODBC's session could insert/update on the remote table until I release lock. Something like "lock table table_name@remotedatabase in exclusive mode nowait"
by default the gateway starts its own transaction on the SQL Server side and as soon as you even execute a select it locks the data until you execute a commit (even when only selecting data). Unfortunately the amount of data being locked now depends on the SQL Server version and on the lock mode chosen by the SQl Server (row/page/table).
Another option would be to explicit set a transaction isolation level for your gateway session using the passthrough command:
c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@MSODBCSQLSERVER_DG4ODBC_EMGTW_1123_DB;
DBMS_HS_PASSTHROUGH.PARSE@MSODBCSQLSERVER_DG4ODBC_EMGTW_1123_DB(c, 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
ret := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@MSODBCSQLSERVER_DG4ODBC_EMGTW_1123_DB(c);
dbms_output.put_line(ret ||' passthrough output');
Please check out your SQL Server documentation which isolation level will fit for your needs.