Subject Re: deadlock - update conflicts with concurrent update
Author Sean
--- In, Helen Borrie <helebor@...> wrote:
> At 12:34 AM 7/06/2007, you wrote:
> >In my heavy loaded server application I sometimes see exceptions of
> >---------
> >FirebirdSql.Data.FirebirdClient.FbException: deadlock
> >update conflicts with concurrent update
> >---------
> >
> >When using a blank database it doesn't have the problem.
> A blank database doesn't have anything to update so it would follow
> that there would be no update conflicts. :-)
> >But after
> >running for a couple of days when the database size becomes bigger
> >(around 1GB), the exception happens more and more...
> It's not a question of database size (and 1 Gb is not very big,
> anyway!) Update conflicts occur when one transaction tries to update
> or delete a record that another transaction is holding already with a
> pending update or deletion, awaiting a commit or rollback of that
> >I am using firebird 1.5.4, .net provider 2.0.1. The connection pool
> >size is 1000, basically caches all db connections. Sometimes there may
> >be 100 threads spawned trying to insert/update records.
> >
> >What can I do to avoid update conflicts as much as possible? shall I
> >limit the thread number or connection pool size?
> If your system genuinely has the resources to support all that, the
> database server won't complain. Your problem is about uncommitted
> transactions...something that your application is solely responsible
> for. If you're doubtful about what your code is doing, raise a
> question on the firebird-net-provider list, preferably with an
> example that illustrates what your application flow does typically.
> ./heLen

In my application transactions are committed right away (or roll back
if exception is caught) without delay.

But there is a main table with many columns. Many of them are updated
by different type of transactions. For example, "UpdateCPU"
transaction updates "Last_CPU" field, and "UpdateMemoryUsage"
transaction updates "Last_memory_usage" field. Will these two
transactions conflict with each other when they update the same record?

If yes, how can I maximize the concurrency, shall I split the fields
into different tables?