Mstdlib-1.24.0
Functions
SQL Helpers
SQL Subsystem

Functions

M_int64 M_sql_gen_timerand_id (M_sql_connpool_t *pool, size_t max_len)
 
M_uint64 M_sql_rollback_delay_ms (M_sql_connpool_t *pool)
 

Detailed Description

SQL Helpers for various situations

Function Documentation

◆ M_sql_gen_timerand_id()

M_int64 M_sql_gen_timerand_id ( M_sql_connpool_t *  pool,
size_t  max_len 
)

Generate a time-based + random unique id suitable for primary key use rather than using an auto-increment column.

It is not recommended to use auto-increment columns for portability reasons, therefore a suitable unique id needs to be chosen that has a low probability of conflict. There is no guarantee this key is unique, so integrators should handle conflicts by regenerating the key and re-attempting the operation (even though this may be an extremely low probability).

The generated key is a combination of the current timestamp in UTC and a random suffix. The reason for a timestamp prefix is some databases cannot handle purely random numbers as they cause index splits that cause exponential slowdown as the number of rows increase. This can be observed with MySQL in particular.

Where possible, a 64bit (signed) column should be used for the unique id, which has a maximum length of 18 digits where all digits can contain any value. A 32bit signed integer has a maximum of 9 digits where all digits can contain any value. 32bit integers are strongly discouraged as the possibility for conflict is much higher and would limit the total number of possible rows considerably (max 99,000 rows per day, but conflicts will be highly probable over a couple of thousand rows per day).

The current formats based on length are listed below:

  • 17-18+ digits : YYYJJJSSSSS{6-7R}
  • 16 digits : YYJJJSSSSS{6R}
  • 14-15 digits : YJJJSSSSS{5-6R}
  • 13 digits : YJJJSSSS{6R}
  • 11-12 digits : YJJJSS{6-7R}
  • 9-10 digits : YJJJ{5-6R}
  • <9 : {1-8R}

Where:

  • Y = last digit of year
  • YY = last 2 digits of year
  • YYY = last 3 digits of year
  • JJJ = Julian day of year (0-365)
  • SSSSS = Second of day (0-86399)
  • SSSS = Second of day divided by 10 (0-8639) (more fine grained than alternative of HHMM 0-2359)
  • SS = Second of day divided by 1000 (0-86) (more fine grained than alternative of HH 0-23)
  • {#R},{#-#R} = number of random digits
Note
A time prefix is used solely for the purpose of reducing database load by making the values as incremental as possible, while still having a random portion to avoid conflicts. These are not meant to be human-interpretable numbers, and formats may change in the future. These should essentially appear completely random to a human.
Parameters
[in]poolInitialized pool object
[in]max_lenLength of unique id to generate in digits. Valid range is 9-18. May return fewer digits only when the time prefix begins with zero.
Returns
64bit signed integer representation of usable unique id, or 0 on misuse.

◆ M_sql_rollback_delay_ms()

M_uint64 M_sql_rollback_delay_ms ( M_sql_connpool_t *  pool)

Random delay to use for a rollback to assist in preventing continual deadlocks and rollbacks.

A random delay is returned in milliseconds that can be used when a rollback condition is necessary to help break deadlock loops.

Parameters
[in]poolInitialized pool object
Returns
delay to use in milliseconds.