Locking in merge statement

Hi All,
I have a table which contains some status information.
Table1 (device varchar(200) primary key, NumberOfUsage int)

Now I have a store procedure in which I would like to insert new
devices, if they do not exists. Otherwise I just want to update the
NumberOfUsages. For this I use the merge statement.

Now I have a question to the locking. Is the table 'Table1' already
locked, when the ON condition is checked? I assume yes, because it is
a single statement.

I would like to prevent problems in multi-user scenario between
concurrent users (as described below with normal select/insert
statements).

e.g.: User1: select NumberofUsage from Table1 where device = 'A' (no
lock is here)
        User2: select NumberofUsage from Table1 where device = 'A' (no
lock is here)
        User1: Insert A because it does not exists (lock is here)
        User2: Insert A because it does not exists (lock is here)
Crash because a alreday exists.

Thanks
Marcel
0
schaf
9/10/2010 11:52:46 AM
sqlserver.server 1327 articles. 0 followers. Follow

2 Replies
1813 Views

Similar Articles

[PageSpeed] 39

>
> e.g.: User1: select NumberofUsage from Table1 where device = 'A' (no
> lock is here)
>        User2: select NumberofUsage from Table1 where device = 'A' (no
> lock is here)
>        User1: Insert A because it does not exists (lock is here)
>        User2: Insert A because it does not exists (lock is here)
> Crash because a alreday exists.

I assume the read and the mutation are in ONE transaction.
Using Isolation level serializable you are protected from double inserts.
With serializable, the reads will cause predicate locking, where the range 
indicated within the where clause is getting locked. Other users can not 
write in this range for the time the transaction is 'open'.

If the read is not within the transaction, or when using a lower level of 
isolation (snapshot isolation is lower as wel), you are not protected 
against double inserts. You could use constraints to prevent this happening. 
For example a unique constraint on the device, if all the devices are 
unique.

Ben Brugman


Do not assume that a single read statement can not cause a lock. (Even if 
not in a transaction).


>
> Thanks
> Marcel 


0
ben
9/10/2010 12:43:04 PM
> I assume the read and the mutation are in ONE transaction.
> Using Isolation level serializable you are protected from double inserts.
> With serializable, the reads will cause predicate locking, where the range
> indicated within the where clause is getting locked. Other users can not
> write in this range for the time the transaction is 'open'.
>
> If the read is not within the transaction, or when using a lower level of
> isolation (snapshot isolation is lower as wel), you are not protected
> against double inserts. You could use constraints to prevent this happening.
> For example a unique constraint on the device, if all the devices are
> unique.

Thanks.
The code above was just a pseudo piece of code to demonstrate possible
uniw constraints.
But I found a very nice description about my question. It's relly
worth to read.

http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx

Regards
Marcel
0
schaf
9/10/2010 1:00:01 PM
Reply:

Similar Artilces:

Merging two message stores
I have two message stores for Windows Mail on my hard drive. I am using one as my current store. The other one I had used a while ago and then abandoned to try other email processors. When I resumed using Mail I started anew. Don't ask me why. I now would like to import the messages from the old store into my current store. Can you suggest a way I might do that? Paul pcrable wrote: > I have two message stores for Windows Mail on my hard drive. I am using > one as my current store. The other one I had used a while ago and then > abandoned to try other email ...

I need advice on mail-merge mailing with Excel 2003 & Outlook 2003
I need to email about 6,000 contacts of mine in a few weeks, and have the following questions: 1) My email provider allows only 100 emails to be sent per hour. As I don't want to be flagged as a spammer, is there a Outlook script or a seperate program that would allow me to do a schedule that would take the total list and send 99 emails from it every hour until it's done. 2) These will not be a "generic mass-mailing", but one that I would actually have a person name go with each seperate email. My contact information (person name & email address) is stored in an E...

IF,Then statement?
Hi All I am trying to find a way to write and if/then statement. I have a column(A) with gas stations (MoBil, Citgo, Etc) next to it I have a column (B) of gas MPG. I want to average each type of gas. So I was thinking of adding a column (C) for each type of gas. If A#=C(header), THEN place B# in C#. I can then take and average the column. I just don't know how to write it? OR is there a simplier way to write it? Thanks Bill -- bhowe ------------------------------------------------------------------------ bhowe's Profile: http://www.excelforum.com/member.php?action=getinfo&user...

Locked File #2
I am using Microsoft Publisher on a computer that is on a network. I was working on a file and making changes to a file that was saved on the network. As I was working the computer froze up and shut down. Now I cannot get into that file. The error reads: The file is locked because it is currently being used by another program. Please close the other program and try opening or importing the file again. I cannot find where the file is open. I can't open the file from other computers on the network. The original file was on a different hard drive then the computer I was using to make the chan...

If statements??
I have a table of dates that looks like this: FY2010.Q1 FY2010.Q2 FY2010.Q3 FY2010.Q4 FY2010TOTAL 2009.Sep.30 2009.Dec.22 2010.Mar.31 2010.Jun.30 2010.Jun.30 9/30/2009 12/22/2009 3/31/2010 6/30/2010 12/22/2009 92 83 0 0 175 I would like to create a formula that looks at the current date and gives me the number of days in each quarter. When it comes to the quarter end, in this case FY2010.Q1, then give me the number of days in the quarter. Answer ...

how do I merge 2 cells in excel without loosing data
how to merge two colums? one contains text rows the other contains numeric data without loosing any of the data this means merging the data also thanks for your help Try Debra's nice coverage at her: http://www.contextures.com/xlCombine01.html She also provides some examples of how to format the numeric data part when it's combined with text under the "Formatting Examples" section - scroll down towards the end of the page -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "wimmat" <wimmat@discussions.micro...

Report Sorting/Grouping statement too long to edit
The too long to edit Statement is: =IIf([Rcd]<=36,(2*[Rcd]+5-((([Rcd]-1) Mod 12))), _ IIf([Rcd]>36,IIf(Int((([Rcd]-((([Rcd]-1) Mod _ 6)+1))/6)/2)= _ (([Rcd]-((([Rcd]-1) Mod 6)+1))/6)/2,(-2*[Rcd]+150)+ _ (((([Rcd]-37) Mod 6)*3)-9),((-2*[Rcd])+150)+ _ (((([Rcd]-37) Mod 6)*3)-15)),0)),"") The statement can be broken into two parts at the second IIf. But then I don't know how to take the output of the first statement and feed it into the second statement. Any suggestions will be appreciated. Chuck This looks like a "business rule" type expression. Create a sm...

Merging Scenarios
I created a file which I then sent to two people to create best and worst case scenarios for their input cells. All scenario names are unique – we’ve added our initials after the Best Case etc. I have merged the scenarios from the other two people into my file and created a Scenario Pivot Table Report. I am using the pivot table report because it enables me to see the combined impact of the different scenarios, which it does. However, the page fields are showing only the options of All and MyName. I changed the Author field in the file properties to indicate the other people’s n...

Can I merge two money files?
I am running Home & Business 2007. We tracked our finances from Jan 1-June 30 in one file and Jul 1-today in another. (The nature of the business change and its owners). So, now our accountant would like to combine the informaiton into one file. Can this be done without me manually entering each transaction? Any help is APPRECIATED!! Thanks. In microsoft.public.money, jrwillden wrote: >I am running Home & Business 2007. We tracked our finances from Jan 1-June 30 >in one file and Jul 1-today in another. (The nature of the business change >and its owners). So, now our ac...

Merging Labels #2
Hello, I'm back with the same question. How do you get labels to merge from a database on one sheet? Not individual names on one sheet each? The instructions I have received so far does not work: 1. Under setup page size, create a blank page that is the size of one label. 2. Under Page Setup, select the option "Change Copies per Sheet", Click on "Multiple Copies Per Sheet". I still get multiple labels on one sheet with one name???? >-----Original Message----- >Hello, I'm back with the same question. How do you get labels to merge from >a database...

Mail Merge Publications as E-mails in Publishher 2003
I don't know what I'm missing but I'm sure someone can set me straight. 1) I want to send a Publisher e-mail template to people in my Outlook contacts. I'm familiar with mail merging in Word. 2) When I go through the mail merging process in Publisher, I see no option to actually send it as an e-mail. 3) I end up with a merge that has individual publications but no option to send these as e-mails. 4) If I do a similiar process in Word, at some point after I select that I want an e-mail, I get an option following through with the e-mail procedure. 5) I have read several post...

Editing individual pages when using merge fields
So I am making a catalog of sorts. (Actually it is a Class Reunion book of classmates) I am merging data from a Excel database into the catalog. Each item (Class-member) is to have its own page I have set up a Publisher "form" where I have inserted the merge fields where I would normally want them displayed. My problem is some items in the database do not have all the fields filled in. The Class-members opted to not fill them in. Some will need more pictures inserted and text blocks will need to be moved to accommodate that. So, if I complete a merge and go to a page where I...

Lock an object to a slide
Can anyone tell me how to lock an object to a slide in PowerPoint? For example, if I'm inserting multiple objects, once I have the first one in the place I want it, can I tell PowerPoint not to let it move from that place? Thanks!! :) I'm turning on the ESP and guessing here because you're not telling us much! Maybe, just mayb,e Tools > Auto Correct options > Autoformat as You Type > UNTICK auto layout for inserted objects?? -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint_hints_and_tips_tutoria...

can I lock screen so only clicked links move slides in sldeshow
I have created question slides with Yes and No links to the next corresponding slides eg if answer yes link goes to slide 2 if answer No goes to slide 3 this happens during slide show presentation I want to be able to lock the rest of the slide so that only a link click can progress to the next slide because the process does not run in order if you just click anywhere on the screen to progress the slides On Jan 28, 5:19=A0pm, Workworry <Workwo...@discussions.microsoft.com> wrote: > I have created =A0question slides with Yes and No links to the next > corresponding ...

Data Validation
I am having problems with 7 Nested IF functions in data validation. What is the best way around formulas using more than 7 IF functions? How do you write an IF function in VBA where there are more than seven statements? Thanks Can you provide a more detailed explanation of what you're trying to do? There are many ways to get around a bunch of nested if statements. -- Biff Microsoft Excel MVP "CC" <coralie@ihug.co.nz> wrote in message news:9C695B13-7E9C-46E1-915B-F6E880D9AB0E@microsoft.com... >I am having problems with 7 Nested IF functions in data validation. &...

Why can I not lock a cell throught code?
I have some code, whereby I am trying to lock a cell. I keep getting the message: Runtime error '1004': Unable to set the Locked property of the Range class. I dont know why this is happening. Can anyone help? Here is a sample of my code: ------------------------------------------------------------------------------ Private Sub Worksheet_Change(ByVal Target As Range) CalculateDates End Sub Private Sub CalculateDates() 'Application.ActiveWorkbook.Worksheets("Sheet1").Active Application.ActiveWorkbook.Worksheets("Sheet1").Unprotect Application...

Visio 2002 locks up when using "History"
Whenever I'm in an Open File dialog in Visio and I click on "History" (in the Look In column), I get the following error: An unexpected error ocurred: Not enough memory available to complete this operation. Close other programs to make more memory available, and then try again." Of course, I exit out of all other apps and it's still stuck in the same error, can't dismiss the error dialog. Have to end task to get out of it. Fairly annoying. Windows XP and Visio 2002 Pro, oodles of memory (1 gig) and plenty of other resources. Any ideas? Thanks. ...

Compare and Merge
Is there a workaround for simulating Compare and Merge without having to Share the Workbook tj I'm new to excel, I don't know what 'compare and merge' does and how it would be applicable to my question I need to keep all the sheets together , the work book is saved under a clients name so all staff can find all the relavant details on that client in one place. ...

Joining IIF statements together
Hi I have the following two IIF statements which give the right results when used separately but I need to join them together so Access is assessing all conditions. I've tried multiple ways though come back with varied errors - from syntax, commas, too many arguments etc ... Is there actually a way I can join these? =IIf([JobSubStatus]="Awaiting Appointment Advice" And DCount([JobID],"QryInProgress","[JobID] = " & [JobID])=1,Now()-[datereceived],[JobStatusDate]-[datereceived]) =IIf(IsNull([JobSubStatus]),Now()-[datereceived],IIf([j...

Requirements to merge in a MS Word document
What are the requirements to have a Access database as a source of data to merge in a MS Word document? I have databases that worked fine, but after modifying the database, the query that was the source is not longer visible to Word. Maybe is because I added VBA functions to the query. I don't know. Word to show that was an error shows only two empty fields: M__ and M__1. Greetings from Paraguay. Claudio Bogado Pompa. Yes, you are correct, when using functions, then the queries can't be used directly by word. I have working sample that "gets around" this problem. Try the...

Mail merge type of function??
I'm trying to create invoices in Excel and need to be able to do something similar to a mail merge. Is there a way to do this in Excel? The customer requires the invoice to be in Excel and there are about 400 sites that I don't want to enter the data manually for each one. ...

Creating a customer accounts statement vis query?
Hi, i use access to create invoices for jobs that i do.. currently i have the following...Jobs database that includes a the cost for the job. I print an invoice from this information... The layout is basically the following..job.customerIDjob.descriptionjob.datejob.chargeI also record payments in a separate database called Payments because the payments are sometimes different than the charges...payment.customerIDpayment.datepayment.amountI would love to create a report that uses the data from both of these tables and generates a statement like so...Date Credit Debit1/2/07 �5...

Merge data by macro
Hi all, I got data in column A and B as show below. Row A B ......col 1 ID DATA ...headings 2 XY01 Record 3 Time 4 Left 5 XY02 Time 6 Right 7 XY03 System 8 Record 9 Time 10 Left I need macro which should merge column B data and put result in column C as shown below Row A C ......col 1 ID DATA ...headings 2 XY01 Record Time Left 3 4 5 XY02 Time Right 6 7 XY03 System Record Ti...

How to Produce a Mail Merged email with attachement
Hi, I am trying to send an email to people in my Outlook 2003 contacts list selected by category using mail merge. This works fine. However I now wish to add a single identical attachment to each merged email, but haven't found a way. Can anyone help please. Cheers Ian Outlook does not support that. You'd need to use a third party utility: http://www.slipstick.com/addins/mail.htm#massmail -- Russ Valentine [MVP-Outlook] "Ian Mitchell" <ianm@melbpc.org.au> wrote in message news:q4ctv1143qesihmb64g8dapfdv1ptiuae4@4ax.com... > Hi, > > I am trying to send ...

Using Excel data to create mail merge/report
I have an excel data file which has 4 columns - employee number, employee name, orgcode, and # per org I have entries for 1 to 14 employees in each orgcode, and the first entry has the total for that org in column D "# per org code", but the rest of the entries in that org code don't have an entry in the 4th column If it were a simple merge I would know what to do, but what I need ideally is to be able to merge into a letter the information for each org code - or at least to print out a page which lists an org code and the names in that orgcode Is this possible to do this? I&#...