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.