The execution of ExecuteNonQuery by oraclecommand is suspended

string sql = "INSERT INTO TempTable (A, B, C) VALUES ('2222222', '1111111', 'RRR')";
OracleCommand cmd = new OracleCommand(sql, connection);
cmd.ExecuteNonQuery();

 

When executing this code, the first insertion failed. When stop debug restarts, it is found that the program runs to cmd.ExecuteNonQuery It’s stuck when I’m in. The reason for this is Oracle’s block mechanism.

If session “A” inserts a row in a table with a primary key, session “A” will block ANY other session attempting to insert the same primary key until session “A” commits or rolls back the insert. All other attempts to insert will “hang”.

At this point, I change the primary key 2222222 of SQL statement into 33333333 in the code, and then I can insert it smoothly. This indicates that this operation will be blocked only when the primary key is the same. Because when 2222222 was inserted, the program ended unexpectedly, but the Oracle transaction was not committed or rolled back, so this primary key was locked. Note that it’s just the primary key of this value.

there is another problem here, that is, when I open SQL developer and execute the primary key statement of 2222222, the execution will be completed smoothly without blocking. Now, when I execute SQL statements with code, the blocking phenomenon will still occur. But when I restart vs, create a new project and write these codes, it is also equivalent to another program to execute the insert statement, which will also get stuck. May be related to the. Net provider used?Because you are using the same provider, it is stuck. Because SQL developer doesn’t use this provider, it won’t get stuck?It’s just a guess.

This kind of lock is called tmlock, or row transaction lock. You can use SQL statements to check whether there is such a lock
or not

SELECT sid, id1 FROM v$lock WHERE TYPE=’TM’

SID ID1
76 14197

SELECT * FROM dba_ objects WHERE object_ id = 14197;

OBJECT_ NAME
——————————————————————————————————————————–
TemTable

 

How to release this TM lock?row transaction lock

Just execute a commit command in the SQL developer UI.


Read More: