Lock tables in Eloquent / DB

I suppose you have an unique index in table so if the same bid is to be saved (really a rare case) you'll get a PDO error that you can manage.

@bestmomo Interesting. Haven't thought of that. This actually might be a more elegant solution for this particular problem.

@creativeorange db transactions are made for this :

DB::transaction(function ($bid_value) {

    // Get $max_bid

    // If $bid_value > $max_bid then save the bid

    // If $bid_value <= $max_value then throw an exception

});

Db transactions are atomic operations at db level.

Hi @creativeorange maybe you are looking for something as following for Locking capabilities:

https://github.com/laravel/framework/blob/5.0/src/Illuminate/Database/Query/Builder.php#L155-L159

https://github.com/laravel/framework/blob/5.0/src/Illuminate/Database/Query/Builder.php#L1266-L1296

@JarekTkaczyk could you explain with some sample within your book? Thanks for advanced.

Hope it helps you.

@codeatbusiness "Locking is required only when developing scripts that first read a value from a database and later write that value to the database."

@pmall I dont see how transaction avoid this issue.

You can do pessimistic lock in laravel. Chec this:
http://laravel.com/docs/5.0/queries#pessimistic-locking

Yes @bestmomo I know that, thanks for your explanation.

Thanks for all the replies, guys! Although @bestmomo his first answer solved my particular problem, it didn't actually answer my initial question: How to lock a table for the lifetime of a request?

@codeatbusiness thanks for the links to the source code. It's good to know there are possibilities in Laravel's codebase to solve my problem. However, I'm not quite sure how to implement this.

@SachinAgarwal I did read this in the documentation. But I think it's not that clear how to use pessimistic locking. If I use sharedLock or lockForUpdate, what does it do? Do I have to unlock lockForUpdate after all my queries are finished? And what does sharedLock mean?

@pmall thanks for the DB::transaction method, I think this might be a possible way to go about it. But after @bestmomo's reply "I dont see how transaction avoid this issue.", I'm not sure if I could use transactions. @bestmomo : could you explain why this solution doesn't avoid this issue?

A long story short: How do I lock a table for writing until the first user completed his bid? Or: How do I prevent a second user for executing his read and write request to the database before the first user's request is finished?

Why not write a stored proc. On update check the current bid against the saved bid. Reject the bid if it does not meet your criteria. Blocking resources by table locking is not good a practice. Also depending on the DB engine it may be hard to consistently accomplish. How about a Mutex?

http://stackoverflow.com/questions/10561535/synchronized-stored-procedure-execution-in-mysql

@creativeorange Sorry, My bad, you cannot lock table for writing with pessimistic lock. You can lock only for reading and updating.
@bestmomo gave you the answer. I would suggest the same as you will have a time gap. its very rare case when 2 transactions take place at same time. :)

@creativeorange I just said I was not sure about transaction. I think it's a good way but must include some locking read option to work as required.

Okay, to recapulate: I have three (maybe more) options.

1. The solution @bestmomo gave me (and the one I will use). In my schema: $table->unique(['auction_id', 'amount']);
2. Lock the table, execute the READ and WRITE queries and unlock the table. After doing some more research very many developers, among @jimmck, advice against this solution. Especially because other parts of my application use this table as well.
3. Writing a stored procedure. This might be a good solution as well, but I think a stored procedure would be a bit overkill.

Thanks @bestmomo, @SachinAgarwal, @jimmck and @codeatbusiness for all the replies and the effort you guys put in answering my question.

I humbly believe you will find the unique index solution to make your very slow as the number of bids per item increase. The cost of scanning the index on each insert plus the handing of the error can be prohibitive. Also many developers are taught not to rely on Exceptions and system error handling as a means of process control. See Josh Bloch author of Effective Java. I know its PHP, but good design is language agnostic. Not to trumpet my idea over others, but database Stored procedures are bread & butter tasks in day to day DB coding. Databases today provide ample tools to solve concurrency issues. Which begs the question what database aren you using? Jim

Thanks @jimmck! I really appreciate your reply. You convinced me to take stored procedures under consideration and I will try this out next monday. I will post my code here.

@bestmomo this is the purpose of transactions. I cant see what's wrong.