How do I lock a set of items in SQL?

I'm basically new to SQL and the locking is confusing.

The case I'm puzzling about now involves how to handle editing/开发者_StackOverflowupdating of an item that is described by a group of records. When it's time to write back the changes there might be more or fewer records than the original.

(Also, if there's any good reference on SQL locking I would like to know about it. I haven't found anything.)

Re: The comments:

I'm currently working with SQL Server but I'm hoping to minimize any dependencies on a particular database.

I understand about transactions but I don't see how to apply them in this particular case because records may be created or deleted in the process.

Do I just lock the parent item during save and not worry about the records that comprise it?

---

It is very unlikely that you need to worry about managing the database locking manually. Maybe you could say a bit more about what you are trying to do - possibly with some code - and we could point you in the right direction. If you want this kind of help, let us know what database you're using, as well.

If you're just looking for a place to start reading, I recommend one or all of these books:

• SQL Queries for Mere Mortals (http://www.amazon.com/dp/0321444434/)
• Database Design for Mere Mortals (http://www.amazon.com/dp/0201752840/)
• Learning SQL (http://www.amazon.com/dp/0596520832/)
• SQL Cookbook (http://www.amazon.com/dp/0596009763/)

There are plenty of good online resources, as well, which I can point you to once I understand what you really need. FWIW, I caution SQL beginners against googling to answer their SQL and database design questions - for the same reason I caution against using google to perform self-diagnosis of possible medical issues; you will likely learn from the experience, but you'll also almost certainly be led down many unnecessary side alleys.

Good luck!

---

Make sure that auto commit is turned off and use select for update. The syntax is db specific.Here is an example:

select * from emp where id = 2 for update

---

Your question is not 100% clear on what you are trying to do.

If you need to write information to more than table, use a Transaction.

Updating a group of records in one table can be written as a single UPDATE statement and this is atomic.