AUTO INCREMENT in Starcounter

Question

For example considering the Sample App at http://starcounter.io/tutorials/1-introduction-to-part-1/ InvoiceDemo

Following Database objects are declared

using Starcounter;

[Database]
public class Invoice {
    public int InvoiceNo;
    public string Name;
    public decimal Total {
        get {
            return Db.SQL<decimal>(@"SELECT sum(r.Total)
                                     FROM InvoiceRow r
                                     WHERE r.Invoice = ?",
                                     this).First;
        }
    }
    public QueryResultRows<InvoiceRow> Items {
        get {
            return Db.SQL<InvoiceRow>(@"SELECT r
                                       FROM InvoiceRow r
                                       WHERE r.Invoice = ?",
                                       this);
        }
    }

    public Invoice() {
        new InvoiceRow() {
            Invoice = this
        };
    }
}

[Database]
public class InvoiceRow {
    public Invoice Invoice;
    public string Description;
    public int Quantity;
    public decimal Price;
    public decimal Total {
        get {
            return Quantity * Price;
        }
    }

    public InvoiceRow() {
        Quantity = 1;
    }
}

If I want to make sure I know the order of Invoice Rows that has been added, I would in standard SQL DB just use an Auto Increment ID. What is the best practice for this in Starcounter?

Answer 1

Starcounter does not provide auto-incremented user ids and I doubt it will due to different reasons. For example:

1. Such functionality requires centralised counter and serialisation of requests to it. This is a performance issue.
2. Serialisation order depends on internal implementation and not necessary meet expectations of an application developer.

An example of when it is no obvious what order means and expected by application:

I need to be sure I can sort the rows in order by creation

Creation time can mean:

1. When record is inserted and committed.
2. When record is allocated in memory.
3. When user submitted a request, which leads to create the record.
4. When a handler was invoked, which creates the record.

Since records can be added simultaneously in different transactions, the order of them can differ significantly between the options. Only application developer knows which order is meant and how it will affect user experience (UX).

Starcounter allocates counter-based object identity, so it is similar to auto-increment id, but for all objects/records in the database. However, it is important to remember:

1. There are scenarios when group of ids can be pre-allocated, so the incremental order becomes broken. For example, replicator scenario.
2. It is not necessary that the order is the same as the developer expects, which is discussed above.
3. Slight improvements in the allocator implementation or in the full-stack framework can produce different order than before in some occasions. This can break UX.
4. The allocation scheme of the object identity is very likely to be changed in future for performance reason. Thus proceedings of object identity number will not correspond to proceedings in time.

Because of the last point using Starcounter's object identity for ordering record by the creation time is not good idea.

I guess auto-incremented ids in other databases have similar problems. So using them is not so safe if UX should be preserved or significant changes might be met in future (and usually cannot be foreseen).

My opinion is that the order is application specific and should be implemented in the application logic. I see several ways:

1. Use timestamp field to identify time ordering and the object identity for ordering records with the same timestamp in deterministic way, since it is guaranteed that object identity is always the same for the same record.
2. [EDIT] Have a shared counter object outside database. You will need to implement serialisation to it through locks or else. For example, __sync_bool_compare_and_swap or Interlocked.Increment can be used, which is atomic operation and no locks required, but it is cache unfriendly. Note that it is important to access it outside re-triable transaction scope, i.e., with snapshot isolation. However, using it in such transaction will still work, but counter will be increased on each retry.
3. Use database object to store the counter. The main problem is that it will be too many conflicts on high load due to optimistic concurrency.
4. [EDIT] As suggested by @warpech: obtain highest existing id, e.g., SELECT MAX(InvoiceRowNo) FROM InvoiceRow, and increment it. Add unique constraint by creating unique index to avoid the same ids in the case of concurrency. In the case of concurrent increment conflict between transactions will happen and one of the transactions will be retried, while the other will succeed. Note that on high load the conflict might happen often and some unlucky request will be delayed considerably or will never pass thru.