Deadlock occurred

[#1]

hi,

I got a deadlock in SM21,this deadlock has locked my cube,makes the request of the cube is not available for reporting.I can see the data in rsa1 and the table ,but i cant see it in any query creat in this cube.

i think this should be settled by basis,but our basis can not work it out.so ,i have to fix it myself.

how can i fix it?

here is the message:

**************************************************************************************************************************

Time Type Nr Clt User TCode Grp N Text

18:05:07 BTC 017 *** user BY O Deadlock occurred

Deadlock occurred

Details

Recording at local and central time........................ 2010/12/09 18:05:07

Task...... Process User...... Terminal Session TCode Program Cl Problem cl Package

09793 Background Processor No. 017 user 1 RSBATCH_EXECUTE_PROZESS S Operation Trace SBAC

Module nam Line Table Name Field Name

dbsh 1143

Documentation for system log message BY O :

A database deadlock occurs when two processes lock each other's

resources and are therefore unable to proceed. This problem can only

be solved by terminating one of the two transactions. The database

more or less at random terminates one of the transactions.

Example:

Process 1 locks resource A.

Process 2 locks resource B.

Process 1 requests resource A exclusively (-> lock) and waits for

process 2 to end its transaction.

Process 2 requests resource B exclusively (-> lock) and waits for

process 1 to end its transaction.

For example, resources are table records, which are locked by a

modification or a Select-for-Update operation.

Technical details

File Offset RecFm System log type Grp N variable message data

4227 557820 h Database Error (Non-SQL) BY O dbsh 1143

[Former Member · Accepted solution]

hi,

the cube is locked as two processes which updates data in the cube have locked each other out.

the solution will be to kill either process.

Find the data loads which are taking place in the cube.

Find the corresponding job in SM37 for the data loads, if the job can be canceled or deleted in SM37 then do it there only if not then get the job details.

From the job details get the details of the server and the job id for the specific data load.

Go to SM51 and choose the server, then choose the job and end it without core process or with core process.

this way the job will be canceled and the cube locks will be removed.

regards.

Arvind.

[Former Member · Accepted solution]

Hi Arvind,

I checked SM37,all of the works in this day has been u201CCanceledu201D or u201CFinishedu201D.

In SM51,there are some processes waiting, but its just some numbers of process ID and process type (BEG,DIA,ENQ,SPO,UP2,UPD) .i can not find the point.

[Former Member · Accepted solution]

hi,

check the SM37 for jobs for the user id mentioned in SM21. check all the jobs irrespective of the days, as in SM21 it is giving date for 2010/12/09 process lock.

cancel all the jobs running for this user id in SM37, after that the lock will be removed.

regards,

Arvind.

[Former Member · Accepted solution]

hi Arvind,

thankU for attention.

the date of the lock in SM21 is 20101209.

there is no running job for the user in SM37, all the jobs are finished or canceled.i'v delete all the jobs for the user between 20101102 and 20101210,nomatter what the statu is.

but the deadlock in SM21 is still there.

[Former Member · Accepted solution]

sm21 is a log. this means it holds every event happened in your system. whatever you do, the entry will stay there as it happened. so if your goal is to delete this entry, then i can say that you won't achieve this.

now if you have a specific problem due to the fact that you had a lock somewhere, state this in detail, meaning if you had the lock during one specific load, due you have it again when reloading?

M.

[Former Member · Accepted solution]

hi,

As stated above SM21 is logs of the system generated by different jobs or actions in the system.

if there is no jobs locking this cube then the cube will run fine..

the SM21 logs cannot be deleted..its just a log , it does not lock the cube..

please explain the prob you are facing.

regards,

Arvind.

[Former Member · Accepted solution]

hi,

thankU for explaining, now i know that i was wrong, SM21 is just a log, it cant help me unlock the cube.

my problem is, i can see the data of the cube ZFI01 in RSA1 and SE11, it means there is data in this cube ZFI01.

but i cant see any data in query.the message is "No Applicable Data Found.". the query is new ,no filter.

when i right check qube ZFI01 --- manage --- requests. the " request for reporting available" is empty. the other cubes has been marked as a symbol, meens " request is available for reporting".how can i make the request of cube ZFI01 have this symbol ?

now i'm lost ...

[Former Member · Accepted solution]

does your cube have aggregates? roll them up. Manage --> tab roll up --> set the details and execute

M.

[Former Member · Accepted solution]

hi M,

i'v been reloaded data into this qube many times, before the loading, i deleted all the data in the qube.

the problem is standing, can't see any data in query.

the query is new, no filter. i can see data of the qube in RSA1 and SE11.

[Former Member · Accepted solution]

can you see data in listcube

M.

[Former Member · Accepted solution]

the request is rollup, but still not available for request.

what is listcube?

[Former Member · Accepted solution]

it's a transaction. yo can see the data in the cube here. if you have data here, you should see it also in the query.

the job for the aggregates finished succesfully?

M.

[Former Member · Accepted solution]

it worked, the rollup.just wait for a few minutes,it's available for request now.

why is the rollup worked?can i use it in a chain?

[Former Member · Accepted solution]

if you have aggregates on a cube, you need to perform a roll up. it's like that...

there's a specific roll up step in the rspc that you can use in the process chains

as the problem seems to be solved, close the thread...

M.

[Former Member]

thankyou M

[Former Member]

is there any basis can help me?because the message in SM21 tell me that its a Database Error.

make the yellow statu in SM21 into red or green might work.can anyone tell me how to do it?

And,we have restart the service computer, it doesnot work.

[Former Member]

Hi,

While loading into cube,you need to delete index and then load data into cube and then re-create index.When you dont delete index, while loading, records should get placed into the table and it will search for each record and try to place that record into that respective data.So each time this has to check complete data.Deleting indexes is to improve the performance of loading into cube .

Thanks,

Asha

[Former Member]

you can start deleting the deadlock in sm12. when loading data to the infocube you should drop the index before as this leads often to locks.

M.

[Former Member]

hi M,

ThankU for your attention, but the problem is, i cant find any lock in SM12.

Edited by: yuanyuan dong on Dec 20, 2010 8:51 AM

[Former Member]

then the lock is not the problem. check if there's no red request in the cube. do you have BIA? if yes you need to construct the BIA indexes and only then the data will be availbale for reporting.

M;

[Former Member]

hi M,

what is BIA?

[Former Member]

it's the famous accelerator. not every customer has one.

if you don't find any solution, you can try reloading the request. delete the index before you load, and recreate afterwards.

M.

[Former Member]

hi M,

i have delete all the data in this qube first, and then i used a chain to delete the index --- transfer the data --- creat the index.

it is still can not work.

and the other chain only creat the index ,still...no change

Edited by: yuanyuan dong on Dec 20, 2010 9:52 AM