| Subject | Transaction hung when (n) users execute the same procedure at the same time |
|---|---|
| Author | ricke_t |
| Post date | 2011-05-23T17:57:51Z |
Hello Folks
Firebird 1.5.6.5026
Delphi 7
FIBPlus 6.6.9
This is an anomaly that has been quite a challenge to solve for far. I have a procedure that runs on the server and does many updates and inserts to a table or two and creates a deadlock if (n) users do the call at the same time. If I setup 5-7 users to run the stored procedure from the front end (delphi app), have them click the button to run the stored procedure at the same time and keep clicking the button until the SQL hourglass goes to a regular hour glass, the database becomes corrupt and the end users have to CTL-ALT-DEL to get out of the program. If I look at the log at this time before firebird is restarted, the log appears to be ok. When I restart firebird, the log shows the inconsistency database corruption error. If I look at the transactions with FSQL, it indeed shows many transaction out there where it only showed the end users log in transaction to begin with.
Things we tried,
1. Rolling back the transaction when the deadlock exception occurs.
2. If the deadlock exception occurs, trap the exception exit out the delphi procedure that called it.
3. Would like to commit in the procedure after each update or insert but firebird won't let us do that.
4. Tried to detect if the procedure was running and not to let the other users run the procedure until the FIFO process has ended.
In my personal opinion, if ya hit yourself with a baseball bat and it doesn't feel good, don't do it again. However, our end users feel that they should be able to run the procedure as many times they want by as many users they want at any given point in time.
Kind regards,
Ricke333
Firebird 1.5.6.5026
Delphi 7
FIBPlus 6.6.9
This is an anomaly that has been quite a challenge to solve for far. I have a procedure that runs on the server and does many updates and inserts to a table or two and creates a deadlock if (n) users do the call at the same time. If I setup 5-7 users to run the stored procedure from the front end (delphi app), have them click the button to run the stored procedure at the same time and keep clicking the button until the SQL hourglass goes to a regular hour glass, the database becomes corrupt and the end users have to CTL-ALT-DEL to get out of the program. If I look at the log at this time before firebird is restarted, the log appears to be ok. When I restart firebird, the log shows the inconsistency database corruption error. If I look at the transactions with FSQL, it indeed shows many transaction out there where it only showed the end users log in transaction to begin with.
Things we tried,
1. Rolling back the transaction when the deadlock exception occurs.
2. If the deadlock exception occurs, trap the exception exit out the delphi procedure that called it.
3. Would like to commit in the procedure after each update or insert but firebird won't let us do that.
4. Tried to detect if the procedure was running and not to let the other users run the procedure until the FIFO process has ended.
In my personal opinion, if ya hit yourself with a baseball bat and it doesn't feel good, don't do it again. However, our end users feel that they should be able to run the procedure as many times they want by as many users they want at any given point in time.
Kind regards,
Ricke333