Use DB2 Sequences during commit

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Use DB2 Sequences during commit

jtuchel
Hi,

I'd like to use DB Sequences for new objects in certain tables. Think of invoice numbers or such.

The problem is, the numbers have to be consecutive, without holes. So the best (and maybe only really working) solution I can think of is using the Sequences of the DB server. The thing is, a new sequence may only be assigned during commit, because if I implement something else, there may be problems in a multi user environment.

The whole thing should work like auto-assigned primary keys. BUT: not every inserted row needs its own number. The idea is that you can set a checkmark in the apllication that says: auto-assign a number, and the system will assign the number on commit. In other cases, users will enter the number by hand (which is a new problem, because they may only enter numbers that have been assigned already...)

Ah, and one thing is also important and makes the whole affair spicy: The sequences aren't per Table, they are per User!

So here are my two most obvious questions:

1. What can I do to make Glorp request a new number during commit, but not for every insert?
2. How can I check numbers against that sequence in order to avoid new rows with sequences that were assigned already.

I guess there is no ready-made solution, but every idea counts...

Thanks for reading

Joachim

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Use DB2 Sequences during commit

Esteban A. Maringolo
I have a similar use case for Invoices/Orders.

I rely on database sequences for this, but I'm not happy with my design either, because I get minor gaps from time to time. However I think this isn't ORM's responsibility, but is something that the application has to deal with.

The best solution I used years ago (not with GLORP) was to have a table of "counters", with row-level locking, so only one transaction was able to be processed at once. It was a bottleneck, but we didn't have that many req/sec, so in practice if there was a blocking, wasn't perceivable by the user. 

One wild guess using GLORP is to find a way to hook before the RowMap is created, just like GLORP does for primary keys that are sequence based.

I'm also open to see what others have to suggest here.

Regards,


Esteban A. Maringolo

2015-01-29 3:58 GMT-03:00 jtuchel <[hidden email]>:
Hi,

I'd like to use DB Sequences for new objects in certain tables. Think of invoice numbers or such.

The problem is, the numbers have to be consecutive, without holes. So the best (and maybe only really working) solution I can think of is using the Sequences of the DB server. The thing is, a new sequence may only be assigned during commit, because if I implement something else, there may be problems in a multi user environment.

The whole thing should work like auto-assigned primary keys. BUT: not every inserted row needs its own number. The idea is that you can set a checkmark in the apllication that says: auto-assign a number, and the system will assign the number on commit. In other cases, users will enter the number by hand (which is a new problem, because they may only enter numbers that have been assigned already...)

Ah, and one thing is also important and makes the whole affair spicy: The sequences aren't per Table, they are per User!

So here are my two most obvious questions:

1. What can I do to make Glorp request a new number during commit, but not for every insert?
2. How can I check numbers against that sequence in order to avoid new rows with sequences that were assigned already.

I guess there is no ready-made solution, but every idea counts...

Thanks for reading

Joachim

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Use DB2 Sequences during commit

Maarten Mostert

What if you make your order number unique, and then everytime you want to insert you first count the number of rows to get the unique next number then insert a new row with this number accompagnied with an error handler, if it fails someone else just took it, if commits correctly you got your unique number and then update the rest of your order ..

 

my 2cts

 

@+Maarten



> "Esteban A. Maringolo" <[hidden email]> |

I have a similar use case for Invoices/Orders.
I rely on database sequences for this, but I'm not happy with my design either, because I get minor gaps from time to time. However I think this isn't ORM's responsibility, but is something that the application has to deal with.
The best solution I used years ago (not with GLORP) was to have a table of "counters", with row-level locking, so only one transaction was able to be processed at once. It was a bottleneck, but we didn't have that many req/sec, so in practice if there was a blocking, wasn't perceivable by the user. 
One wild guess using GLORP is to find a way to hook before the RowMap is created, just like GLORP does for primary keys that are sequence based.
I'm also open to see what others have to suggest here.
Regards,

Esteban A. Maringolo

2015-01-29 3:58 GMT-03:00 jtuchel <[hidden email]>:
Hi,

I'd like to use DB Sequences for new objects in certain tables. Think of invoice numbers or such.

The problem is, the numbers have to be consecutive, without holes. So the best (and maybe only really working) solution I can think of is using the Sequences of the DB server. The thing is, a new sequence may only be assigned during commit, because if I implement something else, there may be problems in a multi user environment.

The whole thing should work like auto-assigned primary keys. BUT: not every inserted row needs its own number. The idea is that you can set a checkmark in the apllication that says: auto-assign a number, and the system will assign the number on commit. In other cases, users will enter the number by hand (which is a new problem, because they may only enter numbers that have been assigned already...)

Ah, and one thing is also important and makes the whole affair spicy: The sequences aren't per Table, they are per User!

So here are my two most obvious questions:

1. What can I do to make Glorp request a new number during commit, but not for every insert?
2. How can I check numbers against that sequence in order to avoid new rows with sequences that were assigned already.

I guess there is no ready-made solution, but every idea counts...

Thanks for reading

Joachim
--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Use DB2 Sequences during commit

jtuchel
Hi Maarten,

the problem is that the number is not globally unique, nor is it unique at all. You can add new entries that get the same number as an existing one. And the sequence itself is on a per-user basis, meaning each user has their own squence starting with 1.

Joachim

Am Donnerstag, 29. Januar 2015 08:42:43 UTC+1 schrieb [hidden email]:

What if you make your order number unique, and then everytime you want to insert you first count the number of rows to get the unique next number then insert a new row with this number accompagnied with an error handler, if it fails someone else just took it, if commits correctly you got your unique number and then update the rest of your order ..

 

my 2cts

 

@+Maarten



> "Esteban A. Maringolo" <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="LcoItr2PkGgJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">emari...@...> |

I have a similar use case for Invoices/Orders.
I rely on database sequences for this, but I'm not happy with my design either, because I get minor gaps from time to time. However I think this isn't ORM's responsibility, but is something that the application has to deal with.
The best solution I used years ago (not with GLORP) was to have a table of "counters", with row-level locking, so only one transaction was able to be processed at once. It was a bottleneck, but we didn't have that many req/sec, so in practice if there was a blocking, wasn't perceivable by the user. 
One wild guess using GLORP is to find a way to hook before the RowMap is created, just like GLORP does for primary keys that are sequence based.
I'm also open to see what others have to suggest here.
Regards,

Esteban A. Maringolo

2015-01-29 3:58 GMT-03:00 jtuchel <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="LcoItr2PkGgJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">jtu...@...>:
Hi,

I'd like to use DB Sequences for new objects in certain tables. Think of invoice numbers or such.

The problem is, the numbers have to be consecutive, without holes. So the best (and maybe only really working) solution I can think of is using the Sequences of the DB server. The thing is, a new sequence may only be assigned during commit, because if I implement something else, there may be problems in a multi user environment.

The whole thing should work like auto-assigned primary keys. BUT: not every inserted row needs its own number. The idea is that you can set a checkmark in the apllication that says: auto-assign a number, and the system will assign the number on commit. In other cases, users will enter the number by hand (which is a new problem, because they may only enter numbers that have been assigned already...)

Ah, and one thing is also important and makes the whole affair spicy: The sequences aren't per Table, they are per User!

So here are my two most obvious questions:

1. What can I do to make Glorp request a new number during commit, but not for every insert?
2. How can I check numbers against that sequence in order to avoid new rows with sequences that were assigned already.

I guess there is no ready-made solution, but every idea counts...

Thanks for reading

Joachim
--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="LcoItr2PkGgJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">glorp-group...@googlegroups.com.
To post to this group, send email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="LcoItr2PkGgJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">glorp...@....
Visit this group at <a href="http://groups.google.com/group/glorp-group" target="_blank" rel="nofollow" onmousedown="this.href='http://groups.google.com/group/glorp-group';return true;" onclick="this.href='http://groups.google.com/group/glorp-group';return true;">http://groups.google.com/group/glorp-group.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href='https://groups.google.com/d/optout';return true;" onclick="this.href='https://groups.google.com/d/optout';return true;">https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="LcoItr2PkGgJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">glorp-group...@googlegroups.com.
To post to this group, send email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="LcoItr2PkGgJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">glorp...@....
Visit this group at <a href="http://groups.google.com/group/glorp-group" target="_blank" rel="nofollow" onmousedown="this.href='http://groups.google.com/group/glorp-group';return true;" onclick="this.href='http://groups.google.com/group/glorp-group';return true;">http://groups.google.com/group/glorp-group.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href='https://groups.google.com/d/optout';return true;" onclick="this.href='https://groups.google.com/d/optout';return true;">https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Use DB2 Sequences during commit

jtuchel
In reply to this post by Esteban A. Maringolo
Esteban,

I cannot really use DB sequences, but I wasn't aware of it earlier today. The number is on a per-user basis, so not globally unique.

I think I am now on the right track with using triggers (and maybe stored procedures). These will be relatively complex and outside of the control of my Glorp Application. That is the only thing I don't like about this solution

But I have no better idea than triggers, because

* the sequence is not a global sequence, the numbers start with an initial value for each customer each year...
* not every insert needs a new number, only inserts that don't come with a number or which come with a special attribute/switch (some boolean that says: I want to get an automatic number)

So my current idea is this:

I implement a "before insert" trigger on the table that looks at the new row. If the new row says "i want an auto-number", it does a select on some table in which I have the last assigned number for this customer/year, adds 1 to the number and puts it into the new row.
Then I implement an after insert trigger that updates the "last assigned" table. Maybe that can even be done in the before insert trigger.

the great thing about this is: I can meet all the requirements, and if the trigger fails, the whole transaction fails, so no gaps (in my theory). Maybe for this doing all in one trigger is the better idea - not sure about this.

The not so great thing: this is highly DBMS specific and out of the control of my application. And: I need to find out how to get this new number back into Glorp. I guess this will be done by refreshing the inserted object after the insert.

Any ideas? Better ways to do this?

Joachim




Am Donnerstag, 29. Januar 2015 08:25:33 UTC+1 schrieb Esteban A. Maringolo:
I have a similar use case for Invoices/Orders.

I rely on database sequences for this, but I'm not happy with my design either, because I get minor gaps from time to time. However I think this isn't ORM's responsibility, but is something that the application has to deal with.

The best solution I used years ago (not with GLORP) was to have a table of "counters", with row-level locking, so only one transaction was able to be processed at once. It was a bottleneck, but we didn't have that many req/sec, so in practice if there was a blocking, wasn't perceivable by the user. 

One wild guess using GLORP is to find a way to hook before the RowMap is created, just like GLORP does for primary keys that are sequence based.

I'm also open to see what others have to suggest here.

Regards,


Esteban A. Maringolo

2015-01-29 3:58 GMT-03:00 jtuchel <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="mxrkVMvL3EUJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">jtu...@...>:
Hi,

I'd like to use DB Sequences for new objects in certain tables. Think of invoice numbers or such.

The problem is, the numbers have to be consecutive, without holes. So the best (and maybe only really working) solution I can think of is using the Sequences of the DB server. The thing is, a new sequence may only be assigned during commit, because if I implement something else, there may be problems in a multi user environment.

The whole thing should work like auto-assigned primary keys. BUT: not every inserted row needs its own number. The idea is that you can set a checkmark in the apllication that says: auto-assign a number, and the system will assign the number on commit. In other cases, users will enter the number by hand (which is a new problem, because they may only enter numbers that have been assigned already...)

Ah, and one thing is also important and makes the whole affair spicy: The sequences aren't per Table, they are per User!

So here are my two most obvious questions:

1. What can I do to make Glorp request a new number during commit, but not for every insert?
2. How can I check numbers against that sequence in order to avoid new rows with sequences that were assigned already.

I guess there is no ready-made solution, but every idea counts...

Thanks for reading

Joachim

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="mxrkVMvL3EUJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">glorp-group...@googlegroups.com.
To post to this group, send email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="mxrkVMvL3EUJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">glorp...@....
Visit this group at <a href="http://groups.google.com/group/glorp-group" target="_blank" rel="nofollow" onmousedown="this.href='http://groups.google.com/group/glorp-group';return true;" onclick="this.href='http://groups.google.com/group/glorp-group';return true;">http://groups.google.com/group/glorp-group.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href='https://groups.google.com/d/optout';return true;" onclick="this.href='https://groups.google.com/d/optout';return true;">https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Use DB2 Sequences during commit

Alan Knight-2
First off, if you really can't have gaps at all your problem is very difficult. At any point up until the transaction really and truly commits, it might fail to commit. So you can't know until after it's happened if it works or not. There's also the corresponding problem of two users who write the same sequence number simultaneously. If you don't have a database constraint to forbid that, then it might happen. It sounds like that might be ok for you, though. And database sequences don't usually guarantee not to have gaps.

As far as doing it in Glorp, Glorp has sequence policy objects in the database platform and per column. This is necessary so you can have things using database sequences (which differ between databases significantly) or other mechanisms. So you should be able to plug in anything you want. And the sequences generally work such that if there's a value there already they won't do anything, so that part of it should be pretty easy.

What you want to do to get the number is an interesting question. You could just have a table with a row per user and the highest sequence number so far. That's probably one of the more efficient mechanisms. You could even use that like an optimistic lock value. Update invoicenumbers set next = 125 where user = 12345 and next = 124. That will fail if the next has been incremented somewhere else. But there are many other mechanisms you could use.


On Thu Jan 29 2015 at 5:45:23 AM jtuchel <[hidden email]> wrote:
Esteban,

I cannot really use DB sequences, but I wasn't aware of it earlier today. The number is on a per-user basis, so not globally unique.

I think I am now on the right track with using triggers (and maybe stored procedures). These will be relatively complex and outside of the control of my Glorp Application. That is the only thing I don't like about this solution

But I have no better idea than triggers, because

* the sequence is not a global sequence, the numbers start with an initial value for each customer each year...
* not every insert needs a new number, only inserts that don't come with a number or which come with a special attribute/switch (some boolean that says: I want to get an automatic number)

So my current idea is this:

I implement a "before insert" trigger on the table that looks at the new row. If the new row says "i want an auto-number", it does a select on some table in which I have the last assigned number for this customer/year, adds 1 to the number and puts it into the new row.
Then I implement an after insert trigger that updates the "last assigned" table. Maybe that can even be done in the before insert trigger.

the great thing about this is: I can meet all the requirements, and if the trigger fails, the whole transaction fails, so no gaps (in my theory). Maybe for this doing all in one trigger is the better idea - not sure about this.

The not so great thing: this is highly DBMS specific and out of the control of my application. And: I need to find out how to get this new number back into Glorp. I guess this will be done by refreshing the inserted object after the insert.

Any ideas? Better ways to do this?

Joachim




Am Donnerstag, 29. Januar 2015 08:25:33 UTC+1 schrieb Esteban A. Maringolo:
I have a similar use case for Invoices/Orders.

I rely on database sequences for this, but I'm not happy with my design either, because I get minor gaps from time to time. However I think this isn't ORM's responsibility, but is something that the application has to deal with.

The best solution I used years ago (not with GLORP) was to have a table of "counters", with row-level locking, so only one transaction was able to be processed at once. It was a bottleneck, but we didn't have that many req/sec, so in practice if there was a blocking, wasn't perceivable by the user. 

One wild guess using GLORP is to find a way to hook before the RowMap is created, just like GLORP does for primary keys that are sequence based.

I'm also open to see what others have to suggest here.

Regards,


Esteban A. Maringolo

2015-01-29 3:58 GMT-03:00 jtuchel <[hidden email]>:
Hi,

I'd like to use DB Sequences for new objects in certain tables. Think of invoice numbers or such.

The problem is, the numbers have to be consecutive, without holes. So the best (and maybe only really working) solution I can think of is using the Sequences of the DB server. The thing is, a new sequence may only be assigned during commit, because if I implement something else, there may be problems in a multi user environment.

The whole thing should work like auto-assigned primary keys. BUT: not every inserted row needs its own number. The idea is that you can set a checkmark in the apllication that says: auto-assign a number, and the system will assign the number on commit. In other cases, users will enter the number by hand (which is a new problem, because they may only enter numbers that have been assigned already...)

Ah, and one thing is also important and makes the whole affair spicy: The sequences aren't per Table, they are per User!

So here are my two most obvious questions:

1. What can I do to make Glorp request a new number during commit, but not for every insert?
2. How can I check numbers against that sequence in order to avoid new rows with sequences that were assigned already.

I guess there is no ready-made solution, but every idea counts...

Thanks for reading

Joachim

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to glorp-group...@googlegroups.com.
To post to this group, send email to [hidden email].

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Loading...