Subject Re: Multiuser Simultaneous INSERT and SELECT queries on Firebird database,
Author Adam
--- In, "mauleen2006"
<mauleen2006@...> wrote:
> Hello,
> I have a problem regarding Multiple users Simultaneous using
> INSERT and SELECT queries on Firebird database. The detail problem
> scenario is as follows:-
> I have a table and multiple users access the table at the same
> time. If one user is inserting the data , another user at the same
> time fires a select query to get the max no from the same table. Now
> at one point my VB.Net application hangs.
> Now is it possible to use some type of locking system to lock the
> until one user finishes the insert query and does not allow the
> query to fire until the process is complete.
> This is a very urgent requirement.
> Please find the code below to understand the problem scenario:-
> fbcmd = New FbCommand("Insert into Dummy_tab values(" & i &
> ",'Mauleen')", fbcon)
> fbcmd.ExecuteNonQuery()
> Code for getting the max no:-
> fbcmd = New FbCommand("Select max(id) from dummy_tab", fbcon)
> fbcmd.ExecuteScalar()

Of course that is not going to be multiple user safe, it is also
going to be exceptionally slow if you have no descending index. You
need to use generators to create a guaranteed unique number for a
primary key etc.

create generator gen_dummy_tab;

Now the query:
select gen_id(gen_dummy_tab_id, 1) from RDB$DATABASE

Gives you a unique number guaranteed.