产生的序列号中的多用户SaaS应用程序 [英] Generating sequential numbers in multi-user saas application

问题描述

人们如何产生auto_incrementing整数特定用户在一个典型的SaaS应用程序？

How do people generate auto_incrementing integers for a particular user in a typical saas application?

例如，对于所有的发票为特定用户发票号码应auto_incrementing和从1.导轨id字段开始可以在这种情况下，不能使用，因为它是在所有的用户共享。

For example, the invoice numbers for all the invoices for a particular user should be auto_incrementing and start from 1. The rails id field can't be used in this case, as it's shared amongst all the users.

关闭我的头顶，我可以指望所有的用户有发票，然后加1，但没有人知道任何更好的解决方案？

Off the top of my head, I could count all the invoices a user has, and then add 1, but does anyone know of any better solution?

推荐答案

对于任何关系数据库的典型解决方案也能像

Typical solution for any relation database could be a table like

user_invoice_numbers (user_id int primary key clustered, last_id int)

和存储过程或类似

update user_invoice_numbers set last_id = last_id + 1 where user_id = @user_id
select last_id from user_invoice_numbers where user_id = @user_id

这会为用户的工作（如果每个用户有几个同时运行的事务），但公司来自不同用户的交易将无法正常工作（例如，当你需要companies_invoice_numbers），因为同一个公司内部可能阻止对方会有在这个表中的性能瓶颈。

It will work for users (if each user has a few simultaneously running transactions) but will not work for companies (for example when you need companies_invoice_numbers) because transactions from different users inside the same company may block each other and there will be a performance bottleneck in this table.

最重要的功能要求，你应该检查是您的系统是否允许有空白发票编号或不。当您使用标准的auto_increment，你允许的差距，因为在大多数数据库中，我知道，当你回滚事务，递增的数字将不会被回滚。考虑到这一点，你可以用下面的指导方针之一提高性能

The most important functional requirement you should check is whether your system is allowed to have gaps in invoice numbering or not. When you use standard auto_increment, you allow gaps, because in most database I know, when you rollback transaction, the incremented number will not be rolled back. Having this in mind, you can improve performance using one of the following guidelines

1）排除您使用从长期运行的事务获取新号码的程序。让我们假设插入发票的过程是一个长期运行的事务复杂的服务器机端逻辑。在这种情况下，你首先获得一个新的ID，然后在单独的事务中插入新的发票。如果最后的事务将被回滚，自动编号将不会减少。但user_invoice_numbers不会被锁定了很长一段时间，所以很多用户同时可以插入发票的同时

1) Exclude the procedure that you use for getting new numbers from the long running transactions. Let's suppose that insert into invoice procedure is a long running transaction with complex server-side logic. In this case you first acquire a new id , and then, in separate transaction insert new invoice. If last transaction will be rolled back, auto-number will not decrease. But user_invoice_numbers will not be locked for long time, so a lot of simultaneous users could insert invoices at the same time

2）的不要使用传统的事务数据库来存储数据与去年ID为每个用户。：当你需要保持简单的键列表和值也有很多小而快速的数据库引擎能做到这一点为你工作。的键/值数据库列表。也许 memcached的是最流行的。在过去，我看到的项目中，其中使用Windows注册表，甚至是文件系统中实现简单的键/值存储区。有一个目录下每个文件名是关键，每个文件中是最后一个ID。而这种粗糙的解决方案仍优于使用SQL表，因为锁被发出，很快释放，并没有涉及到的交易范围。

2) Do not use a traditional transactional database to store the data with last id for each user. When you need to maintain simple list of keys and values there are lot of small but fast database engines that can do that work for you. List of Key/Value databases. Probably memcached is the most popular. In the past, I saw the projects where simple key/value storages where implemented using Windows Registry or even a file system. There was a directory where each file name was the key and inside each file was the last id. And this rough solution was still better then using SQL table, because locks were issued and released very quickly and were not involved into transaction scope.

嗯，如果我提出的优化似乎是过于复杂的项目，忘了现在这样，直到你实际上会遇到性能问题。在大多数项目中简单的方法，一个额外的表将快下班pretty的。

Well, if my proposal for the optimization seems to be overcomplicated for your project, forget about this now, until you will actually run into performance issues. In most projects simple method with an additional table will work pretty fast.

这篇关于产生的序列号中的多用户SaaS应用程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！