Big problem - Need urgent help!


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: Big problem - Need urgent help!

Hybrid View

  1. #1
    Saiful Guest

    Big problem - Need urgent help!


    Hi Gurus

    I need some big time assistance this time.

    I have a situation where many similar applications that are running on client
    machines need to access a database count field in which these applications
    will read the count value of the database, increment it and later update
    it back into the database.

    By doing this, I hope to achieve the goal where each application would then
    only have a single different count value everytime they access the database.
    No two or more applications would be able to have an identical count value
    at any time. Sadly, this does not happen.

    For example, two applications accessing the same database table at almost
    the same time with the Select statement, both applications will read the
    same value of the count. And therefore both application will have the same
    count value. Now this is wrong. Is there a way that I could do such that
    both application does not retrieve the same count value.

    Locking the database is only when you insert, delete or update the database.
    Using select statement does not lock anything as no editing was being done.
    For my application, I select the record first, increment it and then update
    later. Is there a way where all this can be done within a single operation
    and the database can lock it such that other applications cannot read the
    value while this application is still accessing the database count field.



    rgds,
    Saiful









  2. #2
    James T. Stanley Guest

    Re: Big problem - Need urgent help!

    You could set your isolation level to serializable, start a transaction, and
    then perform the select.

    However, what is your goal for generating this unique number? If you simply
    want an integer that is unique for each connection, just return the @@SPID
    system variable (which is unique for each connection).

    "Saiful" <md-saifulamri_omar@hp.cm> wrote in message
    news:3a9b0d5b$1@news.devx.com...
    >
    > Hi Gurus
    >
    > I need some big time assistance this time.
    >
    > I have a situation where many similar applications that are running on

    client
    > machines need to access a database count field in which these applications
    > will read the count value of the database, increment it and later update
    > it back into the database.
    >
    > By doing this, I hope to achieve the goal where each application would

    then
    > only have a single different count value everytime they access the

    database.
    > No two or more applications would be able to have an identical count value
    > at any time. Sadly, this does not happen.
    >
    > For example, two applications accessing the same database table at almost
    > the same time with the Select statement, both applications will read the
    > same value of the count. And therefore both application will have the same
    > count value. Now this is wrong. Is there a way that I could do such that
    > both application does not retrieve the same count value.
    >
    > Locking the database is only when you insert, delete or update the

    database.
    > Using select statement does not lock anything as no editing was being

    done.
    > For my application, I select the record first, increment it and then

    update
    > later. Is there a way where all this can be done within a single operation
    > and the database can lock it such that other applications cannot read the
    > value while this application is still accessing the database count field.
    >
    >
    >
    > rgds,
    > Saiful
    >
    >
    >
    >
    >
    >
    >
    >




  3. #3
    Saiful Guest

    Re: Big problem - Need urgent help!


    Hi James

    I am by no means an expert in Database. Can you eplicitly tell me what you
    mean by set isolation level to serializable, start a transaction, etc.

    My knowledge on Database is using SQL statement to select, delete, insert
    and update records into the database. It does not go further than that. Hope
    you can assist me in this area.

    FYI, I am using SQL Server 7.0. Would like to know how to this in other database
    type such as Informix and Oracle.

    rgds,
    Saiful

    "James T. Stanley" <jstanley@powerwayinc.com> wrote:
    >You could set your isolation level to serializable, start a transaction,

    and
    >then perform the select.
    >
    >However, what is your goal for generating this unique number? If you simply
    >want an integer that is unique for each connection, just return the @@SPID
    >system variable (which is unique for each connection).
    >
    >"Saiful" <md-saifulamri_omar@hp.cm> wrote in message
    >news:3a9b0d5b$1@news.devx.com...
    >>
    >> Hi Gurus
    >>
    >> I need some big time assistance this time.
    >>
    >> I have a situation where many similar applications that are running on

    >client
    >> machines need to access a database count field in which these applications
    >> will read the count value of the database, increment it and later update
    >> it back into the database.
    >>
    >> By doing this, I hope to achieve the goal where each application would

    >then
    >> only have a single different count value everytime they access the

    >database.
    >> No two or more applications would be able to have an identical count value
    >> at any time. Sadly, this does not happen.
    >>
    >> For example, two applications accessing the same database table at almost
    >> the same time with the Select statement, both applications will read the
    >> same value of the count. And therefore both application will have the

    same
    >> count value. Now this is wrong. Is there a way that I could do such that
    >> both application does not retrieve the same count value.
    >>
    >> Locking the database is only when you insert, delete or update the

    >database.
    >> Using select statement does not lock anything as no editing was being

    >done.
    >> For my application, I select the record first, increment it and then

    >update
    >> later. Is there a way where all this can be done within a single operation
    >> and the database can lock it such that other applications cannot read

    the
    >> value while this application is still accessing the database count field.
    >>
    >>
    >>
    >> rgds,
    >> Saiful
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>

    >
    >



  4. #4
    MarkS Guest

    Re: Big problem - Need urgent help!


    Isolation Levels
    When locking is used as the concurrency control mechanism, it solves concurrency
    problems. This allows all transactions to run in complete isolation of one
    another, although there can be more than one transaction running in Microsoft®
    SQL Server™ at any one time.

    Serializability is the property such that the database state achieved by
    running a set of concurrent transactions is equivalent to the database state
    that would be achieved if the set of transactions were executed serially
    in some order.

    SQL-92 Isolation Levels
    Although serialization is important to transactions to ensure that the data
    in the database remains correct and meaningful, many transactions do not
    always require full isolation. For example, several writers are working on
    different chapters of the same book. New chapters can be submitted to the
    project at any time. However, after a chapter has been edited, a writer cannot
    make any changes to the chapter without the editor’s approval. In this way,
    the editor can be assured of the accuracy of the book project at any point
    in time, despite the arrival of new unedited chapters. This is an example
    of the read committed isolation level: The editor can see both previously
    edited chapters and any new, recently submitted chapters.

    The level at which a transaction is prepared to accept inconsistent data
    is termed the isolation level. It is the degree to which one transaction
    must be isolated from other transactions. A lower isolation level increases
    concurrency but at the expense of data correctness. Conversely, a higher
    isolation level ensures that data is correct, but can negatively affect concurrency.
    The isolation level required by an application determines the locking behavior
    SQL Server uses.

    SQL-92 defines four isolation levels, all of which are supported by SQL Server:


    Read uncommitted (the lowest level where transactions are isolated just enough
    to ensure that physically corrupt data is not read)
    Read committed (SQL Server default level)
    Repeatable read
    Serializable (the highest level, where transactions are completely isolated
    from one another)
    If transactions are run at an isolation level of serializable, then any concurrent
    overlapping transactions are guaranteed to be serializable.

    The four isolation levels allow different types of behavior.

    Isolation level Dirty read Nonrepeatable read Phantom
    Read uncommitted Yes Yes Yes
    Read committed No Yes Yes
    Repeatable read No No Yes
    Serializable No No No


    Transactions must be run at an isolation level of repeatable read or higher
    to prevent lost updates that can occur when two transactions each retrieve
    the same row, and then later update the row based on the originally retrieved
    values. If the two transactions update rows using a single UPDATE statement
    and do not base the update on the previously retrieved values, lost updates
    cannot occur at the default isolation level of read committed.

    "Saiful" <md-saifulamri_omar@hp.com> wrote:
    >
    >Hi James
    >
    >I am by no means an expert in Database. Can you eplicitly tell me what you
    >mean by set isolation level to serializable, start a transaction, etc.
    >
    >My knowledge on Database is using SQL statement to select, delete, insert
    >and update records into the database. It does not go further than that.

    Hope
    >you can assist me in this area.
    >
    >FYI, I am using SQL Server 7.0. Would like to know how to this in other

    database
    >type such as Informix and Oracle.
    >
    >rgds,
    >Saiful
    >
    >"James T. Stanley" <jstanley@powerwayinc.com> wrote:
    >>You could set your isolation level to serializable, start a transaction,

    >and
    >>then perform the select.
    >>
    >>However, what is your goal for generating this unique number? If you simply
    >>want an integer that is unique for each connection, just return the @@SPID
    >>system variable (which is unique for each connection).
    >>
    >>"Saiful" <md-saifulamri_omar@hp.cm> wrote in message
    >>news:3a9b0d5b$1@news.devx.com...
    >>>
    >>> Hi Gurus
    >>>
    >>> I need some big time assistance this time.
    >>>
    >>> I have a situation where many similar applications that are running on

    >>client
    >>> machines need to access a database count field in which these applications
    >>> will read the count value of the database, increment it and later update
    >>> it back into the database.
    >>>
    >>> By doing this, I hope to achieve the goal where each application would

    >>then
    >>> only have a single different count value everytime they access the

    >>database.
    >>> No two or more applications would be able to have an identical count

    value
    >>> at any time. Sadly, this does not happen.
    >>>
    >>> For example, two applications accessing the same database table at almost
    >>> the same time with the Select statement, both applications will read

    the
    >>> same value of the count. And therefore both application will have the

    >same
    >>> count value. Now this is wrong. Is there a way that I could do such that
    >>> both application does not retrieve the same count value.
    >>>
    >>> Locking the database is only when you insert, delete or update the

    >>database.
    >>> Using select statement does not lock anything as no editing was being

    >>done.
    >>> For my application, I select the record first, increment it and then

    >>update
    >>> later. Is there a way where all this can be done within a single operation
    >>> and the database can lock it such that other applications cannot read

    >the
    >>> value while this application is still accessing the database count field.
    >>>
    >>>
    >>>
    >>> rgds,
    >>> Saiful
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>

    >>
    >>

    >



  5. #5
    James T. Stanley Guest

    Re: Big problem - Need urgent help!

    First, I want to know what purpose the unique number serves. This may not be
    the most effective way to generate it.

    James

    "Saiful" <md-saifulamri_omar@hp.com> wrote in message
    news:3a9c62ba$1@news.devx.com...
    >
    > Hi James
    >
    > I am by no means an expert in Database. Can you eplicitly tell me what you
    > mean by set isolation level to serializable, start a transaction, etc.
    >
    > My knowledge on Database is using SQL statement to select, delete, insert
    > and update records into the database. It does not go further than that.

    Hope
    > you can assist me in this area.
    >
    > FYI, I am using SQL Server 7.0. Would like to know how to this in other

    database
    > type such as Informix and Oracle.
    >
    > rgds,
    > Saiful
    >
    > "James T. Stanley" <jstanley@powerwayinc.com> wrote:
    > >You could set your isolation level to serializable, start a transaction,

    > and
    > >then perform the select.
    > >
    > >However, what is your goal for generating this unique number? If you

    simply
    > >want an integer that is unique for each connection, just return the

    @@SPID
    > >system variable (which is unique for each connection).
    > >
    > >"Saiful" <md-saifulamri_omar@hp.cm> wrote in message
    > >news:3a9b0d5b$1@news.devx.com...
    > >>
    > >> Hi Gurus
    > >>
    > >> I need some big time assistance this time.
    > >>
    > >> I have a situation where many similar applications that are running on

    > >client
    > >> machines need to access a database count field in which these

    applications
    > >> will read the count value of the database, increment it and later

    update
    > >> it back into the database.
    > >>
    > >> By doing this, I hope to achieve the goal where each application would

    > >then
    > >> only have a single different count value everytime they access the

    > >database.
    > >> No two or more applications would be able to have an identical count

    value
    > >> at any time. Sadly, this does not happen.
    > >>
    > >> For example, two applications accessing the same database table at

    almost
    > >> the same time with the Select statement, both applications will read

    the
    > >> same value of the count. And therefore both application will have the

    > same
    > >> count value. Now this is wrong. Is there a way that I could do such

    that
    > >> both application does not retrieve the same count value.
    > >>
    > >> Locking the database is only when you insert, delete or update the

    > >database.
    > >> Using select statement does not lock anything as no editing was being

    > >done.
    > >> For my application, I select the record first, increment it and then

    > >update
    > >> later. Is there a way where all this can be done within a single

    operation
    > >> and the database can lock it such that other applications cannot read

    > the
    > >> value while this application is still accessing the database count

    field.
    > >>
    > >>
    > >>
    > >> rgds,
    > >> Saiful
    > >>
    > >>
    > >>
    > >>
    > >>
    > >>
    > >>
    > >>

    > >
    > >

    >




  6. #6
    Saiful Guest

    Re: Big problem - Need urgent help!


    Hi James

    Purpose is to differentiate all the pallet numbers that gets produced after
    each completion of a product. Therefore, in each production line, there is
    an application that is running that produces this unique number. How it does
    this production of unique number is thru reading a database count field that
    keeps track of the pallet number to assign.

    After reading each pallet number, this field value would be incremented by
    one. Hence next number would be different for the each pallet. Problem comes
    when more than one application gets to read this count field thru the select
    statement. As far as I know, locking of transaction happens only when you
    edit a row eg, insert, update, delete. No locking happens when you try to
    select a recordset. (Correct me if I am wrong!)

    I am open to better ideas of how to do this. But accessing a database value
    is most logical for now except that I must make sure that only one application
    can only read the count field row and then increment it at one time.

    rgds,
    Saiful


    "James T. Stanley" <jstanley@powerwayinc.com> wrote:
    >First, I want to know what purpose the unique number serves. This may not

    be
    >the most effective way to generate it.
    >
    >James
    >
    >"Saiful" <md-saifulamri_omar@hp.com> wrote in message
    >news:3a9c62ba$1@news.devx.com...
    >>
    >> Hi James
    >>
    >> I am by no means an expert in Database. Can you eplicitly tell me what

    you
    >> mean by set isolation level to serializable, start a transaction, etc.
    >>
    >> My knowledge on Database is using SQL statement to select, delete, insert
    >> and update records into the database. It does not go further than that.

    >Hope
    >> you can assist me in this area.
    >>
    >> FYI, I am using SQL Server 7.0. Would like to know how to this in other

    >database
    >> type such as Informix and Oracle.
    >>
    >> rgds,
    >> Saiful
    >>
    >> "James T. Stanley" <jstanley@powerwayinc.com> wrote:
    >> >You could set your isolation level to serializable, start a transaction,

    >> and
    >> >then perform the select.
    >> >
    >> >However, what is your goal for generating this unique number? If you

    >simply
    >> >want an integer that is unique for each connection, just return the

    >@@SPID
    >> >system variable (which is unique for each connection).
    >> >
    >> >"Saiful" <md-saifulamri_omar@hp.cm> wrote in message
    >> >news:3a9b0d5b$1@news.devx.com...
    >> >>
    >> >> Hi Gurus
    >> >>
    >> >> I need some big time assistance this time.
    >> >>
    >> >> I have a situation where many similar applications that are running

    on
    >> >client
    >> >> machines need to access a database count field in which these

    >applications
    >> >> will read the count value of the database, increment it and later

    >update
    >> >> it back into the database.
    >> >>
    >> >> By doing this, I hope to achieve the goal where each application would
    >> >then
    >> >> only have a single different count value everytime they access the
    >> >database.
    >> >> No two or more applications would be able to have an identical count

    >value
    >> >> at any time. Sadly, this does not happen.
    >> >>
    >> >> For example, two applications accessing the same database table at

    >almost
    >> >> the same time with the Select statement, both applications will read

    >the
    >> >> same value of the count. And therefore both application will have the

    >> same
    >> >> count value. Now this is wrong. Is there a way that I could do such

    >that
    >> >> both application does not retrieve the same count value.
    >> >>
    >> >> Locking the database is only when you insert, delete or update the
    >> >database.
    >> >> Using select statement does not lock anything as no editing was being
    >> >done.
    >> >> For my application, I select the record first, increment it and then
    >> >update
    >> >> later. Is there a way where all this can be done within a single

    >operation
    >> >> and the database can lock it such that other applications cannot read

    >> the
    >> >> value while this application is still accessing the database count

    >field.
    >> >>
    >> >>
    >> >>
    >> >> rgds,
    >> >> Saiful
    >> >>
    >> >>
    >> >>
    >> >>
    >> >>
    >> >>
    >> >>
    >> >>
    >> >
    >> >

    >>

    >
    >



  7. #7
    Simon Sellick Guest

    Re: Big problem - Need urgent help!


    Saiful,

    I assume you are using SQL Server - isn't there a sequence generator? I
    thought that you could use a sequence as a column in a table, so could you
    insert a row instead of selecting a number, then use the value from the new
    row?

    If that is no good, could you use a stored procedure to generate the number?
    You can then make the T-SQL procedure run a complete transaction (select,
    increment, update, commit) without interruption and return you the value.

    Hope I haven't missed the point here...

    Simon.



  8. #8
    James T. Stanley Guest

    Re: Big problem - Need urgent help!

    Now I understand your need.

    The key is to set your isolation level (with the SET TRANSACTION ISOLATION
    LEVEL command) to serializable before beginning your transaction.

    James

    "Saiful" <md-saifulamri_omar@hp.com> wrote in message
    news:3a9ef6ec$1@news.devx.com...
    >
    > Hi James
    >
    > Purpose is to differentiate all the pallet numbers that gets produced

    after
    > each completion of a product. Therefore, in each production line, there is
    > an application that is running that produces this unique number. How it

    does
    > this production of unique number is thru reading a database count field

    that
    > keeps track of the pallet number to assign.
    >
    > After reading each pallet number, this field value would be incremented by
    > one. Hence next number would be different for the each pallet. Problem

    comes
    > when more than one application gets to read this count field thru the

    select
    > statement. As far as I know, locking of transaction happens only when you
    > edit a row eg, insert, update, delete. No locking happens when you try to
    > select a recordset. (Correct me if I am wrong!)
    >
    > I am open to better ideas of how to do this. But accessing a database

    value
    > is most logical for now except that I must make sure that only one

    application
    > can only read the count field row and then increment it at one time.
    >
    > rgds,
    > Saiful
    >
    >
    > "James T. Stanley" <jstanley@powerwayinc.com> wrote:
    > >First, I want to know what purpose the unique number serves. This may not

    > be
    > >the most effective way to generate it.
    > >
    > >James
    > >
    > >"Saiful" <md-saifulamri_omar@hp.com> wrote in message
    > >news:3a9c62ba$1@news.devx.com...
    > >>
    > >> Hi James
    > >>
    > >> I am by no means an expert in Database. Can you eplicitly tell me what

    > you
    > >> mean by set isolation level to serializable, start a transaction, etc.
    > >>
    > >> My knowledge on Database is using SQL statement to select, delete,

    insert
    > >> and update records into the database. It does not go further than that.

    > >Hope
    > >> you can assist me in this area.
    > >>
    > >> FYI, I am using SQL Server 7.0. Would like to know how to this in other

    > >database
    > >> type such as Informix and Oracle.
    > >>
    > >> rgds,
    > >> Saiful
    > >>
    > >> "James T. Stanley" <jstanley@powerwayinc.com> wrote:
    > >> >You could set your isolation level to serializable, start a

    transaction,
    > >> and
    > >> >then perform the select.
    > >> >
    > >> >However, what is your goal for generating this unique number? If you

    > >simply
    > >> >want an integer that is unique for each connection, just return the

    > >@@SPID
    > >> >system variable (which is unique for each connection).
    > >> >
    > >> >"Saiful" <md-saifulamri_omar@hp.cm> wrote in message
    > >> >news:3a9b0d5b$1@news.devx.com...
    > >> >>
    > >> >> Hi Gurus
    > >> >>
    > >> >> I need some big time assistance this time.
    > >> >>
    > >> >> I have a situation where many similar applications that are running

    > on
    > >> >client
    > >> >> machines need to access a database count field in which these

    > >applications
    > >> >> will read the count value of the database, increment it and later

    > >update
    > >> >> it back into the database.
    > >> >>
    > >> >> By doing this, I hope to achieve the goal where each application

    would
    > >> >then
    > >> >> only have a single different count value everytime they access the
    > >> >database.
    > >> >> No two or more applications would be able to have an identical count

    > >value
    > >> >> at any time. Sadly, this does not happen.
    > >> >>
    > >> >> For example, two applications accessing the same database table at

    > >almost
    > >> >> the same time with the Select statement, both applications will read

    > >the
    > >> >> same value of the count. And therefore both application will have

    the
    > >> same
    > >> >> count value. Now this is wrong. Is there a way that I could do such

    > >that
    > >> >> both application does not retrieve the same count value.
    > >> >>
    > >> >> Locking the database is only when you insert, delete or update the
    > >> >database.
    > >> >> Using select statement does not lock anything as no editing was

    being
    > >> >done.
    > >> >> For my application, I select the record first, increment it and then
    > >> >update
    > >> >> later. Is there a way where all this can be done within a single

    > >operation
    > >> >> and the database can lock it such that other applications cannot

    read
    > >> the
    > >> >> value while this application is still accessing the database count

    > >field.
    > >> >>
    > >> >>
    > >> >>
    > >> >> rgds,
    > >> >> Saiful
    > >> >>
    > >> >>
    > >> >>
    > >> >>
    > >> >>
    > >> >>
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>

    > >
    > >

    >




  9. #9
    Saiful Guest

    Re: Big problem - Need urgent help!


    Hi Guys

    Thanks for the reply that I have been receiving so far. I appreciate it.


    Simon - What you say does make sense. I am looking into that as well.

    Once again, thanks all.


    rgds,
    Saiful


    "Simon Sellick" <simon.sellick@tesco.net> wrote:
    >
    >Saiful,
    >
    >I assume you are using SQL Server - isn't there a sequence generator? I
    >thought that you could use a sequence as a column in a table, so could you
    >insert a row instead of selecting a number, then use the value from the

    new
    >row?
    >
    >If that is no good, could you use a stored procedure to generate the number?
    > You can then make the T-SQL procedure run a complete transaction (select,
    >increment, update, commit) without interruption and return you the value.
    >
    >Hope I haven't missed the point here...
    >
    >Simon.
    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center