如何确定实际的数据库行插入顺序? [英] How can I determine the actual database row insertion order?

问题描述

我有一个多线程进程，该进程将多个记录插入到一​​个表中.插入是在存储过程中执行的，生成的序列INTO是一个变量，该变量随后在INSERT内部使用.

I have a multithreaded process which inserts several records into a single table. The inserts are performed in a stored procedure, with the sequence being generated INTO a variable, and that variable is later used inside of an INSERT.

鉴于我没有在INSERT本身内部执行mysequence.nextval，这使我认为两个并发进程可以按一个顺序抓取序列，然后以相反的顺序进行插入.如果是这种情况，那么序列号将无法反映出真正的插入顺序.

Given that I'm not doing mysequence.nextval inside the INSERT itself, it makes me think that it is possible for two concurrent processes to grab a sequence in one order, then do the inserts in the reverse order. If this is the case, then the sequence numbers will not reflect the true order of insertion.

我还在每个插入的DATE列中记录了sysdate，但是我注意到通常两个记录的日期匹配，因此我需要按顺序号进行排序以打破平局.但是鉴于先前的问题，这似乎并不能保证实际的插入顺序.

I also record the sysdate in a DATE column for each of my inserts, but I've noticed that often times the dates for two records match and I need to sort by the sequence number to break the tie. But given the previous issue, this doesn't seem to guarantee the actual insert order.

如何确定插入数据库的绝对顺序?

How can I determine the absolute order of insertion into the database?

推荐答案

DATE数据类型仅使用秒，而TIMESTAMP则使用毫秒.这样可以解决问题吗?

DATE datatypes only go to seconds, whereas TIMESTAMP goes to milliseconds. Would that address the problem?

根据Oracle文档:

According to Oracle's docs:

时间戳:年，月和日的值 日期，小时，分钟和 时间的第二个值，其中 fractional_seconds_precision是 小数位数 第二日期时间字段的一部分. 接受的值 fractional_seconds_precision为0至 9.默认值为6.默认格式由 NLS_DATE_FORMAT参数或 由NLS_TERRITORY隐式地 范围.大小从7到 11个字节，取决于精度. 此数据类型包含日期时间 字段YEAR，MONTH，DAY，HOUR，MINUTE， 第二.它包含分数 秒，但没有时区.

TIMESTAMP: Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It contains fractional seconds but does not have a time zone.

date不能，

DATE:有效日期范围从1月1日开始 公元前4712年至公元9999年12月31日.这 确定默认格式 由NLS_DATE_FORMAT明确 参数或由 NLS_TERRITORY参数.大小是 固定为7个字节.该数据类型 包含日期时间字段YEAR， MONTH，DAY，HOUR，MINUTE和SECOND. 它没有小数秒或 时区.

DATE: Valid date range from January 1, 4712 BC to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.

当然，我不确定在写入记录时为何如此重要，但这是一种可以解决您的问题的方式.

Of course, having said that, I am not sure why it matters when the records were written, but that is a way that might solve your problem.

这篇关于如何确定实际的数据库行插入顺序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！