Hi,

using sql server 2000 with classic asp

have a table with a field "series count"

every invoice made will have an incrementing series count

so when everytime an invoice is made:

conn.begintrans
set rs = conn.execute("select series_count from table1") ' one field /
one record in table1
count = rs(0) + 1
inv_number = count
conn.execute("update table1 set series_count = " & count) ' only one
record in table, set new val

if err.number <> 0 then conn.rollbacktrans else conn.committrans

till now i used to use this on an ms access database and i have never
had a problem
it's worked wonderfully, isolation level is READ COMMITTED

now i am going to use this on sql server 2000 whose isolation level is
READ COMMITTED by default.... there will be 100's of ppl using the
site at the same time.....is READ COMMITTED fine for the above code to
work or should I use SERIALIZATION ?

best regards,
shripal.

On 03.09.2008 17:52, shripaldalal wrote:
> Hi,
>
> using sql server 2000 with classic asp
>
> have a table with a field "series count"
>
> every invoice made will have an incrementing series count
>
> so when everytime an invoice is made:
>
> conn.begintrans
> set rs = conn.execute("select series_count from table1") ' one field /
> one record in table1
> count = rs(0) + 1
> inv_number = count
> conn.execute("update table1 set series_count = " & count) ' only one
> record in table, set new val
>
> if err.number <> 0 then conn.rollbacktrans else conn.committrans
>
> till now i used to use this on an ms access database and i have never
> had a problem
> it's worked wonderfully, isolation level is READ COMMITTED
>
> now i am going to use this on sql server 2000 whose isolation level is
> READ COMMITTED by default.... there will be 100's of ppl using the
> site at the same time.....is READ COMMITTED fine for the above code to
> work or should I use SERIALIZATION ?

Change your approach. Either use an autoincrement column and
SCOPE_IDENTITY (if you can live with holes in the sequence caused by
rolled back transactions) or store the counter in a separate table and
do an UPDATE with an OUTPUT clause to return the incremented value. But
note that the latter approach is a potential bottleneck if there are
multiple concurrent accesses and / or transactions are long lived.

Kind regards

robert

No, it's not. The Read Committed transaction isolation level doesn't
prohibit two different connections to read the same value of series_count
before the update take place. The probality is low but not zero. As you
are on SQL-Server 2000, you should use a single update statement; something
like:

declare @i int;
update table1 set @i = series_count = (series_count + 1) where IdTable = 1;
select @i;

You can execute this in a single Conn.Execute statement because it will be a
single batch. For the above statement, Read Commited will now be sufficient
because the update take place in the same instruction as the reading and the
update lock will be held until the end of the transaction.

Notice that in a professional database, this is still insufficient because
there is a possibility of having hole in your system is there is any problem
later in the client's code. In a real application, the creation of
additional records into the tables should be done inside the same
transaction - possibly with a flag indicating that this record has just been
created but has yet to be used - in order to ensure that there will be no
hole (a legal requirement in some countries).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"shripaldalal" <shripaldalal@gmail.com> wrote in message
news:9d1c6f56-be7f-486c-a841-0ede66ef0e07@i24g2000prf.googlegroups.com...
> Hi,
>
> using sql server 2000 with classic asp
>
> have a table with a field "series count"
>
> every invoice made will have an incrementing series count
>
> so when everytime an invoice is made:
>
> conn.begintrans
> set rs = conn.execute("select series_count from table1") ' one field /
> one record in table1
> count = rs(0) + 1
> inv_number = count
> conn.execute("update table1 set series_count = " & count) ' only one
> record in table, set new val
>
> if err.number <> 0 then conn.rollbacktrans else conn.committrans
>
> till now i used to use this on an ms access database and i have never
> had a problem
> it's worked wonderfully, isolation level is READ COMMITTED
>
> now i am going to use this on sql server 2000 whose isolation level is
> READ COMMITTED by default.... there will be 100's of ppl using the
> site at the same time.....is READ COMMITTED fine for the above code to
> work or should I use SERIALIZATION ?
>
> best regards,
> shripal.

Hi, so is serialization good enough for this job ? the number that i
get i concatenate it like inv/ + (number i get) and put it in the
table in a field where no duplicates are allowed so the good thing is
by any chance even if there's a duplicate number one of the
transactions will generate an error and get rolled back, but if
serialization is best for this job i dont mind using that for this
job..... should i use serialization ?

On Sep 4, 5:57am, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> No, it's not. The Read Committed transaction isolation level doesn't
> prohibit two different connections to read the same value of series_count
> before the update take place. The probality is low but not zero. Asyou
> are on SQL-Server 2000, you should use a single update statement; something
> like:
>
> declare @i int;
> update table1 set @i = series_count = (series_count + 1) where IdTable = 1;
> select @i;
>
> You can execute this in a single Conn.Execute statement because it will be a
> single batch. For the above statement, Read Commited will now be sufficient
> because the update take place in the same instruction as the reading and the
> update lock will be held until the end of the transaction.
>
> Notice that in a professional database, this is still insufficient because
> there is a possibility of having hole in your system is there is any problem
> later in the client's code. In a real application, the creation of
> additional records into the tables should be done inside the same
> transaction - possibly with a flag indicating that this record has just been
> created but has yet to be used - in order to ensure that there will be no
> hole (a legal requirement in some countries).
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "shripaldalal" <shripalda...@gmail.com> wrote in message
>
> news:9d1c6f56-be7f-486c-a841-0ede66ef0e07@i24g2000prf.googlegroups.com...
>
>
>
> > Hi,
>
> > using sql server 2000 with classic asp
>
> > have a table with a field "series count"
>
> > every invoice made will have an incrementing series count
>
> > so when everytime an invoice is made:
>
> > conn.begintrans
> > set rs = conn.execute("select series_count from table1") ' one field /
> > one record in table1
> > count = rs(0) + 1
> > inv_number = count
> > conn.execute("update table1 set series_count = " & count) ' only one
> > record in table, set new val
>
> > if err.number <> 0 then conn.rollbacktrans else conn.committrans
>
> > till now i used to use this on an ms access database and i have never
> > had a problem
> > it's worked wonderfully, isolation level is READ COMMITTED
>
> > now i am going to use this on sql server 2000 whose isolation level is
> > READ COMMITTED by default.... there will be 100's of ppl using the
> > site at the same time.....is READ COMMITTED fine for the above code to
> > work or should I use SERIALIZATION ?
>
> > best regards,
> > shripal.- Hide quoted text -
>
> - Show quoted text -

> Hi, so is serialization good enough for this job ? the number that i
> get i concatenate it like inv/ + (number i get) and put it in the
> table in a field where no duplicates are allowed so the good thing is
> by any chance even if there's a duplicate number one of the
> transactions will generate an error and get rolled back, but if
> serialization is best for this job i dont mind using that for this
> job..... should i use serialization ?

Seriously, I'm not sure to understand what you're saying here but if all you
want is a Yes/No answer than yes, serialization is good for your case and
you should use serialization.

However, I would suggest that you read more carefully my previous post and
also that you start a few days of serious study about transactions,
isolation levels and lockings. In my opinion, not having a clear
understanding of these mecanism is a no-go if you want to work with
SQL-Server.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"shripaldalal" <shripaldalal@gmail.com> wrote in message
news:430065d4-72c2-4545-b7a8-de068a734275@b2g2000prf.googlegroups.com...
Hi, so is serialization good enough for this job ? the number that i
get i concatenate it like inv/ + (number i get) and put it in the
table in a field where no duplicates are allowed so the good thing is
by any chance even if there's a duplicate number one of the
transactions will generate an error and get rolled back, but if
serialization is best for this job i dont mind using that for this
job..... should i use serialization ?

On Sep 4, 5:57 am, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> No, it's not. The Read Committed transaction isolation level doesn't
> prohibit two different connections to read the same value of series_count
> before the update take place. The probality is low but not zero. As you
> are on SQL-Server 2000, you should use a single update statement;
> something
> like:
>
> declare @i int;
> update table1 set @i = series_count = (series_count + 1) where IdTable =
> 1;
> select @i;
>
> You can execute this in a single Conn.Execute statement because it will be
> a
> single batch. For the above statement, Read Commited will now be
> sufficient
> because the update take place in the same instruction as the reading and
> the
> update lock will be held until the end of the transaction.
>
> Notice that in a professional database, this is still insufficient because
> there is a possibility of having hole in your system is there is any
> problem
> later in the client's code. In a real application, the creation of
> additional records into the tables should be done inside the same
> transaction - possibly with a flag indicating that this record has just
> been
> created but has yet to be used - in order to ensure that there will be no
> hole (a legal requirement in some countries).
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "shripaldalal" <shripalda...@gmail.com> wrote in message
>
> news:9d1c6f56-be7f-486c-a841-0ede66ef0e07@i24g2000prf.googlegroups.com...
>
>
>
> > Hi,
>
> > using sql server 2000 with classic asp
>
> > have a table with a field "series count"
>
> > every invoice made will have an incrementing series count
>
> > so when everytime an invoice is made:
>
> > conn.begintrans
> > set rs = conn.execute("select series_count from table1") ' one field /
> > one record in table1
> > count = rs(0) + 1
> > inv_number = count
> > conn.execute("update table1 set series_count = " & count) ' only one
> > record in table, set new val
>
> > if err.number <> 0 then conn.rollbacktrans else conn.committrans
>
> > till now i used to use this on an ms access database and i have never
> > had a problem
> > it's worked wonderfully, isolation level is READ COMMITTED
>
> > now i am going to use this on sql server 2000 whose isolation level is
> > READ COMMITTED by default.... there will be 100's of ppl using the
> > site at the same time.....is READ COMMITTED fine for the above code to
> > work or should I use SERIALIZATION ?
>
> > best regards,
> > shripal.- Hide quoted text -
>
> - Show quoted text -

On 05.09.2008 18:42, Sylvain Lafontaine wrote:
> However, I would suggest that you read more carefully my previous post and
> also that you start a few days of serious study about transactions,
> isolation levels and lockings. In my opinion, not having a clear
> understanding of these mecanism is a no-go if you want to work with
> SQL-Server.

Absolutely agree! Not taking the time to understand the concepts
properly and how SQL Server implements them is a recipe for disaster.
And this is true for other databases as well since there are remarkable
differences between products in this area despite the "simple" theory.

Kind regards

robert