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
1478 Views

Similar Articles

[PageSpeed] 57

>
> 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:

Label Merge
I have a datasource created in excel. Included in the list are name, address1, address2, city, state, zip. The problem is when I import them into excel, if there are blanks for address2, I get a blank line on my labels. Is there any way the blank line can be eliminated on the label if there is not an address2. Thanks! Yes. You need to use an IF field that prints address2 if it exists, along with a line break. Since the line break will be included only if the address is printed, you will not get a blank line. The syntax of the field should be as follows: { IF { MERGEFIE...

e-mail merge through outlook
When I use e-mail merge from Excel to Word (2002 Office XP Editions), the line spacing changes from single-space to double-space and messes up the formatting of my e-mail letter. It is being sent through Outlook 2002 via my Pop account. Anybody know of a workaround or a fix besides changing to a plain text merge??? Please e-mail back with any ideas! Thanks in advance for your help! Eric ...

locking up
This might be more of a GDI question, but since I'm in MFC, I'll ask: Would BitBlt-ing to the screen repeatedly tend to lock up a program, after alt+tabbing to another application? If so, why? This seems to be happening to me, and I want to get to the bottom of the problem. What I have is either a fade loop or a screen-size redraw, both based on a timer and bitblt. Quite often, after I leave the program to go to another app, I cannot return and must "end task". "Steve Russell" <srussell@removethisinnernet.net> wrote in message news:%23YFL5Ks%23F...

Desktop Alert Locks Computer
Greetings, I am seeing a little quirk on some of my desktops. It only happens occasionally, but it happens to different models of PC. Not all the time, but once in awhile, I have users that complain of their PC freezing. The only remedy is to do a hard reboot. I have noticed in these instances, that the desktop mail alert ( the preview that pops up by the system clock when you get a new mail) is showing when it froze. If I go into Outlook and turn off the desktop alert, then the PC freezing stops. So, I know that the Desktop Alert is the cause of the problem. Has anyone else ever seen this? Th...

Lock formula to cell
I want a formula to lock to a cell so that when I add or delete a column the formula will always remain with say column H I know if the sheet is Password protected this will prevent this happening but that is no use when I have to unprotect the sheet to delete a column. Anyone got thoughts on this? regards Pat Hi Pat without using VBA you can't achieve this Frank Pat wrote: > I want a formula to lock to a cell so that when I add or delete a > column the formula will always remain with say column H > > I know if the sheet is Password protected this will prevent this >...

Copy and Paste a formula as Values in a fixed Range as a loop statement using VBA
I have 3 work sheets i.e. a "Summary" sheet, a "Phase1" and a "Phase2" sheet The Phase1 and Phase2 sheets are a result of certain parameters that are entered separately for each of the Phases that produces the results in them. All the 3 sheets have fixed ranges and they are A2:AC151, the dates are within the range C3:AC150 for each Phase. All the 3 sheets have the same structure something like this A B C D E SAP NO CostCenter Dec-03 Jan-04 Feb-04 422100 DTF-Office 60,000 186,000 174,000 422140 DTF-Office 40,000 124,000 116,000...

locked excel File ??
Hi . I am working with my colleges with one Excel file in a shared file system. Sometime - when I open the file, there is the message "locked from College" - but he is definetly out of office and shut down his PC. We are using Excel 2003 + Excel 2007. What can I do?? THnks for help Susan ...

Locked out of database
Not sure what happened but get a message saying "database has been converted from a prior version of Access by using DAO compactdatabase and is now in a partially converted state." It says I should be able to import tables into a new data base but when I try this it just gives the same message about converting and the data base just doesn't open. Please help because lots at stake, have to do a report based on these tables so am waiting with anticipation!!!x Hi Pete, Usually this means you are opening an earlier version of Access in a new version. Make a copy of the data...

Outllook2007 to open my Outlook2002 Win7 says files locked.
I'm following 2002 to 2007 instructions step by step. When I point Outlook to my ..pst file, I get access restriction messages. How do I make sure these files can be accessed by Outlook 2007? Thanks. Which exact steps are these? From your Outlook 2002 machine, copy the pst-file to your Outlook 2007 machine and place it in a convenient location such as My Documents\Outlook\ In Outlook 2007 you can then reconnect to the pst-file via; File-> Open-> Outlook Data File... For details see; http://www.howto-outlook.com/howto/backupandrestore.htm -- Robert Sparnaai...

excel locks up after selecting a cell #2
excel locks up after selecting a cell. When ever, I select a Cell, that will automatically selects all the cell and this freezes the entire computer. Can any body who would help me resolve this issue? Please help.... ...

Money "lock-out" solution-thanks Kurt
Kurt Thanks for the idea. It worked. I unistalled money 2002 and the reinstalled using custom mode...up and running again! Thank -you What happens to all your data when you do that? Do you still have your data? I tried reinstalling, but no luck. I hate to uninstall, since I would lose all the data, right? Would appreciate info. Thanks, Alice >-----Original Message----- >Kurt > >Thanks for the idea. It worked. I unistalled money 2002 >and the reinstalled using custom mode...up and running >again! Thank -you >. > Your data is in a file *.mny. This is ind...

merging workbooks #2
Is there a quick way to merge workbooks? Either by merging open workbooks or by merging them before they are opened? Thanks in advance and Happy Holidays. Lane --- Message posted from http://www.ExcelForum.com/ To get a real answer, you're going to have to be more specific. Does merge workbooks mean: 1. Copy all the sheets in several workbooks to new workbook (so it ends up with lots of sheets) 2. Copy the data from all the worksheets in several workbooks to a single worksheet in a new workbook Either way, are the workbooks to be merged in one folder? Is that folder dedicated ...

Lock autoshape to fixed position
Hello, Does anybody know if it is possible to keep an autoshape to a fixed position. I have a worksheet that has two panes, one is frozen i.e. a fixed header and scrollable rows. On the bottom of the page I want to put a button bar made of shape buttons. The problem is that when I scroll, the buttons scroll too! It would be nices if I can keep them in position. I know that is is possible to add a second window, but that does not look so well... Any ideas? Multiple fixed panes would be a great option! Change the alignment option to be absolute. Picture properties > Layout tab > Advan...

Joining/Merging Cells
I want to merge two cells into one and then, eliminate the 2d cell, e.g., Cell A Cell B Smith James Jones Fred CELL A now becomes Smith, James Jones, Fred I know about =Ax&", "&Bx [where x is the row number]; however, I do NOT want to keep column 2 after I complete the merger. However, if after I merge, and obtain the result I want, I don't know how to retain the result after I delete Column B. So, "adding" effectively adding the two columns together doesn't cut it. Thanks! Ann just select the merged data, Copy ...

Form locking up
I've completed my own Linked Table Manager wizard, so that the connections I create to SQL Server and Oracle database on my network don't require a DSN (DSN-less connections). This wizard exists in its own mda file, which I have added as a reference in several of my databases. In my test database, this wizard runs great. I've got a command button that calls a function (fnRefreshLinks) that exists in the wizard. It opens a form, refreshes a list of the linked tables, tests the link, and provides me with some options. When I run this same function from another database, it...

Where am I being locked out?!
This is an unusual problem and I'm hoping someone will have come across something similar before. Before I start I should state that I've hardly used Windows Server or Active Directory, so go easy on me if this is something obvious! I am having my account locked out on a daily basis. I know that this isn't to do with my machine as it happens even if I get my account unlocked and wait a day without turning my machine on I still can't log on the day after. I'm pretty sure it's a piece of software that I've installed somewhere that keeps trying to con...

Merge accounts and account type issues w Money 2006
I just upgraded from Money Small Business 2004 to 2006. I've never used passport with Money before. I do direct banking with my bank, not via their website. I'm having trouble now when I try to download a statement, Money says the bank doesn't recognize my accounts. It says this could be because of duplicate accounts. Yes, it has duplicated my two checking accounts. My old accounts (1 and 2) are all there, have all their transactions. Then there are duplicates of those accounts, 3 and 4 respectively. So I go to merge accounts, and it says I can't merge a personal...

Why not use this as a lock object in lock
Hello! According to the docs it says that lock (this) is a problem if the instance can be accessed publicly. Can somebody explain what can happen if I have a lock section and use this as the lock object like this. lock(this) { } //Tony On 2010-06-20 17:09, Tony Johansson wrote: > According to the docs it says that lock (this) is a problem if the instance > can be accessed publicly. > Can somebody explain what can happen if I have a lock section and use this > as the lock object like this. > lock(this) > { > > } > http://haacked.com/ar...

"Computer Locked" dialog does not show up
I have windows XP Pro (version 2002) SP3. When I lock my computer, I do not see the "Computer Locked . . . " dialog. Instead, I only see the "Unlock Computer" dialog immediately upon locking. I am concerned about not pressing Ctrl+Atl+Del before entering my password to unlock. Is there a setting that I can change (I have extensive knowledge of windows registry and system tools, but I have not been able to find an option to "Show only unlock screen". ...

locks and more locks
Hello. This is about a SQL Server 2005 back end and Access 2007 front end. There is a front end on each user's machine. This has to do with the locking (or blocking as some call it) issue. Connections from the front end remain open once the user opens the front end until the front end closes, if the user closes the front end at all and not everyone closes the front end when done. There can be approximately 100 (+/-) people using the front end during the day. Some may actually be transmitting data (transactions) and some may not. But my thought is if the connection is a...

Catalog Merge
I am trying to create a catalog in Publisher 2003. I have a catalog that is divided into categories. When the items for a particular category have depleted, I need a the next category of items to start on the next page. Can this be done Chuck.... After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Charles Scally <anonymous@discussions.microsoft.com>... > I am trying to create a catalog in Publisher 2003. I have a catalog > that is divided into categories. When the items for a particular > category have depleted, I need a the next category of items to ...

Keyboard locked
Hello all My keyboard seems to be locked and I can't type anthing. I have a Dell Inspiron 1501(three yrs old). I pressed the fn key with the number lock key and that didn't work. Can someone please help. Thx Dolores Try replacing the keyboard and/or rebooting your computer. "dolores01" <u59578@uwe> wrote in message news:a6fd3466ee816@uwe... > Hello all > My keyboard seems to be locked and I can't type anthing. I have a Dell > Inspiron 1501(three yrs old). I pressed the fn key with the number lock > key > and that didn't work. Can...

Word wrap and merged cells
Hi, I have a cell (A1) formatted to enable word wrap. When I've typed more text than the default column width allows, I can double click on the bottom edge of the row to automatically re-size my row for multiple lines. But if my cell is a merged cell (A1 and A2), double clicking on the bottom edge of the row no longer automatically re- sizes my row. Instead, it reverts it back to the row height of a single line of text. Does anyone know why this is happening? Thanks! Chris, this is a problem, Bug?, in excel with merged cells -- Paul B Always backup your data before trying s...

Using OR in IIF statement
Hi I want to use OR in IIF statement as a condition in the query 'Quarterly' OR IIf([Sampler_loc].[monthID]=Month([tblnewone].[BDate]) AND WeekOfMonth2([tblnewone].[BDate])=1, 'monthly' OR 'Fortnightly','Weekly'))) But it gives me error at 'monthly' or 'Fortnightly' Actually I need 'quarterly' or 'monthly' or 'fortnightly' for same month and week 1 and 'quarterly' or 'weekly' for remaining weeks and same month. Can some provide solution to this. thanks Put ( ) around 'monthly' OR 'Fort...

Determine which mail is locking queue
Came in to work yesterday and exchange was not delivering mail... bummer. I determined the queue was all backed up. Message tracking showed (*from memory) that the messages were being submitted to the advanced queue but would continue no further. I the reboot the server, stop the smtp server when it's back up move and then move all messages in the queue folder to a new folder and start the smtp server. All is well. I stop smtp server, place some of the messages I moved back into the queue folder and start the server again. So on and so forth until messages stop being delivered aga...