[SOLVED] MySQL SELECT FOR UPDATE and autocommit

[eara]

If anyone knows....

I have a db form where user can edit a db entity (lets say members) and i want to generate some sort of serial code (lets say member_code) in MySQL/InnoDb without using the autoinc field, in a multiuser enviroment.

I have created a table for storing the last value for each code in a seperated table/record that lets say
counters(counter_id,counter_value)
and each time i want to get a serial number, i first open a query with the following sql statement

Code: [Select]

select counter_value from counters where counter_id=:counter_id FOR UPDATE;and read the counter_value in my application. Then i calculate the next value, and execute the following sql statement

Code: [Select]

update counters set counter_value=:counter_value where counter_id=:counter_idand finally i call TSQLTransaction.Commit;

All this are packed in a function, lets say GenerateCode(counter_id);

I though that it should work, but in my tests (lazarus 1.2.0)  it didn't (I create a small procedure that calls the GenerateCode() 500 or more and tries to insert the codes in another table as primary key, so that in case it gets a code that is already given to someone else the db will fire a primary key violation exception. The result was db fired the exception ~ 20  times)

After reading again the MySQL docs i find out that SELECT FOR UPDATE has no result if you not turn off the session variable autocommit.
So, i modified my CreateCode() function and put an TSQLConnection.ExecuteDirect('SET autocommit=0'); before issuing the rest statements.
short tested again, and seems to work now.

Now, my problem is that:
I call my GenerateCode() routine as the last step of a TSQLQuery.BeforePost event handler in order to get a serial code. So GenerateCode() will set the autocommit session variable to 0. 

When should i reset the autocommit back to 1????

In my db-forms i always call in AfterPost event the DataSet.ApplyUpdates; and TSQLTransaction.CommitRetaining; methods.
On thought is after the immidiatly after the CommitRetaining or RollbackRetaing (only for the db forms that they call the GenerateCode function );

Any suggestions ? I am asking because i don't know what side effects this may have with the TSQLTransaction component i use.

[zeljko]

afaik from postgresql (using zeos) autocommit should be false if you use transactions. MySQL transactions implementation is pretty new stuff so don't know if you need to set transaction characteristics like you can in postgres eg. READ COMMITTED, SERIALIZABLE etc.
http://www.postgresql.org/docs/9.3/static/tutorial-transactions.html

[LacaK]

After reading again the MySQL docs i find out that SELECT FOR UPDATE has no result if you not turn off the session variable autocommit.
So, i modified my CreateCode() function and put an TSQLConnection.ExecuteDirect('SET autocommit=1'); before issuing the rest statements.

I think, that you should SET autocommit=0 (not 1 as you wrote)
as you wrote it should be turn off. See: https://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html

But AFAIK MySQL component works under explicit transaction control ... START TRANSACTION ... COMMIT or ROLLBACK
And if START TRANSACTION is used then autocommit is turned off. https://dev.mysql.com/doc/refman/5.5/en/commit.html

Which version of FPC do you use?

[eara]

@Lacak, my mistake i mean set autocommit=0 as you wrote (i corrected).
I use the recently release 1.2.2 from lazarus site and it says FPC 2.6.4.
Till now i didn't need in my forms to call Start transaction ( i think i am already in an transaction that is opened from connection)
and my base form code is

Code: [Select]

procedure TfrmDb.qry_AfterPost(DataSet: TDataSet);
var commit: boolean;
begin
  commit:=true;
  with qry_ do try
    ApplyUpdates(0);
    qry_AfterPostBeforeCommit(DataSet);
  except
    on E: Exception do begin
      commit:=false; qry_ErrorBeforeCommit(E,commit);
    end;
  end;
  if commit and Assigned(qry_.Transaction)
  and (qry_.Transaction is TSQLTransaction) then
    with TSQLTransaction(qry_.Transaction) do CommitRetaining;
end;
where "commit:boolean" is just a var that may be used in case of error handling from descedants (not used till now)

but i think i found an anwser to my problem is in http://bugs.freepascal.org/view.php?id=19137

When autocommit is 1, transactions work. START TRANSACTION will disable temporary the autocommit on the server. The COMMIT/ROLLBACK or other implicit commits/rollbacks will reset autocommit to the setting before the transaction. No saving/restoring is required on the client and you should completely leave out the test on autocommit.
A simple test will comfirm: set or leave autocommit=1. START TRANSACTION; INSERT xx; ROLLBACK; will leave the table as is with a innodb table. Do a INSERT xx; ROLLBACK; and the table is modified.

i wil try it again...

[eara]

lol, i found also also this    http://www.marshut.com/irxsnk/sqltransaction-won-t-start.html
where near the end says

2.6.2 didn't have transaction support for MySQL:

Code: [Select]

1122 function TConnectionName.StartdbTransaction(trans: TSQLHandle; AParams : string): boolean;
1123 begin
1124 // Do nothing
1125 end;
This may explain why my previous attempts failed (with lazarus 1.2.0) , and now i chase ghosts (with 1.2.2) ....
Currently i deleted the 'set autocommit=0' and runned again my test without collisions!
So, if i don't set autocommit to zero then i don't need to get it back to 1 and i think my problem is solved, or not ?

[eara]

by the way, it has a lot of fun to read what the developers from the FPC or lazarus team write or say... you think for a while that you are talking with the future 
e.g. i downloaded 2 days ago the 1.2.2 and reported a bug and the man wrote "i can't reproduced this on 1.3"
isn't this nice? You know that in future your problem is solved!