StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTSQL mutual exclusive access in a stored procedure
primarykey
Id
6342732
data
AcceptedAnswerId
6343216
AnswerCount
3
ClosedDate
CommentCount
5
CommunityOwnedDate
CreationDate
2011-06-14T11:23:45.833
FavoriteCount
3
LastActivityDate
2011-06-14T13:27:29.533
LastEditDate
2011-06-14T11:34:49.270
LastEditorUserId
307976
OwnerUserId
307976
ParentId
0
PostTypeId
1
Score
3
ViewCount
5872
LastEditorDisplayName
text
Body
Several web servers access a SQL Server to get a numeric code, when this code doesn't exist, it has to be autogenerated by the SQL Server. I need to ensure that even if two concurrent calls come in and the code doesn't exist, only one code is created and both calls return the same code. So I have to do something like this: <pre><code>begin lock if code exists return code else generate code return code end lock </code></pre> I've been reading a little about isolation levels and table locking, but I have a terrible mess with all that. First I thought that a SERIALIZABLE isolation level is what I need, but apparently it's not. So, what would you do to accomplish a "lock" in TSQL? Thanks a lot. UPDATE: I got this error when I try to set the serializable level using <a href="http://msdn.microsoft.com/en-us/library/aa213026%28v=sql.80%29.aspx" rel="nofollow">this</a> as example: <pre><code>SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE get_code AS BEGIN SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRANSACTION select code from codes where granted is null; END GO </code></pre> <blockquote> Msg 1018, Level 15, State 1, Procedure get_code, Line 4 Incorrect syntax near 'SERIALIZABLE'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax. Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'END'. </blockquote> What does it means?
Tags
<sql-server><multithreading><sql-server-2005><synchronization><locking>
Title
TSQL mutual exclusive access in a stored procedure
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USvtortola
UserOwnerUserId
1. USvtortola
plurals
PostLinksPostIdRelatedPostId
1. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
2. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
3. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
PostLinksRelatedPostIdPostId
1. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
3. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POTSQL mutual exclusive access in a stored procedure
 UserUserId
 USTao
 VoteTypeVoteTypeId
 VTFavorite
2. VO
 singulars
 PostPostId
 POTSQL mutual exclusive access in a stored procedure
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 POTSQL mutual exclusive access in a stored procedure
 UserUserId
 USDamir Sudarevic
 VoteTypeVoteTypeId
 VTFavorite
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.