I have noticed something in Oracle which never happened before to me. Though I frequently do this kind of thing for many years now. In this case it's an Oracle11 instance (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit running on Linux to be precise).
I am importing a dumpfile and at the same time I am increasing the tablespace using statements such as this:
alter tablespace example add datafile '/path/to/oradata/instance/exampleXX.dbf' size 31000m;
I sometimes get the following error when running the above statement:
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
I can restart the alter tablespace statement and it mostly will go all right the second attempt.
I am asking why this happens because it is unusual to me. Does it indicate a bug or impeding filesystem failure? I understand running an import and adding datafiles to a tablespace at the same time may be a lot to ask of a server, however this is server has 4 CPUs, 64 GB and a few TB of SAS raid10 at its disposal.
This is not a production system by the way.
Relevant trace file output:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TT-00000006-00000010 36 11 SX 38 142 S
TT-00000006-00000010 38 142 S 36 11 SX
session 11: DID 0001-0024-0000005D session 142: DID 0001-0026-00000068
session 142: DID 0001-0026-00000068 session 11: DID 0001-0024-0000005D
Rows waited on:
Session 11: no row
Session 142: no row
----- Information for the OTHER waiting sessions -----
Session 142:
sid: 142 ser: 30639 audsid: 0 user: 5/SYSTEM flags: 0x8000051
pid: 38 O/S info: user: example_user, term: UNKNOWN, ospid: 14589
image: example@example (DW00)
current SQL:
CREATE TABLE EXAMPLE ...
----- Error Stack Dump -----
ORA-00060: deadlock detected while waiting for resource
----- Current SQL Statement for this session (sql_id=6bvzdgdn8vqqt8) -----
alter tablespace ...
The stacktrace and memory dump could provide more information however I am not a specialist in troubleshooting those. What I am after is an idea what it might be, someone who had this happen before who could make an informed guess. If it is a bug, a sign the filesystem may be having issues, or that the raid controller couldn't handle the load. Why were the two statements waiting for the resource and why was this resource unavailable.
According to this website (that I found with a very simple web search) it's because your session conflicted with another:
Read more from that article for more information about deadlocks.
It might be helpful to see the actual resource(s) in contention, but I'm guessing that both operations are trying to touch the tablespace metadata and so locking each other out. A good test of that would be to re-arrange your tasks so the
alter tablespacecommand didn't have such a huge run-out (creating the datafile):However, on a machine running 11g and with the resources you describe, I would recommend taking a look at using ASM rather than managing files manually. ASM makes a DBA's life so much easier (Well, mine anyway).
Well, it's a very old post, but I get to that page while trying to solve same problem with my Oracle 12c server. Finally it was because I reached the default limit of 200 datafiles configured in control files:
I had to increase that value, and then I could create a new tablespace o add a datafile to a existing one:
A deadlock occurs when 2 sessions block each other by attempting to update a row, which is already updated by another session but has not been yet committed or rolled back. There can be more than 2 sessions involved, but the main idea is the same.
http://dbpilot.net/2018/01/15/ora-00060-deadlock-detected-while-waiting-for-resource/
...