If you want to run many updates (or a batch update like UPDATE Foo SET
fie...@p WHERE...) inside a single transaction, the DB server will place row
locks on your rows (at least sqlserver will), which will block other
transactions / connections till the transaction is completed or rolled back.
So a queue definitely won't help (it only makes things slower), Craig has to
deal with the locks or use an isolation level where readers can read
uncommitted rows (not recommended). if deadlocks occur, it's likely a matter
of not passing on the same session to the read method called within a
transaction which does writes.
Or use a DB with MVCC or another mechanism which doesn't make writers block
readers and vice versa.
FB
> I would use some queuing system, MSMQ or your custom and background
> thread(s) to update fields. Pushing update notifications into queue
doesn't
> block your tables.
>
> On 1. Sep., 04:31 h., Craig van Nieuwkerk <[email protected]> wrote:
> > Yes, that is what I do already, but still have locking issues when a
> > large number of updates are issued.
> >
> > On Wed, Sep 1, 2010 at 12:18 PM, José F. Romaniello
> >
> >
> >
> > <[email protected]> wrote:
> > > what about using an executable hql;
> > > s.CreateQuery("update Banners b set b.ImpressionsCount =
> > > b.ImpressionsCount
> > > +1 where b.Id = ?").SetParameter(id).ExecuteUpdate()
> > > 2010/8/31 Craig van Nieuwkerk <[email protected]>
> >
> > >> Hello
> >
> > >> I have a web site and on it is an advertising banner (or multiple).
> > >> Every time this banner is displayed I want to increment an
> > >> Impressions field in a table. In a day there could be potentially
> > >> 100k+ updates to this table and I have experienced locking issues
> > >> on the table. To resolve this up until now I have just queued the
> > >> updates so they happen synchronously. Is there a better way to do
> > >> this when using nHibernate?
> >
> > >> Craig.
> >
> > >> --
> > >> You received this message because you are subscribed to the Google
> > >> Groups "nhusers" group.
> > >> To post to this group, send email to [email protected].
> > >> To unsubscribe from this group, send email to
> > >> [email protected].
> > >> For more options, visit this group at
> > >>http://groups.google.com/group/nhusers?hl=en.
> >
> > > --
> > > You received this message because you are subscribed to the Google
> > > Groups "nhusers" group.
> > > To post to this group, send email to [email protected].
> > > To unsubscribe from this group, send email to
> > > [email protected].
> > > For more options, visit this group at
> > >http://groups.google.com/group/nhusers?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups
> "nhusers" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/nhusers?hl=en.
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.