SERIAL-like INT column

Question

I have an app where depending on the type of transaction being added or updated, the ticket number may or may not increment. I can't use a SERIAL datatype for ticket number because it would increment regardless of the transaction type, so I defined ticket number as an INT. So in a multi-user environment if user A is adding or updating a transaction and user B is also doing the same, I test for tran type and if next ticket number is required, then

LET ticket = (SELECT MAX(ticket) [WITH ADDLOCK or UPDLOCK?] FROM transactions) + 1

However this has to be done exactly when the row is being committed or troubles will begin. Can you think of a better way of doing this with: Informix, Oracle, MySQL, SQL-Server, 4Js/Genero or other RDBMS? This is one main factor which will determine what RDBMS I'm going to re-write my app in.

Answer 1

From your tags it's hard to tell what database you are using.

For SQL Server (since it's listed) I suggest

ticket_num = (SELECT MAX(ticket_number) FROM transactions with (updlock)) + 1

Answer 2

With the Informix DBMS, the SERIAL column will not change after it is inserted; indeed, you cannot update a SERIAL value at all. You can insert a new one with either 0 as the value - in which case a new value is generated - or you can insert some other value. If the other value already exists and there is a unique constraint, that will fail; if it does not exist, or if there is no unique constraint on the serial column, then it will succeed. If the value inserted is larger than the largest value previously inserted, then the next number to be inserted will be one larger again. If the number inserted is smaller, or negative, then there is no effect on the next number.

So, you could do your update without changing the value - no problem. If you need to change the number, you will have to do a delete and insert (or insert and delete), where the insert has a zero in it. If you prefer consistency and you use transactions, you could always delete, and then (re)insert the row with the same number or with a zero to trigger a new number. This assume you have a programming language running the SQL; I don't think you can tweak ISQL and Perform to do that automatically.

So, at this point, I don't see the problem on Informix.

With the appropriate version of IDS (anything that is supported), you can use SEQUENCE to control the values inserted too. This is based on the Oracle syntax and concept; DB2 also supports this. Other DBMS have other equivalent (but different) mechanisms for handling the auto-generated numbers.

Answer 3

That's what sequences were created for and which is supported by most databases (MySQL and SQL Server being the only ones that do not have sequences - not 100% sure about Informix though)

Any algorithm that relies on the SELECT MAX(id) anti-pattern is either dead-slow in a multi-user environment or will simply not work correctly in a multi-user environment.

If you need to support MySQL and SQL Server as well, I'd recommend to use the "native" "auto increment" type in each database (serial for PostgreSQL, auto_increment for MySQL, identity, for SQL Server, sequence + trigger in Oracle and so on) and let the driver return the generated ID value

In JDBC there is a getGeneratedKeys() method and I'm sure other interfaces have something similar.