Question:
Our system is experiencing several lock conflicts. We’ve got a consultantlooking at the details, but I was hoping you could explain what’s going onbehind the scenes when records are locked.
Answer:
One of the many potential problem areas handled by a DBMS is concurrencycontrol: making sure that as many users as possible have access to as muchdata as possible without sacrificing the integrity [accuracy] of the data. Themost common means of ensuring integrity is the use of locks. Each DBMSimplements locking in its own way, but there are four isolation levels that aredefined by the SQL-92 standard. You, or your consultant, will have to decidethe appropriate isolation level at either the application or transaction level.The strictest isolation level (Serializable) guarantees the best data integrity,but also has the least concurrency; that is, users will get locked out more often.On the other end of the scale, the Read Uncommitted isolation level providesthe greatest concurrency, but can sacrifice data integrity if used inappropriately.When you’re experiencing lock conflicts on your system, the isolation levelcould be one of the problems. Sometimes the solution is to adjust yourapplication programs to reflect an understanding of the locking scheme ofyour particular DBMS.
