I have a stored procedure that manipulates an integer table field (which represents a sequence number) based on some criteria - the criteria can reset the field back to zero. In a multiuser environment there is a possibility that the field may be referenced by 1 user before it is updated by another user so I would like to prevent this by limiting the stored procedure to only run for 1 user at a time. Is there a way to do this within my stored procedure?
我有一个存储过程，它根据一些条件操作一个整数表字段(表示一个序列号)——这些条件可以将字段重置为零。在多用户环境中，在另一个用户更新字段之前，有可能有一个用户引用该字段，因此我希望通过限制存储过程每次只运行一个用户来防止这种情况。在我的存储过程中有这样的方法吗?
1 个解决方案
#1
7
If you wrap your statements inside a transaction, they will all be performed atomically. You may need to increase the transaction isolation level depending on your needs, though.
如果将语句封装在事务中，那么它们都将以原子方式执行。但是，您可能需要根据需要增加事务隔离级别。
For example, if you don't want anyone else reading or writing to a particular table while you execute a bunch of statements, this statement at the top will make that happen:
例如，如果你在执行一系列语句时不希望其他人读或写某个特定的表，那么顶部的这个语句将会实现:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
I don't recommend escalating to this level unless absolutely necessary, though, because it basically breaks the concurrency benefits.
我不建议升级到这个级别，除非绝对必要，因为它基本上破坏了并发性的好处。
Instead, consider using something lower or reworking your logic to remove the need of the critical section.
相反，可以考虑使用较低的东西，或者重新调整逻辑，以消除关键部分的需要。
#1
7
If you wrap your statements inside a transaction, they will all be performed atomically. You may need to increase the transaction isolation level depending on your needs, though.
如果将语句封装在事务中，那么它们都将以原子方式执行。但是，您可能需要根据需要增加事务隔离级别。
For example, if you don't want anyone else reading or writing to a particular table while you execute a bunch of statements, this statement at the top will make that happen:
例如，如果你在执行一系列语句时不希望其他人读或写某个特定的表，那么顶部的这个语句将会实现:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
I don't recommend escalating to this level unless absolutely necessary, though, because it basically breaks the concurrency benefits.
我不建议升级到这个级别，除非绝对必要，因为它基本上破坏了并发性的好处。
Instead, consider using something lower or reworking your logic to remove the need of the critical section.
相反，可以考虑使用较低的东西，或者重新调整逻辑，以消除关键部分的需要。