Traditional web-based reporting with SAS BI tools

Table locked by Web Report Studio

Reply
Contributor
Posts: 58

Table locked by Web Report Studio

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.

Valued Guide
Posts: 3,206

Re: Table locked by Web Report Studio

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

---->-- ja karman --<-----
Contributor
Posts: 58

Re: Table locked by Web Report Studio

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.

Esteemed Advisor
Posts: 6,668

Re: Table locked by Web Report Studio

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 3,206

Re: Table locked by Web Report Studio

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)

---->-- ja karman --<-----
Contributor
Posts: 58

Re: Table locked by Web Report Studio

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)"

Valued Guide
Posts: 3,206

Re: Table locked by Web Report Studio

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

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 6 replies
  • 3166 views
  • 3 likes
  • 3 in conversation