topic Re: Table locked by Web Report Studio in SAS Web Report Studio

Table locked by Web Report Studio

DavidCaliman — Wed, 28 May 2014 18:36:12 GMT

Is there some setting in the Web Report Studio not to lock a table while to perform a load on Data Integration Studio on the same table?

Is occurring locked table error in insert while a report is running on the Web Report Studio.

Re: Table locked by Web Report Studio

jakarman — Wed, 28 May 2014 19:12:21 GMT

Well you are replacing a whole table and you do not expect any lock?
ACID and RDBMS basics are applicable.

Re: Table locked by Web Report Studio

DavidCaliman — Wed, 28 May 2014 19:27:01 GMT

The opposite occurs:

The table is being locked while I am running the report, not when I'm doing the load.

The table involved is a SAS table.

Re: Table locked by Web Report Studio

Kurt_Bremser — Wed, 28 May 2014 22:10:37 GMT

To overwrite all the files that constitute the MDDB, the load process needs exclusive access. When the OLAP server serves a report from that table, it prevents that exclusive access.

One solution is to stop the OLAP server for the time that is needed to update the MDDB's.

Re: Table locked by Web Report Studio

jakarman — Thu, 29 May 2014 04:09:10 GMT

David you accepted that locking will occur and is necessary when updating data (update lock).
This lock/enqueue has the property only one process is allowed to run, this one.

With that there also a need for locking data when reading data (read lock).

This lock/enqueue has the property many processes are allowed but the only may read not update.

The dead-lock situation is the one where the order of a locks by different processes are that they get waiting on each.
In that situation one of the processes must be killed. A timeout detection often helps.

You are having problems with a SAS-table, That can be caused by:

- The Olap way as Kurt has described. The Olap server is service for all his tables (lock them read).

Batch updates will be blocked as long as that process is running.
- The default open mode for SAS-tables is update,

+ you can change that to readonly (access-readonly) allowing many processes for read. (Blocking a possible update process)

+ you can set OS-controls to be read-only for the file/directory. By this SAS will also not see update locks

+ Use SAS/Share as the goal of that is offering SAS-tables like a RDBMS. (Same as the OLAP service)

Re: Table locked by Web Report Studio

DavidCaliman — Thu, 29 May 2014 14:05:07 GMT

Jaap Karman,thanks for your reply.

how I set the item below?

is there any manual that I can read about it?

"you can change that to readonly (access-readonly) allowing many processes for read. (Blocking a possible update process)"

Re: Table locked by Web Report Studio

jakarman — Thu, 29 May 2014 14:47:45 GMT

access=readonly It is part of the standard libname options. SAS(R) 9.3 Statements: Reference. If you use a libname in the autoexec_usermod or code it is rather straightforward. When it is metadatabase defined you have to use SMC and find a way.

Lock is a function SAS(R) 9.3 Statements: Reference

filelocks is relative new as Unix is missing that by default. Windows and Mainframe have OS locks implemented.
SAS(R) 9.3 Companion for UNIX Environments