?
Solved

Interbase Lock Conflict on no wait transaction!!!

Posted on 2006-05-18
11
Medium Priority
?
3,636 Views
Last Modified: 2012-06-27
Hi Experts,

Can someone please help.
I have Procedure where a add fields to 3 different tables using 1 x IBQuery & 1 x TIBTransaction component both connected to database.

This is a example of my code structure (for explanation purposes)
Note: Transaction.Params = read_committed; rec_version; nowait;


Procedure Add;
var
 X :Integer;
begin
 {The first INSERT is outside loop and works fine}
 Transaction1.Active := True;
 Query1.SQL.Text := //INSERT STATEMENT HERE
 //Assign Parameters HERE
 Query1.ExecSQL;
 Transaction1.Commit;
 
 {Loop where inserting couple of records and here I get Lock Conflict on no wait transaction}
 For X := 0 to 10 do
 begin
   Transaction1.Active := True;
   Query1.SQL.Text := //INSERT STATEMENT HERE
   //Assign Parameters HERE
   Query1.ExecSQL;
   Transaction1.Commit;
 end;
end;

What am I doing wrong here?

Thanks in advance!
0
Comment
Question by:Marius0188
  • 4
  • 4
  • 3
11 Comments
 
LVL 17

Expert Comment

by:TheRealLoki
ID: 16712577
double click on teh TIbTransaction component
set it to "read committed"
see if that helps
0
 
LVL 17

Expert Comment

by:TheRealLoki
ID: 16712590
ah never mind, just read your question correctly :-)
are there any other applications using this database also?
0
 
LVL 4

Expert Comment

by:JDSkinner
ID: 16712859
Have you tried it with the commit outside of the loop.

 For X := 0 to 10 do
 begin
   Transaction1.Active := True;
   Query1.SQL.Text := //INSERT STATEMENT HERE
   //Assign Parameters HERE
   Query1.ExecSQL;
 end;
 Transaction1.Commit;
end;
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Marius0188
ID: 16714483
To answer both of you.

1. Yes there are another server module using the database but when this error occurred the server module where not even running.

2. Yes I have tried the commit outside the loop as well.
Not working.

0
 
LVL 17

Assisted Solution

by:TheRealLoki
TheRealLoki earned 180 total points
ID: 16714576
If you only have the 1 process talking to the database transaction1.active (with the transaction params you mention above)
and you are doing

transaction1.active (effectively "starttransaction")
...
commit
...
transaction1.active
...
commit

(this is what your for loop does)
then you should _not_ get a "lock conflict"

Do you possibly have some database maintenance app like ibconsole, or ems quickdesk that is open?
Do you have any UDF's that could be doing (danger danger!) reentrant calls into the database ?
Is there a 2nd instance of your aplication running (perhaps "dead" in the task list)
0
 

Author Comment

by:Marius0188
ID: 16717097
Nope.
I have only the one application running which connect to db.
Nothing else not even my db tool is open.

That is exactly what I think.
I have several other table inserts working in the same manner but dont have lock conflict.
Its only this piece of code.
0
 
LVL 4

Accepted Solution

by:
JDSkinner earned 195 total points
ID: 16717289
Have a look at the Interbase API Guide
Chapter on Working with Transactions section 5-5
At the bottom of the page a note marked Important, it appears to refer to certain combinations of transaction parameters being problematic, in that they often cause deadlocks.
0
 

Author Comment

by:Marius0188
ID: 16717791
Ok, according to the Interbase API Guide my configuration
may have SOME conflicts on simaltaneous updates (write).

Do updates in a For loop accounts for simaltaneous updates?
Still how will I be fixing this lock conflict I have?
0
 
LVL 4

Expert Comment

by:JDSkinner
ID: 16718192
It depends on what your requirements are, are there other programs accessing the same table concurrently, etc.

These params are optional, if you provide none then a default set of params is loaded, one of which is concurrency.

Try running the program without providing any transaction params, unless you have a specific requirement that you must meet. If so then let are know what you are trying to achieve and we will have a look at it.
0
 

Author Comment

by:Marius0188
ID: 16735447
I have tried your suggestion.
Cleared the Transaction.Params.
But I still got the lock conflict.

The primary requirement is that I want to void "lock conflicts" and also have data committed as soon as possible in order for other network users to be able to always view the latest version of database on lan etc...

I looking forward for help.

Thanks!
0
 
LVL 4

Expert Comment

by:JDSkinner
ID: 16740722
If you must do the insert using your existing method try something along the lines indicated below.

procedure TForm1.DoInsert;
begin
    ibTransaction1.:=true;
    ibQuery1.Active:= false; // needed only if setting < ibQuery1.CachedUpdates:= true >
    ibQuery1.CachedUpdates:= true;  // quicker if a large amount of data is being passed over network
    ibQuery1.Active:true;
    try
      with IBQuery1 do
      begin
        GenerateParamNames:= false; // Only if you have params
        Params.Clear;               // Only if you have params
        ParamCheck:= false;
        Close;
        SQL.Clear;
        { Load SQL Statements with parameters}
        ParamCheck := true;         // Only if you have params
        SQL.Add('<SQL statement line or lines here>');
        SQL.Add('Where <Field>  >= :<ParamName>');
        ParamByName('<ParamName>').Value:= <Variable / Value>;
        Prepared:= true;
        Open;
        Active := true;
    end;
    finally
        ibQuery.applyupdates; // use only if < ibQuery1.CachedUpdates:= true >
        ibTransaction.commit;
    end;
end;


I personally would tend use an ibStoredProc from the interbase Tab.

Have a look at the final comments against this question regarding the method of use of stored Procedures.

http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_21824911.html



0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month14 days, 13 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question