Correct Way to Take a Exclusive Lock

Correct way to take a exclusive lock

If you are only worried about other readers, then you shouldn't need exclusive locks, the pattern

Begin Transaction

  Make Data Inconsistent

  Make Data Consistent

Commit Transaction

Should be fine. The only sessions who will see inconsistent data are those that use nolock or Read Uncommitted, or those that expect to make multiple consistent reads without using Repeatable Rows or Serializable.

In answer to the question, the correct way to take an exclusive lock, in my opinion, is to arrange things so the engine does it for you.

Exclusive lock a table for more than one query

I wouldn't lock the table at all, no need for. I think. When I have 500 tickets, I would create 500 records (tickets). When someone buys a numbers of tickets, you have to update these tickets as SOLD. Use a SELECT FOR UPDATE statement in combination with SKIP LOCKED to get the number of tickets you need and then UPDATE these selected records. Multiple customers can buy tickets at the same time, without issues.

The only thing left, is what to do when someone wants to buy 10 tickets when you don't have 10 tickets anymore.

Exclusive Lock (Row Level) - MySql need an example

START TRANSACTION;
Select apples 
  from fruit 
 where apples = 'golden'
   FOR UPDATE;
...
COMMIT;

The SELECT ... FOR UPDATE statement first locks the row, then returns it to you.

Anyone else trying to do the same thing to the same row at the same time will wait for your COMMIT or ROLLBACK and then they will receive the row... with any changes you made.

If you have no intention of changing the row, you just want to make sure nobody else does can, then you can SELECT ... LOCK IN SHARE MODE.

Note that, either way, it's technically not the "row," it's actually the index record that is being locked, but in InnoDB, "everything is an index" (even a table with no indexes at all does in fact still have one index, siently created by the system) so the net result is the same.

Efficient exclusive lock with ZooKeeper for infrequent operations

What about this?

Each event processor must:

• obtain a read lock via InterProcessReadWriteLock.
• Use NodeCache to watch a "signal" node and listen for changes on this node. When the node exists it means it is daily processing time. When it doesn't exist event processing can proceed.
• When the NodeCache shows that the signal node has been created, the event processors must release their locks and wait for the signal node to be deleted (again by listening with the NodeCache).
• When the NodeCache shows that the signal node has been deleted, the event processors obtain the read locks again and continue processing events.

Once this is set there's no additional ZooKeeper activity while it's all running.

When the once a day operation is ready to run:

• It creates the signal node (as an ephemeral node)
• Acquires the write lock on the same path as the event processors use for their read locks
• Does its periodic processing
• Releases the write lock
• Deletes the signal node

There's a huge caveat with this, however, and that's what can happen with JVM pauses. Please also read this Tech Note for important edge cases.

In a database with shared/exclusive locks, when an UPDATE statement is executed at the beginning of the transaction, how do the locks work?

When PostgreSQL scans the table for rows that meet the WHERE condition of the UPDATE, it doesn't lock rows at all. Only when a candidate row is found, it locks it in EXCLUSIVE mode. If the lock cannot be acquired right away, and the UPDATE is blocked, PostgreSQL waits until it can get the lock and then reads the row again. The behavior depends on the isolation level:

• with READ COMMITTED, if the latest row version still satisfies the WHERE condition, it is locked

• with REPEATABLE READ or SERIALIZABLE, if the row has changed, you get a serialization error and have to retry the transaction

All this is well documented.

How to get an exclusive lock on a file using batch file?

I was skeptical about this initially, but it turns out it can be done by using file redirection. Consider this example:

@echo off

if '%1' == '-lock' (
    shift
    goto :main
)
call %0 -lock > lockfile.txt
goto :eof

:main
echo %DATE% %TIME% - start
TREE C:\
echo %DATE% %TIME% - finish
goto :eof

Whilst the above batch is running, it is not possible to delete lockfile.txt.

Essentially, the batch checks for a '-lock' parameter. If it's not present, it re-executes itself with the -lock parameter and re-directs it's own output to lockfile.txt

It's also possible to create locks for 'critical' sections within a batch e.g.

@echo off
echo %DATE% %TIME% - started

(
    echo Starting TREE
    tree c:\
    echo TREE finished
    ) > lock2.lock

echo %DATE% %TIME% - finished

Sources:

How do you have shared log files under Windows?

http://www.dostips.com/forum/viewtopic.php?p=12454

Exclusive lock on file is downgraded, when another FileChannnel for the locked file is closed

I could not reproduce on Windows.

From the Javadoc of FileLock:

File locks are held on behalf of the entire Java virtual machine.

This file-locking API is intended to map directly to the native locking facility of the underlying operating system. Thus the locks held on a file should be visible to all programs that have access to the file, regardless of the language in which those programs are written.

On some systems, closing a channel releases all locks held by the Java virtual machine on the underlying file regardless of whether the locks were acquired via that channel or via another channel open on the same file. It is strongly recommended that, within a program, a unique channel be used to acquire all locks on any given file.

---

Related Topics