Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Transactions and Deadlock


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 9 - Last Post: Oct 5, 2015 10:01 AM Last Post By: Carlos Matos
Carlos Matos

Posts: 114
Registered: 6/25/01
Transactions and Deadlock  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 30, 2015 10:00 AM
Hi have an application that executes several sql statetments and a stored procedure, all of this in the same transaction, e.g.

IBTransaction.StartTransaction;
IBQuery1.Open; // Selects a max from a field, from a table1
IBQuery2.ExecSql; // Insert on table2
IBQuery3.ExecSql; // Update table3
IBSQL1.ExecQuery; // Executes a stored procedure that does a select on table1, a select on table2 and a select on table3. IT IS HERE THAT THE DEADLOCK IS RAIZED
IBTransaction.Commit;

The deadlock is raized with another user application, e.g., another session on another application.

So, if IBQuery1, IBQuery2, IBQuery3 and IBSQL1 are all in the same transaction, why do I get a deadlock error? Shouldn't be a wait for the first started transaction to commit?

My IBTransaction parameters are read_committed and no_rec_version. I'm not specifying nowait parameter because I want the second transaction waits for the first transaction commit with no error, e.g., I do not want that deadlock error is raized.

Am i doing something wrong? Are stored procedures executes in different transactions, e.g., even if IBSQL1 is in the same transaction as IBQuery1, IBQuery2 and IBQuery3, when I execute IBSQL1.ExecQuery to execute the stored procedure, the stored procedure himself starts a transaction?

Need help on this.
Thanks
Carlos Matos

Posts: 114
Registered: 6/25/01
Re: Transactions and Deadlock  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 1, 2015 2:20 AM   in response to: Carlos Matos in response to: Carlos Matos
Anyone?!! Jeff Overcash, any comment? Can you help?
Elton Barbosa

Posts: 9
Registered: 7/19/00
Re: Transactions and Deadlock  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 1, 2015 6:16 AM   in response to: Carlos Matos in response to: Carlos Matos
Carlos Matos wrote:
Hi have an application that executes several sql statetments and a stored procedure, all of this in the same transaction, e.g.

IBTransaction.StartTransaction;
IBQuery1.Open; // Selects a max from a field, from a table1
IBQuery2.ExecSql; // Insert on table2
IBQuery3.ExecSql; // Update table3
IBSQL1.ExecQuery; // Executes a stored procedure that does a select on table1, a select on table2 and a select on table3. IT IS HERE THAT THE DEADLOCK IS RAIZED
IBTransaction.Commit;

The deadlock is raized with another user application, e.g., another session on another application.

So, if IBQuery1, IBQuery2, IBQuery3 and IBSQL1 are all in the same transaction, why do I get a deadlock error? Shouldn't be a wait for the first started transaction to commit?

My IBTransaction parameters are read_committed and no_rec_version. I'm not specifying nowait parameter because I want the second transaction waits for the first transaction commit with no error, e.g., I do not want that deadlock error is raized.

Am i doing something wrong? Are stored procedures executes in different transactions, e.g., even if IBSQL1 is in the same transaction as IBQuery1, IBQuery2 and IBQuery3, when I execute IBSQL1.ExecQuery to execute the stored procedure, the stored procedure himself starts a transaction?

Need help on this.
Thanks

It seems that you doesn't really understand transactions. A "nowait" or "wait" parameter doesn't really means that deadlock error will not be raised.
Also, another session application means another transaction.
If you want a better explanation, you should explain exact what you are doing, including SQLs in both sessions.
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Transactions and Deadlock [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 1, 2015 9:12 AM   in response to: Carlos Matos in response to: Carlos Matos
Carlos Matos wrote:
Hi have an application that executes several sql statetments and a stored procedure, all of this in the same transaction, e.g.

IBTransaction.StartTransaction;
IBQuery1.Open; // Selects a max from a field, from a table1
IBQuery2.ExecSql; // Insert on table2
IBQuery3.ExecSql; // Update table3
IBSQL1.ExecQuery; // Executes a stored procedure that does a select on table1, a select on table2 and a select on table3. IT IS HERE THAT THE DEADLOCK IS RAIZED
IBTransaction.Commit;

The deadlock is raized with another user application, e.g., another session on another application.

So, if IBQuery1, IBQuery2, IBQuery3 and IBSQL1 are all in the same transaction, why do I get a deadlock error? Shouldn't be a wait for the first started transaction to commit?

My IBTransaction parameters are read_committed and no_rec_version. I'm not specifying nowait parameter because I want the second transaction waits for the first transaction commit with no error, e.g., I do not want that deadlock error is raized.

Am i doing something wrong? Are stored procedures executes in different transactions, e.g., even if IBSQL1 is in the same transaction as IBQuery1, IBQuery2 and IBQuery3, when I execute IBSQL1.ExecQuery to execute the stored procedure, the stored procedure himself starts a transaction?

Need help on this.
Thanks

If all your stored procedure is doing is selecting from those table it can not
deadlock. Reads can never deadlock in InterBase unless you are specifically
putting a read lock on a table in your transaction parameters which I've never
seen done.

No there are no internal transactions being used behind the scenes for any IBX
component with the exception of getting meta data for building TField
information which is a one time cache.

My guess right now is that the exception is actually happening in your IBQuery3
line, but when you look at it in the debugger you are seeing the red line on the
IBSQL1 which indicates the return line, not the calling line.

If you are getting a deadlock on your IBSQL1 line then either that stored
procedure updates a record or it calls another SP that does. reads can never
raise a deadlock error.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
quinn wildman

Posts: 856
Registered: 12/2/99
Re: Transactions and Deadlock [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 1, 2015 10:18 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Jeff's answer is almost certainly the correct here. I have seen
deadlock's with InterBase 2009 when you wouldn't necessarily expect
them. You can see the fixes listed here:
http://docwiki.embarcadero.com/InterBase/XE7/en/Resolved_Defects
However, almost always there is some sort of update to the database
involved. So, if you are using InterBase 2009, then updating to a later
version of InterBase might solve your problem. However, you should make
sure it is not a classic deadlock as Jeff has outlined before
considering anything else.

Jeff Overcash (TeamB) wrote:
If all your stored procedure is doing is selecting from those table it can not
deadlock. Reads can never deadlock in InterBase unless you are specifically
putting a read lock on a table in your transaction parameters which I've never
seen done.

No there are no internal transactions being used behind the scenes for any IBX
component with the exception of getting meta data for building TField
information which is a one time cache.

My guess right now is that the exception is actually happening in your IBQuery3
line, but when you look at it in the debugger you are seeing the red line on the
IBSQL1 which indicates the return line, not the calling line.

If you are getting a deadlock on your IBSQL1 line then either that stored
procedure updates a record or it calls another SP that does. reads can never
raise a deadlock error.
Carlos Matos

Posts: 114
Registered: 6/25/01
Re: Transactions and Deadlock [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 2, 2015 3:14 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
If all your stored procedure is doing is selecting from those table it can not
deadlock. Reads can never deadlock in InterBase unless you are specifically
putting a read lock on a table in your transaction parameters which I've never
seen done.

No there are no internal transactions being used behind the scenes for any IBX
component with the exception of getting meta data for building TField
information which is a one time cache.

My guess right now is that the exception is actually happening in your IBQuery3
line, but when you look at it in the debugger you are seeing the red line on the
IBSQL1 which indicates the return line, not the calling line.

If you are getting a deadlock on your IBSQL1 line then either that stored
procedure updates a record or it calls another SP that does. reads can never
raise a deadlock error.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Thanks Jeff for the explanation.
I figure it out where the problem was.

Here is a sample that I made to reproduce what was happening:
Instance 1 code:

ibtransaction1.StartTransaction;
with ibquery1 do
begin
Sql.clear;
sql.add('update locais_carga15 set des = :c0 where codigo = :c1');
params[0].AsString := 'Testes';
params[1].AsInteger := 2;
execsql;

showmessage('Waiting...');

Sql.clear;
sql.add('update meios_expedicao15 set des = :c0 where codigo = :c1');
params[0].AsString := 'Testes';
params[1].AsInteger := 1;
execsql;

showmessage('Waiting...');
end;
ibtransaction1.Commit;

Instance 2 code:

ibtransaction2.StartTransaction;
with ibquery2 do
begin
Sql.clear;
sql.add('update meios_expedicao15 set des = :c0 where codigo = :c1');
params[0].AsString := 'Testes';
params[1].AsInteger := 1;
execsql;

showmessage('Waiting...');

Sql.clear;
sql.add('update locais_carga15 set des = :c0 where codigo = :c1');
params[0].AsString := 'Testes';
params[1].AsInteger := 2;
execsql;

showmessage('Waiting...');
end;
ibtransaction2.Commit;

Both my transaction has read_commited and no_rec_version parameters.

I put the 2 instances on hold state, on showmessage Wainting. When I press the OK button on both messages on both instances, one of them will raize a deadlock.
Maybe this is trivial for all, but this is the first time something like this happens to me.
Of course this is just an example to reproduce the error, as my code has hundreds of lines and it was hard to figure it out where the deadlock was being raized.

Thanks again.
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Transactions and Deadlock [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 2, 2015 9:38 AM   in response to: Carlos Matos in response to: Carlos Matos
Carlos Matos wrote:
If all your stored procedure is doing is selecting from those table it can not
deadlock. Reads can never deadlock in InterBase unless you are specifically
putting a read lock on a table in your transaction parameters which I've never
seen done.

No there are no internal transactions being used behind the scenes for any IBX
component with the exception of getting meta data for building TField
information which is a one time cache.

My guess right now is that the exception is actually happening in your IBQuery3
line, but when you look at it in the debugger you are seeing the red line on the
IBSQL1 which indicates the return line, not the calling line.

If you are getting a deadlock on your IBSQL1 line then either that stored
procedure updates a record or it calls another SP that does. reads can never
raise a deadlock error.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Thanks Jeff for the explanation.
I figure it out where the problem was.

Here is a sample that I made to reproduce what was happening:
Instance 1 code:

ibtransaction1.StartTransaction;
with ibquery1 do
begin
Sql.clear;
sql.add('update locais_carga15 set des = :c0 where codigo = :c1');
params[0].AsString := 'Testes';
params[1].AsInteger := 2;
execsql;

showmessage('Waiting...');

Sql.clear;
sql.add('update meios_expedicao15 set des = :c0 where codigo = :c1');
params[0].AsString := 'Testes';
params[1].AsInteger := 1;
execsql;

showmessage('Waiting...');
end;
ibtransaction1.Commit;

Instance 2 code:

ibtransaction2.StartTransaction;
with ibquery2 do
begin
Sql.clear;
sql.add('update meios_expedicao15 set des = :c0 where codigo = :c1');
params[0].AsString := 'Testes';
params[1].AsInteger := 1;
execsql;

showmessage('Waiting...');

Sql.clear;
sql.add('update locais_carga15 set des = :c0 where codigo = :c1');
params[0].AsString := 'Testes';
params[1].AsInteger := 2;
execsql;

showmessage('Waiting...');
end;
ibtransaction2.Commit;

Both my transaction has read_commited and no_rec_version parameters.

You want rec_version, not no_rec_version. no_rec_version can block readers.
The IBX IBTransaction component editor has an easy way to setup the 5 most
common transaction levels. It will be very rare you are not using one of those.
IBTransaction also for a couple of releases has a quick way to set those five
in code with the SetTransactionLevel(Level : TIBTransactionLevel) procedure.


I put the 2 instances on hold state, on showmessage Wainting. When I press the OK button on both messages on both instances, one of them will raize a deadlock.
Maybe this is trivial for all, but this is the first time something like this happens to me.
Of course this is just an example to reproduce the error, as my code has hundreds of lines and it was hard to figure it out where the deadlock was being raized.

Thanks again.

Glad you figured it out. What you posted is the classic deadlock situation.
Two different transactions modifying the same record. What's more your example
is the classic deathlock situation, where one is changing record A and the other
record B, and then both each tries to change the other's record before either
commits. If your transaction was in wait mode instead of no wait, both apps
would have just hung there forever.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Carlos Matos

Posts: 114
Registered: 6/25/01
Re: Transactions and Deadlock [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 5, 2015 2:53 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Jeff Overcash (TeamB) wrote:
Glad you figured it out. What you posted is the classic deadlock situation.
Two different transactions modifying the same record. What's more your example
is the classic deathlock situation, where one is changing record A and the other
record B, and then both each tries to change the other's record before either
commits. If your transaction was in wait mode instead of no wait, both apps
would have just hung there forever.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Well Jeff, both transaction are in wait mode and they will not hung forever, you can try it yourself. After 10-12 seconds one (the second app that I hit enter on the waiting message) will raize the deadlock.
If I use the rec_version instead of no_rec_version, I will get error message "Deadlock update conflicts with concurrent update" and what I want is that the second app that reachs the update, wait for the first one commit, without any message to be raized.

Example:
First instance

ibtransaction1.StartTransaction;
with ibquery1 do
begin
Sql.clear;
sql.add('update locais_carga15 set des = :c0 where codigo = :c1');
params[0].AsString := 'Testes';
params[1].AsInteger := 2;
execsql;

showmessage('Waiting 1...');
end;
ibtransaction1.Commit;

Second instance
First instance

ibtransaction2.StartTransaction;
with ibquery2 do
begin
Sql.clear;
sql.add('update locais_carga15 set des = :c0 where codigo = :c1');
params[0].AsString := 'Testes 123';
params[1].AsInteger := 2;
execsql;

showmessage('Waiting 1...');
end;
ibtransaction2.Commit;
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Transactions and Deadlock [Edit] [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 5, 2015 9:31 AM   in response to: Carlos Matos in response to: Carlos Matos
Show your full params for hte transaction.

Carlos Matos wrote:

Jeff Overcash (TeamB) wrote:
Glad you figured it out. What you posted is the classic deadlock situation.
Two different transactions modifying the same record. What's more your example
is the classic deathlock situation, where one is changing record A and the other
record B, and then both each tries to change the other's record before either
commits. If your transaction was in wait mode instead of no wait, both apps
would have just hung there forever.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Well Jeff, both transaction are in wait mode and they will not hung forever, you can try it yourself. After 10-12 seconds one (the second app that I hit enter on the waiting message) will raize the deadlock.
If I use the rec_version instead of no_rec_version, I will get error message "Deadlock update conflicts with concurrent update" and what I want is that the second app that reachs the update, wait for the first one commit, without any message to be raized.

Example:
First instance

ibtransaction1.StartTransaction;
with ibquery1 do
begin
Sql.clear;
sql.add('update locais_carga15 set des = :c0 where codigo = :c1');
params[0].AsString := 'Testes';
params[1].AsInteger := 2;
execsql;

showmessage('Waiting 1...');
end;
ibtransaction1.Commit;

Second instance
First instance

ibtransaction2.StartTransaction;
with ibquery2 do
begin
Sql.clear;
sql.add('update locais_carga15 set des = :c0 where codigo = :c1');
params[0].AsString := 'Testes 123';
params[1].AsInteger := 2;
execsql;

showmessage('Waiting 1...');
end;
ibtransaction2.Commit;

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Carlos Matos

Posts: 114
Registered: 6/25/01
Re: Transactions and Deadlock [Edit] [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 5, 2015 10:01 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Jeff Overcash (TeamB) wrote:
Show your full params for hte transaction.

Carlos Matos wrote:

Jeff Overcash (TeamB) wrote:
Glad you figured it out. What you posted is the classic deadlock situation.
Two different transactions modifying the same record. What's more your example
is the classic deathlock situation, where one is changing record A and the other
record B, and then both each tries to change the other's record before either
commits. If your transaction was in wait mode instead of no wait, both apps
would have just hung there forever.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Well Jeff, both transaction are in wait mode and they will not hung forever, you can try it yourself. After 10-12 seconds one (the second app that I hit enter on the waiting message) will raize the deadlock.
If I use the rec_version instead of no_rec_version, I will get error message "Deadlock update conflicts with concurrent update" and what I want is that the second app that reachs the update, wait for the first one commit, without any message to be raized.

Example:
First instance

ibtransaction1.StartTransaction;
with ibquery1 do
begin
Sql.clear;
sql.add('update locais_carga15 set des = :c0 where codigo = :c1');
params[0].AsString := 'Testes';
params[1].AsInteger := 2;
execsql;

showmessage('Waiting 1...');
end;
ibtransaction1.Commit;

Second instance
First instance

ibtransaction2.StartTransaction;
with ibquery2 do
begin
Sql.clear;
sql.add('update locais_carga15 set des = :c0 where codigo = :c1');
params[0].AsString := 'Testes 123';
params[1].AsInteger := 2;
execsql;

showmessage('Waiting 1...');
end;
ibtransaction2.Commit;

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)


read_commited
no_rec_version
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02