?
Solved

Dealing with Record Locking issues with SQL

Posted on 2008-06-13
3
Medium Priority
?
744 Views
Last Modified: 2008-06-23
My company mainly works in the iSeries environment with RPG programs.  In the RPG programs we have record level access of our files meaning when a record is read it is locked and cannot be accessed by another program trying to update it.  

We have started to make the jump from RPG to C# and ASP.Net applications, still using the iSeries as the database.  As far as I can tell there is no record locking when using SQL in these applications.  I am using iDB2 for ADO.net for a connection to the iSeries.  

Could someone please let me know how you handle record locking when using SQL?  In specific, how do you keep two people from not trying to update the same record at the same time?  I'm interested in ways to do this using both the iSeries and MS SQL server.

Thanks
0
Comment
Question by:weimha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 21778263
sql server has different types of locks -- (row locks, table locks, database locks, etc.  see BOL).  
sql handles locking automatically, but it can be customized.
i believe in your case you may want to use table-level, or even row-level locking hints.
(see 'Hints' in Books Online)

for example, if you were going to do an update, you would do something like this:

update tablename with (rowlock)
set whatever........

0
 
LVL 17

Assisted Solution

by:dbaSQL
dbaSQL earned 800 total points
ID: 21778312
also, you may wish to review setting the transaction isolation level.  again, see BOL, but sql has different transaction isolation levels, which handle the locking

SERIALIZABLE:
Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1200 total points
ID: 21778622
- do those operations in a single transaction
- change the isolation level to serializable (not advised )
- add a 'timestamp' column and read that value before doing the update
  and during the update pass that value in the where condition, the timestamp columns will updated automatically, whenever a row is modified

Aneesh
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question