CODEWITHSUNDEEP
phpmysqllaravellocking
Joe Ijam
Joe Ijam

Reputation: 2179

Laravel DB::beginTransaction causing other transaction locked

In classic php I have 2 process files

1) process1.php => To lock row for .table InnodB.

2) process2.php => To read another row from the same table

I have played with START TRANSACTION AND COMMIT in PHP/MySQL and it is working properly as what I want. What I want is the process2.php can select another record eventhough the process1.php is still running and locking some row.

Unfortunately it does not work in Laravel where my other process was locked until the first process completed eventhough the second process is selecting another table.

Please help me to figure out this issue.

DB::beginTransaction();
$rs = DB::table('test_iap')->where('content', 'waiting')->lockForUpdate()->get();

$sql = "update test_iap set content='Updated' where id=1";
DB::connection('mysql_pencil_main')->update(DB::raw($sql));
sleep(10);# To allow testing at different file process

DB::commit();

Upvotes: 3

Views: 5152

Answers (2)

Ahmad Zahabi
Ahmad Zahabi

Reputation: 1268

The problem is from sleep. I tried the same script without any locks or transactions and the other apis still waiting until the 10 seconds end. Please take a look at this question: How to sleep PHP(Laravel 5.2) in background.

If you want to let the sleep, you can run another program on another port like:

php artisan serve --port 7000

and send the second request on that that port..

Upvotes: 0

Shailesh Ladumor
Shailesh Ladumor

Reputation: 7242

use sharedLock() instead-of lockForUpdate(). A shared lock prevents the selected rows from being modified until your transaction commits. see more description here

DB::beginTransaction();
$rs = DB::table('test_iap')->where('content', 'waiting')->sharedLock()->get();

$sql = "update test_iap set content='Updated' where id=1";
DB::connection('mysql_pencil_main')->update(DB::raw($sql));
sleep(10);# To allow testing at different file process

DB::commit();

Upvotes: 1

Related Questions