What is a lock and what are its causes?

Asked

Viewed 7,324 times

6

I often come across the term: Table x is with lock. What it means and what its causes?

sql terminology lock

2 answers

3


The SGDB regulates itself to the right to manipulate the tables/ records according to the queue of operations, that is, it performs locks to ensure the accomplishment of the pending operations (no comitadas).

Locks Row and lock table:

  • Row Locks
    Prevents any operation in the registry until another operation (update for example) is completed.
    SGDB will not allow a reading on this(s) line(s) not to display outdated data.

  • Table Locks
    Prevents any change, including a simple query to be made in the table. Some operation does not comity is still in process for another session (or even in the same - separated by GO)

Several systems freeze situations occur because of these Locks;

It is possible to force a query to ignore this flag: 

select * from teste with (nolock)

2

In a database, when we perform some DML (update, Insert, delete) operation we are locking, that is, we are blocking certain object. This prevents another operation from making a change to the table or record ("row") we are manipulating.

The lock is something natural, it exists and normally we do not notice it in normal operations. However, when conflicting, large or unfinished operations are performed, we may have many objects located there. To simplify let’s imagine the following scenario:

Zezinho did an update to the users table, but did not commit (instruction indicating end of transaction), IE, he blocked one or more records, if a given application tries to manipulate these locked records, will be waiting for reply, which may result in a locking of this application.

It is valid to mention that there is a record lock and table.

The above scenario is gross, however, I believe that superficially serve to simplify the understanding.

I believe that the following article is enlightening on the subject: http://www.devmedia.com.br/oracle-locks-parte-i/313

Browser other questions tagged sql terminology lock

You are not signed in. Login or sign up in order to post.