Big problem - Need urgent help!

[Saiful]

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

[James T. Stanley]

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
>
>
>
>
>
>
>
>

[Saiful]

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
>>
>>
>>
>>
>>
>>
>>
>>
>
>

[MarkS]

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
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>

[James T. Stanley]

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
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>

[Saiful]

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
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>
>

[Simon Sellick]

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.

[James T. Stanley]

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
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >
> >
>

[Saiful]

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.
>
>