Tengo el siguiente problema,tengo un servicio de sms para un concurso y la idea es darle un nro de cupon unico para un sorteo. Pero como sabran el servicio de sms es bastante usado y todos a la vez te envian sus mensajes. Ejemplo, envian cupon al 4653 y el sistema tendra que lidiar con este multiacceso y darle un cupon unico.
Bueno mi idea es crear una tabla de cupones Cupones con 2 campos ID, NroCupon los dos int ,
La bd es SQLSERVER 2005, el ID tiene que ser Primary Key , y nrocupon debe empezar con 0.
Bueno dado esto.Creare un sp :
begin tran
update cupones set nrocupon=nrocupon+1 where id =1
if @@rowcount=1
begin
declare @NroTick int
select @NroTick=Nrocupon from cupones where id=1
end
commit tran
Begin Try
begin tran
if @NroTick is not null
begin
insert into detalle(NroCelular,IdServicio,Respuesta,IdPregunta,IdPrincipal,NroCupon) values ( @Telefono,@IdServicio,@Respuesta,@Pregunta,@IdPrincipal,@NroTick)
end
commit tran
Set @RespFinal= 'Tu numero de Cupon es '+ cast(@NroTick as nvarchar) + ' ya tienes acumulados ' + cast(@CantMensaje as nvarchar) + @cantcup
End Try
Begin Catch
rollback
Set @RespFinal= 'Muchas gracias por participar ya tienes acumulados ' + cast(@CantMensaje as nvarchar) + @cantcup
End Catch
Basicamente es eso.Uso 2 tran para liberar rapidamente la tabla cupones, podria colocar todo en una sola tran pero con ello no lineraria tan rapido la tabla. Puede ser que haya un error con esto no se, voy a probar en tiempo real :(
TAmbien hago un update primero para asegurar que sea unico mi cupon y no select primero en la primera transaccion.
Mas info lo pueden quitar de aca:
http://blog.chinaunix.net/u/13625/showart_183200.html
1> begin tran
2> update keystorage set lastkey=lastkey+1 where tablename="inserttest"
3> if @@rowcount=1
4> begin
5> declare @lastkey int
6> select @lastkey=lastkey from keystorage where tablename="inserttest"
7> end
8> commit tran
9> begin tran
10> if @lastkey is not null
11> begin
12> insert inserttest (testtext,counter) values ("third line",@lastkey)
13> end
14> commit tran
15> go
This provides a pretty failsafe method of guaranteeing the success of
the select statements involved in the process. You still have a couple
of implementation decisions though:
o One transaction or Two? The above example uses two transactions to
complete the task; one to update the keystorage and one to insert
the new data. Using two transactions reduces the amount of time the
lock is held on keystorage and thus is better for high insertion
applications. However, the two transaction method opens up the
possibility that the first transaction will commit and the second
will roll back, leaving a gap in the sequential numbers. (of
course, this gap is small potatoes compared to the gaps that occur
in Identity values). Using one transaction (deleting lines 8 and 9
in the SQL above) will guarantee absolutely no gaps in the values,
but will lock the keystorage table longer, reducing concurrency in
high insert applications.
o Update first or select first? The examples given generally update
the keystorage table first, THEN select the new value. Performing
the select first (you will have to rework the creation scheme
slightly; by selecting first you're actually getting the NEXT key
to add, where as by updating first, the keystorage table actually
holds the LAST key added) you allow the application to continue
processing while it waits for the update lock on the table.
However, performing the update first guarantees uniqueness (selects
are not exclusive).
Bueno mi idea es crear una tabla de cupones Cupones con 2 campos ID, NroCupon los dos int ,
La bd es SQLSERVER 2005, el ID tiene que ser Primary Key , y nrocupon debe empezar con 0.
Bueno dado esto.Creare un sp :
begin tran
update cupones set nrocupon=nrocupon+1 where id =1
if @@rowcount=1
begin
declare @NroTick int
select @NroTick=Nrocupon from cupones where id=1
end
commit tran
Begin Try
begin tran
if @NroTick is not null
begin
insert into detalle(NroCelular,IdServicio,Respuesta,IdPregunta,IdPrincipal,NroCupon) values ( @Telefono,@IdServicio,@Respuesta,@Pregunta,@IdPrincipal,@NroTick)
end
commit tran
Set @RespFinal= 'Tu numero de Cupon es '+ cast(@NroTick as nvarchar) + ' ya tienes acumulados ' + cast(@CantMensaje as nvarchar) + @cantcup
End Try
Begin Catch
rollback
Set @RespFinal= 'Muchas gracias por participar ya tienes acumulados ' + cast(@CantMensaje as nvarchar) + @cantcup
End Catch
Basicamente es eso.Uso 2 tran para liberar rapidamente la tabla cupones, podria colocar todo en una sola tran pero con ello no lineraria tan rapido la tabla. Puede ser que haya un error con esto no se, voy a probar en tiempo real :(
TAmbien hago un update primero para asegurar que sea unico mi cupon y no select primero en la primera transaccion.
Mas info lo pueden quitar de aca:
http://blog.chinaunix.net/u/13625/showart_183200.html
1> begin tran
2> update keystorage set lastkey=lastkey+1 where tablename="inserttest"
3> if @@rowcount=1
4> begin
5> declare @lastkey int
6> select @lastkey=lastkey from keystorage where tablename="inserttest"
7> end
8> commit tran
9> begin tran
10> if @lastkey is not null
11> begin
12> insert inserttest (testtext,counter) values ("third line",@lastkey)
13> end
14> commit tran
15> go
This provides a pretty failsafe method of guaranteeing the success of
the select statements involved in the process. You still have a couple
of implementation decisions though:
o One transaction or Two? The above example uses two transactions to
complete the task; one to update the keystorage and one to insert
the new data. Using two transactions reduces the amount of time the
lock is held on keystorage and thus is better for high insertion
applications. However, the two transaction method opens up the
possibility that the first transaction will commit and the second
will roll back, leaving a gap in the sequential numbers. (of
course, this gap is small potatoes compared to the gaps that occur
in Identity values). Using one transaction (deleting lines 8 and 9
in the SQL above) will guarantee absolutely no gaps in the values,
but will lock the keystorage table longer, reducing concurrency in
high insert applications.
o Update first or select first? The examples given generally update
the keystorage table first, THEN select the new value. Performing
the select first (you will have to rework the creation scheme
slightly; by selecting first you're actually getting the NEXT key
to add, where as by updating first, the keystorage table actually
holds the LAST key added) you allow the application to continue
processing while it waits for the update lock on the table.
However, performing the update first guarantees uniqueness (selects
are not exclusive).
Comentarios
Publicar un comentario